SELECT

In Polars SQL, the SELECT statement is used to retrieve data from a table into a DataFrame. The basic syntax of a SELECT statement in Polars SQL is as follows:

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, etc. are the columns that you want to select from the table. You can also use the wildcard * to select all columns. table_name is the name of the table or that you want to retrieve data from. In the sections below we will cover some of the more common SELECT variants

Python

register · execute

df = pl.DataFrame(
    {
        "country": ["USA", "USA", "USA", "USA", "USA", "Netherlands"],
        "city": [
            "New York",
            "Los Angeles",
            "Chicago",
            "Houston",
            "Phoenix",
            "Amsterdam",
        ],
        "population": [8399000, 3997000, 2705000, 2320000, 1680000, 900000],
    }
)

ctx = pl.SQLContext(population=df, eager=True)

print(ctx.execute("SELECT * FROM population"))

shape: (6, 3)
┌─────────────┬─────────────┬────────────┐
│ country     ┆ city        ┆ population │
│ ---         ┆ ---         ┆ ---        │
│ str         ┆ str         ┆ i64        │
╞═════════════╪═════════════╪════════════╡
│ USA         ┆ New York    ┆ 8399000    │
│ USA         ┆ Los Angeles ┆ 3997000    │
│ USA         ┆ Chicago     ┆ 2705000    │
│ USA         ┆ Houston     ┆ 2320000    │
│ USA         ┆ Phoenix     ┆ 1680000    │
│ Netherlands ┆ Amsterdam   ┆ 900000     │
└─────────────┴─────────────┴────────────┘

GROUP BY

The GROUP BY statement is used to group rows in a table by one or more columns and compute aggregate functions on each group.

Python

execute

result = ctx.execute(
    """
        SELECT country, AVG(population) as avg_population
        FROM population
        GROUP BY country
    """
)
print(result)

shape: (2, 2)
┌─────────────┬────────────────┐
│ country     ┆ avg_population │
│ ---         ┆ ---            │
│ str         ┆ f64            │
╞═════════════╪════════════════╡
│ USA         ┆ 3.8202e6       │
│ Netherlands ┆ 900000.0       │
└─────────────┴────────────────┘

ORDER BY

The ORDER BY statement is used to sort the result set of a query by one or more columns in ascending or descending order.

Python

execute

result = ctx.execute(
    """
        SELECT city, population
        FROM population
        ORDER BY population
    """
)
print(result)

shape: (6, 2)
┌─────────────┬────────────┐
│ city        ┆ population │
│ ---         ┆ ---        │
│ str         ┆ i64        │
╞═════════════╪════════════╡
│ Amsterdam   ┆ 900000     │
│ Phoenix     ┆ 1680000    │
│ Houston     ┆ 2320000    │
│ Chicago     ┆ 2705000    │
│ Los Angeles ┆ 3997000    │
│ New York    ┆ 8399000    │
└─────────────┴────────────┘

JOIN

Python

register_many · execute

income = pl.DataFrame(
    {
        "country": [
            "USA",
            "USA",
            "USA",
            "USA",
            "Netherlands",
            "Netherlands",
            "Netherlands",
        ],
        "city": [
            "New York",
            "Los Angeles",
            "Chicago",
            "Houston",
            "Amsterdam",
            "Rotterdam",
            "Utrecht",
        ],
        "income": [55000, 62000, 48000, 52000, 42000, 38000, 41000],
    }
)
ctx.register_many(income=income)
result = ctx.execute(
    """
        SELECT income.*, population.population
        FROM population
        LEFT JOIN income ON population.city = income.city
    """
)
print(result)

shape: (6, 4)
┌─────────────┬─────────────┬────────┬────────────┐
│ country     ┆ city        ┆ income ┆ population │
│ ---         ┆ ---         ┆ ---    ┆ ---        │
│ str         ┆ str         ┆ i64    ┆ i64        │
╞═════════════╪═════════════╪════════╪════════════╡
│ USA         ┆ Los Angeles ┆ 62000  ┆ 3997000    │
│ USA         ┆ New York    ┆ 55000  ┆ 8399000    │
│ USA         ┆ Houston     ┆ 52000  ┆ 2320000    │
│ USA         ┆ Chicago     ┆ 48000  ┆ 2705000    │
│ Netherlands ┆ Amsterdam   ┆ 42000  ┆ 900000     │
│ null        ┆ null        ┆ null   ┆ 1680000    │
└─────────────┴─────────────┴────────┴────────────┘

Functions

Polars provides a wide range of SQL functions, including:

Mathematical functions: ABS, EXP, LOG, ASIN, ACOS, ATAN, etc.
String functions: LOWER, UPPER, LTRIM, RTRIM, STARTS_WITH,ENDS_WITH.
Aggregation functions: SUM, AVG, MIN, MAX, COUNT, STDDEV, FIRST etc.
Array functions: EXPLODE, UNNEST,ARRAY_SUM,ARRAY_REVERSE, etc.

For a full list of supported functions go the API documentation. The example below demonstrates how to use a function in a query

Python

execute

result = ctx.execute(
    """
        SELECT city, population
        FROM population
        WHERE STARTS_WITH(country,'U')
    """
)
print(result)

shape: (5, 2)
┌─────────────┬────────────┐
│ city        ┆ population │
│ ---         ┆ ---        │
│ str         ┆ i64        │
╞═════════════╪════════════╡
│ New York    ┆ 8399000    │
│ Los Angeles ┆ 3997000    │
│ Chicago     ┆ 2705000    │
│ Houston     ┆ 2320000    │
│ Phoenix     ┆ 1680000    │
└─────────────┴────────────┘

Table Functions

In the examples earlier we first generated a DataFrame which we registered in the SQLContext. Polars also support directly reading from CSV, Parquet, JSON and IPC in your SQL query using table functions read_xxx.

Python

execute

result = ctx.execute(
    """
        SELECT *
        FROM read_csv('docs/assets/data/iris.csv')
    """
)
print(result)

