Reading a Multi-Line Text File into a Single Table Row
A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 5.x documentation.
The PXF object store connectors support reading a multi-line text file as a single table row. This section describes how to use PXF to read multi-line text and JSON data files in an object store, including how to create an external table that references multiple files in the store.
PXF supports reading only text and JSON files in this manner.
Note: Accessing multi-line files from an object store is very similar to accessing multi-line files in HDFS. This topic identifies the object store-specific information required to read these files. Refer to the PXF HDFS documentation for more information.
Ensure that you have met the PXF Object Store Prerequisites before you attempt to read data from multiple files residing in an object store.
<objstore>:hdfs:multi profile to read multiple files in an object store each into a single table row. PXF supports the following
<objstore> profile prefixes:
|Object Store||Profile Prefix|
|Azure Blob Storage||wasbs|
|Azure Data Lake||adl|
|Google Cloud Storage||gs|
The following syntax creates a Greenplum Database readable external table that references one or more text files in an object store:
CREATE EXTERNAL TABLE <table_name> ( <column_name> text|json | LIKE <other_table> ) LOCATION ('pxf://<path-to-files>?PROFILE=<objstore>:text:multi&SERVER=<server_name>&FILE_AS_ROW=true') FORMAT 'CSV');
The specific keywords and values used in the CREATE EXTERNAL TABLE command are described in the table below.
|<path‑to‑files>||The absolute path to the directory or files in the object store.|
|SERVER=<server_name>||The named server configuration that PXF uses to access the data.|
|FILE_AS_ROW=true||The required option that instructs PXF to read each file into a single table row.|
If you are accessing an S3 object store, you can provide S3 credentials via custom options in the
CREATE EXTERNAL TABLE command as described in Overriding the S3 Server Configuration with DDL.
Refer to Example: Reading an HDFS Text File into a Single Table Row in the PXF HDFS documentation for an example. Modifications that you must make to run the example with an object store include:
Copying the file to the object store instead of HDFS. For example, to copy the file to S3:
$ aws s3 cp /tmp/file1.txt s3://BUCKET/pxf_examples/tdir $ aws s3 cp /tmp/file2.txt s3://BUCKET/pxf_examples/tdir $ aws s3 cp /tmp/file3.txt s3://BUCKET/pxf_examples/tdir
CREATE EXTERNAL TABLEsyntax and
LOCATIONkeywords and settings described above. For example, if your server name is
CREATE EXTERNAL TABLE pxf_readfileasrow_s3( c1 text ) LOCATION('pxf://BUCKET/pxf_examples/tdir?PROFILE=s3:text:multi&SERVER=s3srvcfg&FILE_AS_ROW=true') FORMAT 'CSV'