google-bigquery - 如何随机混洗 bigquery 数据集中大表的所有行？

Question

“select id from table order by rand()” 等查询所需的内存将超过分配的内存，从而导致查询失败。如何从一个非常大的表中随机排列所有行？表的大小超过 10 亿行。

Answer 1

Business of our team requires to sample from the whole dataset repeatedly. The sample size varies each time from 80 million to 100k ...

以下是针对 BigQuery 标准 SQL 并使用具有 12+ 十亿行的表，因此应该也适用于您 :o)

查询的模式是:

#standardSQL
SELECT start_position 
FROM `bigquery-public-data.genomics_rice.Rice3K_DeepVariant_Os_Nipponbare_Reference_IRGSP_1_0`
WHERE MOD(CAST(<total number of rows in table> * RAND() AS INT64), <ratio of sample>) = 1

正式 = CAST(/AS INT64)
并代替 1在 = 1 - 可以是 0 到 <ratio of sample> - 1 之间的任何整数

所以对于 100K 的样本 - 你可以使用

#standardSQL
SELECT start_position 
FROM `bigquery-public-data.genomics_rice.Rice3K_DeepVariant_Os_Nipponbare_Reference_IRGSP_1_0`
WHERE MOD(CAST(12186710727 * RAND() AS INT64), 121867) = 1

上面在 8 秒内返回了 99,770 个采样行

对于 80M 的样本 - 你可以使用

#standardSQL
SELECT start_position 
FROM `bigquery-public-data.genomics_rice.Rice3K_DeepVariant_Os_Nipponbare_Reference_IRGSP_1_0`
WHERE MOD(CAST(12186710727 * RAND() AS INT64), 152) = 1    

在 13 秒内返回了 80,175,660 个采样行