While Polars does support writing queries in SQL, it's recommended that users familiarize themselves with the expression syntax for more readable and expressive code. As a primarily DataFrame library, new features will typically be added to the expression API first. However, if you already have an existing SQL codebase or prefer to use SQL, Polars also offers support for SQL queries.
Note
In Polars, there is no separate SQL engine because Polars translates SQL queries into expressions, which are then executed using its built-in execution 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 queries.
ContextPolars uses the SQLContext to manage SQL queries . The context contains a dictionary mapping DataFrames and LazyFrames names to their corresponding datasets1. The example below starts a SQLContext:
There are 2 ways to register DataFrames in the SQLContext:
LazyFrames and DataFrames in the global namespacePython
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)
# Other option: register 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.
Python
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 to Polars triggers a conversion to the Arrow format. This conversion has a computation cost. Converting a Pandas DataFrame backed by Arrow on the other hand will be free or almost free.
Once the SQLContext is initialized, we can register additional Dataframes or unregister existing Dataframes with:
registerregister_globalsregister_manyunregisterSQL queries are always executed in lazy mode to benefit from lazy optimizations, so we have 2 options to collect the result:
eager_execution to True in SQLContext. With this parameter, Polars will automatically collect SQL resultseager to True when executing a query with execute, or collect the result with collect.We execute SQL queries by calling execute on a SQLContext.
Python
# For local files use scan_csv instead
pokemon = pl.read_csv(
"https://gist.githubusercontent.com/ritchie46/cac6b337ea52281aa23c049250a4ff03/raw/89a957ff3919d90e6ef2d34235e6bf22304f3366/pokemon.csv"
)
ctx = pl.SQLContext(register_globals=True, eager_execution=True)
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 │
└─────┴───────────────────────┴────────┴────────┴───┴─────────┴───────┴────────────┴───────────┘
SQL queries can be executed just as easily from multiple sources. In the example below, we register :
And we join them together with SQL. Lazy reading allows to 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.
Python
# Input data:
# products_masterdata.csv with schema {'product_id': Int64, 'product_name': Utf8}
# products_categories.json with schema {'product_id': Int64, 'category': Utf8}
# sales_data is a Pandas DataFrame with schema {'product_id': Int64, 'sales': Int64}
ctx = pl.SQLContext(
products_masterdata=pl.scan_csv("products_masterdata.csv"),
products_categories=pl.scan_ndjson("products_categories.json"),
sales_data=pl.from_pandas(sales_data),
eager_execution=True,
)
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 │
╞════════════╪══════════════╪════════════╪═══════╡
│ 1 ┆ Product A ┆ Category 1 ┆ 100 │
│ 2 ┆ Product B ┆ Category 1 ┆ 200 │
│ 3 ┆ Product C ┆ Category 2 ┆ 150 │
│ 4 ┆ Product D ┆ Category 2 ┆ 250 │
│ 5 ┆ Product E ┆ Category 3 ┆ 300 │
└────────────┴──────────────┴────────────┴───────┘
Polars does not support the full SQL language, in Polars you are allowed to:
CREATE statements CREATE TABLE xxx AS ...SELECT statements with all generic elements (GROUP BY, WHERE,ORDER,LIMIT,JOIN, ...)WITH tablename AS)SHOW TABLESThe following is not yet supported:
INSERT, UPDATE or DELETE statementsSELECT p.Name from pokemon AS p)ANALYZE, EXPLAINIn the upcoming sections we will cover each of the statements in more details.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4