Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Use PXF object store connector to read multiline text from S3 into a single table row in Greengage DB

Anton Monakov

The PXF object store connector lets you read one or more single- and multiline text files in an object store, loading each file into a single table row and column. This may be useful for reading multiple files into the same Greengage DB external table, for example when individual JSON files each contain a separate record. Only text and JSON files can be loaded in this manner, including files with embedded line feeds.

This topic describes how to configure and use the PXF object store connector for reading multiline text data in an object store by using external tables and provides practical examples.

Create an external table using the PXF protocol

To create a Greengage DB external table to read multiline text data from an object store, use the following general syntax:

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> TEXT|JSON | LIKE <other_table> )
    LOCATION ('pxf://<path_to_data>?PROFILE=<objstore>:text:multi&FILE_AS_ROW=true[&<custom_option>=<value>[...]]')
    FORMAT 'CSV';
Keyword Value

<table_name>

The name of the table to create

<column_name> TEXT|JSON

The column to read the data file into. The external table must define exactly one column, with its type set to TEXT or JSON depending on the source file type. The entire file contents is loaded into this column.

If you don’t need to preserve the original formatting or key order, you can set the column type to JSONB instead of JSON. When using JSONB, the initial data scan is slightly slower than in JSON, but subsequent operations on JSONB are significantly faster.

Learn more in About JSON data

LIKE <other_table>

Specifies a table from which the new external table automatically copies all column names, data types, and distribution policy

<path_to_data>

The path to the directory or file in an object store. When the <server_name> configuration includes a pxf.fs.basePath property setting, the <path_to_data> value is considered to be relative to the base path specified. Otherwise, the path is considered absolute. The value must not include the dollar sign ($) character.

When reading multiple files, ensure that all of them are of the same type (text or JSON). When reading multiple JSON files, ensure that each file is a complete record and contains the same record type

PROFILE=<objstore>:text:multi

The profile is specified as the <objstore>:text:multi pair, where <objstore> is the object store prefix.

The following <objstore> prefixes and the corresponding object stores are supported:

  • wasbs — Azure Blob Storage

  • adl — Azure Data Lake

  • gs — Google Cloud Storage

  • s3 — Amazon S3, MinIO, and other S3-compatible object stores

FILE_AS_ROW=true

The required option that instructs PXF to read each file into a single table row. When this option is specified, no additional custom or formatting options are supported

FORMAT

To read multiline text data in an object store, CSV must be specified

<custom_option>

One of the custom options provided in the LOCATION string as described below

SERVER=<server_name>

The named server configuration that PXF uses to access the data. If the option is omitted, the default server configuration is used

IGNORE_MISSING_PATH

The action to take when <path_to_data> is missing or invalid. If set to false (default), an error is returned. If set to true, PXF ignores missing path errors and returns an empty fragment. Only applies to readable external tables and is ignored in the case of writable external tables

Examples

These examples demonstrate how to configure and use the PXF object store connector for reading multiline text data from an object store by using external tables.

Prerequisites

To try out the practical examples, connect to the Greengage DB master host as gpadmin using psql as described in Connect to Greengage DB via psql. Then create the customers test database and connect to it:

DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers

To be able to create an external table using the PXF protocol, enable the PXF extension in the database as described in Register PXF in a database in PXF documentation:

CREATE EXTENSION pxf;

Configure the PXF S3 connector

To have PXF connect to an object store, you need to create the corresponding server configuration and then synchronize it to the Greengage DB cluster:

  1. On the Greengage DB master host, log in as gpadmin.

  2. Go to the $PXF_BASE/servers directory and create an S3 server configuration directory named s3. Depending on your object storage, copy the required server configuration file from $PXF_HOME/templates to $PXF_BASE/servers/s3. The example uses the configuration file based on the minio-site.xml template.

    $ mkdir $PXF_BASE/servers/s3
