Cooladata’s events and sessions tables are automatically partitioned by time. Cooladata also enables the partitioning of other external tables in your project by a timestamp or date column.
Partitioning makes sure you run only on the selected time range in the table instead of the entire table, improving performance and minimizing query run time. Partitioned tables can be created by models, aggregation tables , Google Cloud Storage or Gmail integrations and are only available for querying in Standard SQL dialect.
Table Partitioning Settings
Table partitioning requires two settings:
Defines the column in the table which the table is partitioned by. Supports DATE or TIMESTAMP data type.
Data written to a partitioned table is automatically directed to the appropriate partition based on the date value (expressed in UTC) in the partitioning column.
When querying the partitioned table, a filter on this partition column will automatically direct the query to run only on the relevant partitions.
Require Partition Filter
Defines whether queries running on this table will require the partition column in the WHERE clause or not. When TRUE, queries selecting from this table without the partition column filter will fail.
Creating a partitioned Table using an Aggregation Table
Aggregation Tables automatically run scheduled calculations and data aggregations and save them in a separate, permanent table in your Cooladata project. To make this table partitioned, the Aggregation Table query should be in Standard SQL dialect (creating a partitioned table using Legacy SQL is not supported), and the following fields need to be filled in before the aggregation table’s first run:
Notice that after the aggregation table’s first run the table can only be queried using Standard SQL dialect.
Creating a partitioned Table using a Model
Models are designed to add R and Python capabilities to your Cooladata’s workflow. The results of the R or Python script is saved to a table in your Cooladata project.
To make this table partitioned, you need to fill in the following fields before the model’s first run:
Notice that after the model’s first run the model’s table can only be queried using Standard SQL dialect.
Creating a partitioned Table using an Integration
Gmail or Google Cloud Storage integrations allow you to upload an external file to a table in your Cooladata project.
To make this table partitioned, open the advanced section in the integration settings and fill in the following fields before the integration’s first run:
Notice that after the integration’s first run the table can only be queried using Standard SQL dialect.