Introduction
While Polars supports interaction with SQL, it's recommended that users familiarize themselves with the expression syntax to produce more readable and expressive code. As the DataFrame interface is primary, new features are typically added to the expression API first. However, if you already have an existing SQL codebase or prefer the use of SQL, Polars does offer support for this.
Note
There is no separate SQL engine because Polars translates SQL queries into expressions, which are then executed using its own engine. This approach ensures that Polars maintains its performance and scalability advantages as a native DataFrame library, while still providing users with the ability to work with SQL.
Context
Polars uses the SQLContext object to manage SQL queries. The context contains a mapping of
DataFrame and LazyFrame identifier names to their corresponding datasets1. The example below
starts a SQLContext:
ctx = pl.SQLContext()
Register Dataframes
There are several ways to register DataFrames during SQLContext initialization.
- register all
LazyFrameandDataFrameobjects in the global namespace. - register explicitly via a dictionary mapping, or kwargs.
df = pl.DataFrame({"a": [1, 2, 3]})
lf = pl.LazyFrame({"b": [4, 5, 6]})
# Register all dataframes in the global namespace: registers both "df" and "lf"
ctx = pl.SQLContext(register_globals=True)
# Register an explicit mapping of identifier name to frame
ctx = pl.SQLContext(frames={"table_one": df, "table_two": lf})
# Register frames using kwargs; dataframe df as "df" and lazyframe lf as "lf"
ctx = pl.SQLContext(df=df, lf=lf)
We can also register Pandas DataFrames by converting them to Polars first.
import pandas as pd
df_pandas = pd.DataFrame({"c": [7, 8, 9]})
ctx = pl.SQLContext(df_pandas=pl.from_pandas(df_pandas))
Note
Converting a Pandas DataFrame backed by Numpy will trigger a potentially expensive conversion; however, if the Pandas DataFrame is already backed by Arrow then the conversion will be significantly cheaper (and in some cases close to free).
Once the SQLContext is initialized, we can register additional Dataframes or unregister existing
Dataframes with:
registerregister_globalsregister_manyunregister
Attach an Iceberg catalog
Instead of registering each table, you can attach an Iceberg catalog with register_catalog.
Queries then read its tables by name:
from pyiceberg.catalog import load_catalog
ctx = pl.SQLContext()
ctx.register_catalog("lake", load_catalog("lake"), default_namespace="tpch", default=True)
ctx.execute("SELECT * FROM lineitem") # lake: tpch.lineitem
ctx.execute("SELECT * FROM tpch.lineitem") # the default catalog: tpch.lineitem
ctx.execute("SELECT * FROM lake.tpch.lineitem") # catalog lake: tpch.lineitem
A table name is resolved in this order:
- a CTE, or a table registered in the context;
- a table of the attached catalog that the name starts with;
- a table of the default catalog (with
default=True), where a name without a namespace getsdefault_namespace.
Catalog tables are scanned lazily, like scan_iceberg: a table loads when the query's schema is
first needed. The name after the catalog is passed to the catalog as written, so catalog identifiers
are case-sensitive, and a quoted part such as "a.b" is one part (catalogs differ in whether they
accept a dot in it). A column can be qualified by any trailing part of the table's full name:
lineitem.k, tpch.lineitem.k or lake.tpch.lineitem.k.
With a default catalog, any name that is neither a CTE nor a registered table is looked up in the
catalog, so a misspelled name is too: a missing table raises NoSuchTableError when it loads, which
can be as late as collect(). Writing the full name, such as lake.tpch.lineitem, makes the
catalog explicit.
Catalogs attached inside a with SQLContext() block are detached on exit, like the tables
registered there.
Create, alter, write and drop catalog tables and schemas
CREATE TABLE and DROP TABLE resolve the table name as a query does, and act on the catalog:
ctx.execute("CREATE TABLE orders (id BIGINT NOT NULL, amount DOUBLE)") # empty lake.tpch.orders
ctx.execute("CREATE TABLE big_orders AS SELECT * FROM orders WHERE amount > 1000")
ctx.execute("CREATE TEMP TABLE staging AS SELECT * FROM orders") # registered in the context
ctx.execute("DROP TABLE big_orders") # dropped from the catalog
- The statements run at
execute().CREATE TABLE ... AScommits the rows together with the create, so a failed query creates no table. A catalog that cannot stage a create gets an empty table first, dropped again if filling it fails, unless another writer changed it. NOT NULLmakes a column a required field.LOCATION '...'andWITH ('format-version' = 2, ...)set the table's location and properties.PARTITION BY (region, day(ordered_at), bucket(16, id))partitions the table, with Iceberg's transforms:year,month,day,hour,bucket(N, column)andtruncate(W, column).CREATE TEMP TABLEalways creates a registered table instead.DROP TABLEdrops a registered table of that name first, else the catalog table, permanently.DROP TABLE ... PURGEalso deletes the table's data files.DELETEis not supported on catalog tables.
CREATE SCHEMA and DROP SCHEMA create and drop a namespace. A name that starts with an attached
catalog is a namespace of it, any other name one of the default catalog (not under
default_namespace):
ctx.execute("CREATE SCHEMA IF NOT EXISTS lake.sales WITH ('location' = 's3://bucket/sales')")
ctx.execute("DROP SCHEMA lake.sales") # only an empty namespace: no tables or child namespaces
ALTER TABLE changes a catalog table's columns or properties, one change per statement:
ctx.execute("ALTER TABLE lake.sales.orders ADD COLUMN discount DOUBLE")
ctx.execute("ALTER TABLE lake.sales.orders RENAME COLUMN qty TO quantity")
INSERT INTO appends rows to a catalog table. INSERT OVERWRITE replaces all of its rows, in every
partition. A WHERE in its query selects the new rows, not the ones replaced. DELETE deletes the
rows matching a condition that compares columns with constants. TRUNCATE deletes all rows:
ctx.execute("INSERT INTO lake.sales.orders (id, quantity) VALUES (1, 10), (2, 20)")
ctx.execute("INSERT OVERWRITE lake.sales.orders SELECT * FROM staged_orders")
ctx.execute("DELETE FROM lake.sales.orders WHERE quantity = 0")
ctx.execute("TRUNCATE TABLE lake.sales.orders")
See SQLContext.register_catalog for the details.
Serialized plans
A serialized plan over catalog tables can contain the catalog's properties and storage options, which may include credentials. Treat serialized plans as secret.
Execute queries and collect results
SQL queries are always executed in lazy mode to take advantage of the full set of query planning optimizations, so we have two options to collect the result:
- Set the parameter
eagerto True inSQLContext; this ensures that Polars automatically collects the LazyFrame results fromexecutecalls. - Set the parameter
eagerto True when executing a query withexecute, or explicitly collect the result usingcollect.
We execute SQL queries by calling execute on a SQLContext.
shape: (5, 13)
┌─────┬───────────────────────┬────────┬────────┬───┬─────────┬───────┬────────────┬───────────┐
│ # ┆ Name ┆ Type 1 ┆ Type 2 ┆ … ┆ Sp. Def ┆ Speed ┆ Generation ┆ Legendary │
│ --- ┆ --- ┆ --- ┆ --- ┆ ┆ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ str ┆ str ┆ str ┆ ┆ i64 ┆ i64 ┆ i64 ┆ bool │
╞═════╪═══════════════════════╪════════╪════════╪═══╪═════════╪═══════╪════════════╪═══════════╡
│ 1 ┆ Bulbasaur ┆ Grass ┆ Poison ┆ … ┆ 65 ┆ 45 ┆ 1 ┆ false │
│ 2 ┆ Ivysaur ┆ Grass ┆ Poison ┆ … ┆ 80 ┆ 60 ┆ 1 ┆ false │
│ 3 ┆ Venusaur ┆ Grass ┆ Poison ┆ … ┆ 100 ┆ 80 ┆ 1 ┆ false │
│ 3 ┆ VenusaurMega Venusaur ┆ Grass ┆ Poison ┆ … ┆ 120 ┆ 80 ┆ 1 ┆ false │
│ 4 ┆ Charmander ┆ Fire ┆ null ┆ … ┆ 50 ┆ 65 ┆ 1 ┆ false │
└─────┴───────────────────────┴────────┴────────┴───┴─────────┴───────┴────────────┴───────────┘
Execute queries from multiple sources
SQL queries can be executed just as easily from multiple sources. In the example below, we register:
- a CSV file (loaded lazily)
- a NDJSON file (loaded lazily)
- a Pandas DataFrame
And join them together using SQL. Lazy reading lets you only load the necessary rows and columns from the files.
In the same way, it's possible to register cloud datalakes (S3, Azure Data Lake). A PyArrow dataset
can point to the datalake, then Polars can read it with scan_pyarrow_dataset.
# Input data:
# products_masterdata.csv with schema {'product_id': Int64, 'product_name': String}
# products_categories.json with schema {'product_id': Int64, 'category': String}
# sales_data is a Pandas DataFrame with schema {'product_id': Int64, 'sales': Int64}
with pl.SQLContext(
products_masterdata=pl.scan_csv("docs/assets/data/products_masterdata.csv"),
products_categories=pl.scan_ndjson("docs/assets/data/products_categories.json"),
sales_data=pl.from_pandas(sales_data),
eager=True,
) as ctx:
query = """
SELECT
product_id,
product_name,
category,
sales
FROM
products_masterdata
LEFT JOIN products_categories USING (product_id)
LEFT JOIN sales_data USING (product_id)
"""
print(ctx.execute(query))
shape: (5, 4)
┌────────────┬──────────────┬────────────┬───────┐
│ product_id ┆ product_name ┆ category ┆ sales │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ str ┆ str ┆ i64 │
╞════════════╪══════════════╪════════════╪═══════╡
│ 4 ┆ Product D ┆ Category 2 ┆ 250 │
│ 3 ┆ Product C ┆ Category 2 ┆ 150 │
│ 1 ┆ Product A ┆ Category 1 ┆ 100 │
│ 2 ┆ Product B ┆ Category 1 ┆ 200 │
│ 5 ┆ Product E ┆ Category 3 ┆ 300 │
└────────────┴──────────────┴────────────┴───────┘
Compatibility
Polars does not support the complete SQL specification, but it does support a subset of the most common statement types.
Note
Where possible, Polars aims to follow PostgreSQL syntax definitions and function behaviour.
For example, here is a non-exhaustive list of some of the supported functionality:
- Write a
CREATEstatements:CREATE TABLE xxx AS ... - Write a
SELECTstatements containing:WHERE,ORDER,LIMIT,GROUP BY,UNIONandJOINclauses ... - Write Common Table Expressions (CTE's) such as:
WITH tablename AS - Explain a query:
EXPLAIN SELECT ... - List registered tables:
SHOW TABLES - Drop a table:
DROP TABLE [IF EXISTS] tablename - Truncate a table:
TRUNCATE TABLE [IF EXISTS] tablename
The following are some features that are not yet supported:
INSERT,UPDATEorDELETEstatements- Meta queries such as
ANALYZE
In the upcoming sections we will cover each of the statements in more detail.
-
Additionally it also tracks the common table expressions as well. ↩