Adding Automatic Partition Filters Based on Another Column in BigQuery

ChartFactor Toolkit
1

Hello, I have a BigQuery table with a very large amount of data. We partitioned the table using a hash column generated from another column, to keep queries cheaper.

However, users don’t know they need to filter on the hash-based partition field, so they only filter on the original column. This causes the queries to scan the full table.

Is it possible, using the toolkit, to automatically add a secondary filter on the partition column whenever a filter is applied to the original source column?

2

You can achieve this using a feature we recently added called the routing property. It allows you to define automatic routing rules so that when a user filters a specific field, an additional filter is applied to the corresponding partition column.

Here’s an example configuration:

// Define providers
var providers = [{
    name: 'Google BQ',
    provider: 'google-bigquery',
    ...
    metadata: {
      "your-project:your_dataset.your_table": {
        routing: [
          {
            field: 'company',
            partitionField: 'company_hash',
            query: "MOD(ABS(FARM_FINGERPRINT('{filter_value}')), 10)"
          }
        ]
      }
    }
}];

With this setup, whenever a filter is applied to the company field, the toolkit automatically adds the appropriate filter on company_hash, routing the query to the correct partition based on the original field’s value.

3

Understood, and the example looks good. The only issue is that my hash column is generated using a different method, so I’d rather rely on function-based partitioning to ensure the values match correctly.

Thansk for your help