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
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.
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( ).
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.
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.
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 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`)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With