Partitioning tables help improve query performance and cost optimization. Google BigQuery, as a columnar store, only charges for the columns that are read when a query is ran. Partitioning allows to slice billing rows for the intersection of column queried and partitions used.
There are two types of table partitioning in BigQuery:
The syntax to query these partitions used to be the following:
SELECT c1, c3 FROM ...
WHERE_PARTITIONTIME BETWEEN "2-18-01-03" AND "2018-01-05"
Problem
This kind of syntax is not Strategy-friendly, as the engine would require having a _PARTITIONTIME field sourcing an attribute. That field doesn't physically exist in the table description and therefore cannot be used as an attribute.
BigQuery Solution
Create a BigQuery view containing all fields in addition to this _PARTITIONTIME column:
CREATE OR REPLACE VIEW ... AS
SELECT_PARTITIONTIME AS PARTITIONTIME, c1, c3 FROM
Strategy Solution
If you do not want to create these views in BigQuery, you can create a dummy PartitionDate Filter attribute in Strategy, defined as:
ApplySimple("_PARTITIONTIME", [AnyColumnName])
Note: This will work in Strategy Developer for IT and in Strategy Web/Desktop for Business Users. Web/Desktop requires making use of a Live Connect dataset (either Data Import - Pick Tables or from Existing Objects).
Partition Time attribute definition in Web. This only works when using a Live Connect Dataset.
Partition Time field from Developer.
Use this attribute in all filters ran on this table.
Use the following syntax to query partitioned tables:
SELECT c1, c3 FROM ...
WHERE eventDate BETWEEN "2018-01-01" AND "2018-01-04"
Data billed with a partitioned query is highlighted in green. Without a partition, the billing would match the yellow blocks which would be much higher.
If eventDate physically exists in the table, a Strategy semantic graph can be mapped there. The seamlessly generated SQL relies on the BigQuery partition.
