3 ms·
A few days ago, at work, I transcoded a 13GB file from a proprietary binary format in to a 190GB JSON file... just so I could use jq[0]. This was a small file b
by nly 6y ago
A few days ago, at work, I transcoded a 13GB file from a proprietary binary format in to a 190GB JSON file... just so I could use jq[0]. This was a small file by our standards.
Performance matters
[0] https://stedolan.github.io/jq/ https://stedolan.github.io/jq/
- smt88 6y agoI've done this same thing (advanced text searches from a proprietary format), but I just inserted it into a local SQLite database instead. Is there a reason JSON/jq made more sense for your purposes than SQLite (or even a local Mongo or Postgres DB, if you wanted to stick with JSON)?
- remexre 6y agopipes are really nice, jq lets you use them on json; compactness of syntax is another plus
- nly 6y agoThe data structures are often nested, and can change quite often, so spending time normalizing all of those in to columns and tables wouldn't make a lot of sense. I suppose on the human level the more important reason is I often get requests from non-techies in the company to see snippets from this data. They can read JSON just fine (Well, most of them... a few still insist on using Excel, so I have to flatten the JSON to csv with jq)
- smt88 6y ago> spending time normalizing all of those in to columns and tables wouldn't make a lot of sense I agree with you (depending on the use-case, of course). I was talking about dumping the data into one of those databases (SQLite, Postgres, or MongoDB) and using their JSON querying functionality.
- derefr 6y agoIf you're really talking about streaming a JSON Lines file into a SQL DB as a series of binary JSON documents, that might make a lot of sense. If, however, you're really talking about a single JSON document, then you're going to be generating that 190GB intermediate JSON file either way. Plugging it into a database to query it just seems like an extra step for little benefit. (It's basically a variant on the question of whether you'll get better whole-pipeline latency from a data warehouse, or a data lake — which has no general answer.)
- hombre_fatal 6y ago"JSON Lines" isn't "streamable JSON", it's just JSON that's easier to use with existing tools in a streaming fashion. Just like you can stream an HTML/XML file with a SAX parser, you can do the same with JSON. I don't think there's any reason it's necessary to have an 190gb json file, nor anything stopping one from incrementally dumping it into Sqlite. Though it would depend on the format of the proprietary file. But I will add that there's an obvious benefit to dumping the data into the database: it has indexes and querying capacity that doesn't involve full 190GB file scans. The I/O of a 190gb scan alone takes time.
- smt88 6y agoThe DB can also provide transparent compression, turning that 190GB file into something hopefully a small fraction of the size.
- JamesSwift 6y agoI got deep into sqlite json handling recently and its actually has really powerful json support out-of-the-box. I thought I was going to have to move to Postgres for some of my parsing/querying but haven't had the need so far.
- abraxas 6y agoThe explosion in size is terriying. I'm guessing the JSON mostly contained numerics?
- sroussey 6y agoGet your google location history. On an android phone is can be gigabytes. Just one user.
- teleforce 6y agoSorry if this appears as a silly question, just wondering that the "proprietary format" that you're transcoding is a well-known format to you right?