7 ms·
Show HN: ZSV (Zip Separated Values) columnar data format
A columnar data format built using simple, mature technologies.
- modulus1 2y agoCan't store tabs or newlines, odd choice.
- hafthor 2y agoyeah, this is a limitation from the TSV format this is based on - there is an extension to the format that supports storing binary blobs - ref: https://github.com/Hafthor/zsvutil?tab=readme-ov-file#nestedbinary-data https://github.com/Hafthor/zsvutil?tab=readme-ov-file#nested...
- alexandreyc 2y agoBasically it's the same limitations as CSV. At least you could use something less likely to appear in data as record sepator (like 0x1E) Otherwise it's an interesting idea!
- tboerstad 2y ago0x1E is the record separator, in ASCII precisely for this purpose. Too bad it’s not popular, here we’re stuck with inferior TSV/CSV
- orf 2y agoStrings can contain 0x1E, so it has exactly the same issues as a tab character but with all the downsides of it not being an easy, “simple” character.
- mattnewton 2y agoI can't easily type that out - and once the format can't be read / editing in a simple text editor, I'm starting to lean towards a nice binary format like protobuf.
- bobbylarrybobby 2y agoAs far as I know, thanks to quoting it is possible to put basically any data you want in a CSV.
- layer8 2y agoThe problem is there is no uniform standard for quoting and escaping in CSV, and different software uses different variants.
- Dylan16807 2y agoThere is a standard, and it is very simple and easy to use. Different software uses different variants because we're not allowed to have nice things and devs are too lazy to use something slightly more complicated than .split(',') Though if you're going to ban some common characters anyway like TSV, you might as well use CSV and ban commas, newlines, and quotation marks.
- inimino 2y ago(Offtopic, but just FYI) it's tenet (principle) not tenant (building resident).
- hafthor 2y agodoh. fixed. thanks!
- nextaccountic 2y agocan't you just do quoting?
- olejorgenb 2y agohttps://github.com/Hafthor/zsvutil?tab=readme-ov-file#what-are-some-key-shortcomings-of-zsv https://github.com/Hafthor/zsvutil?tab=readme-ov-file#what-a... > Any escaping or encoding of these characters would make the format less human-readable, harder to parse and could introduce ambiguity and consistency problems. Found the wording of "could introduce ambiguity and consistency problems" a bit odd, but guess they mean that even if things are specified precisely (so there's no ambiguity) not everyone would follow the rules or something? And they want to play nice with other tools following the TSV "standard"
- 8n4vidtmkvmk 2y agoPlease. I wrote a csv parser a couple weeks ago in an hour or two. It's not that hard to handle the quoting and edge cases. Yes, maybe different parsers will handle them differently, but just document your choices and that's that. How is ambiguity better than completely disallowing certain chars? That's a non-starter
- deleted 2y ago[deleted]
- romanows 2y agoReading quick, it's because the tab is used to indicate nested tabular data in a column. I wonder why not just have a zsv in the zsv?
- deleted 2y ago[deleted]
- jiggawatts 2y agoThis copied the superficial data layout without the key benefit of modern columnar formats: segment elimination. Most such formats support efficient querying by skipping the disk read step entirely when a chunk of data is not relevant to a query. This is done by splitting the data into segments of about 100K rows, and then calculating the min/max range for each column. That is stored separately in a header or small metadata file. This allows huge chunks of the data to be entirely skipped if it falls out of range of some query predicate. PS: the same compression ratio advantages could be achieved by compressing columns stored as JSON arrays, but such a format could encode all Unicode characters and has a readily available decoder in all mainstream programming languages.
- mlyle 2y agoIt's there, specified as an optional feature. > Price⇥⇥0 {rows:2, distinct:2, minvalue:111.11, maxvalue:222.22} 111.11⮐222.22⮐ > Price⇥⇥1 {rows:1, distinct:1, minvalue:333.33, maxvalue:333.33} 333.33⮐
- hafthor 2y agoI like your idea of storing columns as JSON arrays. I might play around with that. Thanks for giving it a look.
- jiggawatts 2y agoI have a sinking feeling like I’ve unleashed something here. Some future programmer will be cursing my name as they try to make columnar JSON decoding performant.
- hafthor 2y agohehe. I added an alternative JSON inner format spec to the readme. I need to add JSON and CSV support to the zsvutil itself next. I may actually change the spec to default to JSON. All because of you. haha.
- orthoxerox 2y agoIt is simple, but how do you access the price in row #1234567890? If your data doesn't have this many records and can fit into RAM, a basic NLJSON or CSV will work just as well.
- CapitalistCartr 2y agoWhat is NLJSON?
- svieira 2y agoNew Line delimited JSON
- chuckadams 2y agoAlso known as JSONL, or JSON Lines. Basically a file of JSON objects separated by newlines. Popular format for logs these days for obvious reasons.
- rzzzt 2y agoNDJSON is the shorthand I've seen: https://github.com/ndjson/ndjson-spec https://github.com/ndjson/ndjson-spec
- eichin 2y agohttps://jsonlines.org/ https://jsonlines.org/ was the first "this is trivial but let's write it down so maybe the name will stick" spec for it (from 2013ish)
- mirekrusin 2y agoMissed opportunity to just call it JSONS.
- cm2187 2y agoLike parquet this isn't really meant for RDBMS type of database, more like for analytics over large datasets. I work in an environment where we typically have tables with over 300 columns, 10s if not 100s millions of rows daily. When you want to do a simple sum/group by involving 2 or 3 columns, it is great to have a column store file format, where you only read the columns you need and those are compressed. The price you pay is that it is inefficient for single record access, or for "select * " kind of queries.
- psanford 2y agoThe only benefit this format provides is the ability to read some columns without needing to read all columns. Unfortunately it is not a seekable format. That's a pretty big miss. It also wouldn't be that hard to make it seekable. All you would have to do is make each tsv file two columns: record-id, value.
- deleted 2y ago[deleted]
- karaterobot 2y agoWhat do you mean it's not seekable? > ZIP files are a collection of individually compressed files, with a directory as a footer to the file, which makes it easy to seek to a specific file without reading the whole file... The nature of .zip files makes it possible to seek and read just the columns required without having to read/decode the other columns.
- thehappypm 2y agoSeeking within a column
- hafthor 2y agoThere's two ways to limit the number of column-rows you have to read. One is by file partitioning, that is having many ZSV files rather than one giant one, ideally organized by partitioning key field(s). The other way is mentioned as an extension to the format itself which functions much like rowgroups do in Parquet. https://github.com/Hafthor/zsvutil?tab=readme-ov-file#row-groups https://github.com/Hafthor/zsvutil?tab=readme-ov-file#row-gr... Thanks for taking a look.
- pbnjay 2y agoWouldn’t be too hard to add a secondary “file” in the zip with an extra index
- avidphantasm 2y agoWe need to have a come to Jesus meeting about these columnar formats.
- deleted 2y ago[deleted]
- andenacitelli 2y agoCan we just all converge on Parquet + Arrow and call it a day please? Too much effort being put into 1..N ways to solve a problem that would be better put towards a single standard. We work with Parquet + Arrow every day at $DAYJOB in a ML and Big Data context and it's been great. We don't even think we're using it to its fullest potential, but it's never been the bottleneck for us.
- pdimitar 2y agoHow is the data schema description language btw? I haven't used either yet.
- andenacitelli 2y agoHaven't used it directly myself. We mostly just use it for DataFrame crunching via pandas and/or polars (our usage is mixed) which tends to benefit nicely from columnar access.
- jonbaer 2y agoCheck out DFLib (https://dflib.org https://dflib.org)
- btbuildem 2y agoColour me out of the loop, but what is the utility of this type of approach? I can't seem to grok this.
- theamk 2y agomany colums (100's) and you only need one. This approach makes it much faster.
- warthog 2y agoThis is very promising for nested datapoints
- hwbunny 2y ago[flagged]
- deleted 2y ago[deleted]
- jitl 2y agoI think “human readability” isn’t a great feature for a columnar data format, because once you get data on a scale where the column oriented layout makes sense, you’re way past the scale where a human would be want to read over the stored data anyways. Like, no human is going to read 50k rows, much less 10m rows. I guess it’s nice you can spot check the rows using only zip & head -n 10 and paste, but I don’t think that nice-ness is a good reason to pick a format that forbids common ASCII characters and doesn’t have widespread support. It’s guess there’s a sort of perma-computing angle here, this format is simple enough that you could pack a lot of almanac data into it, and given a working zlib get it back out with very limited dependencies. But given the petabytes of parquet files out there, I feel like the format is here to stay, much like sqlite is here to stay. EDIT: there is a great handy CLI tool for doing SQL on parquet, csv, sqlite3, and other tabular data formats called duckdb. Handy for wrangling and analyzing tabular data from 100 to 10m rows and up.
- lionkor 2y agoEven more so if you store personal data in there, which ofc would be encrypted per row.
- deleted 2y ago[deleted]
- rhelz 2y ago> Like, no human is going to read 50k rows, much less 10m rows. Well, its 2AM, some dork has checked in code which breaks production, and it absolutely positively has to be fixed by 6:00am before the customer comes in. Your bleary eyes are scaring through log files and data files, trying to find the answer.. ... believe me, you will appreciate human-readable formats for both of those. You just want to cat out the the entries in the db which the new code can't handle... the last thing you want to do is to have to invoke some other tool or write some other script to make the data human readable. And when you find the problem, you will want to just be able to edit a text file containing test cases to verify the fix. You don't want to write some script to generate and insert the data....at 2am, you are likely to write a buggy script which may keep you from realizing that you've already fixed the problem....or worse, indicate that you have fixed the problem when you haven't. Fewer moving parts is always better.
- makmanalp 2y agoThe core idea with these compressed columnar "big data" formats is that they minimize storage accesses. Nothing else really matters as much. If you're gonna have to load a sizable chunk of the file to get to the bits you need, the format you store it in starts mattering less. What this gets right: Part of the reason you want to store columns together is that similar values compress well, so you could reduce your IO: smaller files are faster to load into memory. However in many cases (e.g. Arrow, Parquet) lightweight compression formats are preferred here, e.g. run length encoding (1,1,1,1,1,1,5,5,5,5,3,3,3 -> 6x1,5x4,3x3) or dictionary encoding (if your column is enum-like, you can store each enum value as a byte flag) because they can be scanned without decoding, amplifying your savings. What it misses on (IMHO): - There's a metadata field but it doesn't contain any offsets to access a specific column quickly. So if you have 8 columns of 2GB each, to just get to the 7th column you have to read 12GB first which is quite wasteful. If you store just an offset, you could be reading a handful of bytes. Massive savings. - Within each column, how do you get to the range of values you want? Most columnar formats have stripes (i.e. stored in chunks of X rows each) which contain statistics (this stripe or range of values contains min value A, max value B) that allow you to skip chunks really fast. So again within that 2GB you have to read not much more than you strictly have to. If this reminds you of an on-disk tree where you first hop to a column and then hop to some specific stripes, yeah, that's pretty much the idea. ----- Sidenote: I've generally concluded that "human readable" is only a virtue for encoding formats that aren't doing heavy lifting, like the API call your web app is sending to the backend. Even in that case, your HTTP request is wrapped in gzip, wrapped in TLS, wrapped in TCP and chunked to all hell. No one complains about the burden caused by those. So what's one more layer of decoding? We can just demand to have tools that are not terrible, and the result is pretty transparent to us. The format is mostly for the computer, not you. When I hear about stuff like terabytes of JSON just being dumped into s3 buckets and then consumed again by some other worker I have a fit because it's so easy and cheap these days not to be that wasteful.
- hafthor 2y agoThanks for taking a look. Regarding seekable columns, that's the reason why I use the ZIP file format. It has a central directory at the end of the ZIP file that has locations to each file inside, making it so you can seek to a specific column file to extract.
- haolez 2y agoI love this kind of minimalist and clever solution where one developer delivers value similar to that of projects with tens of developers involved. Unfortunately, it's still not enough to defeat the true army of complexity, which contains thousands of developers :)
- hafthor 2y agoThanks for taking a look.