External tables

Yandex MPP Analytics for PostgreSQL allows you to work with data from sources that are external to a Yandex MPP Analytics for PostgreSQL cluster. This functionality uses external tables, i.e., special objects in a database that reference tables, buckets, or files from external sources. External DBMS data is accessed via the Greenplum® Platform Extension Framework (PXF) protocol; files on external file servers are accessed via the Greenplum® Parallel File Server (gpfdist) utility. Greenplum® and Apache Cloudberry™ use different gpfdist utilities. For more information about the utilities, see these Greenplum® and Apache Cloudberry™ guides.

With external tables, you can:

• Query external data sources.
• Load datasets from external sources into a Yandex MPP Analytics for PostgreSQL cluster database.
• Join local and external tables in queries.
• Write data to external tables or files.

External data sources for PXF operationsExternal data sources for PXF operations

Yandex MPP Analytics for PostgreSQL uses external data sources to create external tables. Each source is similar to a web server configuration used to access data in external DBMS's. This is why sources are only used for PXF operations.

Sources enable you to do the following:

• In the configuration, specify the parameters that cannot be included in an SQL query for creating a PXF external table.
• Avoid explicitly specifying the user password in an SQL query for creating an external table.
• Simplify your SQL query for creating a table: with a dedicated source properly configured, there is no need to list configuration parameters in your query.
• Simplify your configuration update: it is enough to redefine the parameters at the source only once without changing them for each table separately.

Working with files on external sources using gpfdistWorking with files on external sources using gpfdist

gpfdist is a file server that provides Yandex MPP Analytics for PostgreSQL cluster hosts with parallel access to files on a remote server via the HTTP protocol.

This utility is installed on all Yandex MPP Analytics for PostgreSQL cluster segment hosts, but to work with external files you need to install and run it on the server those files are stored on.

To work with files using gpfdist, follow these steps:

1. On the remote server, run gpfdist, which will open access to a specified directory on a specified port.
2. Create an external table in the Yandex MPP Analytics for PostgreSQL cluster. In the LOCATION parameter, specify the gpfdist protocol, server address, port, and file path or file group mask.

When accessing such a table, the cluster's segment hosts connect to gpfdist in parallel, each segment reading or writing its own portion of the data. Data flows between the segments and the remote server directly bypassing the master host. During loading, data is distributed between segments either evenly or as per the specified distribution key. This improves performance when handling large amounts of external data.

With gpfdist, you can:

• Use delimited text files (TEXT, CSV, and CUSTOM formats), as well as gzip and bzip2 compressed files.
• Use external tables to read data from files and write data to files. Do this by creating a table with the READABLE or WRITABLE option on.
• Specify multiple files and multiple servers running gpfdist in a single external table.
• Distribute the network load by running multiple instances of the utility on the same server using different directories and ports.

The server running gpfdist must be available at the specified port from the network the Yandex MPP Analytics for PostgreSQL cluster is connected to. You should configure security groups for this to work. If the server running gpfdist is outside of Yandex Cloud and accessible via the internet, configure a NAT gateway in the cluster network.

External data sources are not used for gpfdist: all connection parameters are specified in the SQL query used to create the external table.

Use casesUse cases

• Creating an external table from a Yandex Object Storage bucket table using a configuration file
• Getting data from external sources using named queries

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.

Apache® and Apache Cloudberry™ are registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries.