8 ms·

I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API

I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wise, by carefully defining index on expressions. You can somehow normalize it with views (SQLite doesn't support materialized views). And of course it's almost always faster to query data where it exists (via SQL) instead of fetching it from disk and querying it say Python. You pay too much IO cost. (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) The json dump is not stored in binary, but in text format, but honestly I haven't seen this to be a problem, plus you can easily run queries at the CLI and pipe the output to jq, sed etc. If your application is data warehouse-like and read-heavy (for example an internal reporting dashboard) I can't see any reason why you should pay the cost of setting up a Postgres or MongoDB instance (although I do love both.) It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure).

stuxnet79 8y ago
> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to the database engine?
- fforflo 8y ago
  As usual: it depends... If you're doing prototyping and are working in a Jupyter notebook, sure, go ahead and work on the Pandas-level. Once however you're done with prototyping and have settled to a "final_df" (I bet you have something like that in your last notebook cells), maybe you should think transforming some of the "columns" to sql queries (which are VCS-able, sometimes are faster, and most importantly other people can use them too. And instead of 10 people loading 10 different DFs, you can have 10 people querying the same table/view.
- pletnes 8y ago
  Dask has an out-of-memory dataframe implementation. Works great! I think it might support sql queries for that matter.
- paulddraper 8y ago
  > Assuming there is enough RAM available You will also have to assume you don't care much about the latency introduced by transferring everything over to your working process. (And in batch data processing situations you typically don't care much.)
- perturbation 8y ago
  Dplyr works great with SQL (both SQLite and others).
blattimwind 8y ago
> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure). WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and need workarounds; essentially they delay the BEGIN of a transaction until you issue DML statements which obviously breaks snapshot isolation entirely). WAL mode allows one writer at a time without impeding readers. (See https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... for the pysqlite workaround)
- fforflo 8y ago
  I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess. Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D
  - BoorishBears 8y ago
    I read that first sentence like 3 times and still don't get it.
    - sansnomme 8y ago
      Often people use SQLite for sheer convenience instead of perceived performance or resource efficiency i.e. because SQLite is zero config, works out of the box. Sure spinning up a docker container with PostgreSQL is easy enough but why do that when you can use the default standard library with SQLite already embedded and linker configed?
      - BoorishBears 8y ago
        SQLite is an alternative to fopen not a full blown DB... And it’s performance and reliability are a huge part of why it runs on millions of devices everywhere. Your browser was never going to embed a build of PostgreSQL and Docker. Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. It’s like saying specifying the file directory SQLite uses is config.
thousandautumns 8y ago
> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) You really don't even need to know SQL anymore to keep things out of memory. In R, the dplyr/dbplyr package has SQL translations so you can utilize the exact same syntax as you would on in-memory data frames and it will execute as SQL using the database as a backend. Not saying people shouldn't learn SQL regardless, but even that shouldn't be an excuse for doing everything in-memory these days.

stuxnet79 8y ago

> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to the database engine?

fforflo 8y ago
As usual: it depends... If you're doing prototyping and are working in a Jupyter notebook, sure, go ahead and work on the Pandas-level. Once however you're done with prototyping and have settled to a "final_df" (I bet you have something like that in your last notebook cells), maybe you should think transforming some of the "columns" to sql queries (which are VCS-able, sometimes are faster, and most importantly other people can use them too. And instead of 10 people loading 10 different DFs, you can have 10 people querying the same table/view.
pletnes 8y ago
Dask has an out-of-memory dataframe implementation. Works great! I think it might support sql queries for that matter.
paulddraper 8y ago
> Assuming there is enough RAM available You will also have to assume you don't care much about the latency introduced by transferring everything over to your working process. (And in batch data processing situations you typically don't care much.)
perturbation 8y ago
Dplyr works great with SQL (both SQLite and others).

blattimwind 8y ago

> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure). WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and need workarounds; essentially they delay the BEGIN of a transaction until you issue DML statements which obviously breaks snapshot isolation entirely). WAL mode allows one writer at a time without impeding readers. (See https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... for the pysqlite workaround)

fforflo 8y ago
I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess. Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D
- BoorishBears 8y ago
  I read that first sentence like 3 times and still don't get it.
  - sansnomme 8y ago
    Often people use SQLite for sheer convenience instead of perceived performance or resource efficiency i.e. because SQLite is zero config, works out of the box. Sure spinning up a docker container with PostgreSQL is easy enough but why do that when you can use the default standard library with SQLite already embedded and linker configed?
    - BoorishBears 8y ago
      SQLite is an alternative to fopen not a full blown DB... And it’s performance and reliability are a huge part of why it runs on millions of devices everywhere. Your browser was never going to embed a build of PostgreSQL and Docker. Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen. It’s like saying specifying the file directory SQLite uses is config.

thousandautumns 8y ago

> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) You really don't even need to know SQL anymore to keep things out of memory. In R, the dplyr/dbplyr package has SQL translations so you can utilize the exact same syntax as you would on in-memory data frames and it will execute as SQL using the database as a backend. Not saying people shouldn't learn SQL regardless, but even that shouldn't be an excuse for doing everything in-memory these days.