Querying Parquet with Precision Using DuckDB

https://duckdb.org/2021/06/25/querying-parquet.html

by mytherin • 5 years ago

129 32 5 years ago

polskibus2• 5 years ago

Now, if only I could somehow take that parquet processing optimizer out of DuckDB and into a Postgres fdw reading parquet files... that would make my ETLs work great. Does anyone know of another open source project that uses an optimizer to perform queries on parquet files?

eatonphil3• 5 years ago

Why won't you just copy your parquet data into Postgres?

taffer5• 5 years ago

Three reasons I can think of:

- Postgres is a row store optimized for transactional workloads, whereas Parquet is a column-oriented format optimized for analytical workloads.

- Database storage is typically very expensive SSD storage optimized for fast IO and high availability. Parquet files, on the other hand, can be stored in inexpensive object storage such as S3.

- Loading is an additional and possibly unnecessary step

mytherin2• 5 years ago

DuckDB is designed to be used as an embedded library, so it should be totally feasible to use it as part of a FDW.

Alternatively, you can run DuckDB as part of a plpython function:

  CREATE FUNCTION pyduckdb ()
    RETURNS integer
  AS $$
    import duckdb
    con = duckdb.connect()
    return con.execute('select 42').fetchall()[0][0]
  $$ LANGUAGE plpythonu;
  SELECT pyduckdb();

taffer5• 5 years ago

There is a parquet fdw for postgres:

https://github.com/adjust/parquet_fdw

polskibus2• 5 years ago

Yes, I know, but is it as smart as duckdb?

legg0myegg02• 5 years ago

Here is a DuckDB FDW for Postgres! I have not used it, but it sounds like what you need! https://github.com/alitrack/duckdb_fdw

legg0myegg02• 5 years ago

The best part of this is just how easy it is! Just a pip install and you're up and running using industry standard Postgres SQL!

wenc7• 5 years ago

I just downloaded duckdb and used it to query parquet files. It's amazing. Finally, an easy way to query Parquet data without spinning up a local Spark instance or doing pd.read_parquet. It's pretty fast too.

I've always felt that Parquet (or ORC) ought to be more popular mainstream formats than they are, but in practice the interactive tooling for Parquet has always been weak. For years, there wasn't a good GUI viewer (well, there were a few half-hearted attempts) or a good SQL query tool for Parquet like there is for most relational databases, and lazily examining the contents of a large Parquet folder from the command line has always been a chore. At least now, DuckDB's parquet support moves the needle on the last point.

mkarlsch• 5 years ago

We used Apache Drill a few years back and it could do exactly that - run locally and provide a SQL interface to query parquet files. Still great to see more and better options!

wenc7• 5 years ago

Drill is a fair choice (it supports tons of formats), but for me it was a bit heavy -- it required Java and plus it returned results in a form that wasn't easily manipulated (the result is a table in the console drawn with text-chars [1]). If you had a really long result set, you couldn't do a buffered scroll due to text UI limitations -- everything gets printed out and you have to either do manual pagination using LIMIT clauses or just have a really long scrollback buffer. This kind of breaks the continuity of many data analysis workflows.

With DuckDB, the return result is a Python data structure (list of tuples), which is amenable to further manipulation without exporting to another format.

Many GUI db tools often return results in a tabular form which can be copy-pasted into other GUI apps (or copy-pasted as CSV).

[1] https://drill.apache.org/docs/drill-in-10-minutes/#querying-...

scottmcdot2• 5 years ago

Which GUI do you use now? For me I have to use Impala via HUE, only after I have created an external table from the parquet file. I really don't like web based GUIs, especially when my employer has a crappy server running the GUI rendering.

wenc7• 5 years ago

I don't because I haven't found a good one for Parquet. I would really like something like SSMS for SQL Server.

I would poke at local Parquet files either through a local instance of Spark (or DataBricks when I was using that for remote data folders) or use Pandas' read_parquet from Jupyter.

mytherin2• 5 years ago

Glad that DuckDB is useful to you!

Some more notes that might be of use:

* We also have a command line interface (with syntax highlighting) [1]

* You can use .df() instead of .fetchall() to retrieve the results as a Pandas DataFrame. This is faster for larger result sets.

* You can also use DuckDB to run SQL on Pandas DataFrames directly [2]

[1] https://duckdb.org/docs/installation/

[2] https://duckdb.org/2021/05/14/sql-on-pandas.html

wenc7• 5 years ago

Thanks! Those sound like awesome features! (.df() is extremely useful).

antman• 5 years ago

I like especially (2), well thought out presentation thanks

nyokodo2• 5 years ago

Finally, an easy way to query Parquet data without spinning up a local Spark instance

What's the burning pain-point here? Spinning up a spark shell is extremely quick and easy.

wenc7• 5 years ago

That's a fair question, and I guess my answer would be "ergonomics". I tend to work in Python not Scala, and there's a certain amount of boilerplate that is necessary to even get Spark functional. For instance, this is a code snippet to spin up local Spark (not Spark Shell) I have stored in a file which I have to look up because there's no way I can commit this to memory:

    import findspark
    findspark.init()
    from pyspark.sql import SparkSession

    sqlContext = SparkSession.builder.master("local[*]").getOrCreate()
    df = sqlContext.read.parquet('path-to-file/commentClusters.parquet')

Not to mention fussy things you have to do in Spark to alias a table:

    df.createOrReplaceTempView("tableA")
    sqlContext.sql("SELECT count(*) from tableA").show()

Compare this to these 2 (!) DuckDB commands in the Python REPL that I just ran moments ago:

    >>> import duckdb
    >>> duckdb.query("SELECT COUNT(*) FROM '*.parquet'").fetchall()
    [(2,)]

ravanpao• 5 years ago

Vertica provides great support to work with Parquet files as external tables: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Ad...

We use this feature to build a staging layer for incoming files before loading to the data warehouse.

simonw• 5 years ago

I tried out DuckDB before (previous comment: https://news.ycombinator.com/item?id=24531732 ) and thought it looked interesting, but this tutorial has really made me excited about the project.

I've been meaning to figure out Parquet for ages, but now it turns out that "pip install duckdb" gives me everything I need to query Parquet files in addition to the other capabilities DuckDB has!

akdor1154• 5 years ago

DuckDB is awesome.. I played with it as a pandas replacement a while back, because I prefer SQL to pandas' arcane api. A few things were slower than they should be, but it's clearly getting better all the time, and now the basics are in place I'm looking forward to all sorts of crazy optimisation work. Well done and thanks Duckdb team!

Querying Parquet with Precision Using DuckDB

Related Stories