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