Example 6―Multiple file protocols in CSV format with header rows Creates a readable external table named ext_expenses using the file protocol.The wildcard specifications are not the same for all the files. The files are formatted in CSV format and have a header row.
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'file://filehost:5432/data/international/*', 'file://filehost:5432/data/regional/*' 'file://filehost:5432/data/supplement/*.csv' ) FORMAT 'CSV' (HEADER);
Example 7―Readable Web External Table with Script Create a readable web external table that executes a script once per segment host:
CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST FORMAT 'TEXT' (DELIMITER '|');
Example 8―Writable External Table that Writes to a File Create a writable external table named sales_out that uses gpfdist to write output data to a file named sales.out. The files are formatted with a pipe (|) as the column delimiter and an empty space as null.
CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales) LOCATION ('gpfdist://etl1:8081/sales.out') FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') DISTRIBUTED BY (txn_id);
Example 9―Writable External Web Table with Script Create a writable external web table that pipes output data received by the segments to an executable script named to_adreport_etl.sh:
CREATE WRITABLE EXTERNAL WEB TABLE campaign_out (LIKE campaign) EXECUTE '/var/unload_scripts/to_adreport_etl.sh' FORMAT 'TEXT' (DELIMITER '|');
Use the writable external table defined above to unload selected data:
INSERT INTO campaign_out SELECT * FROM campaign WHERE customer_id=123;
Example 10―Readable and Writable External Tables with XML Transformations Greenplum Database now can read and write XML data to and from external tables with gpfdist. For information on setting up an XML transform, see “Transforming XML Data” on page 178. The following code reads XML data into a table.
CREATE READABLE EXTERNAL TABLE prices_readable (LIKE prices) LOCATION ('gpfdist://127.0.0.1:8080/data/prices.xml#transform=prices_input') FORMAT 'text' (delimiter '|') LOG ERRORS INTO prices_errortable SEGMENT REJECT LIMIT 10;
The following code creates a writable external table that transforms the data in the Greenplum Database to XML.
CREATE WRITABLE EXTERNAL TABLE prices_readable (LIKE prices) LOCATION ('gpfdist://127.0.0.1:8080/data/prices.xml#transform=prices_input') FORMAT 'text' (delimiter '|');