The CSV Virtual Table
sqlite.org
sqlite.org
You can import CSV, jsonlines, even human formatted tabulated data like the output from ‘ls -l’ and ‘ps’. And you can import from files directly, gzip archives, pipes, and shell variables.
Eg
ps aux | select count(*), user GROUP BY user ORDER BY 1
It uses sqlite3 under the hood and was written to solve exactly the problem of needing to quickly validate a bunch of CSV files. But it proved so useful that I ended up making it an official part of the shell.
The idea looks perfect for so many of my use cases, but the implementation as a shell is a significant roadblock to anyone invested in their current shell. I'd bet that's a significant portion of the people who would find this useful.
And since the shell is already good for handling structured data (like `jq` et al but not limited to JSON) it also made sense to allow inlining of SQL for when you're working with relational data.
An example of this last point is a recent problem I had at work where I wanted to see which users had 2FA enabled. Unfortunately this service provided a user list API (returns a complex JSON document) and a separate user meta data API (again JSON). The meta data didn't have names on it and the user list didn't have 2FA details on it. So I had two JSON documents that weren't tables but ostensibly held relational data. Using the shell I could convert the two JSON documents into two tables and then use the SQL inlining `select` command to do the relational query. The whole thing was 3 lines of code whereas to do the equivalent in Python / Typescript / etc would have been much more verbose.
> That shell also sends metadata about the documents being piped which means
> you effectively have typed pipes (like Powershell but backwards compatible
> with POSIX tools). So it means you can do more with the table afterwards too.
https://unix.stackexchange.com/questions/197809/propose-addi...
The idea is that such a file descriptor would output lines that are not part of the data yet are not errors. One example usage would be to output the headers of commands such as ps, and murex could use such stdmeta lines as column names.
{
"Content-Type": "application/table",
"Stdin": "ls",
"Meta": {
"Columns": [ "filename", "date", "permissions", "owner" ]
}
}
In an idea world this would be an API rather than file descriptor but that would be backwards incompatible and even the FD use case feels like nice thing that is always going to be out of reach. :(
Thanks for sharing that by the way. I hadn't seen that post before.
Maybe out of ignorance, but in all the years I've programmed I've never had a situation when I said to myself "I need a database"! I either have data in maps/vectors in memory or if I need tables I'll use some table datastructure (like in R/Matlab). So reading a CSV file into a SQL table seems a bit weird - though maybe some people have multi-gig CSV files? I guess at a certain size (when it can't fit in RAM?) you gotta turn to SQL - or maybe I should be using SQL for smaller/simpler problems as well - but I don't really know where to start.
https://online.stanford.edu/courses/soe-ydatabases-databases
Otherwise SQL is best learned by solving problems and googling when you’re stuck.
Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more generally the table) is a super useful abstraction and often much more powerful than lists and dicts.
Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT.
Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX.
Really advanced SQL experts do stuff like this.
Just more generally, do you have some mental model when you move from using something like a dataframe to using SQL?
For instance people working in R rarely talk about using an SQL database - even when they have huge datasets. And I guess I don't really see when you should be thinking about making the switch over.
The only thing I see distinct about databases is it's always hand in hand with web-tech - and it more focused on mutability (which if you are doing some stats your data is usually immutable)
It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of.
Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections.
Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.
The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra.
That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations).
Using SQL via sqlite to manipulate local tables confer similar benefits.
[1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.
It probably says that every day. I've seen that in a lot of self-paced courses
Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large amounts of data on disk, it has built in json queries, there’s a great ecosystem of SQLite libraries, support for it is built into python.
Dataframe abstractions are really easy to understand so I have worked with journalists and biologists who are able to work productively with pandas or R. Transforming and analyzing data is often easier to write with pyspark while spark sql makes it easy to access and extract data from some data sources.
It all really depends on who is your customer, who are your coworkers, how much volume of data do you have, how much time do you have to accomplish your task etc.
You might frequently find the No Starch and O’Reilly books on Humble Bundle, (https://www.humblebundle.com/books?hmb_source=navbar) if that is available in your location.There’s often loads of overlap between bundles. I’m sure I’ve bought the python book about 5 times so far but I don’t mind as it’s great value.
If you want to learn about database theory, however, as well as the practicalities of SQL, then I found that most of the resources I used when I did this at uni were online. The book we used was the Connolly/Begg book (https://www.pearson.com/us/higher-education/program/Connolly...)
I don’t think this effectively answered the “why” of your question, however. My guess is that SQL is a very well established domain language and when it comes to data normalised across many tuples it’s the standard for manipulation.
I don’t think that dataset size is the primary reason to manipulate data via SQL; I think that the moment your programme starts to need more than a single flat file data source, you naturally start to think about normalisation, indexes etc for performance and sanity.
If I may, however: I am coming at this from the other way in that I am far more often manipulating CSV and excel data and would like some good resources in how to quickly load that in to a dataframe in pandas or similar vs using SQL. (If I’m responding to a thread hijack I may as well go all in and totally derail it)
(I think most/all of the comics are entries on her blog if you desperately don't want to pay).
I was checking if sqlite's CSV supports custom separator and unquoted strings for this to work. Found it doesn't.
Use the data-delimiter characters actually defined in the ASCII / Unicode specifications to, you know, delimit your frigging data, and you won't have to bother about whether to quote or not to quote, nor about painstakingly "escaping" (and un-escaping) whatever characters you're (mis)using for delimiters.
Sure as Hell blew away at least my mind when I came across it.
(Not that I got to implement it at work, of course.)
___
[1]: https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...
[2]: https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text
Not sure if this is what you are looking for.
You can try it out: https://superintendent.app
PS. it also loads GBs of file in 10-20s because that part is written in C.
You can then run SQL queries that in the end call your own data functions to generate data. I had a project where I had several diverse data files (each with its own set of functions to iterate over, but no useful querying) where wrapping each data file with a SQL Virtual table allowed med to use SQL to join them up and query them.
Here's a 2014 article where the author exposes redis as a virtual table within your sqlite code using this mechanism: https://charlesleifer.com/blog/extending-sqlite-with-python/
Postgres has some support for this (you run your SQL code but it really talks to some other service, or another postgres database) through foreign data wrappers. There's some Python library to also make adding that easy.
Interestingly a few projects do it in reverse: you are talking the postgres protocol (using library, psql client) to your database server, but it's not postgres but something else entirely (CockroachDB does this).
I like the idea of the entire world being just SQL, but you have to be careful with those wrappers and expose any indexing you might have, otherwise it's lots of expensive table scans.
The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment:
>, and returns that content _as_ _if_ it were rows and columns of an SQL table.
In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The csv is treated as a "virtual" table you can execute SQL queries against.
Compare that to the alternative way of using SQLite ".import" command : https://stackoverflow.com/questions/14947916/import-csv-to-s...
Is it a table or not?
You seem to be argumentative instead of trying to understand why they called it "virtual".
I'll try with another example. Let's pretend you have a 10 gigabyte csv file on disk::
Using a real SQLite table:
CREATE TABLE t1 (...); -- an empty db file of 8192 bytes
.import thefile.csv t1 -- total disk space consumed now 20 GB because .db file is now +10 GB larger
SELECT FROM t1; -- SQLite reads from .db file and not the .csv file
CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); -- disk space stays the same
SELECT from t1; -- SQLite directly reads from csv disk file
At the risk of stating the obvious, the extra "VIRTUAL" keyword in between "CREATE" and "TABLE" thus makes a virtual table.
It is a "table" in the sense that it "acts like a table" via an interface for SQL queries. However, it's not a real table in the sense that there's no data blocks within the .db that represents that table. It's virtual.
I'm not understanding what annoys you about their description.
So yeah the difference matters depending on your use. I don't think you can add indexes for example, unless they are specifically supported by the underlying resource, or implemented in the virtual table extension you're using.
It's not a table.
> If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.
Feels to me like a lot of manual implementations will break this assumption if they naively only try to deal with line breaks and delimiter escaping.
Either way, CSV is fraught enough format that I wouldn't try to ingest files of from unknown sources or from unknown generators.
Should fix all those issues, AFAICS.
So of course abloslutely nobody uses it. :-(
Unsurprisingly there's still a lot of data flowing around with these pure text size header + contents structure files, I didn't know SQLite was in that camp as well.
INSERT INTO regular_table SELECT * FROM csv_table