FugueSQL: SQL-ish for pandas, dask, spark

https://fugue-tutorials.readthedocs.io/en/latest/tutorials/fugue_sql/index.html

by aiNohY6g • 5 years ago

74 12 5 years ago

fugue-tutorials.readthedocs.io

bothra90• 5 years ago

Is this solving similar problems as Ray [1]?

[1] https://www.ray.io/

elephantum2• 5 years ago

mwexler2• 5 years ago

Well, sort of. Fugue overall is a scaling engine like ray. The specific link to yet another SQL access layer to a dataset doesnt really have an analog on ray, but has some nice features.

I love these SQL layers but they can obfuscate how they implement their transforms. So, they can speed up filter and join creation and coding... til something breaks and then you have to go atomic anyway.

goodwanghan4• 5 years ago

Hey, I am the author of Fugue.

Fugue is a higher level abstraction compared to Ray. It provides unified and non-invasive interfaces for people to use Spark, Dask and Pandas. Ray/Modin is also on our roadmap.

It provides both Python interface (not pandas-like) and Fugue SQL (standard SQL + extra features). Users can choose the one they are most comfortable with as the semantic layer for distributed computing, they are equivalent.

With Fugue, most of your logic will be in simple Python/SQL that is framework and scale agnostic. From the mindset to the code, Fugue minimizes your dependency on any specific computing frameworks including Fugue itself.

Please let me know if you want to learn more. our slack is in the README of the fugue repo

Fugue repo: https://github.com/fugue-project/fugue Tutorials: https://fugue-project.github.io/tutorials/

dayeye2006• 5 years ago

What kind of parser does FugueSQL use? Does it use Apache Calcite?

crimsoneer• 5 years ago

So, I'm a data science person who uses SQL largely to integrate with python or just for pretty straightforward extracts - my company is very data not mature. I've previously used MySql, postGresql, and have been considered DuckDB (though not clear I can store stuff outside of temp memory). Is this good? Is something else good?

goodwanghan4• 5 years ago

Fugue SQL extends standard SQL so you can use SQL-ish language end to end.

For example, let's assume you use duckdb or mysql or other standard sql for data processing, how do you deal with intermediate tables?

You may have to frequently use CREATE TABLE and INSERT or write gigantic WITH statement to avoid the hassle. I guess many people use both.

In Fugue SQL, you can use simple assignment to do the same thing (Fugue may translate the assignment to CREATE TABLE or do something more efficient)

-------

a = CONNECT duckdb SELECT * FROM table1

b = LOAD "/tmp/src.parquet"

c = SELECT * FROM a LEFT SEMI JOIN b ON a.key = b.key

OUTPUT c USING mysql_dumper(table_name="temp", mode="append")

d = SELECT * FROM b UNION SELECT * FROM c

SAVE d OVERWRITE "/tmp/tmp.parquet"

--------

Look at the above Fugue SQL example, it's self-explaining, but how much extra code you have to write to achieve the same semantic without Fugue SQL?

Fugue SQL can provide a unified and minimal way to express your logic in SQL-ish way, while the real executions are done but those wonderful database solutions you mentioned.

kvnkho• 5 years ago

Hello, FugueSQL also supports SQL being the dominant language when describing computation. Current SQL interfaces are normally invoked between predominantly Python code. FugueSQL was created to support the other way around with SQL invoking Python code.

For example, with a python function

```python

# schema: , c:int

def add_new_col(df:pd.DataFrame) -> pd.DataFrame:

    df['c'] = 1

    return df

query = """SELECT FROM df TRANSFORM USING add_new_col"""

fsql(query).run()

# to run on Spark

fsql(query).run(SparkExecutionEngine)

```

FugueSQL: SQL-ish for pandas, dask, spark

Related Stories