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.
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:
@~/greenplum_stage/ parallel = 8;
Load the data from the user's staging location into a Snowflake table named snowflake_table:
copy into snowflake_table
file_format = (type = csv)
on_error = 'continue'
validation_mode = 'return_all_errors'
purge = false;
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!