Reading and Writing Parquet Data in an Object Store

The PXF object store connectors support reading and writing Parquet-format data. This section decribes how to use PXF to access Parquet-format data in an object store, including how to create and query an external table that references a Parquet file in the store.

PXF Parquet write support is a Beta feature.

Note: Accessing Parquet-format data from an object store is very similar to accessing Parquet-format data in HDFS. This topic identifies object store-specific information required to read and write Parquet data, and links to the PXF HDFS Parquet documentation where appropriate for common information.

Prerequisites

Ensure that you have met the PXF Object Store Prerequisites before you attempt to read data from or write data to an object store.

Data Type Mapping

Refer to Data Type Mapping in the PXF HDFS Parquet documentation for a description of the mapping between Greenplum Database and Parquet data types.

Creating the External Table

The PXF <objstore>:parquet profiles support reading and writing data in Parquet-format. PXF supports the following <objstore> profile prefixes:

Object Store Profile Prefix
Azure Blob Storage wasbs
Azure Data Lake adl
Google Cloud Storage gs
Minio s3
S3 s3

Use the following syntax to create a Greenplum Database external table that references an HDFS directory. When you insert records into a writable external table, the block(s) of data that you insert are written to one or more files in the directory that you specified.

CREATE [WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-dir>
    ?PROFILE=<objstore>:parquet&SERVER=<server_name>[&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

The specific keywords and values used in the CREATE EXTERNAL TABLE command are described in the table below.

Keyword Value
<path‑to‑dir> The absolute path to the directory in the object store.
PROFILE=<objstore>:parquet The PROFILE keyword must identify the specific object store. For example, s3:parquet.
SERVER=<server_name> The named server configuration that PXF uses to access the data.
<custom‑option>=<value> Parquet-specific custom options are described in the PXF HDFS Parquet documentation.
FORMAT ‘CUSTOM’ Use FORMATCUSTOM’ with (FORMATTER='pxfwritable_export') (write) or (FORMATTER='pxfwritable_import') (read).
DISTRIBUTED BY If you plan to load the writable external table with data from an existing Greenplum Database table, consider specifying the same distribution policy or <column_name> on the writable external table as that defined for the table from which you plan to load the data. Doing so will avoid extra motion of data between segments on the load operation.

If you are accessing an S3 object store, you can provide S3 credentials directly in the CREATE EXTERNAL TABLE command as described in Overriding the S3 Server Configuration.

Example

Refer to the Example in the PXF HDFS Parquet documentation for a Parquet write/read example. Modifications that you must make to run the example with an object store include:

  • Using the CREATE EXTERNAL TABLE syntax and LOCATION keywords and settings described above for the writable external table. For example, if your server name is s3srvcfg:

    CREATE WRITABLE EXTERNAL TABLE pxf_tbl_parquet_s3 (location text, month text, number_of_orders int, total_sales double precision)
      LOCATION ('pxf://BUCKET/pxf_examples/pxf_parquet?PROFILE=s3:parquet&SERVER=s3srvcfg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
    
  • Using the CREATE EXTERNAL TABLE syntax and LOCATION keywords and settings described above for the readable external table. For example, if your server name is s3srvcfg:

    CREATE EXTERNAL TABLE read_pxf_parquet_s3(location text, month text, number_of_orders int, total_sales double precision)
      LOCATION ('pxf://BUCKET/pxf_examples/pxf_parquet?PROFILE=s3:parquet&SERVER=s3srvcfg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');