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

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

#21

Post by eatonphil » Wed, Sep 21, 2022, 6:06 PM UTC

margarina72 wrote: post #20

eatonphil wrote: post #2

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

dsq is a great tool, using it regularly to work against csv files, and really nice to use.

Awesome to hear!

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

#22

Post by bachmeier » Wed, Sep 21, 2022, 6:06 PM UTC

These tools, useful as they are, appear to be only for querying files. AFAICT, insert and update are typically not supported as a way to modify the data.

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

#23

Post by pessimizer » Wed, Sep 21, 2022, 6:25 PM UTC

josebrwn wrote: post #5

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."

I don't understand this objection. Being able to compile something with no dependencies is different from being able to run something with no dependencies.

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

#24

Post by jmt_ » Wed, Sep 21, 2022, 6:28 PM UTC

You can do the same thing with csvsql from csvkit: https://towardsdatascience.com/analyze-csvs-with-sql-in-comm...

Since csvkit comes with so many other tools, I'm not sure I see a reason to use q over csvsql

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

#25

Post by DandyDev » Wed, Sep 21, 2022, 6:28 PM UTC

josebrwn wrote: post #5

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."

I think you're confusing installing the compiled package and using it (which doesn't require anything else) with _compiling_ the package, which requires XCode on MacOS

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

#26

Post by Merad » Wed, Sep 21, 2022, 6:32 PM UTC

If you like the idea of this but aren't on Linux or want something with a little ui involved, check out the Rainbow CSV extension for VS Code, vim, Sublime, and Atom. It includes RBQL [0] which lets you query CSVs with a sql-like syntax.

(Not affiliated with Rainbow CSV or RBQL at all, just a happy user)

[0]: https://rbql.org/

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

#27

Post by redavni » Wed, Sep 21, 2022, 6:32 PM UTC

For Windows users who click here, this functionality is built in.

https://learn.microsoft.com/en-us/cpp/data/odbc/data-source-...

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

#28

Post by tanin » Wed, Sep 21, 2022, 6:33 PM UTC

I've made a similar app, but I aim it to be more convenient, especially for people who don't want to handle installation and command line.

Check it out: https://superintendent.app -- it is a paid app though.

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

#29

Post by redsaz » Wed, Sep 21, 2022, 6:35 PM UTC

jfoutz wrote: post #13

redsaz wrote: post #4

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.

Have you looked at the Unix command ‘join’? This is a cool tool, but I think join is pretty much everywhere.

Hmm, I'll have to check it out!

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

#30

Post by bobivl » Wed, Sep 21, 2022, 6:45 PM UTC

redsaz wrote: post #4

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.

Cut, sort, join and awk can be pretty powerful and fast. If it becomes too tedious to manually write them, you can also use BigBash [1] to convert a SQL query automatically to a one-liner that only use these tools to execute the query.

[1] http://bigbash.it