PostgreSQL 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 PostgreSQL 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 PostgreSQL:

• Hostname
• Port
• Database name
• Username
• Password
• Schema and 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": "my-source",
 "source-type": "postgresql",
 "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>",
 "target-name": "<any name you like>",
 "target-type": "snowflake",
 "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>",
 "source-schema": "<source schema 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>"
}

Example Curl commands

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

curl -d '{"source-name": "my-source", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "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": "my-source", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "target-name": "<any name you like>", "target-type": "snowflake", "target-connection-string": "<snowflake username>:<snowflake password>@<account identifier>.snowflakecomputing.com/<db name>", "source-schema":"<source schema 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": "517b67f4-8c8d-4515-9ea5-b18c5dd3b400",
               "created-at": "2023-11-20T14:53:48.204493847Z",
               "status": "queued",
               "keep-files": false,
               "tmp-dir": "/tmp/sqlpipe/517b67f4-8c8d-4515-9ea5-b18c5dd3b400",
               "pipe-file-dir": "/tmp/sqlpipe/517b67f4-8c8d-4515-9ea5-b18c5dd3b400/pipe-files",
               "final-csv-dir": "/tmp/sqlpipe/517b67f4-8c8d-4515-9ea5-b18c5dd3b400/final-csv",
               "source-connection-info": {
                       "instance-name": "my-source",
                       "type": "postgresql",
                       "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-schema": "public",
               "source-table": "my_table",
               "target-schema": "public",
               "target-name": "postgresql_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.