5 ms·

Joining CSV and JSON data with an in-memory SQLite database

126 points by edward 5y ago

simonw 5y ago
This post is about a new feature in my sqlite-utils tool, which is a combination CLI utility and Python library for productively working with SQLite database files: https://sqlite-utils.datasette.io/en/latest/changelog.html#v3-10 https://sqlite-utils.datasette.io/en/latest/changelog.html#v...
- raziel2p 5y ago
  This tool seems amazing. I'm kinda upset I haven't discovered it before.
- mongol 5y ago
  Before doing the select I would probably want to show the schema. Since the JSON could be arbitrarily complex... How would I do that?
  - simonw 5y ago
    Good point, I should add that to the docs. You can see the schema in a couple of ways: sqlite-utils memory blah.json "select sql from sqlite_master" Or sqlite-utils memory blah.json --dump The second option will dump out both the SQL schema and the INSERT statements, so you probably want to | head that. You've inspired me to add a --schema option which does the dump without also including the inserted rows. Issue here: https://github.com/simonw/sqlite-utils/issues/288 https://github.com/simonw/sqlite-utils/issues/288
    - simonw 5y ago
      I just released the new `--schema` option in sqlite-utils 3.11: https://sqlite-utils.datasette.io/en/latest/changelog.html#v3-11 https://sqlite-utils.datasette.io/en/latest/changelog.html#v...
      - mongol 5y ago
        Really cool, great improvement!
- DemocracyFTW 5y ago
  Would you like to speak to the rationale to incorporate a sort-of ORM into Datasette? According to the docs, I can iterate over a subset of rows by writing for row in db[ 'dogs' ].rows_where( 'age > 1', select = 'name, age', order_by = 'age desc' ): ... but how is that better than just writing the equivalent SQL statement, sql = "select name, age from dogs where age > 1 order by age desc;" for row in ...: Most of the arguments in the pythonized form are, after all, just SQL fragments, so it's not like you get column name parametrization (you'd still have to properly escape and concatenate the `select` argument). Perusing the docs I found that considerable effort is spent on explaining this and other ORM-like features, instead of just saying "use the SQL you already know" (people without this knowledge won't be easily able to use this API productively anyway).
  - simonw 5y ago
    sqlite-utils isn't (yet) a dependency of Datasette. The goal with sqlite-utils isn't to build an ORM - that .rows_where() method is definitely the most ORM-like piece of it, and it's purely there as a SQL-builder - it was a natural extension of the .rows property, which grew extra features (like order_by) over time as they were requested by users, eg https://github.com/simonw/sqlite-utils/issues/76 https://github.com/simonw/sqlite-utils/issues/76 The vast majority of the library is aimed at making getting data IN to SQLite as easy as possible. Datasette is mainly about executing SELECT queries, and I found myself writing a lot of code to populate those database files - which grew into a combination library and CLI tool. So yeah - I don't use the rows_where() feature much in my own code - I tend to use db.query() directly - but I've evolved the method over time based on user feedback. I don't have particularly strong opinions about it one way or the other.
    - DemocracyFTW 5y ago
      Ah OK that's interesting. I would've surmised the `.rows_where()` API having grown out of your own need to repeatedly stitch together pieces of SQL for dynamic queries, but it apparently did not go that way.
edoceo 5y ago
Side note: Sqlite mem-only DBs are amazing for working with dataset you want to load then get counts and groups and other fun things. Loops and other stuff in your code get replaced by (cleaner?) SQL. It's also way easier to see what you were doing 6mo later and the performance is not terrible.
- pletnes 5y ago
  I’ve heard this before, but what do you get over pandas, which has groupby, filtering etc? Skip one dependency? Usage in other languages than python?
  - atoav 5y ago
    Sqlite has probably much wider (and more stable) support with other languages than pandas. Also it is a different thing. Pandas is very nice to do data analytics or crunch numbers on reocurring data, but I wouldn't replace a database with it.
    - pletnes 5y ago
      I agree that it can’t replace a database, for most use cases. But an in-memory database I’ve yet to find a «real» usecase for.
  - dgb23 5y ago
    If pandas gives you a full relational API with arbitrary data then in isolation it doesn’t make much of a difference. SQL is more portable so to speak, but a library like that may introduce less friction. Pragmatism is advised here. The big idea here is to use relational logic programming to express data transformation outside of storage access. The paper „Out of the Tar Pit“ proposed this as a way to reduce accidental complexity.
  - selimnairb 5y ago
    I recently started using pandas to do groupby and aggregation. It’s nice to have, but the whole time I kept wishing it would just let me run an SQL query without adding another dependency. Having learned SQL long ago, I find it to be much more intuitive and expressive. I guess it’s all just what you know.
2ion 5y ago
I've been going at this kind of task for quite some time now most frequently with pandas as the "tee" kind of tools to pull different kinds of data sources together. This looks neat as well though and probably more convenient in a shell-style & scripting environment. Might be useful e.g. when doing things in cloud-init or sth - a verstile tool to stick into VM base iamges.
eatonphil 5y ago
I recently started looking into the most mature SQL database for the browser. My goal was similar, to be able take data as json or HTTP responses or csv or whatever and be able to pass them through a SQL engine with the user providing custom code/data the whole way. Turns out the most mature JavaScript database is SQLite ported to webassembly: https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js. Based on that research, I wrote a bit about running SQL and other languages entirely in the browser here: https://datastation.multiprocess.io/blog/2021-06-16-languages-you-can-run-in-the-browser.html https://datastation.multiprocess.io/blog/2021-06-16-language...
smartmic 5y ago
I do not know sqlite-utils, but my first thought was: why not do it purely using jq and sqlite3's CLI ? Okay, this is interactive but so are almost all of my exploratory data analysis tasks. Just a few steps: convert JSON to CSV with jq, fire up interactive sqlite3 CLI which connects to in-memory database by default, run two .import FILE TABLE statements and finally the SQL query. Alternatively one could use the SQLite JSON1 extension and write a small script if the task should be automated (and you do not like jq's syntax for JSON->CSV conversion)
- simonw 5y ago
  Because that's more steps. Everything you can do with the new "sqlite-utils memory" command is possible without it, but now you can get a ton of stuff done with a single tool in a one-line shell script (as opposed to several lines that tue together several different tools).
