4 ms·

I 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 ago
  Parquet 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 ago
    Wait, so I create a new file for every message?
    • wenc 1y ago
      Typically 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);
      • aynyc 1y ago
        I was really surprised duckdb choked on 500GB. That's maybe a week's worth of data. The partitioning of partquet files might be an issue as not all data are neatly partitioned by date. We have trades with different execution dates, clearance dates and other date values that we need query on.
        • wenc 1y ago
          It doesn’t usually choke on 500 gb of data. I query 600 gb (equivalent to a few TBs of CSVs?) of parquets daily. It’s not the size of the data. It’s the type of data. If date partitioning doesn’t work, just find another chunking key. The key is to get it into parquet format. CSV is just hugely inefficient. Or spin up a larger compute instance with more memory. I have 256gb on mine. I tried running an Apache Spark job (8 machine cluster) on a data lake of 300 Gb of TSVs once. This was a distributed cluster. There was one join in it. It timed out after 8 hours. I realized why — Spark had to do many full table scans of the TSVs and it was just so inefficient. CSV formats are ok for straight up reads, but any time you have to do analytics operations like aggregate or join them at scale, you’re in for a world of pain. DuckDB has better CSV handling than Spark but a large dataset in a poor format will stymie any engine.
          • aynyc 1y ago
            We have a spark cluster too. Then switch to Athena. I just dislike the cost structure. The problem with disk based partition is keys are difficult to manage properly.
            • wenc 1y ago
              Did Athena on CSV work for you? I've used Athena and it struggles with CSV at scale too. Btw I'm not suggesting to use Spark. I'm saying that even Spark didn't work on large TSV datasets (it only takes a JOIN or GROUP BY to kill the query performance). The CSV data storage format is simply the wrong one for analytics. Partitioning is irreversible, but coming up with a thoughtful scheme isn't that hard. You just need to hash something. Even something as simple as a HNV hash on some meaningful field is sufficient. In one of my datasets, I chunk it by week, then by HNV modulo 50 chunks, so it looks like this: /yearwk=202501/chunk=24/000.parquet Ask an LLM to suggest partioning scheme or think of one. CSV is the mistake. The move here is to get out of CSV. Partitioning is secondary -- partitioning here is only used for chunking the Parquet, nothing else. You are not locked into anything.
              • 1y ago