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

PXF overview

Anton Monakov

The data managed by your organization can reside across many different external systems as well as in a variety of formats. Greenplum Platform Extension Framework (PXF) is an extensible framework that lets you query a wide range of data sources from Greengage DB. PXF speeds up data retrieval with highly parallel, high-throughput access. It also allows joining and filtering data sets remotely and returning only the results rather than performing an expensive full data load operation.

PXF provides connectors that are installed with Greengage DB and let you access external data sources, including Hadoop (HDFS, Hive, and HBase), S3-compatible object stores, SQL databases (MySQL, Oracle, DB2, PostgreSQL, etc.), and network file systems. These connectors map an external data source to a Greengage DB external table definition.

PXF can access the data stored in any of these formats:

  • Avro, AvroSequenceFile

  • JSON

  • ORC

  • Parquet

  • RCFile

  • SequenceFile

  • Text (plain, delimited, with embedded line feeds, fixed-width)

PXF architecture

A Greengage DB deployment consists of a master host, a standby master host, and multiple segment hosts. A single PXF service process runs on each Greengage DB host and allocates a worker thread for each segment instance on the host that participates in a query against an external table. The PXF services on multiple segment hosts communicate with the external data store in parallel. The PXF service processes that are running on the master and standby master hosts are not involved in data transfer.

The PXF framework is composed of three main components:

  • PXF extension is a C client library that implements the PostgreSQL extension. The extension runs on each database segment and communicates with the PXF server via REST API calls.

  • PXF server is a Java web application deployed in Apache Tomcat running on each Greengage DB segment host in the cluster. The PXF server receives requests from the PXF extension and translates those requests to the external systems.

  • PXF Command Line Interface (CLI) is a Go application, which provides a convenient way to manage PXF servers running on Greengage DB hosts. PXF CLI lets you run commands on a specific PXF server or across the entire installation. These commands include starting and stopping the server, checking the server status, and others. See Overview of the pxf commands and Overview of the pxf cluster commands for more details.

After you configure and initialize PXF, you need to start a single PXF JVM process on each Greengage DB segment host. This long-running process concurrently serves multiple query requests. When you run a query against a Greengage DB external table using the PXF protocol, the query plan is generated and then dispatched from the Greengage DB master to the segments.

The PXF extension running on each Greengage DB segment process, in turn, forwards the request to the PXF server running on the same host. In a typical deployment topology, a PXF server is placed on each Greengage DB segment host that runs one or more segment processes. A PXF server receives at least as many requests as the number of segment processes running on the host, or more if a query is complex and contains multiple processing slices. Each such request gets a PXF server thread assigned to it.

PXF architecture overview

Internal architecture

Internally, PXF defines three interfaces to read and write data from external data sources: the Fragmenter, the Accessor, and the Resolver. PXF supplies multiple implementations for these interfaces that utilize different access protocols and support various data formats.

To simplify usability, PXF provides a concept of a profile, a named entity that encapsulates a combination of specific implementations of a Fragmenter, Accessor, and Resolver.

Fragmenter

PXF Fragmenter is a functional interface that splits the external dataset into a list of independent fragments that can be read in parallel. A fragment is the smallest indivisible chunk of data that is processed by a single PXF server thread. The Fragmenter does not retrieve the actual data, it works only with metadata, which describes the location of a given data fragment and optionally the offset of the data within the overall dataset. The nature of fragments is different for various types of external systems, and the process of obtaining fragments metadata is also different: for example, it can be a call to Hadoop HDFS NameNode for a file stored in Hadoop HDFS or a calculation of interval buckets for a partitioned JDBC table.

Processing of a single query is distributed across all Greengage DB segments and multiple PXF server threads, which run independently of each other. When a query runs on an external table using the PXF protocol, each Greengage DB segment performs two types of REST calls to the PXF server: a single fragmentation call and zero or more bridge calls. During the fragmentation call, the Fragmenter divides the overall external dataset into data fragments along with metadata about each fragment.