mihaitodor 5y ago
Sounds like a great use case for https://www.benthos.dev/ https://www.benthos.dev/

126 points by edward 5y ago

This post is about a new feature in my sqlite-utils tool, which is a combination CLI utility and Python library for productively working with SQLite database files: https://sqlite-utils.datasette.io/en/latest/changelog.html#v3-10 https://sqlite-utils.datasette.io/en/latest/changelog.html#v...

raziel2p 5y ago
This tool seems amazing. I'm kinda upset I haven't discovered it before.
mongol 5y ago
Before doing the select I would probably want to show the schema. Since the JSON could be arbitrarily complex... How would I do that?
- simonw 5y ago
  Good point, I should add that to the docs. You can see the schema in a couple of ways: sqlite-utils memory blah.json "select sql from sqlite_master" Or sqlite-utils memory blah.json --dump The second option will dump out both the SQL schema and the INSERT statements, so you probably want to | head that. You've inspired me to add a --schema option which does the dump without also including the inserted rows. Issue here: https://github.com/simonw/sqlite-utils/issues/288 https://github.com/simonw/sqlite-utils/issues/288
  - simonw 5y ago
    I just released the new `--schema` option in sqlite-utils 3.11: https://sqlite-utils.datasette.io/en/latest/changelog.html#v3-11 https://sqlite-utils.datasette.io/en/latest/changelog.html#v...
    - mongol 5y ago
      Really cool, great improvement!
