Doing a database join with CSV files

Re:Doing a database join with CSV files by kyllo on Tuesday December 31, 2019 @07:08PM (#21925696)

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...

[ Parent | on HN ]

Re:Doing a database join with CSV files by f311a on Tuesday December 31, 2019 @07:40PM (#21925827)

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.

[ Parent | on HN ]

Re:Doing a database join with CSV files by creddit on Tuesday December 31, 2019 @09:22PM (#21926240)

On average each line was 1GB of data!? Can you give a rough description of the content of the two files?

[ Parent | on HN ]

Re:Doing a database join with CSV files by tsimionescu on Wednesday January 01, 2020 @06:05AM (#21927585)

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.

[ Parent | on HN ]

Re:Doing a database join with CSV files by fourthark on Tuesday December 31, 2019 @10:01PM (#21926362)

600G / 300M = 2K

[ Parent | on HN ]

Re:Doing a database join with CSV files by creddit on Wednesday January 01, 2020 @09:43AM (#21928203)

Woof, yeah, bad arithmetic by me. Thanks!

[ Parent | on HN ]