User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
sql_create

Usage

derived_table: customer_order_facts {
  sql_create: {
      SQL statement ;;

  }
}

Hierarchy

sql_create

Default Value

None

Accepts

A SQL statement

Definition

sql_create enables custom Data Definition Language (DDL) commands for building PDTs. sql_create will issue a statement as is, without Looker’s usual error checking. The only requirement is that the statement results in the creation and execution of a PDT. This lets you, for example, create PDTs that support Google’s BigQuery ML (BQML) machine learning models.

Google is scheduled to make an update to BQML in August, 2019, that will require Looker 6.12 or later for use with BQML.

Examples

Create a PDT for BQML queries that predict likelihood of future purchases:

view: future_purchase_model { derived_table: { datagroup_trigger: bqml_datagroup sql_create: CREATE OR REPLACE MODEL ${SQL_TABLE_NAME} OPTIONS(model_type='logistic_reg' , labels=['will_purchase_in_future'] , min_rel_progress = 0.005 , max_iterations = 40 ) AS SELECT * EXCEPT(fullVisitorId, visitId) FROM ${training_input.SQL_TABLE_NAME};; } }

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 create_process to Create a PDT in Multiple Steps

If your database dialect requires custom DDL commands, and you want to issue multiple commands to create a PDT, you can use create_process to issue multiple custom DDL commands in a specific order.

Top