jolmg/cq

Query CSVs using SQL

cq

Wrapper over sqlite inspired by jq for querying CSVs using SQL

Examples of use

$ cat t/foobar.csv
foo,bar
1,2
$ cq t/foobar.csv -q 'select foo, bar + 10 as bar_2 from foobar;'
foo,bar_2
1,12
$ cat t/barbaz.csv
bar,baz
2,3
$ cq t/* -q 'select foo, baz from foobar, barbaz where foobar.bar = barbaz.bar;'
foo,baz
1,3

The tablenames are the basenames of the files without their optional .csv extension. One can also provide explicit names for the tables in 2 ways. One is with prefix assignment:

$ cq f:=t/foobar.csv b:=t/barbaz.csv -q 'select foo, baz from f, b where f.bar = b.bar;'
foo,baz
1,3

And the other one is with suffix assignment:

$ cq t/foobar.csv=:f t/barbaz.csv=:b -q 'select foo, baz from f, b where f.bar = b.bar;'
foo,baz
1,3

Notice one uses := and the other one is flipped =:. The former looks better when combining with <() process substitution:

cq \
  a:=<(ssh server_a generate_csv) \
  b:=<(ssh server_b produce_different_csv)

The latter works better with brace expansion:

cq a/very/long/path/{long_filename=:a,elsewhere/another_file=:b}

Output headers can be disabled with +H, and re-enabled with -H. The last option overrides the formers so you can set a default in a shell alias.

$ cq +H t/foobar.csv -q 'select foo, bar from foobar;'
1,2

Output mode can be set with -o:

$ cq -o column t/foobar.csv -q 'select foo, bar from foobar;'
foo         bar       
----------  ----------
1           2         

Supported output modes can be listed with sqlite3 <<< '.help mode'.

There's a --help option for more.

cq's Issues

Remove \r's when outputting CSVs

I find myself constantly piping through tr -d '\r'. I should at least add an option.

Enable explicit assigning of types to columns

I remember once having trouble with the default type used for columns and having to cast them in the SQL, so it might be nice to have some way to specify types. I'm not sure what the ideal interface for something like this would be.

One simple and dumb idea is to take the CSV header and pass it to sqlite as:

create table $table_name ($header);

that would allow us to optionally specify types and other things in the header. This would fail with quoting in the header, and it would be harder to sanitize the user input.

Another is to somehow include type information in the explicit assignment syntax, but I don't want to over-complicate the code either.

Add -f <sql_file> as an alternative to -q, and require one of the two

Add support for other RDBMSes like MySQL/MariaDB or PostgreSQL

Sqlite lacks support for some SQL features, so it'd be nice to be able to use other database engines.

When no CSVs are provided, add assignment t:=- as default

Even if stdin is a terminal, for the case where one wants to paste the CSV into the terminal.

Add option for cache.

Hi,
I made and used my own utility for similar purpose.

If csv file is very large, every .import csv procedure makes delay.
So I solved the problem with backed up dump of imported csv sqlite db.
The following is my script.

#!/bin/bash
if [[ $# -eq 0 ]]; then
    cat << HELP

Usage:

${0##*/} myfile.csv  ---> view csv fields schema and sample data.
${0##*/} myfile.csv 'select ... from myfile ... ;' ---->  query csv.

HELP
    exit 1
fi

csv_file=$1
shift

table=${csv_file%%.*}

query=".schema
select * from $table limit 1;"

if [[ "$@" ]]; then
    query="$@"
fi

if [[ -f "$table.db" ]]; then
cat << EOF | sqlite3 $table.db
.mode csv
.headers on
$query
EOF

else
cat << EOF | sqlite3
.mode csv
.import $csv_file $table
.headers on
.backup main $table.db
$query
EOF

fi

How about adding option like -c ( cache) or -d ( use database) for making dumb db at first execution and using dumped db if db file exists.