DemocracyFTW 5y ago
Would you like to speak to the rationale to incorporate a sort-of ORM into Datasette? According to the docs, I can iterate over a subset of rows by writing for row in db[ 'dogs' ].rows_where( 'age > 1', select = 'name, age', order_by = 'age desc' ): ... but how is that better than just writing the equivalent SQL statement, sql = "select name, age from dogs where age > 1 order by age desc;" for row in ...: Most of the arguments in the pythonized form are, after all, just SQL fragments, so it's not like you get column name parametrization (you'd still have to properly escape and concatenate the `select` argument). Perusing the docs I found that considerable effort is spent on explaining this and other ORM-like features, instead of just saying "use the SQL you already know" (people without this knowledge won't be easily able to use this API productively anyway).
- simonw 5y ago
  sqlite-utils isn't (yet) a dependency of Datasette. The goal with sqlite-utils isn't to build an ORM - that .rows_where() method is definitely the most ORM-like piece of it, and it's purely there as a SQL-builder - it was a natural extension of the .rows property, which grew extra features (like order_by) over time as they were requested by users, eg https://github.com/simonw/sqlite-utils/issues/76 https://github.com/simonw/sqlite-utils/issues/76 The vast majority of the library is aimed at making getting data IN to SQLite as easy as possible. Datasette is mainly about executing SELECT queries, and I found myself writing a lot of code to populate those database files - which grew into a combination library and CLI tool. So yeah - I don't use the rows_where() feature much in my own code - I tend to use db.query() directly - but I've evolved the method over time based on user feedback. I don't have particularly strong opinions about it one way or the other.
  - DemocracyFTW 5y ago
    Ah OK that's interesting. I would've surmised the `.rows_where()` API having grown out of your own need to repeatedly stitch together pieces of SQL for dynamic queries, but it apparently did not go that way.

edoceo 5y ago

Side note: Sqlite mem-only DBs are amazing for working with dataset you want to load then get counts and groups and other fun things. Loops and other stuff in your code get replaced by (cleaner?) SQL. It's also way easier to see what you were doing 6mo later and the performance is not terrible.

pletnes 5y ago
I’ve heard this before, but what do you get over pandas, which has groupby, filtering etc? Skip one dependency? Usage in other languages than python?
- atoav 5y ago
  Sqlite has probably much wider (and more stable) support with other languages than pandas. Also it is a different thing. Pandas is very nice to do data analytics or crunch numbers on reocurring data, but I wouldn't replace a database with it.
  - pletnes 5y ago
    I agree that it can’t replace a database, for most use cases. But an in-memory database I’ve yet to find a «real» usecase for.
- dgb23 5y ago
  If pandas gives you a full relational API with arbitrary data then in isolation it doesn’t make much of a difference. SQL is more portable so to speak, but a library like that may introduce less friction. Pragmatism is advised here. The big idea here is to use relational logic programming to express data transformation outside of storage access. The paper „Out of the Tar Pit“ proposed this as a way to reduce accidental complexity.
- selimnairb 5y ago
  I recently started using pandas to do groupby and aggregation. It’s nice to have, but the whole time I kept wishing it would just let me run an SQL query without adding another dependency. Having learned SQL long ago, I find it to be much more intuitive and expressive. I guess it’s all just what you know.

2ion 5y ago

I've been going at this kind of task for quite some time now most frequently with pandas as the "tee" kind of tools to pull different kinds of data sources together. This looks neat as well though and probably more convenient in a shell-style & scripting environment. Might be useful e.g. when doing things in cloud-init or sth - a verstile tool to stick into VM base iamges.

eatonphil 5y ago

I recently started looking into the most mature SQL database for the browser. My goal was similar, to be able take data as json or HTTP responses or csv or whatever and be able to pass them through a SQL engine with the user providing custom code/data the whole way. Turns out the most mature JavaScript database is SQLite ported to webassembly: https://github.com/sql-js/sql.js https://github.com/sql-js/sql.js. Based on that research, I wrote a bit about running SQL and other languages entirely in the browser here: https://datastation.multiprocess.io/blog/2021-06-16-languages-you-can-run-in-the-browser.html https://datastation.multiprocess.io/blog/2021-06-16-language...

smartmic 5y ago

I do not know sqlite-utils, but my first thought was: why not do it purely using jq and sqlite3's CLI ? Okay, this is interactive but so are almost all of my exploratory data analysis tasks. Just a few steps: convert JSON to CSV with jq, fire up interactive sqlite3 CLI which connects to in-memory database by default, run two .import FILE TABLE statements and finally the SQL query. Alternatively one could use the SQLite JSON1 extension and write a small script if the task should be automated (and you do not like jq's syntax for JSON->CSV conversion)

simonw 5y ago
Because that's more steps. Everything you can do with the new "sqlite-utils memory" command is possible without it, but now you can get a ton of stuff done with a single tool in a one-line shell script (as opposed to several lines that tue together several different tools).

mihaitodor 5y ago

Sounds like a great use case for https://www.benthos.dev/ https://www.benthos.dev/