CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-2:8081/*' ) FORMAT 'TEXT' (DELIMITER '|');
Example 2―Multiple Greenplum file server (gpfdist) instances Creates a readable external table named ext_expenses using the gpfdist protocol from all files with the txt extension. The files are formatted with a pipe ( | ) as the column delimiter and an empty space as null.
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8082/*.txt' ) FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
Example 3―Multiple secure Greenplum file server (gpfdists) instances Creates a readable external table named ext_expenses using the gpfdists protocol from all files with the txt extension. The files are formatted with a pipe ( | ) as the column delimiter and an empty space as null.
First, run gpfdist with the --ssl option. Then, execute the following command.
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gpfdists://etlhost-1:8081/*.txt', 'gpfdists://etlhost-2:8082/*.txt' ) FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
The external table is accessed in single row error isolation mode. An error table (err_customer) is specified. Any data formatting errors that are found in the input data will be discarded to err_customer, along with a description of the error. err_customer can later be queried in order to see the nature of errors and reload the rejected data after fixing the issues. If the count of badly formatted data rows on any specific segment is greater than five (specified as the SEGMENT REJECT LIMIT value), the entire external table operation will be aborted and no rows will be processed.
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8082/*.txt' ) FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5;
Create the same readable external table definition as above, but with CSV formatted files:
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8082/*.txt' ) FORMAT 'CSV' ( DELIMITER ',' ) LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5;
Example 5―TEXT Format on a Hadoop Distributed File Server (HDFS) Creates a readable external table named ext_expenses using the gphdfs protocol. The files are formatted with a pipe ( | ) as the column delimiter.
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ( 'gphdfs://hdfshost-1:8081/data/filename.txt' ) FORMAT 'TEXT' (DELIMITER '|');
Note: Only one data path is permitted with gphdfs. For examples of read