BigQuery - partitioning example

bq-partitioning.md

BigQuery Partitioning

This is a little investigation into how to create Column Partitions in BigQuery.

Create Table

CREATE TABLE test.Events(
  id int64,
  type string,
  info string,
  time date
) PARTITION BY time

Test Data

INSERT INTO test.Events(id, type, info, time) 
VALUES (1, 'birth', 'brother1', '1969-03-01'),
       (2, 'birth', 'brother2', '1970-12-01'),
       (3, 'birth', 'brother3', '1976-09-01'),
       (4, 'marriage', 'brother2', '1991-09-01'),
       (5, 'offspring', 'brother2 has son1', '1993-12-01'),
       (6, 'offspring', 'brother2 has daughter1', '1996-09-01'),
       (7, 'marriage', 'brother3', '2006-07-01'),
       (8, 'divorce', 'brother2', '2010-03-01'),
       (9, 'marriage', 'brother2', '2011-09-01'),
       (9, 'death', 'brother2', '2020-01-01')

Querying

The following scans 372 bytes:

SELECT * from test.Events WHERE type='birth'

Where as the putting on the time constraint now only takes 99 bytes

SELECT * from test.Events WHERE type='birth' and time < '1980-01-01'