4 ms·

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 se

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://cldellow.com/2018/06/22/sqlite-parquet-vtable.html

pstuart 4y ago
Dang, that's a bummer -- thanks for the intel, as well as the article / Parquet code.
mattewong 4y ago
https://github.com/liquidaty/zsv/blob/main/app/external/sqlite3/sqlite3_csv_vtab-zsv.c 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
- westurner 4y ago
  What are the differences between the zsv and csv parsers? Is csvw with linked data URIs also doable?
  - mattewong 4y ago
    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...
    - westurner 4y ago
      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.

pstuart 4y ago

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

mattewong 4y ago

https://github.com/liquidaty/zsv/blob/main/app/external/sqlite3/sqlite3_csv_vtab-zsv.c 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

westurner 4y ago
What are the differences between the zsv and csv parsers? Is csvw with linked data URIs also doable?
- mattewong 4y ago
  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...
  - westurner 4y ago
    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.