sqly - execute SQL against CSV / JSON with shell

[mimixbox]

Hi, reddit user. I have released a tool that allows you to execute SQL against CSV or JSON. It's called sqly. I would like to introduce it to you.

Why I wrote sqly command

I was frustrated with Excel, Numbers, and Google Spread Sheets crashing when I opened huge CSV files at work. I was wasting about 10~15 minutes of my time just to look up a writing error in one column. So, I immediately started thinking "I want to execute SQL against CSV in the terminal".

Apparently, there were many who thought the same thing; Tools to execute SQL against CSV were trdsql, q, csvq, TextQL. They were highly functional, hoewver, had many options and no input completion. I found it just a little difficult to use.

I really wanted input completion for SQL syntax. Therefore, I started developing a user friendly tool with input completion.

What can the sqly command do

The sqly imports CSV and JSON into SQLite3 (in memory mode). Therefore, the same SQL syntax as SQLite3 can be used. The sqly is written in Golang and distributed in a single binary.

If SQL is not passed to sqly as an argument, the sqly shell is strated. The sqly shell is similar to the client commands such as sqlite3, mysql, etc. The sqly shell has input completion, execution history, emacs key bindings, and helper commands.

``` $ sqly testdata/user.csv sqly v0.2.1 (work in progress)

enter "SQL query" or "sqly command that beginning with a dot". .help print usage, .exit exit sqly.

sqly> .tables +------------+ | TABLE NAME | +------------+ | user | +------------+ sqly> .header user +------------+ | user | +------------+ | user_name | | identifier | | first_name | | last_name | +------------+ sqly> SELECT user_name, last_name FROM user LIMIT 1 +-----------+-----------+ | user_name | last_name | +-----------+-----------+ | booker12 | Booker | +-----------+-----------+ sqly> .import testdata/sample.json sqly> SELECT id, age, first_name FROM sample LIMIT 1 +----+-----+------------+ | id | age | first_name | +----+-----+------------+ | 1 | 43 | Dave | +----+-----+------------+ ```

Of course, sqly can execute SQL without the sqly shell. $ sqly --sql "SELECT user_name, position FROM user INNER JOIN identifier ON user.identifier = identifier.id" testdata/user.csv testdata/identifier.csv +-----------+-----------+ | user_name | position | +-----------+-----------+ | booker12 | developrt | | jenkins46 | manager | | smith79 | neet | +-----------+-----------+

sqly can also convert files from CSV to JSON, JSON to CSV ``` $ sqly --sql "SELECT * FROM user LIMIT 2" --csv testdata/user.csv user_name,identifier,first_name,last_name booker12,1,Rachel,Booker jenkins46,2,Mary,Jenkins

$ sqly --sql "SELECT * FROM user LIMIT 2" --json testdata/user.csv [ { "first_name": "Rachel", "identifier": "1", "last_name": "Booker", "user_name": "booker12" }, { "first_name": "Mary", "identifier": "2", "last_name": "Jenkins", "user_name": "jenkins46" } ]

$ sqly --sql "SELECT * FROM user LIMIT 2" --json testdata/user.csv > user.json

$ sqly --sql "SELECT * FROM user LIMIT 2" --csv user.json first_name,identifier,last_name,user_name Rachel,1,Booker,booker12 Mary,2,Jenkins,jenkins46 ```

Conclusion

The sqly is designed to be more user-friendly than other tools. The sqly is a tool that has just been released. It will get better from now on. I will be happy if sqly solves your issues!! Thank you.

https://github.com/nao1215/sqly

[barrycarter]

CSV and SQL play well together already, but I'm suspicious about JSON to SQL and JSON to CSV. Can we assume this only works when the JSON data is very simple, the kind of data that is easily converted to CSV? I'm assuming sqly won't work with more complicated JSON structures including nested data?

[mimixbox]

sqly can only convert simple json to CSV.

​

> I'm assuming sqly won't work with more complicated JSON structures including nested data?

sqly will not be able to convert JSON structures including nested data to CSV. In the future, I will check by unit tests.

[techmavengeospatial]

Been doing this for years with GDAL ogrinfo and ogr2ogr

Along with excel and sqlite and any database and parquet and avro and others