Taking the Spark out of PySpark by converting to SQL
Project description
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 (many more in development):
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 to run PySpark DataFrame code without having to use a Spark cluster
- Users who want a SQL representation of their DataFrame code for debugging or sharing with others
- Users who want a DataFrame API that leverages the full power of their engine to do the processing
Installation
# BigQuery
pip install "sqlframe[bigquery]"
# DuckDB
pip install "sqlframe[duckdb]"
# Postgres
pip install "sqlframe[postgres]"
# Snowflake
pip install "sqlframe[snowflake]"
# Standalone
pip install 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(), 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.
Example Usage
from sqlframe.bigquery import BigQuerySession
from sqlframe.bigquery import functions as F
from sqlframe.bigquery import Window
session = BigQuerySession()
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()
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 |
+------+---------------------------+----------------+
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
sqlframe-1.6.0.tar.gz
(17.1 MB
view details)
Built Distribution
sqlframe-1.6.0-py3-none-any.whl
(127.5 kB
view details)
File details
Details for the file sqlframe-1.6.0.tar.gz.
File metadata
- Download URL: sqlframe-1.6.0.tar.gz
- Upload date:
- Size: 17.1 MB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/5.1.0 CPython/3.10.12
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | f6954876608ba558243bffc2ca03066481958abc98866622d0f81975162955c5 |
|
| MD5 | f336ecc70d926a78c1fda9838515449d |
|
| BLAKE2b-256 | a5e65a4ea1a8f01af26e9601a1ca5b61c04f53d7e6989df5a3d88923bf7362a6 |
File details
Details for the file sqlframe-1.6.0-py3-none-any.whl.
File metadata
- Download URL: sqlframe-1.6.0-py3-none-any.whl
- Upload date:
- Size: 127.5 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/5.1.0 CPython/3.10.12
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 191bad519608b9888a28f38c82f6932827cf47a302931ce5a38c1e947ccfee27 |
|
| MD5 | f5735c4255a197cbd343397b9809a49d |
|
| BLAKE2b-256 | c57ed121e7c306ff054b641ff181ae0bc245e7456f6d262173796fd97748003d |
