MySQL to Snowflake Open-Source Transfer Tool

SQLpipe is a free, open-source tool that can transfer the result of a query, or an entire table, from MySQL to Snowflake.

If SQLpipe helps you, please consider starring our GitHub repository! It really helps us.

Step 1 – Install / run SQLpipe

The easiest way to run SQLpipe is with Docker:

docker run --publish 9000:9000 --name sqlpipe sqlpipe/sqlpipe

There are multiple ways to acquire the program, however. You can view all of those ways on the SQLpipe downloads page.

Step 2 – Gather connection information

Gather the following information for MySQL:

• Hostname
• Port
• Database name
• Username
• Password
• Table name (for full table transfer) or write a query

Gather the following information for Snowflake:

• Database name
• Username
• Password
• Schema name
• Account ID

Step 3 – Submit a transfer command

Transferring data is accomplished by sending a POST request to SQLpipe's /transfer/create route. You can send this kind of command any way you want - from an Airflow DAG, a Cron command, or just Curl on your command line. Here is an outline of the kind of payload you must submit:

{
 "source-name": "<any name you like>",
 "source-type": "mysql",
 "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific",
 "target-name": "<any name you like>",
 "target-type": "snowflake",
 "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>",
 "source-table": "<source table name>",
 "target-table": "<target table name>",
 "drop-target-table-if-exists": true,
 "create-target-table-if-not-exists": true,
 "create-target-schema-if-not-exists": true,
 "target-schema": "<target schema name>"
}

Note on MySQL sources: you must supply a "parseTime" and url-encoded "loc" query parameter in the source connection string.

Example Curl commands

To move the result of a query, you can use the following command as a starting command:

curl -d '{"source-name": "<any name you like>", "source-type": "mysql", "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific", "target-name": "<any name you like>", "target-type": "snowflake", "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>", "query": "<query>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>"}' localhost:9000/transfers/create

To move an entire table (and gain slightly better automatic type inference), you can use the following command as a starting template:

curl -d '{"source-name": "<any name you like>", "source-type": "mysql", "source-connection-string": "<username>:<password@tcp(<hostname>:<port>)/<db name>?parseTime=true&loc=US%2FPacific", "target-name": "<any name you like>", "target-type": "snowflake", "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>", "source-table": "<source table name>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true, "create-target-schema-if-not-exists": true, "target-schema": "<target schema name>"}' localhost:9000/transfers/create

If the command is successful, the program will tell you that a transfer was queued:

{
       "query-transfer": {
               "id": "8c1ed553-4620-4c6c-9d88-0e639dc2a30c",
               "created-at": "2023-11-21T11:16:19.182338913Z",
               "status": "queued",
               "keep-files": false,
               "tmp-dir": "/tmp/sqlpipe/8c1ed553-4620-4c6c-9d88-0e639dc2a30c",
               "pipe-file-dir": "/tmp/sqlpipe/8c1ed553-4620-4c6c-9d88-0e639dc2a30c/pipe-files",
               "final-csv-dir": "/tmp/sqlpipe/8c1ed553-4620-4c6c-9d88-0e639dc2a30c/final-csv",
               "source-connection-info": {
                       "instance-name": "my-source",
                       "type": "mysql",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "target-connection-info": {
                       "instance-name": "my-target",
                       "type": "snowflake",
                       "hostname": "",
                       "database": "",
                       "username": ""
               },
               "drop-target-table-if-exists": true,
               "create-target-schema-if-not-exists": true,
               "create-target-table-if-not-exists": true,
               "source-table": "my_table",
               "target-schema": "public",
               "target-name": "mysql_my_table",
               "delimiter": "{dlm}",
               "newline": "{nwln}",
               "null": "{nll}"
       }
}

If you make a mistake submitting the data, SQLpipe will do its best to tell you what's wrong. For example, if you forgot to specify a target table, it would return:

{
       "error": {
               "target-table": "must be provided"
       }
}

Conclusion

Yes, it really is that easy!

If you’d like more in-depth documentation, or want to let us know about a bug, we please make an issue on SQLpipe’s Github page.