31:T115e,### A `"xsv_reader"` API for multiple files or URLs The [`xsv_reader`](https://github.com/asg017/sqlite-xsv/blob/main/docs.md#xsv_reader) API lets you pre-define a schema for a CSV and define the data source at query time. This is especially helpful when your data is across multiple CSV files, which you can all query with `csv_reader` and `fsdir()` like so: ```sql create virtual table temp.students_reader using csv_reader( id integer, name text, age integer, progess real ); with files as ( select name as path from fsdir('tests/data/student_files') ) select files.path, students.* from files join students_reader(files.path) as students where files.path like '%.csv'; /* ┌────────────────────────────────┬────┬───────────┬─────┬─────────┐ │ path │ id │ name │ age │ progess │ ├────────────────────────────────┼────┼───────────┼─────┼─────────┤ │ tests/data/student_files/a.csv │ 1 │ alex │ 10 │ 0.9 │ │ tests/data/student_files/a.csv │ 2 │ adrian │ 20 │ 0.8 │ │ tests/data/student_files/a.csv │ 3 │ andres │ 30 │ 0.7 │ │ tests/data/student_files/c.csv │ 1 │ craig │ 70 │ 0.4 │ │ tests/data/student_files/c.csv │ 2 │ catherine │ 90 │ 0.5 │ │ tests/data/student_files/c.csv │ 3 │ coin │ 80 │ 0.6 │ │ tests/data/student_files/b.csv │ 1 │ brian │ 60 │ 0.1 │ │ tests/data/student_files/b.csv │ 2 │ beto │ 50 │ 0.2 │ │ tests/data/student_files/b.csv │ 3 │ brandy │ 40 │ 0.3 │ └────────────────────────────────┴────┴───────────┴─────┴─────────┘ */ ``` This can also be used with [`sqlite-http`](https://github.com/asg017/sqlite-http) to query CSVs from a URL! ```sql .load ./http0 -- Reading a CSV from the wonderful LA Times COVID project -- https://github.com/datadesk/california-coronavirus-data create virtual table temp.cdph_age_reader using csv_reader( date, age text, confirmed_cases_total int, confirmed_cases_percent float, deaths_total int, deaths_percent float ); create table cdph_age as select * from temp.cdph_age_reader( http_get_body( 'https://raw.githubusercontent.com/datadesk/california-coronavirus-data/master/cdph-age.csv' ) ); select * from cdph_age limit 5; /* ┌────────────┬───────┬───────────────────────┬─────────────────────────┬──────────────┬────────────────┐ │ date │ age │ confirmed_cases_total │ confirmed_cases_percent │ deaths_total │ deaths_percent │ ├────────────┼───────┼───────────────────────┼─────────────────────────┼──────────────┼────────────────┤ │ 2023-01-03 │ 0-4 │ 371691 │ 0.034 │ 32 │ 0.0 │ │ 2023-01-03 │ 80+ │ 292252 │ 0.027 │ 37038 │ 0.378 │ │ 2023-01-03 │ 18–34 │ 3416056 │ 0.312 │ 1655 │ 0.017 │ │ 2023-01-03 │ 35–49 │ 2530259 │ 0.231 │ 6135 │ 0.063 │ │ 2023-01-03 │ 50–59 │ 1379087 │ 0.126 │ 10892 │ 0.111 │ └────────────┴───────┴───────────────────────┴─────────────────────────┴──────────────┴────────────────┘ */ ```32:T7df,## Future Updates `sqlite-xsv` is still considered beta, and there's many more functions and tools I want to add to make it easier to integrate in today's modern data work. For example, in the near future there will be "wildcard" options in a new `xsvs` table, where you can specify wildcard globs to refer to multiple files in a single directly (which can be done today with `xsv_reader` and `fsdir`, but is less ergonomic). ```sql create virtual table students using csvs( pattern="./fall2023/**.csv" ); select filename, * from students; /* ┌────────────────────────────────┬────┬───────────┬─────┬─────────┐ │ filename │ id │ name │ age │ progess │ ├────────────────────────────────┼────┼───────────┼─────┼─────────┤ │ tests/data/student_files/a.csv │ 1 │ alex │ 10 │ 0.9 │ │ tests/data/student_files/a.csv │ 2 │ adrian │ 20 │ 0.8 │ │ tests/data/student_files/c.csv │ 3 │ coin │ 80 │ 0.6 │ │ tests/data/student_files/b.csv │ 4 │ brandy │ 40 │ 0.3 │ └────────────────────────────────┴────┴───────────┴─────┴─────────┘ */ ``` Additionally, there will one day be optional HTTP and S3 support, for directly querying CSVs from direct URLs and S3 buckets. ```sql create virtual table logs using csv( source="s3://your_bucket/logs.csv" ); select * from logs; ``` These changes will take time to do correctly. If you want to see this happen faster (or want customized features for your team), consider [supporting my work](https://alexgarcia.xyz/work.html#support-open-source).33:[{"id":"105e1447ae6bd7ee2fc6a4c907bed8655302a32d7f25c746f81e2cd3b633b315406955b9bdb0e79024385a799dd977b1422486b8e517593aedb626ca60659cb9","href":"https://static.observableusercontent.com/files/105e1447ae6bd7ee2fc6a4c907bed8655302a32d7f25c746f81e2cd3b633b315406955b9bdb0e79024385a799dd977b1422486b8e517593aedb626ca60659cb9","name":"board.jpg","type":"image/jpeg","lastModified":1673310428979.125,"size":75891},{"id":"7c0700fd93f1a8e0715aa5b25db57dde5af91022a88b5883ba31b005672e42ddfd770e04610f31ada7daf45e53a5ffae5c1cd2768ac9f9009686f54876410199","href":"https://static.observableusercontent.com/files/7c0700fd93f1a8e0715aa5b25db57dde5af91022a88b5883ba31b005672e42ddfd770e04610f31ada7daf45e53a5ffae5c1cd2768ac9f9009686f54876410199","name":"coffee.jpg","type":"image/jpeg","lastModified":1673310428980.783,"size":54880},{"id":"967786f332eeaad07ace2cfce8271d0fa5830af99da4a461ff5a0a1ad7a0cbd8c8a233b1a5d183ab4a1da2dce3ee0ea54c21927523505ff036f35b80b8a94e3c","href":"https://static.observableusercontent.com/files/967786f332eeaad07ace2cfce8271d0fa5830af99da4a461ff5a0a1ad7a0cbd8c8a233b1a5d183ab4a1da2dce3ee0ea54c21927523505ff036f35b80b8a94e3c","name":"computer.jpg","type":"image/jpeg","lastModified":1673310429001.046,"size":44391},{"id":"f9840f6a25a116b237f200c17fd26da0618e70e7bdb9713d7c9633edaa01dea03eec3787f747a36e048860baaf0d32248c2d48c3fb226bc1f73de95b0751d568","href":"https://static.observableusercontent.com/files/f9840f6a25a116b237f200c17fd26da0618e70e7bdb9713d7c9633edaa01dea03eec3787f747a36e048860baaf0d32248c2d48c3fb226bc1f73de95b0751d568","name":"results.json","type":"application/json","lastModified":1673562925450.386,"size":10361},{"id":"6ec297146bc5156897c7ee3e6e870042ac784b82d4bf99c8a149745e3ad6d96272eb684fbc8b9b0e2b9e0c26c84e5ed1f6c58b6459fe43a4012e2b3824ebb959","href":"https://static.observableusercontent.com/files/6ec297146bc5156897c7ee3e6e870042ac784b82d4bf99c8a149745e3ad6d96272eb684fbc8b9b0e2b9e0c26c84e5ed1f6c58b6459fe43a4012e2b3824ebb959","name":"aggregate@1.png","type":"image/png","lastModified":1673569355726.323,"size":36613}] 2f:{"title":"Introducing sqlite-xsv: The Fastest CSV Parser for SQLite","cells":[{"id":0,"value":"# Introducing sqlite-xsv: The Fastest CSV Parser for SQLite\n\n[sqlite-xsv](https://github.com/asg017/sqlite-xsv) is a new SQLite extension for querying CSVs, TSVs, and other-SVs, written in Rust, based on [sqlite-loadable](https://github.com/asg017/sqlite-loadable-rs) and the excellent [csv crate](https://docs.rs/csv/latest/csv/). \n","pinned":false,"hidden":false,"mode":"md"},{"id":10,"value":"

\n ${imgs.map(d=>htl.html`

`)}\n

\n DALL·E attempts for this post's thumbnail\n ","pinned":false,"hidden":false,"mode":"html"},{"id":82,"value":"## Speed\n\nsqlite-xsv is the fastest CSV SQLite extension that currently exists, and is one of the fastest CSV reading tools out there! \n","pinned":false,"hidden":false,"mode":"md"},{"id":64,"value":"viewof replay = Inputs.button(\"Replay\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":62,"value":"bench = (replay,\nimportCell(\"chart\", \"@asg017/hyperfine-benchmark-viz1\", {\n animated: true,\n highlight: [\"./sqlite-xsv.sh\"],\n compareTo: \"./sqlite-xsv.sh\",\n title: \"\",\n footer: \"sqlite-xsv v0.0.1 on a DigitalOcean Droplet, 8GB of RAM\",\n data: await FileAttachment(\"results.json\").json()\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":97,"value":"However, `sqlite-xsv` is not a silver bullet. For analytical queries on CSV files (GROUP BYs, ORDER BYs, etc.), then new-age data tools like DuckDB and DataFusion outperform by a large margin.","pinned":false,"hidden":false,"mode":"md"},{"id":99,"value":"aggregate = FileAttachment(\"aggregate@1.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":104,"value":"See [the benchmarks](https://github.com/asg017/sqlite-xsv/tree/main/benchmarks) for more details and caveats.\n","pinned":false,"hidden":false,"mode":"md"},{"id":44,"value":"## How sqlite-xsv is different than the rest\n\nCurrent in SQLite, you have 2 options for parsing CSVs: the [official SQLite CSV virtual table](https://www.sqlite.org/csv.html) and sqlean's [vsv counterpart](https://github.com/nalgeon/sqlean/blob/main/docs/vsv.md). Both work as advertised, and are perfectly fine by itself!\n\nHowever, sqlite-xsv provides various ergonomic benefits over these two extensions, such as:\n\n### Querying gzip'ed and zstd compressed files directly\n\nIf your CSVs are compress with `gzip` or `zstd`, there's no need to uncompress before querying! Just query the file directly and go along your merry way\n\n```sql\ncreate virtual table temp.students_gz using csv(\n filename=\"students.csv.gz\"\n);\n\nselect * from temp.students_gz;\n\n\ncreate virtual table temp.students_zst using csv(\n filename=\"students.csv.zst\"\n);\n\nselect * from temp.students_zst;\n```\n","pinned":false,"hidden":false,"mode":"md"},{"id":122,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":111,"value":"### Flexible column names in the constructor\n\nIf you want to provide custom column names into the CSV or VSV extensions, you have to awkward include them in the `schema=\"\"` argument like so:\n\n```sql\ncreate virtual table students using csv(\n filename=\"students.csv\",\n schema=\"create table x(id integer, name text, age integer, progress real)\"\n);\n```\n\nBut with `sqlite-xsv`, you can provide column declarations directly in the constructor! Small change, but huge quality-of-life improvement with syntax highlighting, long declarations, special types, etc.\n\n```sql\ncreate virtual table students using csv(\n filename=\"students.csv\",\n id integer,\n name text,\n age integer,\n progress real\n)\n```","pinned":false,"hidden":false,"mode":"md"},{"id":58,"value":"$32","pinned":false,"hidden":false,"mode":"md"},{"id":25,"value":"## Stay in the loop!\n\nThis is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me).\n","pinned":false,"hidden":false,"mode":"md"},{"id":35,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":13,"value":"imgs = Promise.all([\n FileAttachment(\"board.jpg\").url(),\n FileAttachment(\"coffee.jpg\").url(),\n FileAttachment(\"computer.jpg\").url()\n])","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"import { preview, previews } from \"@d3/gallery\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":66,"value":"import { importCell } from \"@mbostock/dataflow\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}