Usage
view: view_name { derived_table: { partition_keys: [ "created_date" ] ... } }
Hierarchy
partition_keys - or - partition_keys |
Default Value
None
Accepts
One or more partitioned column names
Special Rules
partition_keys is supported only on specific dialects
|
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, such as PDTs and aggregate tables.partition_keys
is not supported for derived tables without a persistence strategy.In addition, the
partition_keys
parameter is not supported for derived tables that are defined usingcreate_process
orsql_create
.
When you create a persistent derived table (PDT) or an aggregate table, if your underlying database table uses partitioning, Looker can use that partitioning.
See the Dialect support for
partition_keys
section for the list of dialects that supportpartition_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 — 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 |
Apache Druid | No |
Apache Druid 0.13+ | No |
Apache Druid 0.18+ | No |
Apache Hive 2.3+ | No |
Apache Hive 3.1.2+ | No |
Apache Spark 3+ | No |
ClickHouse | No |
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 | No |
Dremio | No |
Dremio 11+ | No |
Exasol | No |
Firebolt | No |
Google BigQuery Legacy SQL | Yes |
Google BigQuery Standard SQL | Yes |
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 |
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 2+ | No |
SingleStore | No |
SingleStore 7+ | No |
Snowflake | No |
Teradata | No |
Trino | Yes |
Vector | No |
Vertica | No |