5 ms·

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

by 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.
    - wenc 5y ago
      Oh I didn't think to do that. That's something I could definitely explore, particularly for apps like Tableau. Thanks!
scottmcdot 5y 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.
- wenc 5y 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.
  - scottmcdot 5y ago
    Same, I would really love a local-running tool like SSMS or Teradata SQL Assistant. CDSW has been good to use with Python (interacting with spark.sql) as a somewhat IDE however it's also web-based. The best thing I have gotten to work is Python within VS Code (running locally) tunnelling to a CDSW project, where I again can run spark.sql. However, it still prints tables out in ASCII way instead of the nice way of SSMS or Teradata SQL Assistant.
mytherin 5y 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/ https://duckdb.org/docs/installation/ [2] https://duckdb.org/2021/05/14/sql-on-pandas.html https://duckdb.org/2021/05/14/sql-on-pandas.html
- wenc 5y ago
  Thanks! Those sound like awesome features! (.df() is extremely useful).
- antman 5y ago
  I like especially (2), well thought out presentation thanks
nyokodo 5y 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.
- wenc 5y 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,)]
  - nyokodo 5y ago
    > I tend to work in Python not Scala Fair enough. I’m more comfortable in Scala than Python. Have you tried using a local Jupyter notebook? That would seem to tick all the boxes. https://jupyter.org/install https://jupyter.org/install
    - wenc 5y ago
      I do use Jupyter and the boilerplate code above is what I usually deploy in Jupyter. Spark syntax is still just a touch more complicated than necessary for my needs because it's solving the much more general distributed computing problem. (not to mention the impedance mismatch -- Spark is very much a Scala native app, and it feels that way to a Python user). DuckDB on the other hand seems to provide a quick way to interact with local Parquet files in Python, which I appreciate.

by wenc 5y ago

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.
  - wenc 5y ago
    Oh I didn't think to do that. That's something I could definitely explore, particularly for apps like Tableau. Thanks!

scottmcdot 5y 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.

wenc 5y 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.
- scottmcdot 5y ago
  Same, I would really love a local-running tool like SSMS or Teradata SQL Assistant. CDSW has been good to use with Python (interacting with spark.sql) as a somewhat IDE however it's also web-based. The best thing I have gotten to work is Python within VS Code (running locally) tunnelling to a CDSW project, where I again can run spark.sql. However, it still prints tables out in ASCII way instead of the nice way of SSMS or Teradata SQL Assistant.

mytherin 5y 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/ https://duckdb.org/docs/installation/ [2] https://duckdb.org/2021/05/14/sql-on-pandas.html https://duckdb.org/2021/05/14/sql-on-pandas.html

wenc 5y ago
Thanks! Those sound like awesome features! (.df() is extremely useful).
antman 5y ago
I like especially (2), well thought out presentation thanks

nyokodo 5y 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.

wenc 5y 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,)]
- nyokodo 5y ago
  > I tend to work in Python not Scala Fair enough. I’m more comfortable in Scala than Python. Have you tried using a local Jupyter notebook? That would seem to tick all the boxes. https://jupyter.org/install https://jupyter.org/install
  - wenc 5y ago
    I do use Jupyter and the boilerplate code above is what I usually deploy in Jupyter. Spark syntax is still just a touch more complicated than necessary for my needs because it's solving the much more general distributed computing problem. (not to mention the impedance mismatch -- Spark is very much a Scala native app, and it feels that way to a Python user). DuckDB on the other hand seems to provide a quick way to interact with local Parquet files in Python, which I appreciate.