Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Random Sampling in Google BigQuery

I just discovered that the RAND() function, while undocumented, works in BigQuery. I was able to generate a (seemingly) random sample of 10 words from the Shakespeare dataset using:

SELECT word FROM (SELECT rand() as random,word FROM [publicdata:samples.shakespeare] ORDER BY random) LIMIT 10 

My question is: Are there any disadvantages to using this approach instead of the HASH() method defined in the "Advanced examples" section of the reference manual? https://developers.google.com/bigquery/query-reference

like image 236
David M Smith Avatar asked Apr 29 '14 21:04

David M Smith


People also ask

How do you do random sampling in BigQuery?

If you want to sample individual rows, rather than data blocks, then you can use a WHERE rand() < K clause instead. However, this approach requires BigQuery to scan the entire table. To save costs but still benefit from row-level sampling, you can combine both techniques.

How do I select a random sample in SQL?

To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).

What is BigQuery not good for?

You need to understand that BigQuery cannot be used to substitute a relational database, and it is oriented on running analytical queries, not for simple CRUD operations and queries.


2 Answers

Great to know RAND() is available!

In my case I needed a predefined sample size. Instead of needing to know the total number of rows and do the division sample size over total rows, I'm using the following query:

SELECT word, rand(5) as rand FROM [publicdata:samples.shakespeare] order by rand #Sample size needed = 10 limit 10 

Summarizing, I use ORDER BY + LIMIT to ramdomize and then extract a defined number of samples.

like image 23
fernandosjp Avatar answered Oct 07 '22 15:10

fernandosjp


For stratified sampling, check https://stackoverflow.com/a/52901452/132438


Good job finding it :). I requested the function recently, but it hasn't made it to documentation yet.

I would say the advantage of RAND() is that the results will vary, while HASH() will keep giving you the same results for the same values (not guaranteed over time, but you get the idea).

In case you want the variability that RAND() brings while still getting consistent results - you can seed it with an integer, as in RAND(3).

Notice though that the example you pasted is doing a full sort of the random values - for sufficiently big inputs this approach won't scale.

A scalable approach, to get around 10 random rows:

SELECT word FROM [publicdata:samples.shakespeare] WHERE RAND() < 10/164656 

(where 10 is the approximate number of results I want to get, and 164656 the number of rows that table has)


standardSQL update:

#standardSQL SELECT word FROM `publicdata.samples.shakespeare` WHERE RAND() < 10/164656 

or even:

#standardSQL SELECT word FROM `publicdata.samples.shakespeare` WHERE RAND() < 10/(SELECT COUNT(*) FROM `publicdata.samples.shakespeare`) 
like image 101
Felipe Hoffa Avatar answered Oct 07 '22 16:10

Felipe Hoffa