Cheap previews and stable samples
Looking at a big table can cost real money, and with TABLESAMPLE you pay for only part of it. A hash gives you a sample that stays the same from one query to the next.
How it works
BigQuery stores a table in blocks. TABLESAMPLE SYSTEM (10 PERCENT) keeps about a tenth of the blocks and skips the rest, so the query reads and pays for about a tenth of the bytes. It picks blocks and never single rows. A block comes with all its rows, so the number of rows changes from run to run. A hash works the other way round. It reads every block and saves nothing, but it picks the same rows every time. FARM_FINGERPRINT(id) turns an id into a fixed number, and the remainder of that number turns it into a bucket. One id always lands in one bucket.
- Write the clause after the table name, and after its alias if it has one:
FROM learn.mot.dim_vehicle TABLESAMPLE SYSTEM (10 PERCENT). The estimate you see before you run already counts only the blocks that are kept. The MOT tables in this course are samples of bigger tables, and the editor shows the sizes of the full tables, so you see the saving the full table would give. - A plain
SELECT *ofdim_vehiclecosts about $0.20 at list price. WithTABLESAMPLE SYSTEM (10 PERCENT)the same query costs about $0.02. - BigQuery keeps
ppercent of the blocks, rounded down, and never fewer than one block whenpis above 0. A table or a partition of up to about 168 MiB is one block, and sampling one block returns every row and reads all of it. That is the largest size seen as one block. The smallest table seen to split was 363 MiB. A partitioned table has at least one block for each partition. - A query reads only the partitions its date filter picks, and a sample works on the blocks of those partitions together.
- A filter computed for each row skips no block.
WHERE RAND() < 0.1andWHERE ABS(MOD(FARM_FINGERPRINT(make), 20)) = 5work out a value for every row, so BigQuery reads every block first.LIMITdoes not lower the bytes read either. A filter on a partition column can skip blocks, because BigQuery knows where those values are stored. - A sample changes between runs. BigQuery can pick other blocks each time, does not cache the result and bills each run. It has no
REPEATABLEoption to ask for the same blocks again.COUNTandSUMover a sample give the totals of the sample. They are not the totals of the table. A block sample of a clustered table takes runs of nearby keys, so it can be biased. FARM_FINGERPRINT(x)returns anINT64that is the same for the same value every time, and it can be negative.MODkeeps the sign of its first argument, soMOD(FARM_FINGERPRINT(x), 10)gives numbers from -9 to 9.ABS(MOD(FARM_FINGERPRINT(x), 10))gives a bucket from 0 to 9. Take theABSof the remainder.ABSof the smallestINT64is an overflow error, soMOD(ABS(FARM_FINGERPRINT(x)), 10)gives the same buckets and fails on that one hash.- Hash the id of the thing that must stay together. To keep each customer's orders together, hash the customer id. A hash of the order id would spread one customer's orders over the buckets.
WHERE ABS(MOD(FARM_FINGERPRINT(customer_id), 20)) = 5keeps about a twentieth of the customers, the same ones in every query, with all their orders.
Common mistake
A table or a partition of one block gives no saving. A sample of it returns every row and reads all of it, and a real run is billed at least 10 MiB for each table anyway. Judge a sample on a table with many blocks.
Worked example
SELECT *
FROM `learn.mot.dim_vehicle` TABLESAMPLE SYSTEM (10 PERCENT)The editor estimates about 3.2 GiB before you run, where the whole table is 32.17 GiB. dim_vehicle has many blocks, so the sample can skip most of them.
Run it twice. The rows can differ, and so can the number of them, because BigQuery can pick other blocks each time.