Q – Run SQL Directly on CSV or TSV Files • Hacker News Board

Q – Run SQL Directly on CSV or TSV Files

#1

Post by gjvc » Wed, Sep 21, 2022, 3:31 PM UTC

Q – Run SQL Directly on CSV or TSV Files
harelba.github.io

Re: Q – Run SQL Directly on CSV or TSV Files

#2

Post by eatonphil » Wed, Sep 21, 2022, 4:38 PM UTC

Q is a great project! Here's a comparison of Q against some of the other tools out there [0] (including dsq, which I wrote). And there's a benchmark in there too [1].

Whichever tool you end up using, I'm sure it will help out with your CLI data exploration!

[0] https://github.com/multiprocessio/dsq#comparisons

[1] https://github.com/multiprocessio/dsq#benchmark

Re: Q – Run SQL Directly on CSV or TSV Files

#3

Post by gizmodo59 » Wed, Sep 21, 2022, 5:01 PM UTC

I have been using Dremio to query large volume of CSV files: https://docs.dremio.com/software/data-sources/files-and-dire...

Although having them in some columnar format is much better for fast responses.

GitHub: https://github.com/dremio/dremio-oss

Re: Q – Run SQL Directly on CSV or TSV Files

#4

Post by redsaz » Wed, Sep 21, 2022, 5:10 PM UTC

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported.

I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

Re: Q – Run SQL Directly on CSV or TSV Files

#5

Post by josebrwn » Wed, Sep 21, 2022, 5:14 PM UTC

http://harelba.github.io/q/#requirements

"q is packaged as a compiled standalone-executable that has no dependencies, not even python itself."

This is not quite true, on MacOS:

"q: A full installation of Xcode.app 12.4 is required to compile this software. Installing just the Command Line Tools is not sufficient.

Xcode can be installed from the App Store. Error: q: An unsatisfied requirement failed this build."

Re: Q – Run SQL Directly on CSV or TSV Files

#6

Post by daniel-s » Wed, Sep 21, 2022, 5:20 PM UTC

Is this similar to CSV virtual tables in sqlite?

https://www.sqlite.org/csv.html

Re: Q – Run SQL Directly on CSV or TSV Files

#7

Post by cma » Wed, Sep 21, 2022, 5:22 PM UTC

Might mix people up with: https://en.m.wikipedia.org/wiki/Q_(programming_language_from...

Re: Q – Run SQL Directly on CSV or TSV Files

#8

Post by eatonphil » Wed, Sep 21, 2022, 5:31 PM UTC

daniel-s wrote: post #6

Is this similar to CSV virtual tables in sqlite? https://www.sqlite.org/csv.html

It's easier to use than that. With virtual tables you must CREATE TABLE for every schema you have which is very tedious if you are querying CSV files with differing columns.

Re: Q – Run SQL Directly on CSV or TSV Files

#9

Post by WASDx » Wed, Sep 21, 2022, 5:34 PM UTC

cma wrote: post #7

Might mix people up with: https://en.m.wikipedia.org/wiki/Q_(programming_language_from...

When will people stop giving things one character names, it's impossible to search for.

Re: Q – Run SQL Directly on CSV or TSV Files

#10

Post by eatonphil » Wed, Sep 21, 2022, 5:37 PM UTC

gizmodo59 wrote: post #3

I have been using Dremio to query large volume of CSV files: https://docs.dremio.com/software/data-sources/files-and-dire... Although having them in some columnar format is much better for fast responses. GitHub: https://github.com/dremio/dremio-oss

They're sort of different categories.

Tools like Q are for command line use.

Dremio is a server/web application, right?

They accomplish the same thing but you might deploy a tool like Q on production servers for adhoc log analysis or install it in a docker container. (Not saying you should, just explaining the difference.)