User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
create_process

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 ;;
  }
}

Hierarchy

create_process

Default Value

None

Accepts

One or more sql_step subparameters

Definition

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

For example, some database dialects don’t support CREATE TABLE as SELECT issued as a single SQL statement; they require separate SQL statements. As a result, traditional SQL-based persistent derived tables (PDTs) can’t be created on these dialects. The create_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 use create_process to support dialects such as Google’s predictive BigQuery ML (BQML) machine learning models.

Example

Create a ctasless_customer_order_facts persistent derived table on a MySQL database in two steps. First, issue the CREATE TABLE SQL statement, defined by the first sql_step subparameter. Second, issue the INSERT INTO SQL statement with a SELECT statement, defined by the second sql_step subparameter:

view: ctasless_customer_order_facts { derived_table: { trigger_datagroup: 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 LookML view parameter.

Use sql_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 use sql_create to define a full SQL CREATE statement to execute and create a PDT in a single step.

Top