BigQuery大表下每50条记录选取1条的实现方案（禁用order by rand()）

Hey there! When working with huge datasets in BigQuery, ORDER BY RAND() is a total performance killer—it triggers a full table scan and expensive global sort, which is completely unfeasible for large tables. Here are three scalable, efficient approaches to get your 1-in-50 sample without breaking a sweat:

1. Hash-Based Sampling (Most Consistent & Fast) #

This method uses a hash function to map rows to a uniform range of values, then filters for rows where the hash modulo 50 equals a fixed value (e.g., 0). It requires no sorting and scales seamlessly with massive tables.

Example Query: #

SELECT *
FROM `your-project.your-dataset.your-table`
-- Use a unique identifier for stable, reproducible results (e.g., user_id, order_id)
WHERE MOD(FARM_FINGERPRINT(CAST(unique_id AS STRING)), 50) = 0

Or if you want maximum randomness using the entire row:

SELECT *
FROM `your-project.your-dataset.your-table`
WHERE MOD(FARM_FINGERPRINT(TO_JSON_STRING(*)), 50) = 0

• Why this works: FARM_FINGERPRINT is a fast, collision-resistant hash function built into BigQuery. The modulo operation ensures roughly 1/50 of rows are selected, with uniform distribution across the dataset.
• Pro tip: Using a unique column (like an ID) instead of the full row ensures the same rows are picked if you re-run the query—perfect for reproducible analysis.

2. Partitioned Row Numbering (For Ordered Samples) #

If your table is partitioned (e.g., by date) and you want to select 1 row per 50 within each partition, use ROW_NUMBER() with a partition clause. This avoids global sorting and leverages partition pruning for faster, cheaper queries.

Example Query: #

WITH partitioned_numbered_rows AS (
  SELECT *,
         -- Use ORDER BY NULL for arbitrary order, or a timestamp/ID for consistent ordering
         ROW_NUMBER() OVER(PARTITION BY DATE(_PARTITIONTIME) ORDER BY NULL) AS row_num
  FROM `your-project.your-dataset.your-partitioned-table`
  -- Optional: Filter to specific partitions to reduce data scanned
  -- WHERE DATE(_PARTITIONTIME) >= '2024-01-01'
)
SELECT * EXCEPT(row_num)
FROM partitioned_numbered_rows
WHERE MOD(row_num, 50) = 0

• Why this works: The ROW_NUMBER() function runs independently within each partition, so you don’t incur the cost of a global sort. If you need samples ordered by insertion time, replace ORDER BY NULL with your timestamp field.

3. TABLESAMPLE SYSTEM (Ultra-Fast Approximate Sampling) #

For the fastest possible sampling (at the cost of slight approximation), use BigQuery’s TABLESAMPLE SYSTEM clause. This samples directly at the storage layer by skipping entire data blocks, making it ideal for quick exploratory analysis.

Example Query: #

SELECT *
FROM `your-project.your-dataset.your-table`
-- 2% is exactly equivalent to 1 in 50 (1/50 = 0.02)
TABLESAMPLE SYSTEM (2 PERCENT)

• Caveat: TABLESAMPLE SYSTEM is approximate—you might get slightly more or fewer rows than 1/50. It can also have bias if data blocks contain similar rows (e.g., all rows from the same date), but it’s unbeatable for speed.

Key Notes: #

• Avoid global ROW_NUMBER() OVER(ORDER BY ...) for massive tables—it will trigger a full sort, which is slow and costly.
• If you need an exact 1-in-50 sample based on insertion order, ensure your table has a sequential auto-increment ID, then filter MOD(id, 50) = 0. If you don’t have an ID, consider adding one during data ingestion.

内容的提问来源于stack exchange，提问作者Nivi