SQLFrame implements the PySpark DataFrame API in order to enable running transformation pipelines directly on database engines - no Spark clusters or dependencies required.
SQLFrame currently supports the following engines:
- BigQuery
- Databricks
- DuckDB
- Postgres
- Snowflake
- Spark
- GizmoSQL (community maintained)
- Redshift (community maintained)
SQLFrame also has a "Standalone" session that be used to generate SQL without any connection to a database engine.
SQLFrame is great for:
- Users who want a DataFrame API that leverages the full power of their engine to do the processing
- Users who want to run PySpark code quickly locally without the overhead of starting a Spark session
- Users who want a SQL representation of their DataFrame code for debugging or sharing with others
- Users who want to run PySpark DataFrame code without the complexity of using Spark for processing
Installation
# BigQuery
pip install "sqlframe[bigquery]"
# Databricks
pip install "sqlframe[databricks]"
# DuckDB
pip install "sqlframe[duckdb]"
# Postgres
pip install "sqlframe[postgres]"
# Snowflake
pip install "sqlframe[snowflake]"
# Spark
pip install "sqlframe[spark]"
# Redshift (in development)
pip install "sqlframe[redshift]"
# Standalone
pip install sqlframe
# Or from conda-forge
conda install -c conda-forge sqlframe
See specific engine documentation for additional setup instructions.
Configuration
SQLFrame generates consistently accurate yet complex SQL for engine execution. However, when using df.sql(optimize=True), it produces more human-readable SQL. For details on how to configure this output and leverage OpenAI to enhance the SQL, see Generated SQL Configuration.
SQLFrame by default uses the Spark dialect for input and output. This can be changed to make SQLFrame feel more like a native DataFrame API for the engine you are using. See Input and Output Dialect Configuration.
Activating SQLFrame
SQLFrame can either replace pyspark imports or be used alongside them. To replace pyspark imports, use the activate function to set the engine to use.
from sqlframe import activate
# Activate SQLFrame to run directly on DuckDB
activate(engine="duckdb")
from pyspark.sql import SparkSession
session = SparkSession.builder.getOrCreate()
SQLFrame can also be directly imported which both maintains pyspark imports but also allows for a more engine-native DataFrame API:
from sqlframe.duckdb import DuckDBSession
session = DuckDBSession.builder.getOrCreate()
Example Usage
from sqlframe import activate
# Activate SQLFrame to run directly on BigQuery
activate(engine="bigquery")
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql import Window
session = SparkSession.builder.getOrCreate()
table_path = '"bigquery-public-data".samples.natality'
# Top 5 years with the greatest year-over-year % change in new families with single child
df = (
session.table(table_path)
.where(F.col("ever_born") == 1)
.groupBy("year")
.agg(F.count("*").alias("num_single_child_families"))
.withColumn(
"last_year_num_single_child_families",
F.lag(F.col("num_single_child_families"), 1).over(Window.orderBy("year"))
)
.withColumn(
"percent_change",
(F.col("num_single_child_families") - F.col("last_year_num_single_child_families"))
/ F.col("last_year_num_single_child_families")
)
.orderBy(F.abs(F.col("percent_change")).desc())
.select(
F.col("year").alias("year"),
F.format_number("num_single_child_families", 0).alias("new families single child"),
F.format_number(F.col("percent_change") * 100, 2).alias("percent change"),
)
.limit(5)
)
>>> df.sql(optimize=True)
WITH `t94228` AS (
SELECT
`natality`.`year` AS `year`,
COUNT(*) AS `num_single_child_families`
FROM `bigquery-public-data`.`samples`.`natality` AS `natality`
WHERE
`natality`.`ever_born` = 1
GROUP BY
`natality`.`year`
), `t39093` AS (
SELECT
`t94228`.`year` AS `year`,
`t94228`.`num_single_child_families` AS `num_single_child_families`,
LAG(`t94228`.`num_single_child_families`, 1) OVER (ORDER BY `t94228`.`year`) AS `last_year_num_single_child_families`
FROM `t94228` AS `t94228`
)
SELECT
`t39093`.`year` AS `year`,
FORMAT('%\'.0f', ROUND(CAST(`t39093`.`num_single_child_families` AS FLOAT64), 0)) AS `new families single child`,
FORMAT('%\'.2f', ROUND(CAST((((`t39093`.`num_single_child_families` - `t39093`.`last_year_num_single_child_families`) / `t39093`.`last_year_num_single_child_families`) * 100) AS FLOAT64), 2)) AS `percent change`
FROM `t39093` AS `t39093`
ORDER BY
ABS(`percent_change`) DESC
LIMIT 5
>>> df.show()
+------+---------------------------+----------------+
| year | new families single child | percent change |
+------+---------------------------+----------------+
| 1989 | 1,650,246 | 25.02 |
| 1974 | 783,448 | 14.49 |
| 1977 | 1,057,379 | 11.38 |
| 1985 | 1,308,476 | 11.15 |
| 1975 | 868,985 | 10.92 |
+------+---------------------------+----------------+
1 maintainer
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Filter files by name, interpreter, ABI, and platform.
If you're not sure about the file name format, learn more about wheel file names.
Copy a direct link to the current filters
File details
Details for the file sqlframe-4.4.0.tar.gz.
File metadata
- Download URL: sqlframe-4.4.0.tar.gz
- Upload date:
- Size: 29.7 MB
- Tags: Source
- Uploaded using Trusted Publishing? Yes
- Uploaded via:
twine/7.0.0 CPython/3.13.14
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
ba0716cdc71bac9aefc2fd76c15a118b7ccb61a3ca79e645935b95ec9c609113
|
|
| MD5 |
d7af76401b7772e9738ed0bb9bd57f46
|
|
| BLAKE2b-256 |
e7b60033601d1eac72422d0135bc4ac676fd16ddbede4b834fcb30f11f2d5682
|
Provenance
The following attestation bundles were made for sqlframe-4.4.0.tar.gz:
Publisher:
publish.workflow.yaml on eakmanrq/sqlframe
-
Statement:
-
Statement type:
https://in-toto.io/Statement/v1 -
Predicate type:
https://docs.pypi.org/attestations/publish/v1 -
Subject name:
sqlframe-4.4.0.tar.gz -
Subject digest:
ba0716cdc71bac9aefc2fd76c15a118b7ccb61a3ca79e645935b95ec9c609113 - Sigstore transparency entry: 2304687632
- Sigstore integration time:
-
Permalink:
eakmanrq/sqlframe@7c98f85ae1472512dcf10a88c623c35769145a4d -
Branch / Tag:
refs/tags/v4.4.0 - Owner: https://github.com/eakmanrq
-
Access:
public
-
Token Issuer:
https://token.actions.githubusercontent.com -
Runner Environment:
github-hosted -
Publication workflow:
publish.workflow.yaml@7c98f85ae1472512dcf10a88c623c35769145a4d -
Trigger Event:
push
-
Statement type:
File details
Details for the file sqlframe-4.4.0-py3-none-any.whl.
File metadata
- Download URL: sqlframe-4.4.0-py3-none-any.whl
- Upload date:
- Size: 222.2 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? Yes
- Uploaded via:
twine/7.0.0 CPython/3.13.14
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
ff73474ab8b21dc5eca9b650dd7191a682ddedc55ef7472cf60a3e91783ca43b
|
|
| MD5 |
6a6b36aa9639fa6e5a4d83edae57eedb
|
|
| BLAKE2b-256 |
ae81b251146309b797a8054804acf0ad19c24b0ac4bac0581a6332fd686be923
|
Provenance
The following attestation bundles were made for sqlframe-4.4.0-py3-none-any.whl:
Publisher:
publish.workflow.yaml on eakmanrq/sqlframe
-
Statement:
-
Statement type:
https://in-toto.io/Statement/v1 -
Predicate type:
https://docs.pypi.org/attestations/publish/v1 -
Subject name:
sqlframe-4.4.0-py3-none-any.whl -
Subject digest:
ff73474ab8b21dc5eca9b650dd7191a682ddedc55ef7472cf60a3e91783ca43b - Sigstore transparency entry: 2304687726
- Sigstore integration time:
-
Permalink:
eakmanrq/sqlframe@7c98f85ae1472512dcf10a88c623c35769145a4d -
Branch / Tag:
refs/tags/v4.4.0 - Owner: https://github.com/eakmanrq
-
Access:
public
-
Token Issuer:
https://token.actions.githubusercontent.com -
Runner Environment:
github-hosted -
Publication workflow:
publish.workflow.yaml@7c98f85ae1472512dcf10a88c623c35769145a4d -
Trigger Event:
push
-
Statement type:
