PySpark Dataframe API Compatible SQL Generator
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:
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]"
# Standalone
pip install sqlframe
See specific engine documentation for additional setup instructions.
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"
# Get the top 5 years with the greatest year-over-year % change in new families with a 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("number of 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 `number of 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 | number of 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-0.1.dev3.tar.gz
(17.1 MB
view details)
Built Distribution
sqlframe-0.1.dev3-py3-none-any.whl
(118.1 kB
view details)
File details
Details for the file sqlframe-0.1.dev3.tar.gz.
File metadata
- Download URL: sqlframe-0.1.dev3.tar.gz
- Upload date:
- Size: 17.1 MB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/5.1.0 CPython/3.12.3
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | b274a284892652988a0e5f97dd4d1641b6009cab1409accd700eccc827d01812 |
|
| MD5 | a91d815500107fa5471b0954eb10be4e |
|
| BLAKE2b-256 | 172cc619ebc18cf30099ba50e661e41d355ba58806894a6452d12b5c3aaa2be4 |
File details
Details for the file sqlframe-0.1.dev3-py3-none-any.whl.
File metadata
- Download URL: sqlframe-0.1.dev3-py3-none-any.whl
- Upload date:
- Size: 118.1 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/5.1.0 CPython/3.12.3
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | ff0d68e161e22d69c7182916d381a940a98816a28e027130dd4263601a4df61d |
|
| MD5 | 42f70afc9df99948220e80556bf53b05 |
|
| BLAKE2b-256 | 9fcbd6407dacc5a0cef19a38c8778b4c698945e0c7c74b054e6518dc5817e7f4 |
