5 ms·
How big are the data sets? I've been trying to get duckdb to work in our company on financial transactions and reporting data. The dataset is around 500GB CSV i
by aynyc 1y ago
How big are the data sets? I've been trying to get duckdb to work in our company on financial transactions and reporting data. The dataset is around 500GB CSV in S3 and duckdb chokes on it.
- snake_doc 1y agoAre you querying from an EC2 instance close to the S3 data? Are the CSVs partitioned into separate files? Does the machine have 500GB of memory? It’s not always duckdb fault when there can be a clear I/O bottleneck…
- aynyc 1y agoNo, the EC2 instance doesn't have 500GB of data. Does DuckDB require that? I actually downloaded the data from S3 to local EBS and still choked.
- broner 1y agoWorks fine for me on TB+ datasets. Maybe you were doing in-memory rather than persistent database and running out of RAM? https://duckdb.org/docs/stable/clients/cli/overview.html#in-memory-vs-persistent-database https://duckdb.org/docs/stable/clients/cli/overview.html#in-...
- aynyc 1y agoWait, do you insert the data from S3 into duckdb? I was just doing select from file.
- fastasucan 1y agoMaybe its your terminal that chockes because it tries to display to much data? 500GB should be no problem.
- broner 1y agoNope, just reading from S3. Check this out: https://duckdb.org/2024/07/09/memory-management.html https://duckdb.org/2024/07/09/memory-management.html
- nojito 1y agoCSV are a poor format to access from S3. Should convert them to parquet then access and analytics becomes cheap and fast.
- aynyc 1y agoI agree. That's how our data is produced. We constantly generate real time data into CSV. As far as I can tell, I can't append to parquet file.
- wenc 1y agoParquet files are already built for append only. Just add a new file. This is a new paradigm for folks who aren’t in big data — the conventional approach usually involves doing a row INSERT. In big data, appending simply means adding a new file - the database engine will immediately recognize its presence. This is why “select * from ‘*.parquet’” will always operate on the latest dataset.
- aynyc 1y agoWait, so I create a new file for every message?
- wenc 1y agoTypically small data is batched. While theoretically you could, I wouldn't create 1 file per row (there would be too many files and your filesystem would struggle). But maybe you can batch 1 day's worth of data (or whatever partitioning works for your data) and write to 1 parquet file? For example, my data is usually batched by yearwk (year + week no), so my directory structure looks like this: /data/yearwk=202501/000.parquet /data/yearwk=202502/000.parquet This is also called the Hive directory structure. When I query, I just do: select * from '/data/**/*.parquet'; This is a paradigm shift from standard database thinking for handling truly big data. It's append-only by file. 500GB in CSVs doesn't sound that big though. I'm guessing when you convert to Parquet (a 1-liner in DuckDB, below) it might end up being 50GBs or so. COPY (FROM '/data/*.csv') TO 'my.parquet' (FORMAT PARQUET);
- higeorge13 1y agoCould you test with clickhouse-local? It always works better for me.
- aynyc 1y agoNo, clickhouse is not considered for some other reason. But I think I might revisit it sometime in the future.
- wenc 1y agoCSV is a pretty bad format any engine will choke on it. It basically requires a full table scan to get at any data. You need to convert it into Parquet or some columnar format that lets engines do predicate pushdowns and fast scans. Each parquet file stores statistics about the data it contains so engines can quickly decide if it’s worth reading the file or skipping it altogether.