Access a Format Table using DataFusion

Updated at:

Use the DataFusion SQL engine built into pypaimon-rust to query DLF Format Tables, write data to them, and manage their partitions from Python. You do not need to install Spark, Flink, or DataFusion separately.

Prerequisites

  • Resources: A DLF Paimon catalog and a database exist. For instructions, see Manage and use Paimon Catalogs and Databases.

  • Permissions: Create a RAM user and obtain its AccessKey. The RAM user needs the required DLF API and data permissions and read access to the target table. Writing data and running partition management statements also require write access. For more information, see Configure permissions.

  • Versions: Use Python 3.10 or later and pypaimon-rust 0.4.0 or later.

  • VPC access: If you use the DLF VPC endpoint, add the VPC from which the program runs to the DLF trusted VPC list. For more information, see Trusted VPC configuration.

  • Public access: DLF public access is disabled by default. If you use the DLF public endpoint, enable public access for the DLF catalog. For more information, see Enable and configure public network access.

Limitations

  • Data writes: Format Tables do not support UPDATE, DELETE, or MERGE INTO. To write data, use INSERT INTO or INSERT OVERWRITE.

  • Partitions outside the table directory: You cannot read partitions registered outside the table directory by using ADD PARTITION ... LOCATION. Queries on such partitions fail.

  • Partition locations: ADD PARTITION does not support the LOCATION clause. Partition directories must be under the table directory.

  • Names over public endpoints: When you access DLF over a public endpoint, database and table names support only letters, digits, and certain special characters.

Notes

  • Public connectivity: Public access has higher network latency than VPC access. In addition to the public DLF uri, configure a public OSS endpoint to access table files.

Install pypaimon-rust

Install pypaimon-rust 0.4.0 or later, and check the installed version:

pip3 install -U pypaimon-rust
pip3 show pypaimon-rust

Connect to a DLF catalog

Create a SQLContext and register the DLF catalog under the name dlf:

from pypaimon_rust.datafusion import SQLContext
catalog_options = {
'metastore': 'rest',
'uri': 'http://${region_id}-vpc.dlf.aliyuncs.com',
'warehouse': '${catalog_name}',
'dlf.region': '${region_id}',
'token.provider': 'dlf',
'dlf.access-key-id': '${access_key_id}',
'dlf.access-key-secret': '${access_key_secret}',
# For public access, uncomment the next line and enter the public OSS endpoint. Omit it for VPC access.
# 'dlf.oss-endpoint': '${oss_public_endpoint}',
}
ctx = SQLContext()
ctx.register_catalog('dlf', catalog_options)

Parameter

Description

metastore

Set to rest.

uri

The DLF endpoint. Use http://${region_id}-vpc.dlf.aliyuncs.com for VPC access or https://dlfnext.${region_id}.aliyuncs.com for public access.

warehouse

The DLF catalog name, which you can find in the DLF console.

dlf.region

The DLF region ID, such as cn-hangzhou.

token.provider

Set to dlf.

dlf.access-key-id

The RAM user's AccessKey ID.

dlf.access-key-secret

The RAM user's AccessKey Secret.

dlf.oss-endpoint

Public OSS endpoint. Required for public access; optional for VPC access.

Replace ${region_id}, ${catalog_name}, ${access_key_id}, and ${access_key_secret} with actual values.

Note

DLF automatically issues the temporary OSS credentials required to access data files. You do not need to configure an OSS AccessKey separately.

After registration, use table names in the form dlf.<database_name>.<table_name>.

Create a Format Table

ctx.sql("""
CREATE TABLE dlf.demo_db.order_detail (
dt       STRING,
order_id BIGINT,
amount   DOUBLE
) PARTITIONED BY (dt)
WITH (
'type'                        = 'format-table',
'file.format'                 = 'parquet',
'metastore.partitioned-table' = 'true'
)
""")

Define each partition column in the column list before listing it in PARTITIONED BY. Do not specify its type in the PARTITIONED BY clause.

Query data

ctx.sql() returns a list of PyArrow RecordBatch objects. You can convert them to a PyArrow Table or a pandas DataFrame:

import pyarrow as pa
batches = ctx.sql("""
SELECT city, SUM(amount) AS total
FROM dlf.demo_db.order_detail
WHERE dt = '20261105'
GROUP BY city
""")
df = pa.Table.from_batches(batches).to_pandas()

You can use standard DataFusion query syntax, including filters, aggregations, joins, subqueries, and window functions. Filters on partition columns are used for partition pruning.

You can also register local data as a temporary table and join it with a Format Table:

dim = pa.record_batch([['hangzhou', 'beijing'], ['Zhejiang', 'Beijing']], names=['city', 'province'])
ctx.register_batch('dlf.demo_db.city_dim', dim)
batches = ctx.sql("""
SELECT d.province, SUM(o.amount) AS total
FROM dlf.demo_db.order_detail o
JOIN dlf.demo_db.city_dim d ON o.city = d.city
GROUP BY d.province
""")
ctx.sql('DROP TEMPORARY TABLE dlf.demo_db.city_dim')
Note

Queries on a table with managed partitions return data only from registered partitions. Spark and Flink automatically register the partitions they write. If you write partitions by using PyPaimon or ossutil, register them before querying. See Manage partitions.

Write data

The following examples append rows, overwrite a specified partition, and overwrite only the partitions written by the statement:

# Append rows. New partitions in a table with managed partitions are registered automatically.
ctx.sql("""
INSERT INTO dlf.demo_db.order_detail VALUES
('20261105', 1, 10.5),
('20261106', 2, 20.0)
""")
# Overwrite a specified partition.
ctx.sql("""
INSERT OVERWRITE dlf.demo_db.order_detail PARTITION (dt = '20261105')
SELECT 3, 30.0
""")
# Without PARTITION, overwrite only the partitions written by this statement. Other partitions remain.
ctx.sql("INSERT OVERWRITE dlf.demo_db.order_detail SELECT '20261106', 4, 40.0")
Important

In pypaimon-rust 0.4.0, writing a partitioned table can crash the Python process with PanicException: index out of bounds if the partition column is not at the beginning of the table schema. The community has fixed this issue. With version 0.4.0, place partition columns first. For other versions, confirm that your version includes the fix.

Manage partitions

The following statements apply to Format Tables with managed partitions enabled.

Statement

Description

SHOW PARTITIONS dlf.demo_db.order_detail

List registered partitions. Add PARTITION (dt = '20261105') to filter them.

ALTER TABLE dlf.demo_db.order_detail ADD IF NOT EXISTS PARTITION (dt = '20261105')

Register a partition and create its directory. Specify all partition columns.

ALTER TABLE dlf.demo_db.order_detail DROP PARTITION (dt = '20261105')

Unregister a partition and delete the data in its directory.

MSCK REPAIR TABLE dlf.demo_db.order_detail

Scan the table directory and register unregistered partition directories. DROP PARTITIONS unregisters partitions whose directories no longer exist. SYNC PARTITIONS does both.

ANALYZE TABLE dlf.demo_db.order_detail COMPUTE STATISTICS NOSCAN

Update partition file counts, sizes, and last write times. Omit NOSCAN to read file footers and count rows in Parquet and ORC files.

TRUNCATE TABLE dlf.demo_db.order_detail PARTITION (dt = '20261105')

Delete the data files in a partition while retaining its registration and directory. Omit PARTITION to empty the entire table.

Important

If you specify only some partition columns in DROP PARTITION, the statement deletes every matching partition. For example, if a table is partitioned by (dt, region), DROP PARTITION (region = 'us') deletes the partitions and data for region = 'us' across all dates.