I have a Google BigQuery dataset of around 16 million customer ids with around 130 attributes related to each one of them (16 million rows / 131 columns). I am trying to create a randomized control group for any small subset of these customers that I want to test. The technologies/solutions I have come up with have all failed so far.


For instance, let's say that subset A is 900 of these customer ids. I am going to create 1000 random groups of subset A (of around 5-10 random customer ids in subset A in each group), and then for each of those groups assign a random set of the 130 customer attributes (again around 5-10) to each of the customer ids in the group. It will end up looking like this (in normalized form):

group_id customer_id attribute value
1        34          abc       4
1        34          xyz       9
1        77          abc       11
1        77          xyz       2
2        34          ccc       7
2        34          yyy       94
2        83          ccc       13
2        83          yyy       9

Notice the first group has customer ids "34", "77" and each contain attributes "abc", "xyz". Second group has customer ids "34", "83" and each contain attributes "ccc", "yyy".

Now for each customer id in each group in subset A, I need to find a single random customer id from the full 16 million customer id dataset (minus the ones in subset A of course) that has the exact same attributes/values as the customer in the group. So from the example above, for instance, I would need to match customer_id=34 from group_id=1 on a customer that also had attributes abc=4 AND xyz=9. If there was no customer like that, then I would want to find the next best match (abc=4 OR xyz=9).


I initially created two normalized tables in Google BigQuery, one for the subset like the above example, and another for the full dataset that was similar:

customer_id attribute value
78          eer       5
78          mmm       2
99          eer       87

That full dataset ended up being about 1.4 billion rows long after making a row for each customer's attributes like shown above. The idea was to join on the attribute/value columns and then aggregate the result to get the matching attribute count of each customer id in the full dataset to each attribute group in the subset. However BigQuery seemed unable to resolve the join (query would run for hours without any sign of resolving).

Tried using Google dataflow to make the join using CoGroupByKey but it also was unable to resolve the dataset after running the group by function for more than 6 hours.

Tried moving the full dataset to Google Bigtable and then using it as a lookup table based on the key (which I made a concatenated string of each customer id's "attribute:value"), but that also ran on Google dataflow for over an hour with no resolution.

Without going into the specifics on how I coded my dataflow jobs, what is (high level) the best way to resolve a complex self-join like this, or is it best to just avoid problems like this?

migrated from superuser.com May 8 at 16:44

This question came from our site for computer enthusiasts and power users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy