## Usage
```
view: view_name {
derived_table: {
partition_keys: [ "created_date" ]
...
}
}
```
|---|---|
| Hierarchy [View File](https://docs.cloud.google.com/looker/docs/reference/param-view.md.txt) [`view`](https://docs.cloud.google.com/looker/docs/reference/param-view-view.md.txt) [`derived_table`](https://docs.cloud.google.com/looker/docs/reference/param-view-derived-table.md.txt) `partition_keys` - or - [Model File](https://docs.cloud.google.com/looker/docs/reference/param-model.md.txt) [`explore`](https://docs.cloud.google.com/looker/docs/reference/param-explore-explore.md.txt) [`aggregate_table`](https://docs.cloud.google.com/looker/docs/reference/param-explore-aggregate-table.md.txt) [`materialization`](https://docs.cloud.google.com/looker/docs/reference/param-explore-aggregate-table.md.txt#materialization) `partition_keys` | Default Value `None`
Accepts One or more partitioned column names
Special Rules `partition_keys` is supported only on [specific dialects](https://docs.cloud.google.com/looker/docs/reference/param-view-partition-keys.md.txt#dialect_support) |
## Definition
The `partition_keys` parameter supports database dialects that have the ability to partition columns. When a query is run that is filtered on a partitioned column, the database will scan only those partitions that include the filtered data, rather than scanning the entire table. Because a smaller subsection of the table is being scanned, this can significantly reduce the time and cost of querying large tables when the appropriate partition and filter are specified.
> The `partition_keys` parameter works only with tables that are [persistent](https://docs.cloud.google.com/looker/docs/derived-tables.md.txt#adding_persistence), such as PDTs and aggregate tables. `partition_keys` is not supported for [derived tables](https://docs.cloud.google.com/looker/docs/creating-ndts.md.txt) without a persistence strategy.
>
> In addition, the `partition_keys` parameter is not supported for derived tables that are defined using [`create_process`](https://docs.cloud.google.com/looker/docs/reference/param-view-create-process.md.txt) or [`sql_create`](https://docs.cloud.google.com/looker/docs/reference/param-view-sql-create.md.txt).
When you create a [persistent derived table](https://docs.cloud.google.com/looker/docs/derived-tables.md.txt#persistent-derived-tables) (PDT) or an [aggregate table](https://docs.cloud.google.com/looker/docs/reference/param-explore-aggregate-table.md.txt), if your underlying database table uses partitioning, Looker can use that partitioning.
> See the [Dialect support for `partition_keys`](https://docs.cloud.google.com/looker/docs/reference/param-view-partition-keys.md.txt#dialect_support) section for the list of dialects that support `partition_keys`.
To add a partitioned column to a PDT or an aggregate table, use `partition_keys` and supply the names of the corresponding columns that are partitioned in the database table.
## Examples
Create a `customer_day_facts` PDT on a BigQuery database with a partition key on the `date` column:
view: customer_order_facts {
derived_table: {
explore_source: order {
column: customer_id { field: order.customer_id }
column: date { field: order.order_time }
derived_column: num_orders {
sql: COUNT(order.customer_id) ;;
}
}
partition_keys: [ "date" ]
datagroup_trigger: daily_datagroup
}
}
Create a `customer_day_facts` SQL-based derived table on a Presto database with partition keys on the `date` and `state` columns:
view: customer_day_facts {
derived_table: {
sql:
SELECT
customer_id,
DATE(order_time) AS date,
COUNT(*) AS num_orders
FROM
order
GROUP BY
customer_id ;;
partition_keys: [ "date", "state" ]
datagroup_trigger: daily_datagroup
}
}
## Dialect support for `partition_keys`
The ability to use `partition_keys` depends on the database dialect your Looker connection is using. In the latest release of Looker, the following dialects support `partition_keys`:
> In BigQuery, partitioning can be used on only one table column, which must be a [date/time column](https://cloud.google.com/bigquery/docs/creating-column-partitions#creating_a_partitioned_table_from_a_query_result) --- so a Looker PDT based on a BigQuery table can use partitioning on only one date/time column.
| Dialect | Supported? |
|---|---|
| Actian Avalanche | No |
| Amazon Athena | Yes |
| Amazon Aurora MySQL | No |
| Amazon Redshift | No |
| Amazon Redshift 2.1+ | No |
| Amazon Redshift Serverless 2.1+ | No |
| Apache Druid | No |
| Apache Druid 0.13.x - 0.17.x | No |
| Apache Druid 0.18+ | No |
| Apache Hive 2.3+ | No |
| Apache Hive 3.1.2+ | No |
| Apache Spark 3+ | No |
| ClickHouse | No |
| ClickHouse 26+ | Yes |
| Cloudera Impala 3.1+ | No |
| Cloudera Impala 3.1+ with Native Driver | No |
| Cloudera Impala with Native Driver | No |
| DataVirtuality | No |
| Databricks | Yes |
| Denodo 7 | No |
| Denodo 8 \& 9 | No |
| Dremio | No |
| Dremio 11+ | No |
| Dremio 26+ with Arrow Flight SQL | No |
| Exasol | No |
| Google BigQuery Legacy SQL | Yes |
| Google BigQuery Standard SQL | Yes |
| Google Cloud AlloyDB for PostgreSQL | No |
| Google Cloud PostgreSQL | No |
| Google Cloud SQL | No |
| Google Spanner | No |
| Greenplum | No |
| HyperSQL | No |
| IBM Netezza | No |
| MariaDB | No |
| Microsoft Azure PostgreSQL | No |
| Microsoft Azure SQL Database | No |
| Microsoft Azure Synapse Analytics | No |
| Microsoft SQL Server 2008+ | No |
| Microsoft SQL Server 2012+ | No |
| Microsoft SQL Server 2016 | No |
| Microsoft SQL Server 2017+ | No |
| MongoBI | No |
| MongoSQL | No |
| MySQL | No |
| MySQL 8.0.12+ | No |
| Oracle | No |
| Oracle ADWC | No |
| PostgreSQL 9.5+ | No |
| PostgreSQL pre-9.5 | No |
| PrestoDB | Yes |
| PrestoSQL | Yes |
| SAP HANA | No |
| SAP HANA 2+ | No |
| SingleStore | No |
| SingleStore 7+ | No |
| Snowflake | No |
| Teradata | No |
| Trino | Yes |
| Vector | No |
| Vertica | No |