$ cd $PXF_BASE/servers/s3
$ cp $PXF_HOME/templates/minio-site.xml .

    In the configuration file, provide the relevant object store connection details:

    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>fs.s3a.endpoint</name>
        <value>storage.example.com</value>
    </property>
    <property>
        <name>fs.s3a.access.key</name>
        <value>${ACCESS_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.secret.key</name>
        <value>${SECRET_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.fast.upload</name>
        <value>true</value>
    </property>
    <property>
        <name>fs.s3a.path.style.access</name>
        <value>true</value>
    </property>
</configuration>
    NOTE

    Notice that the credentials used to authenticate with the storage service are provided via the ACCESS_KEY and SECRET_KEY environment variables.

    You can configure them as follows:

    $ export ACCESS_KEY=<access_key>
$ export SECRET_KEY=<secret_key>

    While it’s possible to set the credentials directly in the configuration file, it is recommended to use environment variables for security reasons.

  3. Synchronize the server configuration to the Greengage DB cluster hosts:

    $ pxf cluster sync

Read multiline text files

  1. In the text folder of the customers bucket on the S3 host, create three plain text data files named customers_1.txt, customers_2.txt, and customers_3.txt having the following content:

    1,John,Doe,john.doe@example.com,123 Elm Street
    2,Jane,Smith,jane.smith@example.com,456 Oak Street
3,Bob,Brown,bob.brown@example.com,789 Pine Street
4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
    5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue
Unit 2B
Second Floor"
6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road
Suite 300
Business Park"
7,Eve,Wilson,eve.wilson@example.com,"PO Box 555
Anytown, USA
Zip Code: 12345"

  2. On the Greengage DB master host, create a readable external table that references the text files. The table has the single customers_data column of type TEXT. In the LOCATION clause, specify the PXF s3:text:multi profile and the server configuration. Use the FILE_AS_ROW=true option to enable loading files into single table rows. In the FORMAT clause, set CSV as the data format:

    CREATE EXTERNAL TABLE customers_text (
    customers_data TEXT
    )
    LOCATION ('pxf://customers/text?PROFILE=s3:text:multi&SERVER=s3&FILE_AS_ROW=true')
    FORMAT 'CSV';

  3. Query the created external table:

    SELECT * FROM customers_text;

    The output should look as follows. Notice that the query returns one row per file, so the number of rows equals the number of files read:

                           customers_data
-------------------------------------------------------------
 1,John,Doe,john.doe@example.com,123 Elm Street
 5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue+
 Unit 2B                                                    +
 Second Floor"                                              +
 6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road    +
 Suite 300                                                  +
 Business Park"                                             +
 7,Eve,Wilson,eve.wilson@example.com,"PO Box 555            +
 Anytown, USA                                               +
 Zip Code: 12345"
 2,Jane,Smith,jane.smith@example.com,456 Oak Street         +
 3,Bob,Brown,bob.brown@example.com,789 Pine Street          +
 4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
(3 rows)

  4. Enable psql expanded display mode and query the customers_text table again:

    $ psql -d customers -x -c 'SELECT * FROM customers_text;'

    The output should look as follows:

    -[ RECORD 1 ]--+------------------------------------------------------------
customers_data | 1,John,Doe,john.doe@example.com,123 Elm Street
-[ RECORD 2 ]--+------------------------------------------------------------
customers_data | 2,Jane,Smith,jane.smith@example.com,456 Oak Street
               | 3,Bob,Brown,bob.brown@example.com,789 Pine Street
               | 4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
-[ RECORD 3 ]--+------------------------------------------------------------
customers_data | 5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue
               | Unit 2B
               | Second Floor"
               | 6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road
               | Suite 300
               | Business Park"
               | 7,Eve,Wilson,eve.wilson@example.com,"PO Box 555
               | Anytown, USA
               | Zip Code: 12345"

Read JSON files

  1. In the json folder of the customers bucket on the S3 host, create two JSON data files named customers_1.json and customers_2.json having the following content:

    {
  "customers": [
    {
      "id": 101,
      "name": "Alice Smith",
      "ordered_items": [
        "laptop",
        "monitor"
      ]
    },
    {
      "id": 102,
      "name": "Bob Johnson",
      "ordered_items": [
        "keyboard",
        "mouse",
        "pad"
      ]
    }
  ]
}
    {
  "customers": [
    {
      "id": 103,
      "name": "Charlie Brown",
      "ordered_items": [
        "headphones"
      ]
    }
  ]
}

  2. On the Greengage DB master host, create a readable external table using the PXF protocol that references the JSON files. The table has the single customers_data column of type JSON. In the LOCATION clause, specify the PXF s3:text:multi profile and the server configuration. Use the FILE_AS_ROW=true option to enable loading files into single table rows. In the FORMAT clause, set CSV as the data format:

    CREATE EXTERNAL TABLE customers_json (
    customers_data JSON
    )
    LOCATION ('pxf://customers/json?PROFILE=s3:text:multi&SERVER=s3&FILE_AS_ROW=true')
    FORMAT 'CSV';

  3. Query the created external table:

    SELECT * FROM customers_json;

    The output should look as follows:

             customers_data
--------------------------------
 {                             +
   "customers": [              +
     {                         +
       "id": 103,              +
       "name": "Charlie Brown",+
       "ordered_items": [      +
         "headphones"          +
       ]                       +
     }                         +
   ]                           +
 }
 {                             +
   "customers": [              +
     {                         +
       "id": 101,              +
       "name": "Alice Smith",  +
       "ordered_items": [      +
         "laptop",             +
         "monitor"             +
       ]                       +
     },                        +
     {                         +
       "id": 102,              +
       "name": "Bob Johnson",  +
       "ordered_items": [      +
         "keyboard",           +
         "mouse",              +
         "pad"                 +
       ]                       +
     }                         +
   ]                           +
 }
(2 rows)

  4. Enable psql expanded display mode and query the customers_json table again:

    $ psql -d customers -x -c 'SELECT * FROM customers_json;'

    The output should look as follows:

    -[ RECORD 1 ]--+-------------------------------
customers_data | {
               |   "customers": [
               |     {
               |       "id": 103,
               |       "name": "Charlie Brown",
               |       "ordered_items": [
               |         "headphones"
               |       ]
               |     }
               |   ]
               | }
-[ RECORD 2 ]--+-------------------------------
customers_data | {
               |   "customers": [
               |     {
               |       "id": 101,
               |       "name": "Alice Smith",
               |       "ordered_items": [
               |         "laptop",
               |         "monitor"
               |       ]
               |     },
               |     {
               |       "id": 102,
               |       "name": "Bob Johnson",
               |       "ordered_items": [
               |         "keyboard",
               |         "mouse",
               |         "pad"
               |       ]
               |     }
               |   ]
               | }

  5. Use the json_array_elements() Greengage DB function to extract the elements of the customers node and view them as individual records:

    SELECT json_array_elements(customers_data -> 'customers') AS customer FROM customers_json;

    The output should look as follows:

    -[ RECORD 1 ]----------------------------
customer | {
         |       "id": 101,
         |       "name": "Alice Smith",
         |       "ordered_items": [
         |         "laptop",
         |         "monitor"
         |       ]
         |     }
-[ RECORD 2 ]----------------------------
customer | {
         |       "id": 102,
         |       "name": "Bob Johnson",
         |       "ordered_items": [
         |         "keyboard",
         |         "mouse",
         |         "pad"
         |       ]
         |     }
-[ RECORD 3 ]----------------------------
customer | {
         |       "id": 103,
         |       "name": "Charlie Brown",
         |       "ordered_items": [
         |         "headphones"
         |       ]
         |     }

  6. Use the -> operator (Get JSON array element) to retrieve the first item in the ordered_items array of each customer node:

    SELECT json_array_elements(customers_data -> 'customers') -> 'ordered_items' -> 0 AS first_item FROM customers_json;

    The output should look as follows:

    -[ RECORD 1 ]------------
first_item | "headphones"
-[ RECORD 2 ]------------
first_item | "laptop"
-[ RECORD 3 ]------------
first_item | "keyboard"
Previous
SequenceFile
Next
Fixed-width text