WarehousePG

Appearance

Connecting to Google Cloud Storage

Configure a server for Google Cloud Storage, then read and write its data through external tables.

Configuring the server ​

Create a server that connects to Google Cloud Storage (GCS).

  1. Create a server directory under $PXF_BASE/servers, and copy the gs-site.xml template from $PXF_HOME/templates into it. For example, to configure a server named gcssrvcfg:

    bash
    mkdir -p $PXF_BASE/servers/gcssrvcfg
cp $PXF_HOME/templates/gs-site.xml $PXF_BASE/servers/gcssrvcfg

  2. Edit gs-site.xml with the path to a Google Cloud service account JSON key file, readable by the PXF service on every host:

    xml
    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>google.cloud.auth.service.account.enable</name>
        <value>true</value>
    </property>
    <property>
        <name>google.cloud.auth.service.account.json.keyfile</name>
        <value><path_to_keyfile></value>
    </property>
</configuration>

    See Configuration templates for the full list of gs-site.xml properties.

  3. Sync the change to every segment host, then restart PXF to apply it:

    bash
    pxf cluster sync
pxf cluster restart

Reading data ​

Read data from the object store by creating a readable external table with the profile for its format and the server you configured. For example, to read a CSV file using the gcssrvcfg server:

sql
CREATE EXTERNAL TABLE pxf_read_example (id int, name text, age int)
    LOCATION ('pxf://<bucket>/<path>/data.csv?PROFILE=gs:text&SERVER=gcssrvcfg')
    FORMAT 'CSV' (delimiter=',');

SELECT * FROM pxf_read_example;

PXF also supports structured formats like Parquet, through the same profile-based syntax:

sql
CREATE EXTERNAL TABLE pxf_parquet_example (
    id bigint,
    created timestamp without time zone,
    status integer
)
    LOCATION ('pxf://<bucket>/<path>/?PROFILE=gs:parquet&SERVER=gcssrvcfg&COMPRESSION_CODEC=snappy')
    FORMAT 'CUSTOM' (FORMATTER = 'pxfwritable_import')
    ENCODING 'UTF8';

See PXF profiles for the full list of supported formats, including worked examples of Avro, JSON, and multi-byte delimiters.

Note

gs:json supports reading only, unlike the equivalent JSON profiles for other object stores.

Writing data ​

Create a writable external table with the pxfwritable_export formatter to write WHPG data out to GCS:

sql
CREATE WRITABLE EXTERNAL TABLE pxf_write_example (
    id bigint,
    created timestamp without time zone,
    status integer
)
    LOCATION ('pxf://<bucket>/<path>/?PROFILE=gs:parquet&SERVER=gcssrvcfg&COMPRESSION_CODEC=snappy')
    FORMAT 'CUSTOM' (FORMATTER = 'pxfwritable_export')
    ENCODING 'UTF8';

INSERT INTO pxf_write_example SELECT id, created, status FROM some_local_table;

To query the data, create a separate readable external table at the same location:

sql
CREATE EXTERNAL TABLE pxf_read_back (
    id bigint,
    created timestamp without time zone,
    status integer
)
    LOCATION ('pxf://<bucket>/<path>/?PROFILE=gs:parquet&SERVER=gcssrvcfg')
    FORMAT 'CUSTOM' (FORMATTER = 'pxfwritable_import')
    ENCODING 'UTF8';

SELECT * FROM pxf_read_back;