HN Explorer

Show HN: SQLFrame – I ran PySpark without Spark on a SQL database (github.com)

11 points by dayflyer ↗ HN
Recently I open-sourced SQLFrame, a DataFrame library that implements the PySpark DataFrame API but removes Spark as a dependency. It does this by generating the corresponding SQL for the DataFrame operations using SQLGlot. Since the output is SQL this also means that the PySpark DataFrame API can now be used directly against other databases without the Spark middleman.

I built this because of two common problems I have faced in my career: 1. I prefer to write complex pipelines in PySpark but they can be hard to read for SQL-proficient co-workers. Therefore I find myself in a tradeoff between maintainability and accessibility. 2. I really enjoy using the PySpark DataFrame API but not every project requires Spark and therefore I'm not able to use the DataFrame library I am most proficient in.

The library currently focuses on transformation pipelines (reading from and writing to tables) and data analysis as key use cases. It does offer some ability to read from files directly but they must be small although this can be improved over time if there is demand for it.

SQLFrame currently supports BigQuery, DuckDB, and Postgres with Clickhouse, Redshift, Snowflake, Spark, and Trino in development or planned. You can use the "Standalone" session to test running against any engine supported by SQLGlot but there could be issues with more advanced functions that will be resolved once officially supported by SQLFrame.

Blog post for more details: https://github.com/eakmanrq/sqlframe/blob/main/blogs/sqlfram...

Would love to answer any questions or hear any feedback you may have!

4 comments

[ 1.5 ms ] story [ 22.3 ms ] thread
zurfer
This is cool and in my mind super useful for migrations.

It seems the main benefit of using something like that in daily life is that it's more convenient to generate complex SQL statements (like pivoting a table with a lot of columns).

However, I never really liked the PySpark dataframe api and looking at the code examples, SQL has the same visual complexity.

Snowflake has built something similar (just for Snowflake) SnowPark [1]. Here one promoted benefit was that you could also inject native Python function and "extend" the SQL dialect. However, I don't think it really took off.

[1] https://github.com/snowflakedb/snowpark-python

dayflyer
Yeah I'm familiar with Snowpark and I think their mistake is they implemented an API very similar to PySpark but not exactly the same. I can actually see that the 2nd most common engine looked at in the code of my repo is Snowflake so I think there is interest in it even with Snowpark. I will be adding Snowflake support next.

In terms of not liking PySpark, I get that the API is not for everyone and if someone doesn't like PySpark then they are unlikely to like SQLFrame. I think it is great we have a variety of options available to us and ideally each team/organization can find an API that works best for their skillsets/needs but also reduces vendor lock. SQLFrame is a great fit for those teams/organizations who want to standadize on PySpark.

anentropic
Have you seen https://ibis-project.org/ ?
dayflyer
Yes I'm familiar with Ibis and we use the same technology to do the SQL generation (SQLGlot). Like most products, I made this due to two problems I commonly faced: 1. Generate SQL that represents a PySpark DataFrame pipeline that I maintain so others who prefer SQL can easily understand it 2. I'm already proficient with the PySpark DataFrame API but not all projects I work on require Spark.

In both cases Ibis would not have been a solution for my issue. I think if you haven't learned either then it would come down to if you are more interested in the learning the PySpark DataFrame API or Ibis DataFrame API.