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 offers support for this.

Context

Polars uses the SQLContext object to manage SQL queries. The context contains a mapping of DataFrame and LazyFrame identifier names to their corresponding datasets¹. The example below starts a SQLContext:

ctx = pl.SQLContext()

Register Dataframes

There are several ways to register DataFrames during SQLContext initialization.

• register all LazyFrame and DataFrame objects 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))

Once the SQLContext is initialized, we can register additional Dataframes or unregister existing Dataframes with:

• register
• register_globals
• register_many
• unregister

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:

1. a CTE, or a table registered in the context;
2. a table of the attached catalog that the name starts with;
3. a table of the default catalog (with default=True), where a name without a namespace gets default_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. 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, a table name that is neither a CTE nor a registered table goes to the catalog, so a misspelled name is looked up there too. A catalog table that does not exist raises NoSuchTableError when it loads, which can be as late as collect(). A name that no catalog can resolve raises "relation ... was not found" instead: for example lineitem when the default catalog has no default_namespace, or tpch.lineitem when an attached catalog named tpch without a default_namespace takes precedence over that namespace. 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. If that detaches the default catalog, the default catalog on entry is restored.

DROP TABLE [IF EXISTS] <name> [PURGE] resolves the name as a query does: a registered table of that name is dropped from the context, else the catalog table is dropped from its catalog. Once no registered table matches, DROP TABLE IF EXISTS of a name reaches the catalog. The drop is permanent and runs at execute(). PURGE also asks the catalog to delete the table's data files, and only applies to catalog tables. A DROP TABLE of a catalog table takes one name.

CREATE TABLE <name> (<column>, ...) resolves the name as a query does and creates an empty table in the catalog it resolves to, at execute(). With a default catalog, that includes names such as tpch.t; CREATE TEMP TABLE always creates a registered table instead, and its name cannot start with an attached catalog. NOT NULL makes a column a required field, and LOCATION '...' or WITH ('location' = '...', 'format-version' = 2) sets the table's location and properties. CREATE TABLE ... AS SELECT, TRUNCATE and DELETE on catalog tables are not supported yet. TRUNCATE TABLE IF EXISTS of a name that does not start with an attached catalog only acts on a registered table.

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 eager to True in SQLContext; this ensures that Polars automatically collects the LazyFrame results from execute calls.
• Set the parameter eager to True when executing a query with execute, or explicitly collect the result using collect.

We execute SQL queries by calling execute on a SQLContext.

pokemon = pl.scan_csv("docs/assets/data/pokemon.csv")
with pl.SQLContext(register_globals=True, eager=True) as ctx:
    df_small = ctx.execute("SELECT * from pokemon LIMIT 5")
    print(df_small)

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   │
╞════════════╪══════════════╪════════════╪═══════╡
│ 2          ┆ Product B    ┆ Category 1 ┆ 200   │
│ 1          ┆ Product A    ┆ Category 1 ┆ 100   │
│ 3          ┆ Product C    ┆ Category 2 ┆ 150   │
│ 4          ┆ Product D    ┆ Category 2 ┆ 250   │
│ 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.

For example, here is a non-exhaustive list of some of the supported functionality:

• Write a CREATE statements: CREATE TABLE xxx AS ...
• Write a SELECT statements containing:WHERE,ORDER,LIMIT,GROUP BY,UNION and JOIN clauses ...
• 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, UPDATE or DELETE statements
• Meta queries such as ANALYZE

In the upcoming sections we will cover each of the statements in more detail.