community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

MySQL Server Big Database - Millions of Rows - Write in using Alteryx

Atom

I need some suggestions or guidance regarding loading very large data sets into MySQL Server.  I have been using the Alteryx Input Data tool to download all data from MySQL to a temporary file on my local machine (in yxdb format) and then after applying arithmetic operations using formulae node, I am writing them back to MySQL using Alteryx output data node but because my database is very large around 50GB so it's taking way more time to upload (more than 12 hours). 

 

My issue is if there is any faster way to load million of records (around 50GB) into one table in MySQL. Any faster process or approach?  Staging?  Any help or suggestions.

 

Below is what i have done so far:

 

.1)Enabled the performance profiling in the workflow configuration > Runtime tab

 

.2) Tried to find Bulk Loader driver as data source for MySQL using Manage In-DB connection but I don't see such option. It is only visible when I select SQL server as the data source.

 

Please let me know if there is any faster way to do this?

 

 

Alteryx Certified Partner

Looking at the Alteryx documentation for MySQL at the link below, I don't see the "Bulk write support is available for standard and in-database workflows." message that is present in some other sources (like SQL Server). The support for bulk loading may not be there, but this might be a good thing to run by support@alteryx.com just the be sure.

 

https://help.alteryx.com/2018.3/DataSources/MySQL.htm

Alteryx
Alteryx

Hi @FAZLM2,

 

Unfortunately we do not currently have a MySQL bulk loader, but I wanted to ask what kind of arithmetic you are performing on the data? Reason being, if you can reproduce those operations with the In-DB tools themselves, you can completely avoid pulling the data down from the database. The In-DB tools only pull data from the database on a Stream Out or a Browse tool, otherwise all the In-DB tools do is build up a SQL statement that then sent to the database to do the work. So if the operations/functions you want to perform are supported by your database, you can use the In-DB tools with the standard MySQL connection and never have to pull the data locally.

 

For example: use a Connect In-DB tool to select your table, a Formula In-DB tool to do your arithmetic, and then a Write Data In-DB tool to create a new table. This workflow will never pull the data locally, but build a query to create that new table from the query.

 

Hopefully that helps and if you would star this post for a MySQL bulk loader on the ideas section, it really helps us prioritize: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/MySQL-bulk-loader/idi-p/6711

 

Thanks,


Hannah Keller

Labels