Data shell

[shawnbot]

One thing I've always wanted was a tool that could a data file that I know nothing about, import it instantaneously into SQLite, then drop me right into the REPL to start querying it. Some things that it could do better than SQLite, though:

• better handling of CSV (and otherwise delimited) files, which is woefully inadequate
• support for JSON, line-delimited JSON, and possibly other formats (maybe GeoJSON and Shapefile using OGR SpatiaLite support?)
• export capability to the same formats
• save out the REPL history as SQL for later use, or even as a standalone script?

[shawnbot]

Some more ideas about how this could work as a CLI:

# creates a table named "foo" and drops you into the sqlite CLI
datashell -t foo.csv

# cast and rename columns
<<YAML
columns:
  Revenue Type:
    name: type
    type: varchar(8)
YAML > config.yml
datashell -c config.yml -t foo.csv \
  'select type, count(*) as count from foo group by type order by count desc'

# select data from a CSV
datashell -t foo.csv 'select id, lower(bar) as baz from foo order by id asc'

# creates two tables, joins them, and outputs CSV
datashell -t left=foo.csv -t right=bar.csv -- \
  'select left.x, right.x from left inner join right on right.id = left.id'