Hacker News Reader

Comment by pstuart | Hacker News Reader

And in fact there is a CSV virtual table available from SQLite but it's not built in the normal client: https://www.sqlite.org/csv.html

It really should be, as the code is tiny and this functionality is not overly exotic.

View on HN

In my experience [1], the CSV virtual table was really slow. Doing some analysis on a 1,291 MB file, a query took 24.7 seconds using the virtual table vs 3.4 seconds if you imported the file first.

The CSV virtual table source code is a good pedagogical tool for teaching how to build a virtual table, though.

[1]: https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html

https://github.com/liquidaty/zsv/blob/main/app/external/sqli... modifies the sqlite3 virtual table engine to use the faster zsv parser. have not quantified the difference, but in all tests I have run, `zsv sql` runs faster (sometimes much faster) than other sqlite3-on-CSV solutions mentioned in this entire discussion (unless you include those that cache their indexes and then measure against a post-cached query). Disclaimer: I'm the main zsv author

What are the differences between the zsv and csv parsers?

Is csvw with linked data URIs also doable?

Not sure what you mean by csvw. But, zsvlib is a CSV parser, and zsv is a CLI that uses zsvlib. zsv also uses the sqlite3 vtable based on the example in the original sqlite3 code, but it modifies it to use the zsvlib parser instead of the original CSV parser. The zsv parser is different from most CSV parsers in how it uses SIMD operations and minimizes memory copying. zsvlib parses CSV based on the same spec that Excel implements, so having data URIs in the CSV is fine, but if you wanted a compound value (e.g. text + link), you would need to overlay your own structure into the CSV text data (for example, embed JSON inside a column of CSV data). Not sure that answers your question but if not, feel free to add further detail and I'll try again...

zsvlib does SIMD CSV like simdjson? Sweet.

Zsvlib on cloudfuzz would be good if that's not already

Yeah linked data schema support is distinct from the parser primitives and xsd data type uris, for example.

Dang, that's a bummer -- thanks for the intel, as well as the article / Parquet code.

Reply on news.ycombinator.com