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 File connector to read and write data between NFS and Greengage DB

Anton Monakov

With the PXF File connector, you can read and write data residing on a Network File System (NFS) mounted on the Greengage DB hosts.

This topic describes how to configure and use the PXF File connector for reading and writing data in NFS by using external tables and provides practical examples.

Before you begin, ensure that:

  • All files are accessible by gpadmin or by the operating system user that started the PXF process.

  • The network file system is correctly mounted at the same local mount point on every Greengage DB host.

  • One or more named PXF server configurations are created as described in Configure a PXF network file system server.

Supported file types

The PXF File connector supports reading and writing the following file types from NFS.

File type Profile name Supported operations

Delimited single-line text

file:text

Read, write

Single-line comma-separated text values (CSV)

file:csv

Read, write

Delimited text with quoted linefeeds

file:text:multi

Read

Fixed width single-line text

file:fixedwidth

Read, write

Avro

file:avro

Read, write

JSON

file:json

Read, write

ORC

file:orc

Read, write

Parquet

file:parquet

Read, write

Create an external table using the PXF protocol

To create a Greengage DB external table to read or write data in NFS, use the following general syntax:

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
    LOCATION ('pxf://<path_to_data>?PROFILE=file:<file_type>[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
    FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>)
    [DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];
Keyword Value

<table_name>

The name of the table to create

<column_name>

The name of the column to create

<data_type>

The data type of the column

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 NFS. The path is considered relative to the pxf.fs.basePath property value specified in the server configuration. The <path_to_data> value must not use relative path notation (such as ./ or ../) nor include the dollar sign ($) character

PROFILE=file:<file_type>

The profile is specified as the file:<file_type> pair, where <file_type> identifies one of the supported file types

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

<custom‑option>=<value>

One of the custom options provided in the LOCATION string depending on the profile. See Custom options, data format, and formatting properties for details

FORMAT <value>

The data format, which can be TEXT, CSV, or CUSTOM. See Custom options, data format, and formatting properties for details

<formatting‑properties>

Formatting properties supported by the profile. See Custom options, data format, and formatting properties for details

DISTRIBUTED BY

When loading data from a Greengage DB table into a writable external table, consider specifying the same distribution policy or column name on both tables. This will avoid extra motion of data between segments on the load operation. Learn more about table distribution in Distribution

Examples

These examples demonstrate how to configure and use the PXF File connector for reading and writing CSV data in NFS by using external tables.

The examples assume that a network file system with the share point /mnt/extdata/pxf is configured and mounted on each Greengage DB cluster host.

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 a PXF network file system server

To have PXF connect to NFS, you need to create the corresponding server configuration as described in Configure a PXF server in PXF documentation 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 a network file system server configuration directory (for example, named nfs):

    $ mkdir $PXF_BASE/servers/nfs

  3. Copy the server template configuration file $PXF_HOME/templates/pxf-site.xml to $PXF_BASE/servers/nfs:

    $ cd $PXF_BASE/servers/nfs
$ cp $PXF_HOME/templates/pxf-site.xml .

  4. The template file includes two mandatory properties that need to be set:

    • pxf.fs.basePath identifies the base network file system share path. The file path specified in the LOCATION clause of the CREATE EXTERNAL TABLE command is considered to be relative to this share path.

    • pxf.service.user.impersonation regulates user impersonation. PXF does not support user impersonation for NFS and accesses it as the operating system user that started the PXF process, usually gpadmin. Therefore, user impersonation must be explicitly turned off.

    Open the template server configuration file in the editor and provide the relevant property values. For example, if the file system share point is the directory named /mnt/extdata/pxf, set it as the pxf.fs.basePath property value:

    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
...
    <property>
        <name>pxf.service.user.impersonation</name>
        <value>false</value>
    </property>
    <property>
        <name>pxf.fs.basePath</name>
        <value>/mnt/extdata/pxf</value>
    </property>
...
</configuration>

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

    $ pxf cluster sync

Read a CSV file from NFS

  1. In the NFS shared folder, create a CSV file named customers.csv 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

  2. On the Greengage DB master host, create a readable external table that references the customers.csv file. In the LOCATION clause, specify the PXF file:csv profile and the server configuration. In the FORMAT clause, set CSV as the data format.

    CREATE EXTERNAL TABLE customers_r (
    id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255)
    )
    LOCATION ('pxf://customers.csv?PROFILE=file:csv&SERVER=nfs')
    FORMAT 'CSV';

  3. Query the created external table:

    SELECT * FROM customers_r;

    The output should look as follows:

     id | first_name | last_name |         email          |      address
