3 ms·
You could. But then you need to write a schema. How do you know what schema to write? You could ask csvkit to do it, but it might not be fast enough on, say, a
by burntsushi 8y ago
You could. But then you need to write a schema. How do you know what schema to write? You could ask csvkit to do it, but it might not be fast enough on, say, a 40GB CSV file. Or maybe it isn't quite accurate enough. xsv might be the tool you use to figure out what the schema should be.
SQLite (or any SQL database) does not cover all use cases. For example, if you need to produce CSV data, and you can fit your transformation into xsv commands, then it might be hard to beat the performance in exchange for the effort you put in.
This is probably an expression problem. Tools don't always neatly fit into orthogonal buckets. If you think in terms of shell pipelines and want to attack the data as it is, then xsv might be good for you. SQLite is, IMO, a pretty massive hammer to apply every single time you want to look at CSV data.
- Ecco 8y agoFor non-lookup csv-to-csv transformation, yes, I guess your tool can easily beat any SQL DB since it can work without any need for an external storage. That also applies to sequential-scan queries too. You make a good point :-) Regarding the schema now, I’m not sure I get your point. I would argue the queries you want to make already dictate a schema (e.g. you want to compute an average ? Use a numeric column).
- burntsushi 8y ago> Regarding the schema now, I’m not sure I get your point. I would argue the queries you want to make already dictate a schema (e.g. you want to compute an average ? Use a numeric column). Yeah that's fine. I think we're just missing each other, that's all. I'm not sure what kind of CSV data you've worked with, but from the stuff I've seen, it's rarely clean. `xsv frequency` and `xsv stats` are great ways to look at your data as it is. For example, a column might be named as if it were a numeric field. Maybe it's first hundred rows are even numeric. But there might be a row where it isn't numeric and isn't even empty, but has some kind of weird value in it. Like, say, 'N/A' or 'null' or 'none' or whatever sentinel value someone decided to use to indicate absence. Data types can be richer than just primitives too. For example, one field might be an enumeration of a fixed set of values, and I can think of at least three different ways you might want to represent that in your schema.
- jgord 8y agoHave to pipe up here and say an enthusiastic / visceral / emotional 'thankyou' to BurntSushi for all your great contributions - xsv, rust-csv, ripgrep etc - not to mention your superb blog articles. Im using xsv to wrangle 500gb of data, it is phenomenally useful and fast. The main datastore is postgresql and it is great, but there are some things pg just isn't fast enough for [hash joins anyone]. I have to do a lot of pre-processing, and xsv is incredible for that. Perhaps the best complement of all - I am seriously looking at moving from node.js to Rust as my daily data/systems programming language _because_ of how performant and elegant xsv / rust-csv / ripgrep are, and how readable the rust code is. I also have renewed respect for the staple of olde unix tools - sort sed grep wc etc xsv is a brilliant addition to that canon of unix lore.
- burntsushi 8y agoThanks for your kind words, I really appreciate them! :-) Please reach out if you'd like any Rust advice!
- shaklee3 8y agoWhich of the two available rust books would you recommend for someone who knows c/c++?
- burntsushi 8y agoHard to say. I own them both but haven't thoroughly read through either. I would probably go with The Rust Programming Language, though, I suspect you can't go wrong with either.
- anarazel 8y ago> but there are some things pg just isn't fast enough for [hash joins anyone] If you've a separable benchmark for this, I'd be curious. I work on PG performance, including hashjoins, and real-world (-ish) benchmarks are good!
- nabla9 8y agohttps://www.sqlite.org/csv.html https://www.sqlite.org/csv.html >The CSV Virtual Table >The CSV virtual table reads RFC 4180 formatted comma-separated values, and returns that content as if it were rows and columns of an SQL table. >The CSV virtual table is useful to applications that need to bulk-load large amounts of comma-separated value content. The CSV virtual table is also useful as a template source file for implementing other virtual tables. >The CSV virtual table is not built into the SQLite amalgamation. It is available as a separate source file that can be compiled into a loadable extension. Typical usage of the CSV virtual table from the command-line shell would be something like this: