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

Q – Run SQL Directly on CSV or TSV Files

harelba.github.io

Post reply on HN
Print view View original

41–50 of 62 posts

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

#41

Post by bob-bot »

Love seeing more projects focused on using SQL to query many things. It is a common and familiar language to query and report.

I help lead an open source project https//steampipe.io which can query CSV with SQL, among 85+ other endpoints like cloud providers, SaaS APIs, code, logs and more using SQL to query and join data: https://hub.steampipe.io/plugins

There is also an interesting dashboards as code concept where you can codify interactive dashboards with HCL + SQL: https://steampipe.io/blog/dashboards-as-code

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

#43

Post by mattewong »

Nice work! I am a fan of tools like this.

However, in my first attempted query (version 3.1.6 on MacOS), I ran into significant performance limitations and more importantly, it did not give correct output.

In particular, running on a narrow table with 1mm rows (the same one used in the xsv examples) using the command "select country, count(1) from worldcitiespop_mil.csv group by country" takes 12 seconds just to get an incorrect error 'no such column: country'.

using sqlite3, it takes two seconds or so to load, and less than a second to run, and gives me the correct result.

Using https://github.com/liquidaty/zsv (disclaimer, I'm one of its authors), I get the correct results in 0.95 seconds with the one-liner `zsv sql 'select country, count(1) from data group by country' worldcitiespop_mil.csv`.

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

#47

Post by Groxx »

aasasd wrote: post #32
bobivl wrote: post #30

Earlier quoted context omitted.

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

Any experienced programmer learns to not use string processing on structured data, because that will bite them in the ass. Meanwhile HN luddites: let me use awk, cut and whatnot despite the existence of an util that explicitly sidesteps this issue.

/me runs the example on bigbash.it, cleaned up a bit:

    (
      trap "kill 0" SIGINT;
      export LC_ALL=C;
      find movies.dat.gz -print0
        | xargs -0 -i sh -c "gzip -dc {} | tail -n +2"
        | sed "s/::/;/g"
        | cut -d $';' -f2
        | sort -t$';'  -k 1,1
        | head -n10
        | awk -F ';' '{print $1}'
    )
Yeah, how about no. That's a very neat site and a clever hack, but there are clear escaping flaws in there for valid movie names.

bash and standard unix tools are a terrible structured-data manipulator. it's part of why `jq` is so widely used and loved, despite being kinda slow and hard to remember at times - it does things correctly, unlike most glued-together tools.

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

#48

Post by pphysch »

aasasd wrote: post #32
bobivl wrote: post #30

Earlier quoted context omitted.

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

Any experienced programmer learns to not use string processing on structured data, because that will bite them in the ass. Meanwhile HN luddites: let me use awk, cut and whatnot despite the existence of an util that explicitly sidesteps this issue.

"structured data" usually means there are delimiting characters, states, etc. AWK can certainly handle this. It's a simple and powerful language.

I don't think I've ever used it to parse JSON, but I've definitely used it to output simple JSON.

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

#49

Post by WaxProlix »

bobivl wrote: post #30
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

Powerful and fast, and also *portable*. It'll run on your low-privileged tools box, 20 year old beige box, vhost, you name it.
Post reply on HN