Please enable JavaScript.
Coggle requires JavaScript to display documents.
Partitioned Table - Coggle Diagram
Partitioned Table
Managing data
Load data into a partitioned table
Browse (or preview) partitioned table data
Query partitioned table data
Append to or overwrite partitioned table data
Modify partitioned table data using data manipulation language (DML) statements
Copy partitioned table data
Stream data into partitioned tables
Export partitioned table data
Introduction
easier to manage + query
improve query performance
control query costs
Classify
Ingestion-time
pseudo column
_PARTITIONTIME
contains a date-based timestamp
for data that is loaded into the table
Date, timestamp
or datetime
partitioned tables
specific column
DATE, TIMESTAMP
or DATETIME
Valid value
can create partitions
Partition:
daily/monthly/yearly/hour
NULL value
Partition:
__NULL__
Value exists outside
Partition:
__UNPARTITIONED__
Date/timestamp/datetime
partitioning versus sharding
time-based naming
[PREFIX]_YYYYMMDD
Integer range
partitioned tables
Required
specific INTEGER column
start of range partitioning (inclusive)
end of range partitioning (exclusive)
interval of each range within the partition
Ex:
column=customer_id
start = 0
end = 6
interval = 3
Integer range partitioning
versus clustering
TODO
Querying
The _PARTITIONDATE pseudo column
Querying partitioned tables
Pruning (limiting) partitions
To limit the partitions that are scanned in a query, use a constant expression in your filter. If you use dynamic expressions in your query filter, BigQuery must scan all of the partitions.
Isolate the partition column in your filter
Managing
Updating properties
Description
Table expiration time
Partition expiration time
Not support INTERGER partition
Schema definition
Labels
partition filter requirements
schema definition
