Usage

derived_table: {  create_process: {    sql_step:      CREATE TABLE ${SQL_TABLE_NAME}      (customer_id int(11),      lifetime_orders int(11));;    sql_step:      INSERT INTO ${SQL_TABLE_NAME}      (customer_id,      lifetime_orders)      SELECT customer_id, COUNT(*)      AS lifetime_orders      FROM order      GROUP BY customer_id;;  }}

Definition

If your database dialect uses custom Data Definition Language (DDL) commands, you can usecreate_process to createpersistent derived tables (PDTs).create_process defines a list of SQL statements that will be executed in the order listed. Each individual SQL statement is specified using thesql_step subparameter. Eachsql_step subparameter can include any legal SQL query. You can define multiplesql_step subparameters, and they will be executed one at a time, in the order they are specified. Looker issues the statements in thesql_step subparameters as is, without Looker's usual error correction.

For example, some database dialects don't supportCREATE TABLE as SELECT issued as a single SQL statement; they require separate SQL statements. As a result, traditionalSQL-based persistent derived tables can't be created on these dialects. Thecreate_process parameter provides an alternate way to create PDTs, by creating a list of separate SQL statements that are issued in sequence.

You can also usecreate_process to support dialects such as the Google predictiveBigQuery ML machine learning models.

Thecreate_process parameter indicates that you are writing the fullCREATE statements for the derived table, including any indexes. To add an index for a derived table withcreate_process, use asql_step parameter to specify the SQL for the index.

For PDTs defined usingcreate_process, you cannot use any of the following parameters:

indexes

cluster_keys

distribution

distribution_style

partition_keys

sortkeys

Example

Create actasless_customer_order_facts persistent derived table on a MySQL database in two steps. First, issue theCREATE TABLE SQL statement, defined by the firstsql_step subparameter. Second, issue theINSERT INTO SQL statement with aSELECT statement, defined by the secondsql_step subparameter:

view: ctasless_customer_order_facts {  derived_table: {    datagroup_trigger: some_datagroup    create_process: {      sql_step: CREATE TABLE ${SQL_TABLE_NAME} (                         customer_id int(11),                         lifetime_orders int(11)                       ) ;;      sql_step: INSERT INTO ${SQL_TABLE_NAME}(customer_id, lifetime_orders)                        SELECT customer_id, COUNT(*) AS lifetime_orders                         FROM order                         GROUP BY customer_id ;;    }  }}

Things to consider

${SQL_TABLE_NAME} substitution operator

You can use the${SQL_TABLE_NAME} substitution operator to substitute in the computed name of the PDT being created. This ensures the SQL statement will correctly include the PDT name given in the LookMLview parameter.

create_process must create a table with the name indicated by the${SQL_TABLE_NAME} substitution operator, or it will be rebuilt from scratch on every trigger check interval that is specified in a connection'sDatagroup and PDT Maintenance Schedule setting (the default is five minutes). This can cause unexpected query traffic on your database or data warehouse.

Usesql_create to create a PDT in one step

If your database dialect requires custom DDL commands, and you want to create a PDT in a single step, you can usesql_create to define a fullSQL CREATE statement to execute and create a PDT in a single step.

Tables defined withcreate_process can't be used for incremental PDTs

To be used as anincremental PDT, a SQL-based PDT must have a query defined using thesql parameter. SQL-based PDTs that are defined with thesql_create parameter or thecreate_process parameter cannot be incrementally built.

This is because Looker uses anINSERT or a MERGE command to create the increments for an incremental PDT. The derived table cannot be defined using custom Data Definition Language (DDL) statements, since Looker wouldn't be able to determine which DDL statements would be required to create an accurate increment.