12 ms·
One-liner for running queries against CSV files with SQLite
- practice9 4y agoIn the past I've used https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv to query some large CSVs
- mrfusion 4y agoHow smart is SQLite at detecting column types from Csv data? I once wrote a Python script to load csv files into SQLite. It had a whole hierarchy of rules to determine the data type of each column.
- mattewong 4y agoIt doesn't detect column types automatically-- they are imported as text. You can, however, use math functions on them and sqlite3 will dynamically convert where possible (e.g. "select number_1_stored_as_text + 1 from mytable" will output 2)
- kbouck 4y ago> sqlite3 :memory: -cmd '.mode csv' ... csv mode can also be set with a switch: sqlite3 :memory: -csv
- rgovostes 4y agoSQLite's virtual table API (https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html) makes it possible to access other data structures through the query engine. You don't need to know much if anything about how the database engine executes queries, you only need to implement the callbacks it needs to do its job. A few years ago I wrote an extension to let me search through serialized Protobufs which were stored as blobs in a regular database. https://github.com/rgov/sqlite_protobuf https://github.com/rgov/sqlite_protobuf
- electroly 4y agoI like the virtual table API a lot but it has some serious drawbacks. You don't need to know much and indeed, you can't know much about the execution engine, even if that knowledge would help you. Many parts of the query are not pushed down into the virtual table. For instance, if the user query is: SELECT COUNT(*) FROM my_vtab; ... the query your virtual table will effectively see is: SELECT * FROM my_vtab; SQLite does the counting. That's great, unless you already know the count and could have reported it directly rather than actually returning every row in the table. You're forced to retrieve and return every row because you have no idea that it was actually just a count. As another example, if the user query includes a join, you won't see the join. Instead, you will receive a series of N queries for individual IDs, even if you could have more efficiently retrieved them in a batch. The join one is particularly nasty. If you're writing a virtual table that accesses a remote resource with some latency, any join will absolutely ruin your performance as you pay a full network roundtrip for each of those N queries. I wrote a module that exposes remote SQL Server/PostgreSQL/MySQL servers as SQLite virtual tables, and joins basically don't work at all if your server is not on your local network. There's nothing I can do about it (other than heuristically guessing what IDs might be coming and request them ahead of time) because SQLite doesn't provide enough information to the virtual table layer. It's my understanding that PostgreSQL's foreign data wrappers (a similar feature to SQLite's virtual tables) push much more information about the query down to the wrapper layer, but I haven't used it myself.
- lazypenguin 4y agoYou also can’t add new columns using alter statements. I really like virtual tables in SQLite but It would be nice if documentation included some limitations and reasons not to use.
- petercooper 4y agoI wrote a module that exposes remote SQL Server/PostgreSQL/MySQL servers as SQLite virtual tables, and joins basically don't work at all if your server is not on your local network. There's nothing I can do about it (other than heuristically guessing what IDs might be coming and request them ahead of time) because SQLite doesn't provide enough information to the virtual table layer. Is it possible to wait until all of the queries are in and then do some query planning at your end to resolve this? Or is it entirely synchronous with no way to do that?
- kitd 4y agoI had to do something very similar for analysing CVE information recently, but I don't remember having to use the :memory: option. I suspect it defaults to that if no .db file is specified. Slightly tangentially, when doing aggregated queries, SQLite has a very useful group_concat(..., ',') function that will concatenate the expression in the first arg for each row in the group, separated by the separator in the 2nd arg. In many situations SQLite is a suitable alternative to jq for simple tabular JSON.
- simonw 4y agoI just tried it without :memory: and it dropped me into the SQLite shell without executing the query: % sqlite3 -cmd '.mode csv' -cmd '.import taxi.csv taxi' \ 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count' SQLite version 3.36.0 2021-06-18 18:58:49 Enter ".help" for usage hints. sqlite>
- kitd 4y agoOk, I just went back to see what I did. In fact I echoed a script into sqlite via stdin, not by using `-cmd ''`. In that case, I didn't need to use the :memory: table, and sqlite exited normally when done.
- dotancohen 4y agoLately I've been using Visidata for any text file that looks like a table or other squarish data source, including JSON. https://www.visidata.org/ https://www.visidata.org/
- dymk 4y agoThis looks like my new best friend
- dotancohen 4y agoJust wait until you discover the file browser ))
- wenc 4y agoVisidata is wonderful. Also for querying large CSV and Parquet files, I use DuckDB. It has a vectorized engine and is super fast. It can also query SQLite files directly. The SQL support is outstanding. https://duckdb.org/ https://duckdb.org/ Just have start the DuckDB REPL and start querying e.g. Select * from ‘bob.CSV’ a Join ‘Mary.parquet’ b On a.Id = b.Id Zips through multi GB files in a few seconds.
- michaelcampbell 4y agoIs the capitalized keywords a requirement? ew (IMO)
- wenc 4y agoNo, that’s an artifact of me typing on my phone. Also if you know SQL you’ll know that SQL keywords are case insensitive in most DBMSes. Don’t be too quick to ew.
- michaelcampbell 4y agoI was ew'ing at capitalized keywords, whether or not they're required. I'll be as quick as I like, thanks.
- throwaway892238 4y agosqlite3 :memory: -cmd '.mode csv' ... It should be a war crime for programs in 2022 to use non-UNIX/non-GNU style command line options. Add it to the Rome Statute's Article 7 list of crimes against humanity. Full blown tribunal at The Hague presided over by the international criminal court. Punishable by having to use Visual Basic 3.0 for all programming for the rest of their life.
- _joel 4y agoWould :memory: even parse in some shells?
- ketralnis 4y agoWhy wouldn't it? I'm not aware of colons being a special character in any shell I can think of
- naniwaduni 4y agoColons are a little bit special in Bournish shells, since they're a delimiter in variable assignments after which tilde expansions, and some shells extend this behavior to command line arguments. Frankly, I don't find it outside the realm of possibility that there's some combination of options that will make :memory: misparse on a popular shell, I just don't know of any...
- chasil 4y agoMy VMS users say the same thing. Wow, do I not want that to happen. I am not a fan of DCL.
- deleted 4y ago[deleted]
- svnpenn 4y agoGo has been doing same thing for a decade now: https://godocs.io/flag https://godocs.io/flag Personally I'm fine with it. The whole, "let's combine 5 letter options into one string", always smacked of excess code golf to me.
- blacksqr 4y agoSquawk: An Awk-like program that uses SQL. Can parse, format, filter, and combine data from multiple files. Powered by SQLite. https://wiki.tcl-lang.org/page/Sqawk https://wiki.tcl-lang.org/page/Sqawk
- Uptrenda 4y agoI'm looking through this guys website for 'today I learned' and at first I'm impressed by how many of them there are. But then I start thinking: when you're trying to solve a problem you search for a lot of data. None of his posts are attributed. He's getting all his information from somewhere and then he goes and posts these articles just ripping off other sources. I can understand when its based on your original work but this website reads more like basic questions posted on Stackoverflow. E.g. 'how to connect to a website with IPv6." Tell me he didn't just Google that and post the result. 0/10
- actionfromafar 4y agoHm, I guess most half-assed techy blogs are 0/10 then. Honestly I don't see a problem with the few posts I looked at. It's like recipes. You can't copyright recipes. At least it's not AI-generated blogspam, but a modicum of at least curating went in here.
- droopyEyelids 4y agoThats actually allowed, if you run your own personal website.
- lemoncurd 4y ago
- simonw 4y agoYou should look harder! I attribute in plenty of these pieces, where appropriate. Here's a query showing the 23 posts that link to StackOverflow, for example: https://til.simonwillison.net/tils?sql=select+*+from+til+where+body+like+%27%25stackoverflow%25%27 https://til.simonwillison.net/tils?sql=select+*+from+til+whe... And 41 where I credit someone on Twitter: https://til.simonwillison.net/tils?sql=select+*+from+til+where+body+like+%27%25twitter.com%25%27 https://til.simonwillison.net/tils?sql=select+*+from+til+whe... More commonly I'll include a link from the TIL back to a GitHub Issue thread where I figured something out - those issue threads often link back to other sources. For that IPv6 one: https://til.simonwillison.net/networking/http-ipv6 https://til.simonwillison.net/networking/http-ipv6 I had tried and failed to figure this out using Google searches in the past. I wrote that up after someone told me the answer in a private Slack conversation - saying who told me didn't feel appropriate there. My goal with that page was to ensure that future people (including myself) who tried to find this with Google would get a better result! (I'm a bit upset about this comment to be honest, because attributing people is something of a core value for me - the bookmarks on my blog have a "via" mechanism for exactly that reason: https://simonwillison.net/search/?type=blogmark https://simonwillison.net/search/?type=blogmark )
- ttyprintk 4y agoYou can also prefix a SQLite import command with |, which hopefully produces text. At the system level, some import scripts can be entirely in one sql file.
- valw 4y agoBtw, am I alone in thinking that DataFrame abstractions in OOP languages (like Pandas in Python) are oftentimes simply inferior to relational algebra? I'm not sure that many Data Scientists are aware of the expressive power of SQL.
- deepsun 4y agoOften -- yes. Always -- no. For example let's try changing/fixing sampling rate of a dataset (.resample() in Pandas). Or something like .cumsum() -- easy with SQL windowing functions, but man they are cumbersome. Or quickly store the result in .parquet. But all the above doesn't matter, because I feel like 99% of Pandas work involves quickly drawing charts on the data look at it or show to teammates.
- wenc 4y agoSQL does not exactly implement relational algebra in its pure form. SQL implements a kind of set theory with relational elements and a bunch of practical features like pivots, window functions etc. Pandas does the same. Most data frame libraries like dplyr etc. implement a common set of useful constructs. There’s not much difference in expressiveness. LINQ Is another language around manipulating sets that was designed with the help of category theory, and it arrives at the same constructs. However SQL is declarative, which provides a path for query optimizers to parse and create optimized plans. Whereas with chained methods, unless one implements lazy evaluation one misses out on look aheads and opportunities to do rewrites.
- valw 4y ago> There’s not much difference in expressiveness > However SQL is declarative Pick one :) the way I see it, if declarativeness is not a factor in assessing expressiveness, then expressiveness reduces to the uninteresting notion of Turing-equivalence.
- wenc 4y agoExpressiveness and declarativeness are different things, no? Are you talking about aesthetics? I’ve used SQL for 20 years and it’s elegant in parts but it also has warts. I talk about this elsewhere but SQL gets repetitive and requires multi layer CTEs to express certain simple aggregations.
- ultrasounder 4y agoThis comes at the most opportune time when I am cranking through selectstarsql,sqlbolt,schemaverse,pgexcercise to bone up on my SQL skills for my upcoming data engineering interviews. SQL IS the secret super power that devs don't know they possess.
- flusteredBias 4y agoI am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv https://github.com/alexhallam/tv transparency: I am the dev of this utility
- samstave 4y ago
- flusteredBias 4y agohttps://github.com/alexhallam/tv#inspiration https://github.com/alexhallam/tv#inspiration
- flusteredBias 4y agoHere is an example of how I would pipe with headers to `tv`. sqlite3 :memory: -csv -header -cmd '.import taxi.csv taxi' 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count' | tv
- queuebert 4y agoWhat a nice tool. I love how Rust has reinvigorated command line utilities.
- corytheboyd 4y agopiping to jq (using json mode of course) also works well for this
- pdimitar 4y agoOr `jless`, which shows you a tree and doesn't flood your terminal.
- corytheboyd 4y ago
- avogar 4y agoUsing ClickHouse you can also process local files in one line using clickhouse-local command tool. And it will look a lot easier: clickhouse local -q "SELECT passenger_count, COUNT(*), AVG(total_amount) FROM file(taxi.csv, 'CSVWithNames') GROUP BY passenger_count" And ClickHouse supports a lot of different file formats both for import and export (you can see all of them here https://clickhouse.com/docs/en/interfaces/formats/ https://clickhouse.com/docs/en/interfaces/formats/). There is an example of using clickhouse-local with taxi dataset mentioned in the post: https://colab.research.google.com/drive/1tiOUCjTnwUIFRxovpRXQ2xVOHLgN_WL8?usp=sharing https://colab.research.google.com/drive/1tiOUCjTnwUIFRxovpRX...
- eatonphil 4y agoClickhouse-local is incredible. It does the best of any similar tool I've benchmarked. But the reason I took it out of the linked benchmarks in OP's post is because it's 2+GB. That's a massive binary. It's the whole server. I'm not sure you want to be distributing this all over the place in general. It's just not in the same category IMO. Disclaimer: I build another tool that does similar things.
- qoega 4y agoIt is that big only with debug symbols. If you take packaged version of ClickHouse it will be much smaller. Or just strip large binary manually if you already have it.
- zX41ZdbW 4y agoIt is shipped with debug info and symbol tables just in case. Without debug info, it will be 350 MB, and compressed can fit in 50 MB: https://github.com/ClickHouse/ClickHouse/issues/29378 https://github.com/ClickHouse/ClickHouse/issues/29378 It is definitely a worth improvement.
- eatonphil 4y agoAha, thanks for the clarification and link. I'll be following that issue.
- ArchD 4y agoIf you need to query against multiple CSVs, e.g. using joins, you could use QHS: https://github.com/itchyny/qhs https://github.com/itchyny/qhs
- gpvos 4y agocsvsql from csvkit[0] can do that too. [0] https://csvkit.readthedocs.io/en/latest/scripts/csvsql.html https://csvkit.readthedocs.io/en/latest/scripts/csvsql.html
- adamgordonbell 4y agoI've become a fan of using SQLite-utils to work with CSV or JSON files. It's a two step process though. One to create and insert into a DB and a second to select from and return. https://sqlite-utils.datasette.io/en/stable/index.html https://sqlite-utils.datasette.io/en/stable/index.html
- simonw 4y agoI added a feature last year that lets you do this as a one-step process - "sqlite-utils memory": https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/ https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/
- adamgordonbell 4y agoVery cool!
- eatonphil 4y agoThose steps still exist though surely, just managed by the one command. If you're querying via SQLite you have to ingest first.
- simonw 4y agoYup - 'sqlite-utils memory' works by creating an in-memory database, importing various different file formats into it, running the query and then throwing everything away again. It's generally not a good fit for anything over a few dozen MBs of data.
- mytherin 4y agoUsing DuckDB [1]: duckdb -c "SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY ALL" DuckDB will automatically infer you are reading a CSV file from the extension, then automatically infer column names from the header, together with various CSV properties (data types, delimiter, quote type, etc). You don't even need to quote the table name as long as the file is in your current directory and the file name contains no special characters. DuckDB uses the SQLite shell, so all of the commands that are mentioned in the article with SQLite will also work for DuckDB. [1] https://github.com/duckdb/duckdb https://github.com/duckdb/duckdb Disclaimer: Developer of DuckDB
- simonw 4y agoHow does the column data type inference work? I've run into that challenge myself in the past.
- mytherin 4y agoThe CSV auto-detector was implemented by Till Döhmen, who did his master thesis on the subject [1] and has actually written a paper about it [2]. Essentially we have a list of candidate types for each column (starting with all types). We then sample a number of tuples from various parts of the file, and progressively reduce the number of candidate types as we detect conflicts. We then take the most restrictive type from the remaining set of types, with `STRING` as a last resort in case we cannot convert to any other type. After we have figured out the types, we start the actual parsing. Note that it is possible we can end up with incorrect types in certain edge cases, e.g. if you have a column that has only numbers besides one row that is a string. If that row is not present in the sampling an error will be thrown and the user will need to override the type inference manually. This is generally rather rare, however. You could also use DuckDB to do your type-inference for you! duckdb -c "DESCRIBE SELECT * FROM taxi.csv" And if you want to change the sample size: duckdb -c "DESCRIBE SELECT * FROM read_csv_auto('taxi.csv', sample_size=9999999999999)" [1] https://homepages.cwi.nl/~boncz/msc/2016-Doehmen.pdf https://homepages.cwi.nl/~boncz/msc/2016-Doehmen.pdf [2] https://ir.cwi.nl/pub/26416/SSDM1111.pdf https://ir.cwi.nl/pub/26416/SSDM1111.pdf
- cube2222 4y agoSince many people are sharing one-liners with various tools... OctoSQL[0]: octosql 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY passenger_count' It also infers everything automatically and typechecks your query for errors. You can use it with csv, json, parquet but also Postgres, MySQL, etc. All in a single query! [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql Disclaimer: author of OctoSQL
- elmomle 4y agoI love the simplicity. Is there support for joins / use of multiple tables?
- cube2222 4y agoYes, certainly! And those multiple tables can come from different data sources, so files or databases.
- eatonphil 4y agoHeads up: cube2222 is the original author of this benchmark. :) I copied it and Simon copied my copy of it.
- cube2222 4y agoThanks for the acknowledgement! Though btw., I think I personally prefer the SPyQL benchmarks[0], as they test a bigger variety of scenarios. This benchmark is mostly testing CSV decoding speed - because the group by is very simple, with just a few keys in the grouping. [0]:https://colab.research.google.com/github/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb https://colab.research.google.com/github/dcmoura/spyql/blob/...
- eatonphil 4y agoActually I have a big issue with that benchmark in that it doesn't ORDER BY. I don't believe all those tools will produce the same result and it's not required by SQL for them to do so. That doesn't change the poor performance of dsq but it does change the relative and absolute scores in that benchmark.
- tgtweak 4y agoThis is far more useful for SQL users than chaining several sed/awk/sort commands on pipe (although a bit against nix principles).
- chrisweekly 4y agoFor a *nixy approach, try lnav (https://lnav.org https://lnav.org)
- keybored 4y agoMODE is one of: ascii Columns/rows delimited by 0x1F and 0x1E Yes!
- chrisweekly 4y agoOne of my all-time favorite (and somehow still-obscure / relatively unknown) tools is called `lnav` ^1. It's a mini-ETL powertool with embedded SQLite, perfect for wrangling log files or other semi-structured data (a few millions of rows are no problem), it's intuitive and flexible... 1. https://lnav.org https://lnav.org
- minusf 4y agolnav is super cool, but as its name says: log navigator, it's more of a less/tail/etc supercharged with sqlite under the hood. of course because it has a flexible format definition it can deal with csv files as well, but it's true power is getting sql queries out of nginx log files and the like without the intermediate step of exporting them to csv.
- eli 4y agothe .import command used for actually loading the CSV is kinda picky about your CSVs being well-formatted. I don't think it supports embedded newlines at all.
- simonw 4y agoI just tested it against a CSV file with newlines that were wrapped in double quotes and it worked correctly. I used this CSV file: https://til.simonwillison.net/tils/til.csv?_stream=on&_size=max https://til.simonwillison.net/tils/til.csv?_stream=on&_size=... And this query: sqlite3 :memory: -cmd '.mode csv' -cmd '.import til.csv til' \ -cmd '.mode json' 'select * from til limit 1' | jq
- eli 4y agoNeat! I guess it got fixed in the, uh, decade or so since I last tried
- wilsonfiifi 4y agoAnother great tool written in Go is CSVQ [0][1] that can be used as a command line or a library. csvq 'select id, name from `user.csv`' [0] https://github.com/mithrandie/csvq https://github.com/mithrandie/csvq [1] https://mithrandie.github.io/csvq/ https://mithrandie.github.io/csvq/
- spapas82 4y agoCan somebody post the equivalent in Windows cmd?
- gpvos 4y agoJust replace the ' with " and lose the \ . For some SQL queries you may have to use more exotic quoting (generally, in cmd you can always quote the next character with ^ ).
- neycmrtn 4y agoThere is also our somewhat older sqlet.py at http://www.sqlet.com http://www.sqlet.com with multiple input files, column index shortcuts.. (and probably overdue for an update).
- mattewong 4y agoOver all these CLIs, I prefer zsv ( https://github.com/liquidaty/zsv https://github.com/liquidaty/zsv )-- then again, I wrote it so my preference should come as no surprise. On my Mac was 340% faster than the OP command, and does a lot more than just SQL/sqlite3-related
- gigatexal 4y agoI’ve been doing this. But I hate it. CSVs need to die. They’re terrible data formats. But here we are. And SQLlite makes things amazing.
- gpvos 4y agoI'm willing to bet CSV will still be around in 200 years. It's ugly, but exceedingly effective.
- wizofaus 4y agoI wouldn't even assume the concept of text files as we know them today will still exist in any meaningful way in 100 years. It's just as likely all digital data will be stored in something like neutral networks with no obvious textual representation. But yes, CSV has had remarkable persistence (the most recent major feature addition I made to our product had to read from a supposedly modern web API response that was in CSV format, despite all the other endpoints returning JSON).
- krylon 4y agoCSV sucks, yes, but for moving/exchanging/aggregating data between various independent sources, it's the least terrible option everyone can process easily.
- alana314 4y agoI like using q for querying CSVs on the command line: https://github.com/harelba/q https://github.com/harelba/q
- AdrenalinMd 4y agoSame. And I believe q uses sqlite under hood, so you can use the same SQL syntax as the one supported by sqlite. Joining multiple csv files is also possible without too much setup. http://harelba.github.io/q/ http://harelba.github.io/q/
- mattewong 4y agoIt's nice that q has caching.... then again, it kind of needs it to solve its performance inefficiency. Running "select *" on a 1mm-row worldcitiespop_mil file, q takes 27 seconds compared to `zsv sql` which takes 1.7 seconds ( https://github.com/liquidaty/zsv https://github.com/liquidaty/zsv ) and also supports multiple file joins. I'm sure q is faster once cached, but taking a 16x performance hit up-front is not for me
- a-dub 4y agoi used to daydream about adding an ALLOW DOING IT LIVE option to cassandra's csql client. in the event that your where clause was incompatible with your table's key, it would just wholesale dump the table in question into a sqlite while indexing the appropriate columns, run the query in question, actually return the result and then properly clean up.
- adren67 4y agoMiller is also a great tool to do computation, transformations on CSV/TSV/JSON/SQLite files... Here is the way to pretty-print the same result with mlr: mlr --icsv --opprint --barred stats1 -a count,mean -f total_amount -g passenger_count then sort -f passenger_count taxi.csv
- swhalemo 4y agoI'm genuinely curious about the use case of this. whenever i have CSVs I either import then into R/python of they're small or some DBMS first if they're large. can somebody tell me what niche this is filling?