How to do Efficient sampling of a fixed number of rows in Google BigQuery

I have a large data set of size N, and want to get a (uniformly) random sample of size n. There are two possible solutions:

SELECT foo FROM mytable WHERE RAND() < n/N\n

This is fast, but doesn't give me exactly n rows (only approximately).

SELECT foo, RAND() as r FROM mytable ORDER BY r LIMIT n\n

This requires to sort N rows, which seems unnecessary and wasteful (especially if n << N).

The best and easiest way to get a random sample from big query:

If you try to execute the following query several times without using cached results, you will got different results.

SELECT *\nFROM `bigquery-samples.wikipedia_benchmark.Wiki1B`\nLIMIT 5\n

Therefore, depends on how randomly you want to have the samples, this maybe a better solution

Asran

posted on