51–60 of 74 posts
I recently wrote an SQLite extension that lets me query stored Protobuf messages. I was inspired by the JSON extension. Adding virtual tables to SQLite with an extension is tricky but kind of magical when you finally have the full query language to play with. https://github.com/rgov/sqlite_protobuf
Earlier quoted context omitted.
What I have found with any relational database that has this kind of JSON storage option(MySQL, PostgresSQL) is that you can take some kind of data that arrives via 3rd party as JSON and just dump it straight into the db as JSON as opposed to having to create a schema for it. It's nice to have the option for the data to still be queryable without having to make it a first class schema with all schema setup involved.…
One pattern that I've used when parsing large json responses is to have a table schema like frequently|used|data|in|these|cols|full_json_response So you pull out the fields you need frequently but have the full json so you can dig in to details the response on an adhoc basis. Not very efficient as you can end up storing a big blob of json, but you're not throwing away any data when you parse the response and your sch…
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 wis…
> (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 th…
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.)
Earlier quoted context omitted.
> 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…
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
Earlier quoted context omitted.
I've done this too. Note that for databases that support array columns, this is much nicer to represent with an array. Postgres supports them, for example. (SQLite doesn't)
Is there a link for the support documentation?
Earlier quoted context omitted.
What I have found with any relational database that has this kind of JSON storage option(MySQL, PostgresSQL) is that you can take some kind of data that arrives via 3rd party as JSON and just dump it straight into the db as JSON as opposed to having to create a schema for it. It's nice to have the option for the data to still be queryable without having to make it a first class schema with all schema setup involved.…
One pattern that I've used when parsing large json responses is to have a table schema like frequently|used|data|in|these|cols|full_json_response So you pull out the fields you need frequently but have the full json so you can dig in to details the response on an adhoc basis. Not very efficient as you can end up storing a big blob of json, but you're not throwing away any data when you parse the response and your sch…
This should be build in standard SQLite.
What are some useful use-cases for this? I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps? Can I create an index on a field within a JSON tuple?
I work quite a lot with SQLite since is the foundation of https://redisql.com/ The JSON module is really a god send! It allows to do things that otherwise would be extremely painful, difficult or not ergonomic. Just to take few examples, here ( http://redbeardlab.tech/rediSQL/blog/JaaS/ ) is a simple way to store JSON and doing manipulation on it, it would have been impossible without the JSON module. In this other e…
What are some useful use-cases for this? I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps? Can I create an index on a field within a JSON tuple?
One thing I like is when getting data from multiple tables. Let's say you have a product with multiple labels: usually you'll get one line per (product, label) tuple so you'll have to do some job application side if you what to get one [product, labels] object per product. With the json aggregate function you can get a (product, json array of labels) tuple per product and just have to do some json_decode in your appl…
What's the payoff of doing [product, labels], particularly if that means a json array of labels? I briefly investigated doing that for my application but I found that it would increase disk space (not really a big problem) but it would also make efficient querying a big chore (e.g. labels->products queries.)
I can see this being a good tradeoff if querying by labels is extremely rare and instead you only ever want to query product->labels. But that's still pretty damn fast with the (product, label) tuple schema.
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 wis…
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.