5 ms·

I was trying to join two 300M lines CSV files using SQLite and Postgres and it took days to merge them on c4.xlarge. Later, I tried Miller and it took less tha

by f311a 7y ago

I was trying to join two 300M lines CSV files using SQLite and Postgres and it took days to merge them on c4.xlarge. Later, I tried Miller and it took less than 4-6 hours. It also takes some time to tune Postgres to make it faster for this particular task.

TylerE 7y ago
Seems like you were doing something naively. Probably would have been much faster if you'd written a simple conversion script to do batch inserts. Perhaps something pathological like commiting (or flushing to disk) after every row?
kyllo 7y ago
4-6 hours still seems like a long time for this, did you have sorted indices on the join columns? How high of cardinality (distinct counts) did the join columns have? Traditional RDBMS are pretty fast at joining when you have the right indices in place, especially when the cardinality of the join columns isn't very high. And how much of that time was just getting the data into the tables? There are fast and slow ways to do that, too...
- f311a 7y ago
  IIRC, it took me 4-7 hours to load 600GB of uncompressed data to the PostgreSQL after disabling WAL and tunning some config variables. I didn't have time to test all possible speed improvements. After import, it takes 3-6 hours additionally to create indexes for those tables. I think the main problem was that I had text indices. I'm not an expert in RDBMS and used a simple join that was taking ages to start producing the actual data. There are definitely a lot of ways to tune such queries and PostgreSQL configs, but I wanted a simple and universal solution.
  - creddit 7y ago
    On average each line was 1GB of data!? Can you give a rough description of the content of the two files?
    - fourthark 7y ago
      600G / 300M = 2K
      - creddit 7y ago
        Woof, yeah, bad arithmetic by me. Thanks!
    - tsimionescu 7y ago
      Not GP and nothing to do with them, but I have an example of a CSV format which yields absurdly long lines. We have a CSV for timeseries data, where for some reason someone decided to force each line to represent one data point. However, for some cases, one data point may contain around 30 different statistics for 4 million different IPs, which get represented as 120 million columns in the CSV (think a CSV header like 'timestamp,10.0.0.1—Throughput,10.0.0.1-DroppedBytes,[...],10.215.188.251-Throughput,[...]'). With large numbers represented as text in a CSV, this can sometimes reach more than one 1GB per line.
faizshah 7y ago
When I've had to do similar, the easiest and fastest way has always been install spark -> Create an external spark sql table from the csvs -> use spark sql to join and aggregate/write. If you haven't used spark before it sounds complicated but its actually really quick and easy. In general the operation on my laptop can get up to 200 MB/s, it's basically IO limited to the SSD of your machine.
- ecnahc515 7y ago
  Similarly, I've used Prestoql for this. It's similar to Spark and Hive.
- lolive 7y ago
  Would be nice if you could write a small article about that.
santa_boy 7y ago
This is a perfect candidate for XSV. You might want to try it. I'd suggest do one try after creating an index using XSV and the second one without. I feel you will be impressed with the performance.
carbocation 7y ago
This would take seconds to minutes with BigQuery or Athena.
- nojvek 7y ago
  It would still take a long time to actually send all that data to BigQuery
  - carbocation 7y ago
    Since the OP noted that they were trying to accomplish this task on a c4.xlarge machine, the data is already "in the cloud" and so transfer should be blazingly fast. Using the zipped CSV at [1] as a size estimate, I'd ballpark 300 million CSV rows as somewhere between 6.7-10.6 GB: (300000000/500000) * 11.10 = 6.7 GB (300000000/500000) * 17.68 = 10.6 GB That could one minute to transfer. (Obviously if the rows contain a ton of data, these size estimates could be off, in an unbounded way.) 1 = http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/ http://eforexcel.com/wp/downloads-18-sample-csv-files-data-s...
anjanb 7y ago
What is Miller ?

by f311a 7y ago

TylerE 7y ago

Seems like you were doing something naively. Probably would have been much faster if you'd written a simple conversion script to do batch inserts. Perhaps something pathological like commiting (or flushing to disk) after every row?

kyllo 7y ago

4-6 hours still seems like a long time for this, did you have sorted indices on the join columns? How high of cardinality (distinct counts) did the join columns have? Traditional RDBMS are pretty fast at joining when you have the right indices in place, especially when the cardinality of the join columns isn't very high. And how much of that time was just getting the data into the tables? There are fast and slow ways to do that, too...

f311a 7y ago
IIRC, it took me 4-7 hours to load 600GB of uncompressed data to the PostgreSQL after disabling WAL and tunning some config variables. I didn't have time to test all possible speed improvements. After import, it takes 3-6 hours additionally to create indexes for those tables. I think the main problem was that I had text indices. I'm not an expert in RDBMS and used a simple join that was taking ages to start producing the actual data. There are definitely a lot of ways to tune such queries and PostgreSQL configs, but I wanted a simple and universal solution.
- creddit 7y ago
  On average each line was 1GB of data!? Can you give a rough description of the content of the two files?
  - fourthark 7y ago
    600G / 300M = 2K
    - creddit 7y ago
      Woof, yeah, bad arithmetic by me. Thanks!
  - tsimionescu 7y ago
    Not GP and nothing to do with them, but I have an example of a CSV format which yields absurdly long lines. We have a CSV for timeseries data, where for some reason someone decided to force each line to represent one data point. However, for some cases, one data point may contain around 30 different statistics for 4 million different IPs, which get represented as 120 million columns in the CSV (think a CSV header like 'timestamp,10.0.0.1—Throughput,10.0.0.1-DroppedBytes,[...],10.215.188.251-Throughput,[...]'). With large numbers represented as text in a CSV, this can sometimes reach more than one 1GB per line.

faizshah 7y ago

When I've had to do similar, the easiest and fastest way has always been install spark -> Create an external spark sql table from the csvs -> use spark sql to join and aggregate/write. If you haven't used spark before it sounds complicated but its actually really quick and easy. In general the operation on my laptop can get up to 200 MB/s, it's basically IO limited to the SSD of your machine.

ecnahc515 7y ago
Similarly, I've used Prestoql for this. It's similar to Spark and Hive.
lolive 7y ago
Would be nice if you could write a small article about that.

santa_boy 7y ago

This is a perfect candidate for XSV. You might want to try it. I'd suggest do one try after creating an index using XSV and the second one without. I feel you will be impressed with the performance.

carbocation 7y ago

This would take seconds to minutes with BigQuery or Athena.

nojvek 7y ago
It would still take a long time to actually send all that data to BigQuery
- carbocation 7y ago
  Since the OP noted that they were trying to accomplish this task on a c4.xlarge machine, the data is already "in the cloud" and so transfer should be blazingly fast. Using the zipped CSV at [1] as a size estimate, I'd ballpark 300 million CSV rows as somewhere between 6.7-10.6 GB: (300000000/500000) * 11.10 = 6.7 GB (300000000/500000) * 17.68 = 10.6 GB That could one minute to transfer. (Obviously if the rows contain a ton of data, these size estimates could be off, in an unbounded way.) 1 = http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/ http://eforexcel.com/wp/downloads-18-sample-csv-files-data-s...

anjanb 7y ago

What is Miller ?