Reading Avro Data from an Object Store
The PXF object store connectors support reading Avro-format data. This section describes how to use PXF to access Avro data in an object store, including how to create and query an external table that references an Avro file in the store.
Note: Accessing Avro-format data from an object store is very similar to accessing Avro-format data in HDFS. This topic identifies object store-specific information required to read Avro data, and links to the PXF HDFS Avro documentation where appropriate for common information.
Prerequisites
Ensure that you have met the PXF Object Store Prerequisites before you attempt to read data from an object store.
Working with Avro Data
Refer to Working with Avro Data in the PXF HDFS Avro documentation for a description of the Apache Avro data serialization framework.
Creating the External Table
Use the <objstore>:avro
profiles to read Avro-format files from an object store. 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 |
The following syntax creates a Greenplum Database readable external table that references an Avro-format file:
CREATE EXTERNAL TABLE <table_name>
( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-file>?PROFILE=<objstore>:avro&SERVER=<server_name>[&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
The specific keywords and values used in the CREATE EXTERNAL TABLE command are described in the table below.
Keyword | Value |
---|---|
<path‑to‑file> | The absolute path to the directory or file in the object store. |
PROFILE=<objstore>:avro | The PROFILE keyword must identify the specific object store. For example, s3:avro . |
SERVER=<server_name> | The named server configuration that PXF uses to access the data. |
<custom‑option>=<value> | Avro-specific custom options are described in the PXF HDFS Avro documentation. |
FORMAT ‘CUSTOM’ | Use FORMAT 'CUSTOM' with the <objstore>:avro profile. The CUSTOM FORMAT requires that you specify (FORMATTER='pxfwritable_import') . |
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.
Example
Refer to Example: Reading Avro Data in the PXF HDFS Avro documentation for an Avro 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/pxf_avro.avro s3://BUCKET/pxf_examples/
Using the
CREATE EXTERNAL TABLE
syntax andLOCATION
keywords and settings described above. For example, if your server name iss3srvcfg
:CREATE EXTERNAL TABLE pxf_s3_avro(id bigint, username text, followers text, fmap text, relationship text, address text) LOCATION ('pxf://BUCKET/pxf_examples/pxf_avro.avro?PROFILE=s3:avro&SERVER=s3srvcfg&COLLECTION_DELIM=,&MAPKEY_DELIM=:&RECORDKEY_DELIM=:') FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');