HDFS Readable and Writable External Table Examples (Deprecated)

Note: The gphdfs external table protocol is deprecated and will be removed in the next major release of Greenplum Database.

The following code defines a readable external table for an HDFS file named filename.txt on port 8081.

  1. =# CREATE EXTERNAL TABLE ext_expenses ( 
  2.         name text,
  3.         date date,
  4.         amount float4,
  5.         category text,
  6.         desc1 text )
  7.    LOCATION ('gphdfs://hdfshost-1:8081/data/filename.txt') 
  8.    FORMAT 'TEXT' (DELIMITER ',');

The following code defines a set of readable external tables that have a custom format located in the same HDFS directory on port 8081.

  1. =# CREATE EXTERNAL TABLE ext_expenses (
  2.         name text,
  3.         date date,
  4.         amount float4,
  5.         category text,
  6.         desc1 text )
  7.    LOCATION ('gphdfs://hdfshost-1:8081/data/custdat*.dat') 
  8.    FORMAT 'custom' (formatter='gphdfs_import');

The following code defines an HDFS directory for a writable external table on port 8081 with all compression options specified.

  1. =# CREATE WRITABLE EXTERNAL TABLE ext_expenses (
  2.         name text,
  3.         date date,
  4.         amount float4,
  5.         category text,
  6.         desc1 text )
  7.    LOCATION ('gphdfs://hdfshost-1:8081/data/?compress=true&compression_type=RECORD
  8.    &codec=org.apache.hadoop.io.compress.DefaultCodec') 
  9.    FORMAT 'custom' (formatter='gphdfs_export');

Because the previous code uses the default compression options for compression_type and codec, the following command is equivalent.

  1. =# CREATE WRITABLE EXTERNAL TABLE ext_expenses (
  2.         name text,
  3.         date date,
  4.         amount float4,
  5.         category text,
  6.         desc1 text )
  7.    LOCATION    ('gphdfs://hdfshost-1:8081/data?compress=true')
  8.    FORMAT 'custom' (formatter='gphdfs_export');

Parent topic: Accessing HDFS Data with gphdfs (Deprecated)