How to get Data from a Mysql Database to Snowflake

Question

is there any clever way to get my data from a mysql datatbase into snowflake? I found two possible ways so far:

Option 1: Put a Snowpipe ontop of the mysql database and the pipeline converts the data automatically. Option 2: I convert tables manually into csv and store them locally and load them via staging into snowflake.

For me it seems strange to convert every table into a csv first. Can I not just push a sql dump file to snowflake? Can I also schedule some reload task in snowflake, so either option1 or 2 get triggered automatically?

Best NicBeC24

Answer 1

I found some very good information regarding MySQL-Snowflake-migrations here: https://hevodata.com/blog/mysql-to-snowflake-data-migration-steps/

The main steps from the webpage above are:

1. Exporting data from MySQL
2. Taking care about data types
3. Stage your files into Snowflake (Internal/External stage)
4. Copy the staged files into the table

If the SQL-dump is just a ".sql-file" in ANSI, yes, of course, you can copy&paste it to your Snowflake worksheet and execute it there.

Regarding scheduling: Yes, in Snowflake there is a functionality called Tasks: https://docs.snowflake.com/en/user-guide/tasks-intro.html You can use them to schedule your COPY INTO-command.