PXF fragmentation

The resulting set of fragment metadata is distributed evenly among the Greengage DB segments by using a hashing function. Each Greengage DB segment then iterates over the assigned subset of fragments and performs a bridge call for each such fragment to read the actual data. A different PXF server thread processes each bridge call for a single fragment of data. During the bridge call, the PXF server thread receives fragment metadata from the Greengage DB segment and fetches data from the external system for the given fragment using a specific Accessor.

Accessor

PXF Accessor is a functional interface responsible for reading or writing data to external data sources and converting it into individual records. Different types of external data sources require different protocols and authentication methods, therefore a separate Accessor is required for each type of external server. PXF provides different Accessors to connect to remote databases using JDBC, cloud object storage vendors, and remote Hadoop clusters. These PXF Accessors are able to read text, Parquet, Avro, CSV, and other data formats.

When an Accessor processes a request to retrieve data for a specific fragment, it first fetches the configuration of the external data source. Next, it analyzes the metadata for the requested fragment and uses the client API libraries for its corresponding protocol to request the required data from the external data source. The obtained data is broken into records, such as lines of a text file, rows in a JDBC result set, or Parquet records. Each such record, while still being in a proprietary format, is then wrapped into a common data structure that is used across PXF to exchange data. When supported by the external data source, for example JDBC, Hive, or S3, the Accessor is also responsible for pushing down predicates and projecting columns from the incoming query.

Resolver

PXF Resolver is a functional interface that decodes (when reading) or encodes (when writing) the record read from the external system by an Accessor into individual fields. It then maps field data types and values into a format recognized by Greengage DB or the external system. Once a record is processed by the Resolver, it can be converted into an on-the-wire representation (such as CSV or binary) and shipped to Greengage DB to be re-created as a tuple, or into a format like Parquet or Avro, and sent to the external system. Resolver implementations are usually data format-specific. PXF provides Resolvers to convert CSV lines, JDBC ResultSet columns, Parquet records, Avro records, and JSON documents.

The following diagram illustrates the interaction between a Fragmenter, an Accessor, and a Resolver when reading and writing data.

Interaction between Fragmenter, Accessor, and Resolver

PXF main concepts and features

Connector

A PXF connector is a logical grouping of multiple components (implementations of Fragmenter, Accessor, and Resolver as well as a Profile) that lets you process data from a given type of external data source. While it does not have a specific technical representation, a connector is a useful concept to describe overall capabilities of PXF when working with external data.

PXF provides the following native connectors to external systems:

  • JDBC connector allows PXF to access an external SQL database such as Oracle, MySQL, PostgreSQL, Hive using the appropriate JDBC driver. Learn more about working with JDBC connectors in Configure PXF JDBC connectors.

  • Cloud connector enables access to data residing in a cloud object storage such as Amazon S3, Google Cloud Storage, or Azure Data Lake.

  • Hadoop connector reads data stored in Hadoop. Learn more about working with Hadoop connectors in Configure PXF Hadoop connectors.

  • Hive connector reads data from Hive tables by accessing metadata from Hive MetaStore and then accessing the underlying files.

  • HBase connector reads data stored in HBase tables.

Server

A PXF server is a named configuration for a connector. A server definition provides the information required for accessing an external data source. This information is data store-specific and may include server location, access credentials, and other relevant properties.

When creating an external Greengage DB table using the PXF protocol, you specify the SERVER=<server_name> option to identify the server configuration from which to obtain the external data store configuration and credentials. The default PXF server is named default. If the SERVER option is omitted, PXF uses the default server. The valid connector configuration for the default server must exist in $PXF_BASE/servers/default/.

Learn more about working with PXF servers in Configure a PXF server.

Profile

A PXF profile is a named mapping representing both the protocol for connecting to an external data source and the format of the data that needs to be processed. For example, to read a text file from S3 cloud storage, you specify the s3:text profile; to read a Parquet file from HDFS, you specify the hdfs:parquet profile.

