GPDB Data Unloading Options
Post date: Nov 21, 2014 2:24:10 PM
1. COPY TO: Copy to command is quick and easy postgresql way to unload data. For example
COPY ( SELECT * FROM sales) TO ‘/tmp/sales.dat’;
Advantage:
Quick and easy
Recommended for small unloads. This method is not recommended for bulk unloads
Can filter output using SELECT
Unload to file or standard output
Is not parallel uses a single process on the master
Source file must be accessible by the master
2. External Writeable: The advantage of Greenplum writable external table are,
High speed bulk unloads
File based or web based
Uses parallel unloading using gpfdist protocol
Local file, named pipes, applications
Allows only INSERT operations
Unload Using a File Based Writable External Table
=# CREATE WRITABLE EXTERNAL TABLE unload_expenses
( LIKE expenses )
LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',
'gpfdist://etlhost-2:8081/expenses2.out')
FORMAT 'TEXT' (DELIMITER ',')
DISTRIBUTED BY (exp_id);
psql –c ‘INSERT INTO unload_expenses SELECT * FROM expenses;’
Unload Using a Writable External Web Table
Command based
– Use EXECUTE to specify a shell command, script, or application
=# CREATE WRITABLE EXTERNAL WEB TABLE output (output text)
EXECUTE 'export PATH=$PATH:/home/gpadmin/programs; myprogram.sh'
FORMAT 'TEXT'
DISTRIBUTED RANDOMLY;
3. gpcrondump:
backup data using Greenplum crondump which dmps database into SQL script files then restore. For example,
gpcrondump -x sachi