Looker Studio can fetch data from BigQuery tables that are
partitioned on a DATE, DATETIME, or TIMESTAMP field. A partitioned table is
divided into segments, called partitions, that make it easier to manage and
query your data. When you divide a large table into smaller partitions, you can
improve query performance and control costs by reducing the number of bytes that are read
by a query. For more information, see Introduction to partitioned tables.
How Looker Studio uses a BigQuery date partition field
Connecting Looker Studio to a date partitioned BigQuery table lets you assign the partition field to be the governing date dimension for reports that use this data source. Learn more about setting report date ranges.
Connect a Looker Studio data source to a date partitioned table
To connect to a date partitioned table, follow these steps:
- Create a new BigQuery data source or edit the connection for an existing data source.
- Enter the connection details for your date partitioned table.
- In the Configuration column in the data source connection page, select the Use fieldname as date range dimension checkbox (where fieldname is the name of the partition field in the BigQuery table).
For example, suppose you have a table in BigQuery that has two date fields,
order_date and ship_date. The table is partitioned by order_date. When you
connect to this table in Looker Studio, you'll see an option that
lets you assign this field as the date range dimension for charts that are based on
this data source.
Effects of required partition filters
BigQuery tables can require the partition field to be used as a filter on the data. This requirement determines whether or not you can edit the Use fieldname as date range dimension option.
No partition filter required in the BigQuery table
If the table doesn't require a partition filter in the BigQuery table, the Use fieldname as date range dimension option is cleared by default.
Selecting the Use fieldname as date range dimension checkbox has the following effects:
- Looker Studio automatically uses the partition field as the Date Range Dimension for charts that are based on this data source.
- You won't be able to select a different field to use as the Date Range Dimension when you configure the charts.
- Charts using this data source will always have a Default date range filter applied. You can apply a custom date range, or you can use the Auto option to filter for the last 28 days.
When the Use fieldname as date range dimension option is not enabled:
- You can select any field that you want to use as the Date Range Dimension when you configure charts that are based on this data source.
- The date range for charts that use this data source can be unfiltered (Auto date range) or you can apply a custom date range.
Partition filter is required in the BigQuery table
If the BigQuery table requires a Partition filter, then the Use fieldname as date range dimension checkbox is automatically selected and can't be cleared. The effects on charts that are based on this data source are the same as described in the previous section when the checkbox is selected.
Limits of connecting to partitioned tables
Because the date partition feature sets the date range dimension for a chart,
Looker Studio can use only partitions that are based on a DATE, DATETIME, or
TIMESTAMP field to provide date filtering in your reports.
Looker Studio supports date partitions only when the Partition by option in BigQuery is set to DAY.
If the BigQuery table has a partition that is based on other data types, such as INTEGER, and the partition filter is required, you must explicitly filter on that field in a custom query. Otherwise, Looker Studio will display an error. Learn more about required filters.