5 ms·

Querying Parquet with Precision Using DuckDB

129 points by mytherin 5y ago

legg0myegg0 5y 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!
polskibus 5y 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?
- eatonphil 5y ago
  Why won't you just copy your parquet data into Postgres?
  - taffer 5y 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
    - eatonphil 5y ago
      Sure but what is the scenario where you wouldn't want to use DuckDB and you'd want to use PostgreSQL instead but wouldn't want to load into PostgreSQL? DuckDB is already a SQL engine. The only additional benefit I'd imagine from PostgreSQL would be stuff you get from it being on disk? But that's why I'm asking, I don't know.
      - taffer 5y ago
        It depends on your situation: If you already have a Postgres instance then the FDW is probably the easier solution.
        eatonphil 5y ago
        Oh yeah that makes sense. I assumed the commenter was just looking for any system to run SQL over Parquet files. If you don't want to use this system that makes it easy, fair enough.
        taffer 5y ago
        > If you don't want to use this system that makes it easy, fair enough. Be kind. Don't be snarky. Have curious conversation; don't cross-examine. Please don't fulminate. Please don't sneer, including at the rest of the community. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
        agons 5y ago
        It didn't read unkind or snarky at all to me - just an amicable way of ending a thread of investigation where the result was a simple "a ha, I understand" moment.
- mytherin 5y 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();
- taffer 5y ago
  There is a parquet fdw for postgres: https://github.com/adjust/parquet_fdw https://github.com/adjust/parquet_fdw
  - polskibus 5y ago
    Yes, I know, but is it as smart as duckdb?
    - taffer 5y ago
      I havent tried it yet, but the parquet_fdw docs say you have to run ANALYZE on the table for optimal performance.
- legg0myegg0 5y 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 https://github.com/alitrack/duckdb_fdw
wenc 5y 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 5y 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!
  - wenc 5y 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-parquet-files https://drill.apache.org/docs/drill-in-10-minutes/#querying-...
    - sixdimensional 5y ago
      You didn't connect to drill using JDBC? You can use any open source GUI with that, like DBeaver.
ravanpao 5y ago
Vertica provides great support to work with Parquet files as external tables: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/ORC-Parquet.htm 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 5y ago
I tried out DuckDB before (previous comment: https://news.ycombinator.com/item?id=24531732 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 5y 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!

129 points by mytherin 5y 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!

polskibus 5y 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?

eatonphil 5y ago
Why won't you just copy your parquet data into Postgres?
- taffer 5y 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
  - eatonphil 5y ago
    Sure but what is the scenario where you wouldn't want to use DuckDB and you'd want to use PostgreSQL instead but wouldn't want to load into PostgreSQL? DuckDB is already a SQL engine. The only additional benefit I'd imagine from PostgreSQL would be stuff you get from it being on disk? But that's why I'm asking, I don't know.
    - taffer 5y ago
      It depends on your situation: If you already have a Postgres instance then the FDW is probably the easier solution.
      - eatonphil 5y ago
        Oh yeah that makes sense. I assumed the commenter was just looking for any system to run SQL over Parquet files. If you don't want to use this system that makes it easy, fair enough.
        taffer 5y ago
        > If you don't want to use this system that makes it easy, fair enough. Be kind. Don't be snarky. Have curious conversation; don't cross-examine. Please don't fulminate. Please don't sneer, including at the rest of the community. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
        agons 5y ago
        It didn't read unkind or snarky at all to me - just an amicable way of ending a thread of investigation where the result was a simple "a ha, I understand" moment.
mytherin 5y 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();
taffer 5y ago
There is a parquet fdw for postgres: https://github.com/adjust/parquet_fdw https://github.com/adjust/parquet_fdw
- polskibus 5y ago
  Yes, I know, but is it as smart as duckdb?
  - taffer 5y ago
    I havent tried it yet, but the parquet_fdw docs say you have to run ANALYZE on the table for optimal performance.
legg0myegg0 5y 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 https://github.com/alitrack/duckdb_fdw

wenc 5y 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 5y 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!
- wenc 5y 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-parquet-files https://drill.apache.org/docs/drill-in-10-minutes/#querying-...
  - sixdimensional 5y ago
    You didn't connect to drill using JDBC? You can use any open source GUI with that, like DBeaver.

ravanpao 5y ago

Vertica provides great support to work with Parquet files as external tables: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/ORC-Parquet.htm 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 5y ago

I tried out DuckDB before (previous comment: https://news.ycombinator.com/item?id=24531732 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 5y 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!