Skip to main content

SQL Query

PyPaimon supports executing SQL queries on Paimon tables, powered by pypaimon-rust and DataFusion.

Installation​

SQL queries require Python 3.10 or newer and the SQL extra:

pip install 'pypaimon[sql]'

This installs the Rust bindings and DataFusion dependencies used by SQL queries.

Usage​

Create a SQLContext, register one or more catalogs with their options, and run SQL queries.

Basic Query​

from pypaimon_rust.datafusion import SQLContext
import pyarrow as pa

ctx = SQLContext()
ctx.register_catalog("paimon", {"warehouse": "/path/to/warehouse"})
ctx.set_current_catalog("paimon")
ctx.set_current_database("default")

# Execute SQL and get PyArrow RecordBatches
batches = ctx.sql("SELECT * FROM my_table")
table = pa.Table.from_batches(batches)
print(table)

# Convert to Pandas DataFrame
df = table.to_pandas()
print(df)

SQLContext can also be imported from pypaimon:

from pypaimon import SQLContext

Table Reference Format​

The default catalog and default database can be configured via set_current_catalog() and set_current_database(), so you can reference tables in multiple ways:

# Direct table name (uses default database)
ctx.sql("SELECT * FROM my_table")

# Two-part: database.table
ctx.sql("SELECT * FROM mydb.my_table")

# Three-part: catalog.database.table
ctx.sql("SELECT * FROM paimon.mydb.my_table")

Multi-Catalog Query​

SQLContext supports registering multiple catalogs for cross-catalog queries:

ctx = SQLContext()
ctx.register_catalog("a", {"warehouse": "/path/to/warehouse_a"})
ctx.register_catalog("b", {
"metastore": "rest",
"uri": "http://localhost:8080",
"warehouse": "warehouse_b",
})
ctx.set_current_catalog("a")
ctx.set_current_database("default")

# Cross-catalog join
batches = ctx.sql("""
SELECT a_users.name, b_orders.amount
FROM a.default.users AS a_users
JOIN b.default.orders AS b_orders ON a_users.id = b_orders.user_id
""")

Register Arrow Batches​

You can register PyArrow RecordBatches as temporary tables:

batch = pa.record_batch([[1, 2], ["alice", "bob"]], names=["id", "name"])
ctx.register_batch("paimon.default.my_temp", batch)
batches = ctx.sql("SELECT * FROM paimon.default.my_temp")

Supported SQL Syntax​

The SQL engine is powered by Apache DataFusion, which supports a rich set of SQL syntax. For the full SQL reference, see the paimon-rust SQL documentation which covers:

  • DDL: CREATE SCHEMA, CREATE TABLE (with PARTITIONED BY, PRIMARY KEY, WITH options), DROP TABLE, ALTER TABLE, CREATE TEMPORARY TABLE/VIEW
  • DML: INSERT INTO, INSERT OVERWRITE (dynamic/static partitions), UPDATE, DELETE, MERGE INTO, TRUNCATE TABLE
  • Procedures: CALL sys.create_tag, CALL sys.rollback_to, etc.
  • Queries: SELECT, column projection, filter pushdown, COUNT(*) pushdown
  • Time Travel: VERSION AS OF, TIMESTAMP AS OF
  • Vector Search: vector_search() table function
  • Full-Text Search: full_text_search() table function
  • Dynamic Options: SET / RESET
  • System Tables: $options, $schemas, $snapshots, $tags, $manifests

For the DataFusion query syntax (JOINs, aggregations, subqueries, CTEs, window functions, etc.), see the DataFusion SQL documentation.

SQL Command​

Execute SQL queries on Paimon tables directly from the command line. This feature is powered by pypaimon-rust and DataFusion.

Install the SQL extra and configure paimon.yaml as shown in CLI basic usage. Use database-qualified table names unless you have explicitly selected a default database.

One-Shot Query​

Execute a single SQL query and display the result:

paimon sql "SELECT * FROM users LIMIT 10"

Output:

id name age city
1 Alice 25 Beijing
2 Bob 30 Shanghai
3 Charlie 35 Guangzhou

Options:

  • --format, -f: Output format: table (default) or json

Examples:

# Direct table name (uses default catalog and database)
paimon sql "SELECT * FROM users"

# Two-part: database.table
paimon sql "SELECT * FROM mydb.users"

# Query with filter and aggregation
paimon sql "SELECT city, COUNT(*) AS cnt FROM users GROUP BY city ORDER BY cnt DESC"

# Output as JSON
paimon sql "SELECT * FROM users LIMIT 5" --format json

JSON output represents timestamps as ISO 8601 strings, preserving fractional seconds (up to nanosecond precision) and timezone offsets when present. SQL NULL values are represented as JSON null.

Interactive REPL​

Start an interactive SQL session by running paimon sql without a query argument. The REPL supports arrow keys for line editing, and command history is persisted across sessions in ~/.paimon_history.

paimon sql

Output:

____ _
/ __ \____ _(_)___ ___ ____ ____
/ /_/ / __ `/ / __ `__ \/ __ \/ __ \
/ ____/ /_/ / / / / / / / /_/ / / / /
/_/ \__,_/_/_/ /_/ /_/\____/_/ /_/

Powered by pypaimon-rust + DataFusion
Type 'help' for usage, 'exit' to quit.

paimon> SHOW DATABASES;
default
mydb

paimon> USE mydb;
Using database 'mydb'.

paimon> SHOW TABLES;
orders
users

paimon> SELECT count(*) AS cnt
> FROM users
> WHERE age > 18;
cnt
42
(1 row in 0.05s)

paimon> exit
Bye!

SQL statements end with ; and can span multiple lines. The continuation prompt > indicates that more input is expected.

REPL Commands:

CommandDescription
USE <database>;Switch the default database
SHOW DATABASES;List all databases
SHOW TABLES;List tables in the current database
SELECT ...;Execute a SQL query
helpShow usage information
exit / quitExit the REPL

See Supported SQL Syntax for the SQL feature reference.