5 ms·

Seems great at first, but how is that better than piping the whole CSV file into SQLite and then doing the processing there? I think CSV is great for data excha

by Ecco 8y ago

Seems great at first, but how is that better than piping the whole CSV file into SQLite and then doing the processing there? I think CSV is great for data exchange but not so great for data processing. By using SQLite (or any other DB actually) you can decide which data to index, and write arbitrarily complex queries in a rather understandable language. I think XSV is kind of reinventing the wheel here.

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 ago
  For 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 ago
  Have 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 ago
    Thanks for your kind words, I really appreciate them! :-) Please reach out if you'd like any Rust advice!
    - shaklee3 8y ago
      Which of the two available rust books would you recommend for someone who knows c/c++?
      - burntsushi 8y ago
        Hard 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 ago
  https://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:
scarejunba 8y ago
I prefer the SQLite option because it's faster when you're doing repeated operations. I load it in once and then run many queries on the same SQLite store. It's very simple and I know I can join etc. with a SQL syntax. But I usually can figure out a schema for my CSV.
- dagenix 8y ago
  Faster than xsv? AIUI, both sqlite and xsv rely on the OS filesystem buffer cache to keep their data in memory.
  - burntsushi 8y ago
    Most definitely, yes. If you setup your indexes correctly in a relational database, then xsv isn't going to come anywhere near performing them as efficiently. The only indexing mechanism xsv has is a record index, which lets it parallelize certain workloads across a CSV file. But it doesn't have any per-field indices like relational databases do, so any kind of join done by xsv requires building the index first in memory. xsv could grow support for field level indices, but it's more work and I don't know whether it's worth it. There's a line to straddle here, because at some point, xsv isn't going to be good enough and you should instead just use a database.
    - dagenix 8y ago
      I don't doubt that sqlite will wipe the floor with xsv for certain types of analysis. I also suspect that the opposite would be true for other types of analysis. Although, you'd know much better than I would - I've used sqlite quite a bit while my only usage of xsv was to work with a 100 line CSV file. Xsv was fast, but, I'm sure most tools would be at that size. What was killer, however, is that xsv gave me a lot of tools that helped exactly what I needed to do - sqlite wouldn't have helped at all. Right tool for the right job and all. What I was more curious about was if the original comment was about a particular case where sqlite beat xsv, or if it was more handwavy. Also, the comment seemed to imply that repeated queries would be faster in sqlite than with xsv but didn't mention anything about indexes. My suspicion would be that without something that can take advantage of an index, xsv and sqlite would perform about the same doing repeated table / file scans.
thom 8y ago
That's a lot of faff for ad-hoc jobs. R with dplyr is more likely the direct competition, and even then I assume this has some speedups.

by Ecco 8y ago

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 ago
For 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 ago
Have 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 ago
  Thanks for your kind words, I really appreciate them! :-) Please reach out if you'd like any Rust advice!
  - shaklee3 8y ago
    Which of the two available rust books would you recommend for someone who knows c/c++?
    - burntsushi 8y ago
      Hard 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 ago
https://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:

scarejunba 8y ago

I prefer the SQLite option because it's faster when you're doing repeated operations. I load it in once and then run many queries on the same SQLite store. It's very simple and I know I can join etc. with a SQL syntax. But I usually can figure out a schema for my CSV.

dagenix 8y ago
Faster than xsv? AIUI, both sqlite and xsv rely on the OS filesystem buffer cache to keep their data in memory.
- burntsushi 8y ago
  Most definitely, yes. If you setup your indexes correctly in a relational database, then xsv isn't going to come anywhere near performing them as efficiently. The only indexing mechanism xsv has is a record index, which lets it parallelize certain workloads across a CSV file. But it doesn't have any per-field indices like relational databases do, so any kind of join done by xsv requires building the index first in memory. xsv could grow support for field level indices, but it's more work and I don't know whether it's worth it. There's a line to straddle here, because at some point, xsv isn't going to be good enough and you should instead just use a database.
  - dagenix 8y ago
    I don't doubt that sqlite will wipe the floor with xsv for certain types of analysis. I also suspect that the opposite would be true for other types of analysis. Although, you'd know much better than I would - I've used sqlite quite a bit while my only usage of xsv was to work with a 100 line CSV file. Xsv was fast, but, I'm sure most tools would be at that size. What was killer, however, is that xsv gave me a lot of tools that helped exactly what I needed to do - sqlite wouldn't have helped at all. Right tool for the right job and all. What I was more curious about was if the original comment was about a particular case where sqlite beat xsv, or if it was more handwavy. Also, the comment seemed to imply that repeated queries would be faster in sqlite than with xsv but didn't mention anything about indexes. My suspicion would be that without something that can take advantage of an index, xsv and sqlite would perform about the same doing repeated table / file scans.

thom 8y ago

That's a lot of faff for ad-hoc jobs. R with dplyr is more likely the direct competition, and even then I assume this has some speedups.