----+------------+-----------+------------------------+-------------------
  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
(4 rows)

Write a CSV file to NFS

  1. On the Greengage DB master host, create a writable external table that writes data to the customers subfolder of the NFS shared folder. In the LOCATION clause, specify the PXF file:csv profile and the server configuration. In the FORMAT clause, set CSV as the data format:

    CREATE WRITABLE EXTERNAL TABLE customers_w (
    id INTEGER,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    address TEXT
    )
    LOCATION ('pxf://customers?PROFILE=file:csv&SERVER=nfs')
    FORMAT 'CSV';

  2. Insert data into the created external table:

    INSERT INTO customers_w (
     id,
     first_name,
     last_name,
     email,
     address
     ) 
VALUES (5,'Alice','Johnson','alice.johnson@example.com','10 Oak Avenue'),
       (6,'Charlie','Williams','charlie.williams@example.com','42 Maple Drive'),
       (7,'Bob','Smith','bob.smith@example.com','7 Pine Court'),
       (8,'Eve','Brown','eve.brown@example.com','12 Birch Lane');

  3. View the contents of the customers subfolder of the NFS shared folder. The file list should look similar to the following:

    230-0000000016_0
230-0000000016_1
230-0000000016_2
230-0000000016_3

  4. Verify the contents of the created files. The output should look as follows:

    8,Eve,Brown,eve.brown@example.com,12 Birch Lane
6,Charlie,Williams,charlie.williams@example.com,42 Maple Drive
7,Bob,Smith,bob.smith@example.com,7 Pine Court
5,Alice,Johnson,alice.johnson@example.com,10 Oak Avenue

Custom options, data format, and formatting properties

The custom options, data format, and formatting properties that you specify when creating an external table that references a file on a network file system are file type-specific.

Single-line text values and CSV

Keyword Value

IGNORE_MISSING_PATH=<boolean>

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

SKIP_HEADER_COUNT=<numlines>

The number of header lines to skip in the beginning of each file before reading the data. The default value is 0, no lines are skipped

COMPRESSION_CODEC

The compression codec to use when writing data: default, bzip2, gzip, or uncompressed (no compression). If not provided (or uncompressed is provided), no data compression is performed

FORMAT <value>

The data format, which can be TEXT when referencing plain text delimited data or CSV when referencing comma-separated value data.

Note that the HEADER option, which commonly designates whether the data file contains a header row, is not supported for external tables using PXF. If a text file includes header lines, use the SKIP_HEADER_COUNT custom option to specify the number of lines to skip at the beginning of each file

delimiter

The delimiter character in the data. For the CSV format, the default <delim_value> is the comma character (,). You can use an E'' escape string constant, for example delimiter=E'\t'

Multiline text

Keyword Value

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

FORMAT

To read multiline text data in NFS, CSV must be specified

Fixed-width text

Keyword Value

NEWLINE

When the line_delim formatter option contains \r, \r\n, or a set of custom escape characters, you must set NEWLINE to CR, CRLF, or the set of bytecode characters, respectively

COMPRESSION_CODEC

The compression codec to use when writing data: default, bzip2, gzip, or uncompressed (no compression). If not provided (or uncompressed is provided), no data compression is performed

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

FORMAT 'CUSTOM'

The custom format with the built-in custom formatter functions for read (fixedwidth_in) and write (fixedwidth_out) operations is used to work with data in NFS

<field_name>='<width>'

The name and the width of the field in characters. Fields must be listed in their physical order. The field names must match the columns listed in the CREATE EXTERNAL TABLE command.

When reading data, if the field value is less than the <width> value, Greengage DB expects the field to be right-padded with spaces to that size. When writing data, if the field value is less than the <width> value, Greengage DB right-pads the field with spaces up to the <width> value

line_delim

The line delimiter character in the data, \n (LF) by default. If the option is provided and contains \r (CR), \r\n (CRLF), or a set of custom escape characters, you must also specify the NEWLINE option and set its value to CR, CRLF or the set of bytecode characters, respectively

Avro

Keyword Value

COLLECTION_DELIM

The delimiter characters placed between entries in a top-level array, map, or record field when mapping an Avro complex data type to a text column during data reading. The default is the comma character (,)

MAPKEY_DELIM

The delimiter characters placed between the key and value of a map entry when mapping an Avro complex data type to a text column during data reading. The default is the colon character (:)

RECORDKEY_DELIM

The delimiter characters placed between the field name and value of a record entry when mapping an Avro complex data type to a text column during data reading. The default is the colon character (:)

SCHEMA

The path to the Avro schema file in NFS. The path is considered relative to the base path, which is specified as the pxf.fs.basePath property value in the server configuration

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

COMPRESSION_CODEC

The compression codec to use when writing data: bzip2, xz, snappy, deflate, or uncompressed (no compression). If not provided (or uncompressed is provided), no data compression is performed

CODEC_LEVEL

The compression level (applicable to the deflate and xz codecs only), which provides the trade-off between speed and compression. Valid values are 1 (fastest) to 9 (most compressed). The default compression level is 6

FORMAT 'CUSTOM'

The custom format with the built-in custom formatter functions for read (pxfwritable_import) and write (pxfwritable_export) operations is used to work with data in NFS

JSON

Keyword Value

IDENTIFIER=<value>

Specified only when accessing JSON data comprised of multiline records. <value> identifies the name of the field whose parent JSON object has to be returned as an individual tuple.

When a nested object also includes a field with the same name as the one specified as IDENTIFIER, PXF might return incorrect results. You can work around this edge case by compressing the JSON file and having PXF read the compressed file

SPLIT_BY_FILE=<boolean>

Defines how to split the data specified in <path_to_data>. The default value is false: PXF creates multiple splits for each file and processes them in parallel. When set to true, PXF creates and processes a single split per file

IGNORE_MISSING_PATH=<boolean>

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

ROOT=<value>

When writing to a single JSON object, identifies the name of the root-level object attribute

COMPRESSION_CODEC

The compression codec to use when writing data: default, bzip2, gzip, or uncompressed (no compression). If not provided (or uncompressed is provided), no data compression is performed.

If a compression codec is specified, the following naming convention applies to written files: <basename>.<json file type>.<compression extension>, for example customers.jsonl.gz

FORMAT 'CUSTOM'

The custom format with the built-in custom formatter functions for read (pxfwritable_import) and write (pxfwritable_export) operations is used to work with data in NFS

ORC

Keyword Value

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

MAP_BY_POSITION

Specifies whether PXF should map an ORC column to a Greengage DB column by position. The default value is false: PXF maps an ORC column to a Greengage DB column by name

COMPRESSION_CODEC

The compression codec to use when writing data: lz4, lzo, zstd, snappy, zlib, or none.

You must explicitly specify none if you do not want PXF to compress the data. Otherwise, PXF compresses the data using Zlib compression

FORMAT 'CUSTOM'

The custom format with the built-in custom formatter functions for read (pxfwritable_import) and write (pxfwritable_export) operations is used to work with data in NFS

Parquet

Keyword Value

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

COMPRESSION_CODEC

The compression codec to use when writing data: snappy, gzip, lzo, or uncompressed (no compression).

You must explicitly specify uncompressed if you do not want PXF to compress the data. Otherwise, PXF compresses the data using Snappy compression

ROWGROUP_SIZE

The size (in bytes) of the row group, which provides a logical partitioning of the data into rows. The default row group size is 8 * 1024 * 1024 bytes

PAGE_SIZE

The size (in bytes) of a page, which divides row groups in a column into column chunks. The default page size is 1 * 1024 * 1024 bytes

ENABLE_DICTIONARY

Specifies whether to enable dictionary encoding. The default value is true; dictionary encoding is enabled when writing Parquet files

DICTIONARY_PAGE_SIZE

When dictionary encoding is enabled, defines a single dictionary page per column, per row group. DICTIONARY_PAGE_SIZE is similar to PAGE_SIZE, but is specified for the dictionary. The default dictionary page size is 1 * 1024 * 1024 bytes

PARQUET_VERSION

The Parquet version; the supported values are v1 (default) and v2

SCHEMA

The path to the Parquet schema file in NFS. The path is considered relative to the pxf.fs.basePath property value specified in the server configuration

FORMAT 'CUSTOM'

The custom format with the built-in custom formatter functions for read (pxfwritable_import) and write (pxfwritable_export) operations is used to work with data in NFS
Previous
Fixed-width text
Next
Iceberg