Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Command-line data analytics

danielcmoura.com

Post reply on HN
Print view View original

11–20 of 26 posts

Re: Command-line data analytics

#11

Post by mildbyte »

cube2222 wrote: post #4

SPyQL is really cool and its design is very smart, with it being able to leverage normal Python functions! As far as similar tools go, if you're interested, I recommend taking a look at DataFusion[0], dsq[1], and OctoSQL[2]. DataFusion is a very (very very) fast command-line SQL engine but with limited support for data formats. dsq is based on SQLite which means it has to load data into SQLite first, but then gives y…

And if you're looking for a similar experience (very fast analytical SQL queries) but over HTTP, for example, to power a public dashboard or a visualization, you can try ROAPI [0] or Seafowl [1], also built on top of DataFusion (disclaimer: working on Seafowl):

[0]: https://github.com/roapi/roapi

[1]: https://github.com/splitgraph/seafowl

Re: Command-line data analytics

#12

Post by dmoura »

zX41ZdbW wrote: post #6

Does SPyQL have any advantages over clickhouse-local?

Things you can do with SPyQL CLI that you can't with clickhouse local (AFAIK, top of my mind, not exhaustive):

- use python code in your queries

- import python libs (just install them with pip/conda)

- write your one UDFs in Python

- run OS commands from within the query (using os.system)

- have guaranty of row order (like in grep, sed, etc)

And there is more, please take a look at: https://spyql.readthedocs.io/en/latest/distinctive.html

Re: Command-line data analytics

#13

Post by samuell »

SPyQL looks fantastic!

The thing that worried me when looking into SQL-tools for CSV-files on the commandline, is the plethora of tools available, and it being hard to find one that feels solid and well-supported enough to become a "default" tool for many daily tasks.

I want to avoid investing a lot of time learning the ins and outs of a tool that might stop being developed in a year from now. I wish for something that can become the "awk of tomorrow", but based on SQL or something similar.

Does anyone have any experiences related to that? Is my worry warranted? Are some projects more well supported than others?

Re: Command-line data analytics

#14

Post by reichardt »

dmoura wrote: post #12
zX41ZdbW wrote: post #6

Does SPyQL have any advantages over clickhouse-local?

Things you can do with SPyQL CLI that you can't with clickhouse local (AFAIK, top of my mind, not exhaustive): - use python code in your queries - import python libs (just install them with pip/conda) - write your one UDFs in Python - run OS commands from within the query (using os.system) - have guaranty of row order (like in grep, sed, etc) And there is more, please take a look at: https://spyql.readthedocs.io/en/l…

As shown in the first example you can pipe data into clickhouse-local: https://clickhouse.com/docs/en/operations/utilities/clickhou...

Re: Command-line data analytics

#16

Post by dmoura »

reichardt wrote: post #14
dmoura wrote: post #12

Earlier quoted context omitted.

Things you can do with SPyQL CLI that you can't with clickhouse local (AFAIK, top of my mind, not exhaustive): - use python code in your queries - import python libs (just install them with pip/conda) - write your one UDFs in Python - run OS commands from within the query (using os.system) - have guaranty of row order (like in grep, sed, etc) And there is more, please take a look at: https://spyql.readthedocs.io/en/l…

As shown in the first example you can pipe data into clickhouse-local: https://clickhouse.com/docs/en/operations/utilities/clickhou...

updated, thank you

Re: Command-line data analytics

#17

Post by dmoura »

mytherin wrote: post #10
cube2222 wrote: post #4

SPyQL is really cool and its design is very smart, with it being able to leverage normal Python functions! As far as similar tools go, if you're interested, I recommend taking a look at DataFusion[0], dsq[1], and OctoSQL[2]. DataFusion is a very (very very) fast command-line SQL engine but with limited support for data formats. dsq is based on SQLite which means it has to load data into SQLite first, but then gives y…

You may also want to have a look at the DuckDB command line client [1]. The shell itself is based on the SQLite client, and DuckDB can be used to natively query CSV and Parquet files. Using extensions, DuckDB can also query SQLite and Postgres databases, and query files over HTTPS and S3. The command line client also has some nifty features like syntax highlighting, and context-aware auto-complete that is coming in t…

DuckDB is great! I love what you guys are building. The main gap for me is native support of JSON (lines), like you have for CSV and Parquet.

Re: Command-line data analytics

#18

Post by cube2222 »

mildbyte wrote: post #11
cube2222 wrote: post #4

SPyQL is really cool and its design is very smart, with it being able to leverage normal Python functions! As far as similar tools go, if you're interested, I recommend taking a look at DataFusion[0], dsq[1], and OctoSQL[2]. DataFusion is a very (very very) fast command-line SQL engine but with limited support for data formats. dsq is based on SQLite which means it has to load data into SQLite first, but then gives y…

And if you're looking for a similar experience (very fast analytical SQL queries) but over HTTP, for example, to power a public dashboard or a visualization, you can try ROAPI [0] or Seafowl [1], also built on top of DataFusion (disclaimer: working on Seafowl): [0]: https://github.com/roapi/roapi [1]: https://github.com/splitgraph/seafowl

That's what I thought about ROAPI as well, until I benchmarked it, and it ended up being very slow[0].

[0]: https://news.ycombinator.com/item?id=32970495

Re: Command-line data analytics

#19

Post by mildbyte »

cube2222 wrote: post #18
mildbyte wrote: post #11

Earlier quoted context omitted.

And if you're looking for a similar experience (very fast analytical SQL queries) but over HTTP, for example, to power a public dashboard or a visualization, you can try ROAPI [0] or Seafowl [1], also built on top of DataFusion (disclaimer: working on Seafowl): [0]: https://github.com/roapi/roapi [1]: https://github.com/splitgraph/seafowl

That's what I thought about ROAPI as well, until I benchmarked it, and it ended up being very slow[0]. [0]: https://news.ycombinator.com/item?id=32970495

It could be the NDJSON parser (DF source: [0]) or could be a variety of other factors. Looking at the ROAPI release archive [1], it doesn't ship with the definitive `columnq` binary from your comment (EDIT: it does, I was looking in the wrong place! https://github.com/roapi/roapi/releases/tag/columnq-cli-v0.3...), so it could also have something to do with compilation-time flags.

FWIW, we use the Parquet format with DataFusion and get very good speeds similar to DuckDB [2], e.g. 1.5s to run a more complex aggregation query `SELECT date_trunc('month', tpep_pickup_datetime) AS month, COUNT(*) AS total_trips, SUM(total_amount) FROM tripdata GROUP BY 1 ORDER BY 1 ASC)` on a 55M row subset of NY Taxi trip data.

[0]: https://github.com/apache/arrow-datafusion/blob/master/dataf...

[1]: https://github.com/roapi/roapi/releases/tag/roapi-v0.8.0

[2]: https://observablehq.com/@seafowl/benchmarks

Post reply on HN