5

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

NicBeC24
  • 47
  • 1
  • 4

1 Answers1

4

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.

Marcel
  • 2,454
  • 1
  • 5
  • 13
  • Hi, thanks for the url. This is pretty much my second option I mentioned. It seems very unhandy to convert a whole database into csv. You jus said, i can surely load a .sql file in ansi into snowflake. I didn't find any information about that, just with copy in the worksheet? The Copy statement needs a format type, there is no sql as far as i see. How do you do that? – NicBeC24 Oct 20 '20 at 07:16
  • COPY INTO-command is NOT the same as Copy&Paste the code into your worksheet. Regarding your sql-file: You can copy&paste the sql commands into your worksheet, yes. Another option is using SnowSQL, the command line interface: https://docs.snowflake.com/en/user-guide/snowsql-use.html Regarding COPY INTO: You can use this command to load data from a staged file into a Snowflake table (not executing your sql-file) – Marcel Oct 20 '20 at 07:25
  • Lets say I got a dump sql file locally on my computer, is there a way to load it into snowflake, without establishing a data base connection? I am just in the trial period and want to evaluate if snowflake will fit out puposes. – NicBeC24 Oct 20 '20 at 11:18