The profile is specified as the PROFILE parameter value when defining an external Greengage DB table using the PXF protocol. PXF then receives the value of the profile parameter with the request from Greengage DB and refers to the system configuration to select the appropriate Fragmenter, Accessor, and Resolver implementations for data retrieval and processing.

Named query

PXF lets you define a custom query, called named query, on the data residing in a remote database. PXF JDBC connector executes this query when retrieving data for the corresponding Greengage DB external table. The main purpose of named queries is to perform complex queries, joins, and aggregation on the data residing in a remote system in that system itself. PXF also wraps the query in an external statement so that it can apply optimizations such as column projection, predicate pushdown, and JDBC read partitioning.

Named queries are especially useful when accessing very large datasets, for example managed by Apache Hive in a Hadoop cluster, since a Hadoop cluster often has more available computational resources than Greengage DB to perform complex data processing. This is a primary use case where a named query provides substantial benefits.

To create a named query, store it in a text file in the PXF server configuration directory for the remote database. For example, the following named query order_report retrieves customer names and their cities with the total amount of all orders placed by each customer in a specific month. The data is aggregated per customer and order month:

SELECT
    c.name,
    c.city,
    SUM(o.amount) AS total,
    EXTRACT(MONTH FROM o.order_date) AS order_month
FROM
    customers c
JOIN
    orders o ON c.id = o.customer_id
GROUP BY
    c.name, c.city, EXTRACT(MONTH FROM o.order_date)
ORDER BY
    c.city, total;

You can then reference this query when defining an external Greengage DB table using the PXF protocol by providing the query name prefixed by the query: string in the LOCATION clause, for example:

