Creating External Tables - Examples

    Note: When using IPv6, always enclose the numeric IP addresses in square brackets.

    Start gpfdist before you create external tables with the gpfdist protocol. The following code starts the gpfdist file server program in the background on port 8081 serving files from directory /var/data/staging. The logs are saved in /home/gpadmin/log.

    Creates a readable external table, ext_expenses, using the gpfdist protocol. The files are formatted with a pipe (|) as the column delimiter.

    1. =# CREATE EXTERNAL TABLE ext_expenses
    2.         ( name text, date date, amount float4, category text, desc1 text )
    3.     LOCATION ('gpfdist://etlhost-1:8081/*')

    Example 2 - Multiple gpfdist instances

    1. =# CREATE EXTERNAL TABLE ext_expenses
    3.     LOCATION ('gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8081/*.txt')
    4.     FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;

    Creates a readable external table, ext_expenses, from all files with the txt extension using the gpfdists protocol. The column delimiter is a pipe ( | ) and NULL is a space (’ ’). For information about the location of security certificates, see .

    1. Run gpfdist with the --ssl option.

    2. Run the following command.

    Example 4 - Single gpfdist instance with error logging

    Uses the gpfdist protocol to create a readable external table, ext_expenses, from all files with the txt extension. The column delimiter is a pipe ( | ) and NULL (’ ’) is a space.

    1. =# CREATE EXTERNAL TABLE ext_expenses
    2.          ( name text, date date, amount float4, category text, desc1 text )
    3.     LOCATION ('gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8082/*.txt')
    4.     FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
    5.     LOG ERRORS INTO expenses_errs SEGMENT REJECT LIMIT 5;

    To create the readable ext_expenses table from CSV-formatted text files:

    1.          ( name text, date date, amount float4, category text, desc1 text )
    2.     LOCATION ('gpfdist://etlhost-1:8081/*.txt', 'gpfdist://etlhost-2:8082/*.txt')
    3.     FORMAT 'CSV' ( DELIMITER ',' )
    4.     LOG ERRORS INTO expenses_errs SEGMENT REJECT LIMIT 5;

    Creates a readable web external table that executes a script once on five virtual segments:

    Example 6 - Writable External Table with gpfdist

    Creates a writable external table, sales_out, that uses gpfdist to write output data to the file sales.out. The column delimiter is a pipe ( | ) and NULL is a space (’ ’). The file will be created in the directory specified when you started the gpfdist file server.

    1. =# CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales)
    2.     LOCATION ('gpfdist://etl1:8081/sales.out')
    3.     FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
    4.     DISTRIBUTED BY (txn_id);
    1. =# CREATE WRITABLE EXTERNAL WEB TABLE campaign_out
    2.         (LIKE campaign)

    Example 8 - Readable and Writable External Tables with XML Transformations

    HAWQ can read and write XML data to and from external tables with gpfdist. For information about setting up an XML transform, see .