Working with Google BigQuery partitioned tables
A partitioned table in Google BigQuery is divided into segments, called partitions. By dividing a large table into smaller partitions, you can improve query performance and control costs by reducing the data read by a query. You specify one column to use for the segmentation.
In Semarchy xDI Designer, you configure BigQuery metadata to hold partition information, and optionally add a custom partition filter. xDI creates queries that only request the relevant partitions, improving query performance and reducing BigQuery costs.
To learn about partitions in Google BigQuery, see the official Google documentation.
Prepare the metadata
Metadata objects for BigQuery use a separate element to describe the partition. This element is saved at the same level as columns, under the DataStore element.
![Screenshot of a BigQuery metadata object](../_images/google-bigquery/bigquery-partition-element.webp)
To set up integration flows that recognize BigQuery partitions, start by creating BigQuery metadata that understands your partition. You can do this during the reverse engineering process, or manually.
Configure by reverse-engineering
If your BigQuery table already has a partition set up, the reverse engineering process is the easiest method to use.
Follow the process to create metadata for BigQuery tables. During the reverse engineering process, xDI Designer detects the partition, and adds the partition to the metadata automatically.
Configure manually
Add your partition information manually if you are editing or designing metadata manually, or if you cannot reverse-engineer the metadata.
-
Find your BigQuery metadata object in the Project Explorer.
-
Open the metadata.
-
Navigate to the DataSchema element, then to the DataStore element representing the partitioned table.
-
Open the context menu on the DataStore element, and select New > Partition. A Partition element appears next to the columns.
-
Open the Partition element properties.
-
Set the Type field, and other parameters, based on your BigQuery table.
The list of partition parameters is as follows:
Partition types
Type | Description |
---|---|
Ingestion Timestamp |
Use this type for tables partitioned by ingestion time. |
Range |
Use this type for tables partitioned based on ranges of values in a specific |
Timestamp |
Use this type for tables partitioned on a |
Parameters by partition type
Parameter | Description |
---|---|
Timestamp Truncation |
What time granularity your partitions use. Possible values are:
|
Parameter | Description |
---|---|
Column |
The name of the column holding the partitioning integer range. You must define this column in your metadata first. |
Min |
The starting value for range partitioning (inclusive). |
Max |
The ending value for range partitioning (exclusive). |
Interval |
The interval of each range within the partition. |
Parameter | Description |
---|---|
Column |
The name of the column holding the partitioning time data. You must define this column in your metadata first. |
Timestamp Truncation |
The time granularity your partitions use. Possible values are:
|
Turn on partition usage
After you define columns and a partition in your BigQuery metadata, you are ready to use them in your integration flows.
You do not need to drag and drop partition nodes to a mapping or process. xDI Designer detects partitions in your metadata automatically.
Use partitions automatically
In mappings and processes, xDI automatically calculates which partitions it needs to call.
-
xDI finds mapped BigQuery data.
-
xDI confirms that partitioning is enabled in the integration template.
-
xDI analyzes the source and target data.
-
For source data, it analyzes filters on the source.
-
For target data, it builds a filter based on the mapped source.
-
-
xDI makes an initial request to Google BigQuery to confirm the partitions that match the filters.
-
The integration flow continues, using the relevant partitions.
Use partitions with template parameters
If you want more control over partition usage, you can set partition parameters manually. This method avoids the initial query to Google BigQuery.
Manual partition settings are only available for the Template INTEGRATION BigQuery integration template. |
-
Open or create a mapping that integrates data to a BigQuery DataStore.
-
Click the Integration icon on the BigQuery DataStore to open its properties.
-
In the Integration Properties, make sure Template INTEGRATION BigQuery is the active template.
-
Check one or both partitioning pruning parameters.
-
If you checked target partition pruning, set a partition filter.
The parameters work as follows:
Parameter | Description |
---|---|
Enable Partition Pruning |
This checkbox turns partition usage on or off for source tables. Source data partitions are calculated from filters on the source table. If there is no filter, xDI ignores partitions and reads the whole table. |
Enable Target Partition Pruning |
This checkbox turns partition usage on or off for target tables. To use partitions on the target, the target column must be part of the update key, and mapped from a source. Target partitions are calculated from the source data mapped to the target. You can override these calculations with the Target Partition Filter parameter. |
Target Partition Filter |
An SQL filter that xDI uses to set which partitions to use on a target table. This setting overrides automatic partition calculations on target tables. When writing the filter, prefix the name of the target column with For example:
|
Save and test your mapping to make sure your filter works.