Querying Delta Lake Tables in S3-compatible object storage

Overview

External tables allow you to access and query data stored in S3-compatible object storage using SQL. You can create an external table that references data in S3-compatible object storage and query the data using standard SQL commands.

Prerequisites

• An EDB Postgres AI account and a Lakehouse node.
• An S3-compatible object storage location with data stored as Delta Lake Tables.
  • See Bringing your own data for more information on how to prepare your data.
• Credentials to access the S3-compatible object storage location, unless it is a public bucket.
  • These credentials will be stored within the database. We recommend creating a separate user with limited permissions for this purpose.

Creating an External Storage Location

The first step is to create an external storage location which references S3-compatible object storage where your data resides. A storage location is an object within the database which you refer to to access the data; each storage location has a name for this purpose.

Creating a named storage location is performed with SQL by executing the pgaa.create_storage_location function. pgaa is the name of the extension and namespace that provides the functionality to query external storage locations. The create_storage_location function takes a name for the new storage location, and the URI of the S3-compatible object storage location as parameters. The function optionally can take a third parameter, options, which is a JSON object for specifying optional settings, detailed in the functions reference. For example, in the options, you can specify the access key ID and secret access key for the storage location to enable access to a private bucket.

The following example creates an external table that references a public S3-compatible object storage location:

SELECT pgaa.create_storage_location('sample-data', 's3://pgaa-sample-data-eu-west-1');

The next example creates an external storage location that references a private S3-compatible object storage location:

SELECT pgaa.create_storage_location('private-data', 's3://my-private-bucket', '{"access_key_id": "my-access-key-id","secret_access_key": "my-secret-access-key"}');

Creating an External Table

After creating the external storage location, you can create an external table that references the data in the storage location. The following example creates an external table that references a Delta Lake Table in the S3-compatible object storage location:

CREATE TABLE public.customer () USING PGAA WITH (pgaa.storage_location = 'sample-data', pgaa.path = 'tpch_sf_1/customer');

Note that the schema is not defined in the CREATE TABLE statement. The pgaa extension expects the schema to be defined in the storage location, and the schema itself is derived from the schema stored at the path specified in the pgaa.path option. The pgaa extension will infer the best Postgres-equivalent data types for the columns in the Delta Table.

Querying an External Table

After creating the external table, you can query the data in the external table using standard SQL commands. The following example queries the external table created in the previous step:

SELECT COUNT(*) FROM public.customer;