A SQLite extension for reading large files line-by-line

157 pointsby polyrandalmost 3 years ago

9 comments

Hey, author here, happy to answer any questions! Also checkout this notebook for a deeper dive into sqlite-lines, along with a slick WASM demonstration and more thoughts on the codebase itself <a href="https://observablehq.com/@asg017/introducing-sqlite-lines" rel="nofollow">https://observablehq.com/@asg017/introducing-sqlite-lines</a>I really dig SQLite, and I believe SQLite extensions will push it to another level. I rarely reach for Pandas or other "traditional" tools and query languages, and instead opt for plain ol' SQLite and other extensions. As a shameless plug, I recently started a blog series on SQLite and related tools and extensions if you want to learn more! Next week I'll be publishing more SQLite extensions for parsing HTML + making HTTP requests <a href="https://observablehq.com/@asg017/a-new-sqlite-blog-series" rel="nofollow">https://observablehq.com/@asg017/a-new-sqlite-blog-series</a>A few other SQLite extensions:- xlite, for reading Excel files, in Rust <a href="https://github.com/x2bool/xlite" rel="nofollow">https://github.com/x2bool/xlite</a>- sqlean, several small SQLite extensions in C <a href="https://github.com/nalgeon/sqlean" rel="nofollow">https://github.com/nalgeon/sqlean</a>- mergestat, several SQLite extensions for developers (mainly Github's API) in Go <a href="https://github.com/mergestat/mergestat" rel="nofollow">https://github.com/mergestat/mergestat</a>

评论 #32289671 未加载

评论 #32289677 未加载

simonwalmost 3 years ago

I love this example from the docs:<pre><code> select line -> '$.id' as id, line -> '$.name' as name from lines_read("my-file.ndjson"); </code></pre> This is using the new -> JSON operator which was added to SQLite a few months ago.The lines_read() thing there is a table-valued function, which means it returns a virtual table that you can query. This is a streaming operation which means it's safe to run it against a 100GB+ file without worrying about it sucking all of the data into memory at once.Where this gets really useful is when you combine it with a create table statement:<pre><code> create table names as select line -> '$.id' as id, line -> '$.name' as name from lines_read("my-file.ndjson"); </code></pre> This should efficiently create a table with that exact subset of the data pulled from the newline-delimited JSON file.

评论 #32291338 未加载

cube2222almost 3 years ago

Hey!OctoSQL[0] author here, this is really impressive! I like this much more than the approach taken by other sqlite-based tools which first load stuff into SQLite and then let you query it.On the other hand, it does have a cons that it doesn't automatically infer the schema of the input JSON and you still have to manually parse the raw lines. Maybe it would be possible to surmount this by exposing a json-dedicated file reading function which also does SQL inference (I'm not knowledgable about SQLite internals)?One piece of feedback is with regard to the benchmarks: I think it would be worth it to add additional benchmarks which work on slightly more complex datasets than the one used here. I did a comparison of this vs OctoSQL on the Brazil dataset, and - as expected - sqlite-lines wiped the floor with it. However, then I ran the following queries on a slightly more complex dataset (the Amazon review dataset in this case, from SPyQL's benchmark notebook[1]):<pre><code> ~> time OCTOSQL_NO_TELEMETRY=1 ./octosql "SELECT COUNT(*), AVG(overall) FROM books.json WHERE reviewerName = 'James'" +-------+-------------------+ | count | avg_overall | +-------+-------------------+ | 3010 | 4.402325581395349 | +-------+-------------------+ real 0m49.805s user 0m32.169s sys 0m9.163s ~> time ./lines0-linux-amd64-sqlite3 :memory: "SELECT COUNT(*), AVG(json_extract(line, '$.overall')) FROM lines_read('books.json') WHERE json_extract(line, '$.reviewerName') = 'James'" 3010|4.40232558139535 real 1m47.933s user 1m27.024s sys 0m11.559s </code></pre> and as you can see, the results go in a very different direction.But anyhow, congrats on the project, and I'm pumped to see what you come up with next![0]: <a href="https://github.com/cube2222/octosql" rel="nofollow">https://github.com/cube2222/octosql</a>[1]: <a href="https://github.com/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb" rel="nofollow">https://github.com/dcmoura/spyql/blob/master/notebooks/json_...</a>

评论 #32289743 未加载

freecodyxalmost 3 years ago

I like the fact that author is extensively documenting the source code, following the sqlite spirit

bicijayalmost 3 years ago

Thats interesting, is there any way to add "custom delimiter templates" (or alternative tools)? I have some files where each line contains different kind of informations, but those are separated by their index on the row, so for example, customer name is the letter index 3 to letter index 10.

评论 #32289058 未加载

评论 #32289412 未加载

qbasic_foreveralmost 3 years ago

Very cool looking! I love using SQL for ad-hoc data analysis, my brain just groks the language so much more easily than other data query languages like python + pandas.

jokoonalmost 3 years ago

I've read that spatialite is not well maintained, is that true?

randyrandalmost 3 years ago

nice simple & reliable approach to a hard perf problem.

didgetmasteralmost 3 years ago

I have built my own database engine on top of a general-purpose data management system I invented. It has an application that will let you create a DB table from a file (Json, JsonL, CSV, etc.). The application uses the Qt windowing framework that also includes a Json parser so I just used it.It seems slower than I would like, so I wonder if your code could be used as a stand alone library for parsing the files? My current implementation is able to parse about 25K Json documents per second when each document has 20 KV pairs. I used the Chicago crime data available for download on their open data portal.How fast will your code parse it? Here is an example of three lines:[ { "ID" : "10224738", "Case Number" : "HY411648", "Date" : "2015-09-05T13:30:00", "Block" : "043XX S WOOD ST", "IUCR" : "0486", "Primary Type" : "BATTERY", "Description" : "DOMESTIC BATTERY SIMPLE", "Location Description" : "RESIDENCE", "Arrest" : "false", "Domestic" : "true", "Beat" : 924, "District" : 9, "Ward" : 12, "Community Area" : 61, "FBI Code" : "08B", "X Coordinate" : "1165074", "Y Coordinate" : "1875917", "Year" : 2015, "Latitude" : 41.815117, "Longitude" : -87.670000 }, { "ID" : "10224742", "Case Number" : "HY411435", "Date" : "2015-09-05T10:55:00", "Block" : "082XX S LOOMIS BLVD", "IUCR" : "0610", "Primary Type" : "BURGLARY", "Description" : "FORCIBLE ENTRY", "Location Description" : "RESIDENCE", "Arrest" : "false", "Domestic" : "false", "Beat" : 614, "District" : 6, "Ward" : 21, "Community Area" : 71, "FBI Code" : "05", "X Coordinate" : "1168430", "Y Coordinate" : "1850165", "Year" : 2015, "Latitude" : 41.744379, "Longitude" : -87.658431 }, { "ID" : "10224745", "Case Number" : "HY411654", "Date" : "2015-09-05T11:30:00", "Block" : "031XX W WASHINGTON BLVD", "IUCR" : "0320", "Primary Type" : "ROBBERY", "Description" : "STRONGARM - NO WEAPON", "Location Description" : "STREET", "Arrest" : "false", "Domestic" : "true", "Beat" : 1222, "District" : 12, "Ward" : 27, "Community Area" : 27, "FBI Code" : "03", "X Coordinate" : "1155536", "Y Coordinate" : "1900515", "Year" : 2015, "Latitude" : 41.882814, "Longitude" : -87.704326 } ]

评论 #32314141 未加载