shape: (150, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ sepal_length ┆ sepal_width ┆ petal_length ┆ petal_width ┆ species   │
│ ---          ┆ ---         ┆ ---          ┆ ---         ┆ ---       │
│ f64          ┆ f64         ┆ f64          ┆ f64         ┆ str       │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 5.1          ┆ 3.5         ┆ 1.4          ┆ 0.2         ┆ Setosa    │
│ 4.9          ┆ 3.0         ┆ 1.4          ┆ 0.2         ┆ Setosa    │
│ 4.7          ┆ 3.2         ┆ 1.3          ┆ 0.2         ┆ Setosa    │
│ 4.6          ┆ 3.1         ┆ 1.5          ┆ 0.2         ┆ Setosa    │
│ 5.0          ┆ 3.6         ┆ 1.4          ┆ 0.2         ┆ Setosa    │
│ …            ┆ …           ┆ …            ┆ …           ┆ …         │
│ 6.7          ┆ 3.0         ┆ 5.2          ┆ 2.3         ┆ Virginica │
│ 6.3          ┆ 2.5         ┆ 5.0          ┆ 1.9         ┆ Virginica │
│ 6.5          ┆ 3.0         ┆ 5.2          ┆ 2.0         ┆ Virginica │
│ 6.2          ┆ 3.4         ┆ 5.4          ┆ 2.3         ┆ Virginica │
│ 5.9          ┆ 3.0         ┆ 5.1          ┆ 1.8         ┆ Virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘

The path can also be a glob, or a list of paths or globs, as in read_parquet(['a.parquet', 'b.parquet']).

read_csv also accepts named options after the path, with the same names and meaning as the options of scan_csv. Write them as name => value (or name := value):

Python

execute

result = ctx.execute(
    """
        SELECT *
        FROM read_csv('docs/assets/data/iris.csv', infer_schema => false, skip_rows_after_header => 147)
    """
)
print(result)

shape: (3, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ sepal_length ┆ sepal_width ┆ petal_length ┆ petal_width ┆ species   │
│ ---          ┆ ---         ┆ ---          ┆ ---         ┆ ---       │
│ str          ┆ str         ┆ str          ┆ str         ┆ str       │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 6.5          ┆ 3           ┆ 5.2          ┆ 2           ┆ Virginica │
│ 6.2          ┆ 3.4         ┆ 5.4          ┆ 2.3         ┆ Virginica │
│ 5.9          ┆ 3           ┆ 5.1          ┆ 1.8         ┆ Virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘

The supported options are:

Layout: has_header, separator, quote_char, comment_prefix, skip_rows, skip_rows_after_header, skip_lines, encoding.
Names and types: new_columns, schema, schema_overrides, infer_schema, infer_schema_length, infer_schema_files, try_parse_dates, decimal_comma.
Values and errors: null_values, ignore_errors.
Ragged rows and multiple files: truncate_ragged_lines, missing_columns, extra_columns, include_file_paths.

Values are SQL literals:

NULL stands for None, as in infer_schema_length => NULL to infer the schema from the whole file, or quote_char => NULL to turn quoting off.
Lists use brackets: new_columns => ['a', 'b'].
schema, schema_overrides and per-column null_values take a dictionary, with types written as SQL type names: schema_overrides => {'id': 'INT', 'name': 'VARCHAR'}. schema_overrides also accepts a list of type names, by position.
Escape sequences need an E'' string, as in separator => E'\t' for tab-separated files.

Unlike scan_csv, read_csv tries to parse dates by default (try_parse_dates => true).

Some options are unstable: those marked as such in the scan_csv API reference are unstable in read_csv too, and may be changed at any point without it being considered a breaking change.

read_ndjson reads newline-delimited JSON (one object per line) and accepts the options of scan_ndjson in the same way: schema, schema_overrides, infer_schema_length, ignore_errors and include_file_paths.

Python

execute

result = ctx.execute(
    """
        SELECT *
        FROM read_ndjson('docs/assets/data/orders.ndjson', schema_overrides => {'date': 'DATE'})
    """
)
print(result)

shape: (3, 4)
┌─────┬────────────┬─────────────────┬───────────┐
│ id  ┆ date       ┆ customer        ┆ items     │
│ --- ┆ ---        ┆ ---             ┆ ---       │
│ i64 ┆ date       ┆ struct[2]       ┆ list[i64] │
╞═════╪════════════╪═════════════════╪═══════════╡
│ 1   ┆ 2024-03-01 ┆ {"Ann","Ghent"} ┆ [3, 7]    │
│ 2   ┆ 2024-03-04 ┆ {"Bo","Leuven"} ┆ [1]       │
│ 3   ┆ 2024-03-09 ┆ {"Cy","Ghent"}  ┆ [2, 5, 8] │
└─────┴────────────┴─────────────────┴───────────┘

The schema is inferred from the first 100 rows, so a key that first appears later is dropped. Use infer_schema_length => NULL to infer it from the whole file.
A nested object read as 'VARCHAR' keeps its JSON text.
read_json reads the same files, but takes only the path. For a JSON array or a pretty-printed JSON document, use pl.read_json instead.

read_parquet accepts the options of scan_parquet in the same way: schema, missing_columns, extra_columns, hive_partitioning, hive_schema, try_parse_hive_dates and include_file_paths.

Python

execute

result = ctx.execute(
    """
        SELECT id, bool_col, path
        FROM read_parquet('docs/assets/data/alltypes_plain.parquet', include_file_paths => 'path')
        LIMIT 3
    """
)
print(result)

shape: (3, 3)
┌─────┬──────────┬─────────────────────────────────┐
│ id  ┆ bool_col ┆ path                            │
│ --- ┆ ---      ┆ ---                             │
│ i32 ┆ bool     ┆ str                             │
╞═════╪══════════╪═════════════════════════════════╡
│ 4   ┆ true     ┆ docs/assets/data/alltypes_plai… │
│ 5   ┆ false    ┆ docs/assets/data/alltypes_plai… │
│ 6   ┆ true     ┆ docs/assets/data/alltypes_plai… │
└─────┴──────────┴─────────────────────────────────┘

Unlike scan_parquet, read_parquet reads hive partitions by default when given one path, for a glob or a single file too: a path such as data/k=1/part.parquet adds a column k. Every key=value folder in such a path counts, so an absolute path can also pick up columns from its parent folders. A list of paths reads them only when it is a single directory, as in scan_parquet. Set hive_partitioning to true or false to choose either way.
When reading several files, the first one defines the columns, unless schema is given. Use missing_columns => 'insert' and extra_columns => 'ignore' for files that differ from it.
As for read_csv, options marked unstable in the scan_parquet API reference are unstable in read_parquet too.

read_ipc accepts the options of scan_ipc in the same way: hive_partitioning, hive_schema, try_parse_hive_dates and include_file_paths.

Python

execute

pl.read_csv("docs/assets/data/iris.csv").write_ipc("docs/assets/data/iris.feather")

result = ctx.execute(
    """
        SELECT species, sepal_length, path
        FROM read_ipc('docs/assets/data/iris.feather', include_file_paths => 'path')
        LIMIT 3
    """
)
print(result)

shape: (3, 3)
┌─────────┬──────────────┬───────────────────────────────┐
│ species ┆ sepal_length ┆ path                          │
│ ---     ┆ ---          ┆ ---                           │
│ str     ┆ f64          ┆ str                           │
╞═════════╪══════════════╪═══════════════════════════════╡
│ Setosa  ┆ 5.1          ┆ docs/assets/data/iris.feather │
│ Setosa  ┆ 4.9          ┆ docs/assets/data/iris.feather │
│ Setosa  ┆ 4.7          ┆ docs/assets/data/iris.feather │
└─────────┴──────────────┴───────────────────────────────┘

Hive partitions are read as for read_parquet: by default for one path, and for a list only when it is a single directory.
As for read_parquet, options marked unstable in the scan_ipc API reference are unstable in read_ipc too.