I have two relatively large (a few million) database tables and want to know if all the rows in table 1 exist in table 2. I have tried using EXCEPT on the two tables but the datasets are just too large.
Is this a problem I could solve using Go? If so how would you go about it? Maybe hashing the rows into a dictionary?
17 comments · 17% upvoted · other discussions
Bloom filter?
This works but isn't a complete answer (unless having a probabilistic response is sufficient). If you want an exact answer you'll need a little more work. If you're happy with approximate answers, then you can use multiple bloom filters to reduce the odds of an incorrect answer to be arbitrarily small. But it's still not exact.
Another answer if both tables are sorted by PK, you can query them both and do a linear time intersection.
I'll have to read up on bloom filters but if there's a decent level of confidence then that might be a good enough solution for now.
The two tables don't have a PK or any useful unique columns that can help batch the processing.
Bloom filters may be helpful. They could answer the question “is this record in this other data set?” With “definitely no”, or “maybe”. If maybe, you do the more expensive check.
Also a map of ids would also be an O(1) check, and many languages, including Go, could hold a million ids in a map/hash to do quick lookups.
Another answer if both tables are sorted by PK, you can query them both and do a linear time intersection.
This deserves more attention. Sort two lists of ids and walk through them at the same time. Compare left to right. If left is <, inc left and count as not in right. If equal, inc both. If left is >, inc right and optionally count as not in left set.
Total time is linear. O(n)
Can you say something about the keys/values you want to match on? Ints? Strings?
There are hundreds of columns with a variety of decimals, varchars, and datetimes unfortunately
what is the nature of the data? are they strings? do they have a lot of duplicated data like email addresses?
Hundreds of rows with floats, strings, and datetimes
[removed]
There's no pk and I'm not sure if there is even column of unique values. There are hundreds of columns...
I don't think Go is relevant, your database is what's relevant.
Starting point: Make a compound index on each table of All The Fields You Care About, which may just be all the fields, but make sure the fields are in the same order. Then it's quite possible that a naive query will work acceptably well. If not, consider exporting them in a predictable format, and there's a well-known trivial solution:
`sort t1 t2 t2 | uniq -u`
This may or may not be fast enough, but "a few million" is pretty trivial. (The results would be any rows in table 1 that weren't found in table 2.)
This problem seems unrelated to the language you write the solution in. Any solution is not going to be language dependent.
NetFutility [OP] -1 points · Jun 2019
Yeah but I thought go might be good for comparing second table values against the first concurrently.