TABLESAMPLE in BigQuery

TABLESAMPLE SYSTEM reads a random percentage of a table's storage blocks, and on-demand queries are billed only for the blocks read. A dry run shows the reduction. Tables under about 1 GB are one block, so the sample is the whole table.

TABLESAMPLE SYSTEM (10 PERCENT) makes a query read about 10% of a table and, with on-demand pricing, pay for about 10%. LIMIT doesn’t reduce the bytes a query reads unless the table is clustered, so TABLESAMPLE is the way to look at a random slice for less money. The catch is that it samples storage blocks, not rows, and on small tables it does nothing.

SELECT SUM(num_characters)
FROM `bigquery-public-data.samples.wikipedia` TABLESAMPLE SYSTEM (10 PERCENT)

The estimate drops with the percentage

samples.wikipedia has 313,797,035 rows and 2.51 GB in the num_characters column. The dry runs below are on that table. Reading one column in full:

SELECT SUM(num_characters) AS chars
FROM `bigquery-public-data.samples.wikipedia`

With 10% and 1% sampled, the estimate is within 0.2% of that fraction of the full scan (250,536,554 against 251,037,628 for 10%):

SELECT SUM(num_characters) AS chars
FROM `bigquery-public-data.samples.wikipedia` TABLESAMPLE SYSTEM (10 PERCENT)

SELECT SUM(num_characters) AS chars
FROM `bigquery-public-data.samples.wikipedia` TABLESAMPLE SYSTEM (1 PERCENT)

The proportion has a floor. At 0.0001 percent the column would be about 2.5 KB, but BigQuery estimates 5,010,731 bytes, roughly 0.2% of the column:

SELECT SUM(num_characters) AS chars
FROM `bigquery-public-data.samples.wikipedia` TABLESAMPLE SYSTEM (0.0001 PERCENT)

So the sample can’t get smaller than a few blocks. A percentage of 0 is estimated at 0 bytes.

What a real run reads

We ran the 10% query three times against the real table. Each run processed 250,536,554 bytes, the same as the dry run, and was billed 250,609,664 (bytes billed are rounded up to a multiple of 1 MiB). The sample is not the same each time, but its size is: the three runs returned 31,314,914, 31,316,581 and 31,316,848 rows. Ten percent of 313,797,035 is 31,379,704, so each run was within 0.2% of the nominal size.

The sum of num_characters over the three samples was 556,670,834,481, 556,568,460,718 and 556,472,060,361. The full table’s sum is 5,576,502,809,700, so ten times a sample is within 0.2% of the truth here. The samples are close on this table. On a clustered table they can be far off, as described below.

Sample results are not scaled

SUM and COUNT over a sample return the sample’s total. To estimate the whole table, multiply by 100 divided by the percentage yourself. For an average or a proportion no correction is needed.

Small tables are one block

BigQuery splits a table or partition into blocks when it is larger than about 1 GB. A smaller table is one block, so TABLESAMPLE selects all of it. Dry-running the same sampling on samples.shakespeare, a table of a few MB, shows no reduction, the whole column:

SELECT SUM(word_count) AS words
FROM `bigquery-public-data.samples.shakespeare` TABLESAMPLE SYSTEM (10 PERCENT)

Sampling a table that is one block returns every row. A temp table of 1,000 rows sampled at 10% returns 1,000:

CREATE TEMP TABLE page_views AS
SELECT n AS view_id FROM UNNEST(GENERATE_ARRAY(1, 1000)) AS n;

SELECT COUNT(*) AS sampled_rows
FROM page_views TABLESAMPLE SYSTEM (10 PERCENT)

Google’s guide adds that with a percentage above zero and a non-empty table, you always get some rows.

Why the results vary, and when they are biased

TABLESAMPLE SYSTEM picks blocks at random and returns every row in the chosen blocks. Two consequences follow:

• Two runs return different rows, and a sample isn’t cached, so each run is billed again. Google’s guide says BigQuery doesn’t cache results of queries with TABLESAMPLE.
• Rows in one block are not independent. In a partitioned or clustered table, rows in a block share a partition or have close clustering values, so a sample is biased toward whole partitions or key ranges. Google’s guide says sample sets from these tables tend to be more biased than from plain tables. If you need an unbiased sample of a clustered table, don’t use block sampling.

If you need row-level randomness you can add WHERE RAND() < 0.1, but that reads the whole table. Combining both reads about 20% of the blocks and then keeps 10% of their rows:

SELECT *
FROM dataset.my_table TABLESAMPLE SYSTEM (20 PERCENT)
WHERE RAND() < 0.1

Maximum bytes billed ignores the sample

Setting maximum_bytes_billed on a sampled query doesn’t work the way the estimate suggests. We set a limit of 1,000,000,000 bytes on the 10% query, which would have been billed 250,609,664 bytes. BigQuery refused to run it:

Query exceeded limit for bytes billed: 1000000000. 2511339520 or higher required.

The check used the size of the unsampled column, 2,511,339,520 bytes (the full scan rounded up to 1 MiB). A limit of 2,500,000,000 failed the same way, and a limit of 3,000,000,000 ran. So a cost limit has to be set for the full table, not for the sample. See controlling costs for what that limit does. The dry run, by contrast, shows the sampled number.

What you can sample

TABLESAMPLE SYSTEM is the only method. TABLESAMPLE RESERVOIR is rejected as “Unsupported sampling method”, and REPEATABLE as “REPEATABLE clause is unsupported”, so you can’t ask for the same sample twice. The percentage can be a query parameter (SYSTEM (@percent PERCENT)), and values outside 0 to 100 are an error:

CREATE TEMP TABLE page_views AS
SELECT n AS view_id FROM UNNEST(GENERATE_ARRAY(1, 1000)) AS n;

SELECT COUNT(*) AS n
FROM page_views TABLESAMPLE SYSTEM (150 PERCENT)

Sampling has to be applied to a base table:

SELECT *
FROM UNNEST([1, 2, 3]) AS n TABLESAMPLE SYSTEM (50 PERCENT)

WITH page_views AS (SELECT n AS view_id FROM UNNEST(GENERATE_ARRAY(1, 1000)) AS n)
SELECT COUNT(*) AS sampled_rows
FROM page_views TABLESAMPLE SYSTEM (10 PERCENT)

A sampled table can appear only once in a statement. Self-joining a sampled table fails, even with two different aliases:

CREATE TEMP TABLE page_views AS
SELECT n AS view_id FROM UNNEST(GENERATE_ARRAY(1, 1000)) AS n;

SELECT COUNT(*) AS n
FROM page_views AS a TABLESAMPLE SYSTEM (10 PERCENT)
JOIN page_views AS b TABLESAMPLE SYSTEM (10 PERCENT) USING (view_id)

The error also says sampling isn’t allowed inside an IN subquery. Google’s guide lists more things as unsupported: views, table functions, tables with row-level security, and the results of subqueries. We found one exception to the last. A subquery of the form FROM (SELECT num_characters FROM wikipedia) TABLESAMPLE SYSTEM (10 PERCENT) was accepted, and its dry-run estimate dropped to 250,536,554 bytes like the direct form. A CTE name is refused, as shown above. Don’t rely on the subquery form, because the documentation says it is not supported.

Joins

Sampling both sides of a join keeps a match only when both rows were sampled. A 10% sample joined to a 20% sample of another table keeps about 2% of the matches. Google’s guide warns that on small tables the join can come back empty.

What it costs

You pay for the blocks read, so a sample costs about its percentage of the full scan, at least a few blocks. Because results are never cached, running the same sample again pays again. A dry run with TABLESAMPLE shows the sampled bytes without running anything.

Querylab.io adds a comment, -- querylab:tablesample=1, that samples every table a query reads without changing the SQL you wrote. BigQuery doesn’t know the comment: Querylab.io applies the sampling when it prices and runs the query, and the status bar shows the saving against the full run (TABLESAMPLE).