table_format

Usage

view: my_view {
  derived_table: {
    table_format: PARQUET
    ...
  }
}
Hierarchy
view
derived_table
table_format
Default Value
PARQUET

Accepts
PARQUET, ORC, AVRO, JSON, or TEXTFILE

Special Rules
table_format is supported only on specific dialects

Definition

The table_format parameter specifies the format that a persistent derived table (PDT) will have in an Athena database, which can be one of the following:

  • Parquet
  • Optimized Row Columnar (ORC)
  • Avro
  • JavaScript Object Notation (JSON)
  • Text file

See the Amazon Athena documentation for details.

See the Dialect support for table_format section below for the list of dialects that support table_format.

The table_format parameter works only with tables that are persistent, such as PDTs and aggregate tables. table_format is not supported for derived tables without a persistence strategy.

Examples

Create a customer_order_facts PDT on an Amazon Athena database using ORC format:

view: customer_order_facts {
  derived_table: {
    explore_source: order {
      column: customer_id { field: order.customer_id }
      column: date { field: order.order_time }
      column: city { field: users.city }
      column: age_tier { field: users.age_tier }
      derived_column: num_orders {
        sql: COUNT(order.customer_id) ;;
      }
    }
    table_format: ORC
    table_compression: SNAPPY
    datagroup_trigger: daily_datagroup
  }
}

Dialect support for table_format

The ability to use table_format depends on the database dialect your Looker connection is using. In the latest release of Looker, the following dialects support table_format:

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
No
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11+
No
Exasol
No
Firebolt
No
Google BigQuery Legacy SQL
No
Google BigQuery Standard SQL
No
Google Cloud PostgreSQL
No
Google Cloud SQL
No
Google Cloud 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 8.0.12+
No
Oracle
No
Oracle ADWC
No
PostgreSQL 9.5+
No
PostgreSQL pre-9.5
No
PrestoDB
No
PrestoSQL
No
SAP HANA
No
SAP HANA 2+
No
SingleStore
No
SingleStore 7+
No
Snowflake
No
Teradata
No
Trino
No
Vector
No
Vertica
No