CREATE EXTERNAL TABLE pxf_query_order_report (
        name text,
        city text,
        total int,
        order_month int
    )
    LOCATION ('pxf://query:order_report?PROFILE=jdbc&SERVER=postgres-server&PARTITION_BY=order_month:int&RANGE=1:12&INTERVAL=3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

This instructs PXF to read the text of a query in a file named order_report.sql in the server configuration directory and dispatch it to the remote server. Once the remote server executes the named query, PXF forwards the received data to Greengage DB to represent the resulting dataset. You can then view the query results as an external table in any SELECT command, for example:

SELECT * FROM pxf_query_order_report;

For a practical example of creating and running a named query, see Use PXF JDBC connector to read and write data between Greengage DB and PostgreSQL.

Column projection

With column projection, only the columns required by the SELECT query on an external table are returned from an external data source. This improves query performance and can also reduce the amount of transferred data. Column projection is automatically enabled in the PXF protocol for Greengage DB external tables.

The following criteria must be met for column projection to occur:

  • The external data source that you are accessing must support column projection. If column projection is not supported, the query is run without it, and the data is filtered on the Greengage DB side.

  • The underlying PXF connector and profile implementation must also support column projection.

  • PXF must be able to serialize the query filter. When a query filter cannot successfully serialize, for example, when the WHERE clause resolves to a boolean type, column projection may be deactivated.

The following PXF connector and profile combinations support column projection on read operations.

Data source Connector Profile

External SQL database

JDBC Connector

jdbc

Hive

Hive Connector

hive (for accessing data stored as Text, Parquet, RCFile, and ORC), hive:rc, hive:orc

Hadoop

HDFS Connector

hdfs:orc, hdfs:parquet

Network File System

File Connector

file:orc, file:parquet

Amazon S3

S3-Compatible Object Store Connectors

s3:orc, s3:parquet

Amazon S3 using S3 Select

S3-Compatible Object Store Connectors

s3:parquet, s3:text

Google Cloud Storage

GCS Object Store Connector

gs:orc, gs:parquet

Azure Blob Storage

Azure Object Store Connector

wasbs:orc, wasbs:parquet

Azure Data Lake

Azure Object Store Connector

adl:orc, adl:parquet

Filter pushdown

PXF filter pushdown allows extracting and passing the constraints from the WHERE clause of a SELECT query to the external data source for filtering. This can improve query performance and can also reduce the amount of transferred data.

You activate or deactivate filter pushdown for all Greengage DB external table protocols, including PXF, by setting the gp_external_enable_filter_pushdown server configuration parameter. The default value is on; set it to off to deactivate filter pushdown, for example:

SHOW gp_external_enable_filter_pushdown;
SET gp_external_enable_filter_pushdown TO 'off';
NOTE

Some external data sources do not support filter pushdown. Filter pushdown may also be not supported with certain data types or operators. If a query accesses a data source that does not support it, the query is run without filter pushdown and the data is filtered on the Greengage DB side.

PXF filter pushdown can be used with these data types (connector- and profile-specific):

  • INT2, INT4, INT8;

  • CHAR, TEXT, VARCHAR;

  • FLOAT;

  • NUMERIC (pushdown is not available for the S3 connector when using S3 Select and for the hive profile when accessing a table created with the STORED AS Parquet option);

  • BOOL;

  • DATE, TIMESTAMP (available only with the JDBC connector, the S3 connector when using S3 Select, the hive:rc and hive:orc profiles, and the hive profile when accessing a table created with the STORED AS RCFile or STORED AS ORC option).

PXF accesses data sources using the profiles exposed by different connectors, and filter pushdown support is determined by the specific connector implementation. The following PXF profiles support some aspects of filter pushdown as well as different arithmetic and logical operations.

Profile <, >, <=, >=, =, <> LIKE IS [NOT] NULL IN AND OR NOT

jdbc

Yes

Yes (4)

Yes

No

Yes

Yes

Yes

*:parquet

Yes (1)

No

Yes (1)

Yes (1)

Yes (1)

Yes (1)

Yes (1)

*:orc (all except hive:orc)

Yes (1,3)

No

Yes (1,3)

Yes (1,3)

Yes (1,3)

Yes (1,3)

Yes (1,3)

s3:parquet and s3:text with S3-Select

Yes

No

Yes

Yes

Yes

Yes

Yes

hbase

Yes

No

Yes

No

Yes

Yes

No

hive:text

Yes (2)

No

No

No

Yes (2)

Yes (2)

No

hive:rc, hive (accessing a table created with STORED AS RCFile)

Yes (2)

No

Yes

Yes

Yes (2)

Yes (2)

Yes

hive:orc, hive (accessing a table created with STORED AS ORC)

Yes (2)

No

Yes

Yes

Yes (2)

Yes (2)

Yes

hive (accessing a table created with STORED AS Parquet)

Yes (2)

No

No

Yes

Yes (2)

Yes (2)

Yes

hive:orc with VECTORIZE=true

Yes (2)

No

No

No

Yes (2)

Yes (2)

No

where:

  1. PXF applies the predicate, rather than the remote system.

  2. PXF supports partition pruning based on partition keys.

  3. PXF filtering is based on file-level, stripe-level, and row-level ORC statistics.

  4. The jdbc profile supports the LIKE operator only for TEXT fields.

PXF does not support filter pushdown for any profile not mentioned in the table above, including *:avro, *:AvroSequenceFile, *:SequenceFile, *:json, *:text, *:csv, *:fixedwidth, and *:text:multi.

JDBC read partitioning

PXF supports read partitioning during JDBC query processing, which distributes the work of retrieving individual partitions among PXF server instances. Each individual PXF server instance appends filters to the WHERE clause of the SELECT statement to restrict the returned dataset to only its assigned partition.

Read partitioning does not refer to whether the data is actually stored in partitioned tables in the remote database. Rather, it provides a hint to PXF that the dataset in the remote table can be processed in parallel, with each partition being retrieved by a separate PXF thread. When the data is also partitioned in the same manner on the storage side in the remote database, the retrieval by the remote database will also be more efficient: to satisfy a query for a partition from PXF, only the given partition needs to be scanned instead of the whole table. Learn more about Greengage DB table partitioning in the Greengage DB documentation.

TIP

To obtain optimal performance, you should choose a partition column that can take advantage of concurrent reads. For example, when using PXF to read from an Oracle database, use an Oracle partitioning key column. When querying Greengage DB, you can use the special gp_segment_id column to have each segment scan only its own data and then hand off that data to the master.

To activate read partitioning, in the LOCATION clause of the CREATE EXTERNAL TABLE command, specify the PARTITION_BY, RANGE, and INTERVAL custom options, for example:

CREATE READABLE EXTERNAL TABLE sales_summary (
        name text,
        month int,
        total int
    )
    LOCATION ('pxf://query:sales?PROFILE=Jdbc&SERVER=mysql-db&PARTITION_BY=month:int&RANGE=1:12&INTERVAL=3')
    FORMAT 'CUSTOM' (formatter='pxfwritable_import');

The PXF JDBC connector then splits a SELECT query into multiple subqueries that retrieve subsets of the data, each of which is called a fragment. The JDBC connector automatically adds extra query constraints (WHERE expressions) to each fragment to guarantee that every tuple of data is retrieved from the external database exactly once.

For example, for the above LOCATION clause that specifies PARTITION_BY=month:int&RANGE=1:12&INTERVAL=3, PXF generates seven fragments: four according to the partition settings and up to three implicitly generated fragments.

PXF read partitioning example

The constraints associated with each fragment are as follows:

  • Fragment 1: WHERE (month < 1) — implicitly-generated fragment for RANGE start-bounded interval;

  • Fragment 2: WHERE (month >= 1) AND (month < 4) — fragment specified by partition settings;

  • Fragment 3: WHERE (month >= 4) AND (month < 7) — fragment specified by partition settings;

  • Fragment 4: WHERE (month >= 7) AND (month < 10) — fragment specified by partition settings;

  • Fragment 5: WHERE (month >= 10) AND (month < 12) — fragment specified by partition settings;

  • Fragment 6: WHERE (month >= 12) — implicitly-generated fragment for RANGE end-bounded interval;

  • Fragment 7: WHERE (month IS NULL) — implicitly-generated fragment.

Create an external table using the PXF protocol

To create a Greengage DB external table using the PXF protocol, in the CREATE EXTERNAL TABLE command, provide the external data store (server name) and the data format (profile name). PXF may require additional information to read or write certain data formats. You can provide this profile-specific information using the optional <custom_option>=<value> component of the LOCATION string.

The general syntax for the CREATE EXTERNAL TABLE command that specifies the PXF protocol is as follows:

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )

    LOCATION('pxf://<path-to-data>?PROFILE=<profile_name>[&SERVER=<server_name>][&<custom_option>=<value>[...]]')
    FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);
Keyword Description

<path‑to‑data>

A directory, file name, wildcard pattern, table name, and so on. The syntax of <path-to-data> depends on the external data source. For example, if the external data store is HDFS, <path-to-data> identifies the absolute path to a specific HDFS file. If the external data store is Hive, <path-to-data> identifies a schema-qualified Hive table name

PROFILE=<profile_name>

The profile that PXF uses to access the data

SERVER=<server_name>

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

<custom‑option>=<value>

Additional options and their values supported by the profile or the server

FORMAT <value>

The data format. PXF profiles support the TEXT, CSV, and CUSTOM formats

<formatting‑properties>

Formatting properties supported by the profile; for example, the format function or delimiter. You can provide additional profile-specific formatting information via the <custom_option>=<value> component of the LOCATION string.

Note that the HEADER option, which commonly designates whether the data file contains a header row, is not supported for Greengage DB external tables using PXF
Previous
PXF
Next
Installation