Migrate Data from Greenplum to Snowflake

Integrating Spark with Snowflake
March 8, 2018
How to Load XML into Snowflake with SQL
March 23, 2018

Migrate Data from Greenplum to Snowflake

How To: Migrate Data from Greenplum to Snowflake

Greenplum Database can dump table data by creating a "writable external table" (actually a delimited text file) using the CREATE WRITABLE EXTERNAL TABLE command and inserting data into this "table." The text file can then be staged and loaded into a Snowflake table.

The Greenplum SQL statements can be executed using a PL/pgSQL command-line client or a pgAdmin III interface. See the Greenplum documentation for full Greenplum SQL command syntax and options.

Example

Create a writable external table named greenplum_table_dump that points to a file named greenplum_table_dump.csv located in the directory specified when the gpfdist program is started:
drop external table if exists tablename_dump;
create writable external table greenplum_table_dump (like
greenplum_table) location ('gpfdist://mdw:8081
/greenplum_table_dump.csv') format 'csv' (delimiter ',');

Load data into the greenplum_table_dump table. This command writes the data to the greenplum_table_dump.csv file in parallel:
insert into greenplum_table_dump (select * from greenplum_table);

Stage the greenplum_table_dump.csv file from the directory where it exists to the current Snowflake user's staging location using the PUT command in SnowSQL:
put file:///server/directory/greenplum_table_dump.csv
@~/greenplum_stage/ parallel = 8;

Load the data from the user's staging location into a Snowflake table named snowflake_table: copy into snowflake_table
from @~/greenplum_stage/
file_format = (type = csv)
on_error = 'continue'
validation_mode = 'return_all_errors'
purge = false;

Relevant Documentation

https://docs.snowflake.net/manuals/user-guide/data-load.html
https://docs.snowflake.net/manuals/sql-reference/sql/put.html
https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html

Find out more about all the benefits Snowflake has to offer for you and your business. Enter your name and email below and we'll be in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *