5 ms·
Solving duplicate data with performant deduplication
- goodroot 3y agoHey! Thanks for upvoting. Happy to answer any questions about deduplication. One thing that's not included in the write-up is that we also address out-of-order indexing alongside deduplication.
- CommanderHux 3y agoThe dataset link seems to be dead. Do you have a mirror?
- goodroot 3y agoEdit: Updated! https://mega.nz/folder/A1BjnSYQ#NQe5qhYLVBqiRwhWRmcVtg https://mega.nz/folder/A1BjnSYQ#NQe5qhYLVBqiRwhWRmcVtg Article is updating too.
- CommanderHux 3y agoThanks. Is Dedup supported on SQL COPY too?
- nhourcard 3y agoNot for CSV import via SQL COPY sadly
- whalesalad 3y agoCan anyone comment on QuestDB vs Clickhouse vs TimescaleDB? Real world experience around ergonomics, ops, etc. Currently using BigQuery for a lot of this (ingesting ~5-10TB monthly) but would like to begin exploring in-house tooling. On the flip side, we still use PSQL/RDS a lot and I enjoy it for the low operations burden - but we're doing some time series stuff with it now that is starting to fall over. TimescaleDB is nice because it is postgres, but afaik cannot work inside RDS. Clickhouse is next on my list for a test deployment, but QuestDB looks pretty neat too.
- gigatexal 3y agoWhat about iceberg tables and a lake approach on GCS and then picking a querying engine?
- whalesalad 3y agoThese are terms I’m sorta familiar with but not sure. Data lake = bunch of noise (everything), iceberg = generated tables or views to read relevant/hot data from the lake?
- gigatexal 3y agoBasically that’s it. Yeah. If you can afford BigQuery just use that but otherwise building off of blob storage and bolting on query engines and catalogs makes for a flexible approach but I find BigQuery solves most problems rather well just throwing money at the problem lol
- benjaminwootton 3y agoThis is the way the industry is going. Table formats such as Delta, Hudi, Iceberg stored on cloud object stores. Though it works amazingly well, it is certainly slower than ingesting the data to be stored and manipulated in native formats.
- nhourcard 3y agoI'd be curious to hear how RDS is starting to fall over with time series data, is it a bottleneck on ingestion, queries, or both?
- whalesalad 3y agoIt’s working okay with a 30 day rolling average… (every day we truncate older rows) we read from it to generate on demand status for per-second performance of tasks in a big job processing engine. But long term we want to have all the data available for historical analysis, trend analysis etc.
- 3y ago
- jimsimmons 3y agoWhat is the best way to deduplicate a corpus of documents
- marginalia_nu 3y agoIf you mean in the sense of dealing with documents that are very similar but not binary identical, a locality sensitive hash would do the job.
- OnlyMortal 3y agoA Rabin finger printing algorithm and a hash of the data it generates. Reference count the hashes.
- goenning 3y agoIf your ClickHouse ReplacingMergeTree returns twice the expected row count is because your query is wrong. You don’t need to FINAL it, just use aggregation on your queries as per their docs
- benjaminwootton 3y agoIndeed, you should still aggregate even on mergetree tables. I'm not sure what is about the database world where people are happy to discuss their competitors and include either mistakes or misinformation. It doesn't seem to happen in other parts of the industry.
- supercoco9 3y agoHi. Sorry if my query offended you. I basically executed literally what Clickhouse recommends at their guides for deduplication https://clickhouse.com/docs/en/guides/developer/deduplication https://clickhouse.com/docs/en/guides/developer/deduplicatio.... Of course you can also materialize with aggregations or just use a group by, or even force optimize of the table. But my point is that you don't really get exactly once guarantees. Whoever is querying that table needs to be aware than a `SELECT * FROM tb` might contain duplicates and needs to adapt their queries accordingly.
- higeorge13 3y agoI believe there are 0 people working with CH and ReplacingMergeTree and don’t know that they have to use final or group by in order to get non duplicate data. It’s mentioned in the table engine page, their knowledge base everywhere. Also i have not recently seen anyone not recommending it. It might have been the case a few years ago, but performance of final has improved and it’s faster than alternatives. People suggest to use MergeTrees obviously but if no alternative, replacing is the way to go.
- adren123 3y agoAn initial import with DuckDB from all the 15 files takes only 36 seconds on a regular (6 years old) desktop computer with 32GB of RAM and 26 seconds (5 times quicker than QuestDB) on a Dell PowerEdge 450 with 20 cores Intel Xeon and 256GB of RAM. Here is the command to input the files: CREATE TABLE ecommerce_sample AS SELECT * from read_csv_auto('ecommerce_*.csv');
- supercoco9 3y agoThank you! (original article writer here) DuckDB is awesome. A couple of comments here. First of all, this is totally my fault, as I didn't explain it properly. I am trying to simulate performance for streaming ingestion, which is the typical use case for QuestDB, Clickhouse, and Timescale. The three of them can do batch processing as well, but they shine when data is coming at high throughput in real time. So, while the data is presented on CSV (for compatibility reasons), I am reading line after line, and sending the data from a python script using the streaming-ingestion API exposed by those databases. I guess the equivalent in DuckDB would be writing data via INSERTS in batches of 10K records, which I am sure would still be very performant! The three databases on the article have more efficient methods for ingesting batch data (in QuestDB's case, the COPY keyword, or even importing directly from a Pandas dataframe using our Python client, would be faster than ingesting streaming data). I know Clickhouse and Timescale can also efficiently ingest CSV data way faster than sending streaming inserts. But that's not the typical use case, or the point of the article, as removing duplicates on batch is way easier than on streaming. I should have made that clearer and will probably update it, so thank you for your feedback. Other than that, I ran the batch experiment you mention on the box I used from the article (had already done it in the past actually) and the performance I am getting is 37 seconds, which is slower than your numbers. The reason here is that we are using a cloud instance using an EBS drive, and those are slower than a local SSD on your laptop. You can use local drives on AWS and other cloud providers, but those are way more expensive and have no persistence or snapshots, so not ideal for a database (we use them sometimes for large one-off imports to store the original CSV and speeding up reads while writing to the EBS drive). Actually one of the claims in DuckDB's entourage is that "big data is dead". With the power in a developer's laptop today you can do things faster than with cloud at unprecedented scale. DuckDB is designed to run locally on a data scientist machine, rather than on a remote server (of course you have Motherduck if you want to do remote, but you are now adding latency and a proprietary layer on top). Once again thank you for your feedback!