Access a Format Table using DataFusion
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, orMERGE INTO. To write data, useINSERT INTOorINSERT 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 PARTITIONdoes not support theLOCATIONclause. 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 |
|
|
Set to |
|
|
The DLF endpoint. Use |
|
|
The DLF catalog name, which you can find in the DLF console. |
|
|
The DLF region ID, such as |
|
|
Set to |
|
|
The RAM user's AccessKey ID. |
|
|
The RAM user's AccessKey Secret. |
| 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.
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')
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")
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 |
|
|
List registered partitions. Add |
|
|
Register a partition and create its directory. Specify all partition columns. |
|
|
Unregister a partition and delete the data in its directory. |
|
|
Scan the table directory and register unregistered partition directories. |
|
|
Update partition file counts, sizes, and last write times. Omit |
|
|
Delete the data files in a partition while retaining its registration and directory. Omit |
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.