The CREATE MODEL statement for autoencoder models

This document describes theCREATE MODEL statement for creatingautoencodermodels in BigQuery by using SQL. Alternatively, you can use theGoogle Cloud console user interface tocreate a model by using a UI(Preview) instead of constructing the SQLstatement yourself.

You can use autoencoder models with theML.PREDICTorAI.GENERATE_EMBEDDINGfunctions to embed data into a lower-dimensional space, and with theML.DETECT_ANOMALIES functionto performanomaly detection.

For more information about supported SQL statements and functions for thismodel, seeEnd-to-end user journeys for ML models.

CREATE MODEL syntax

{CREATE MODEL |CREATE MODEL IF NOT EXISTS |CREATE OR REPLACE MODEL}model_nameOPTIONS(model_option_list)ASquery_statementmodel_option_list:MODEL_TYPE = { 'AUTOENCODER' } ]    [,L1_REG = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,L2_REG = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,L1_REG_ACTIVATION = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,LEARN_RATE = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,OPTIMIZER = { { 'ADAGRAD' | 'ADAM' | 'FTRL' | 'RMSPROP' | 'SGD' } | HPARAM_CANDIDATES([candidates]) } ]    [,ACTIVATION_FN = { 'RELU' | 'RELU6' | 'ELU' | 'SELU' | 'SIGMOID' | 'TANH' } | HPARAM_CANDIDATES([candidates]) } ]    [,BATCH_SIZE = {int64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,DROPOUT = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,HIDDEN_UNITS = {int_array | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]    [,TF_VERSION = { '1.15' | '2.8.0' } ]    [,EARLY_STOP = { TRUE | FALSE } ]    [,MIN_REL_PROGRESS =float64_value ]    [,MAX_ITERATIONS =int64_value ]    [,WARM_START = { TRUE | FALSE } ]    [,NUM_TRIALS =int64_value ]    [,MAX_PARALLEL_TRIALS =int64_value ]    [,HPARAM_TUNING_ALGORITHM = { 'VIZIER_DEFAULT' | 'RANDOM_SEARCH' | 'GRID_SEARCH' } ]    [,HPARAM_TUNING_OBJECTIVES = { MEAN_SQUARED_ERROR | ... } ]    [,KMS_KEY_NAME =string_value ]

CREATE MODEL

Creates and trains a new model in the specified dataset. If the model nameexists,CREATE MODEL returns an error.

CREATE MODEL IF NOT EXISTS

Creates and trains a new model only if the model doesn't exist in thespecified dataset.

CREATE OR REPLACE MODEL

Creates and trains a model and replaces an existing model with the same name inthe specified dataset.

model_name

The name of the model you're creating or replacing. The modelname must be unique in the dataset: no other model or table can have the samename. The model name must follow the same naming rules as aBigQuery table. A model name can:

• Contain up to 1,024 characters
• Contain letters (upper or lower case), numbers, and underscores

model_name is case-sensitive.

If you don't have a default project configured, then you must prepend theproject ID to the model name in the following format, including backticks:

`[PROJECT_ID].[DATASET].[MODEL]`

For example, `myproject.mydataset.mymodel`.

MODEL_TYPE

Syntax

MODEL_TYPE={'AUTOENCODER'}

Description

The model type. This option is required.

L1_REG

Syntax

L1_REG = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) }

Description

The L1 regularization strength of theOPTIMIZER. You can only usethis option whenOPTIMIZER is set to one of the following values:

• ADAGRAD —Implements the ProximalAdagradOptimizer algorithm
• FTRL —Implements the FtrlOptimizer algorithm
• SGD —Implements the ProximalGradientDescentOptimizer algorithm

Arguments

If you aren't running hyperparameter tuning, then you can specify aFLOAT64 value. The default value is0.

If you are running hyperparameter tuning, then you can use one of thefollowing options:

• TheHPARAM_RANGE keyword and twoFLOAT64 values that definethe range to use for the hyperparameter. For example,L1_REG = HPARAM_RANGE(0, 5.0).
• TheHPARAM_CANDIDATES keyword and an array ofFLOAT64 valuesthat provide discrete values to use for the hyperparameter. For example,L1_REG = HPARAM_CANDIDATES([0, 1.0, 3.0, 5.0]).

When running hyperparameter tuning, the valid range is(0, ∞), the default range is(0, 10.0], and the scaletype isLOG.

L2_REG

Syntax

L2_REG = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) }

Description

The L2 regularization strength of theOPTIMIZER. You can onlyuse this option whenOPTIMIZER is set to one of the following values:

• ADAGRAD —Implements the ProximalAdagradOptimizer algorithm
• FTRL —Implements the FtrlOptimizer algorithm
• SGD —Implements the ProximalGradientDescentOptimizer algorithm

Arguments

If you aren't running hyperparameter tuning, then you can specify aFLOAT64 value. The default value is0.

If you are running hyperparameter tuning, then you can use one of thefollowing options:

• TheHPARAM_RANGE keyword and twoFLOAT64 values that definethe range to use for the hyperparameter. For example,L2_REG = HPARAM_RANGE(1.5, 5.0).
• TheHPARAM_CANDIDATES keyword and an array ofFLOAT64 valuesthat provide discrete values to use for the hyperparameter. For example,L2_REG = HPARAM_CANDIDATES([0, 1.0, 3.0, 5.0]).

When running hyperparameter tuning, the valid range is(0, ∞), thedefault range is(0, 10.0], and the scale type isLOG.

L1_REG_ACTIVATION

Syntax

L1_REG_ACTIVATION = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) } ]

Description

The L1 regularizer to apply a penalty on the activations at the latent space.This is a common approach to to achieve high sparsity of data representationsafter dimensionality reduction.

Arguments

If you aren't running hyperparameter tuning, then you can specify aFLOAT64value. The default value is0.

If you are running hyperparameter tuning, use one of the following options:

• TheHPARAM_RANGE keyword and twoFLOAT64 values that definethe range of the hyperparameter. For example,L1_REG_ACTIVATION = HPARAM_RANGE(0.01, 1.0).
• TheHPARAM_CANDIDATES keyword and an array ofFLOAT64 valuesthat provide discrete values to use for the hyperparameter. For example,L1_REG_ACTIVATION = HPARAM_CANDIDATES([0, 0.001, 0.01, 0.1, 1.0]).

When running hyperparameter tuning, the valid range is(0, ∞), the default range is(0, 10.0], and the scaletype isLOG.

LEARN_RATE

Syntax

LEARN_RATE = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) }

Description

The initial learn rate for training.

Arguments

If you aren't running hyperparameter tuning, then you can specify aFLOAT64 value. The default value is0.001.

If you are running hyperparameter tuning, use one of the following options:

• Use theHPARAM_RANGE keyword and specify twoFLOAT64 values that definethe range to use for the hyperparameter. For example,LEARN_RATE = HPARAM_RANGE(0.001, 0.005).
• Use theHPARAM_CANDIDATES keyword and specify an array ofFLOAT64 valuesto provide discrete values to use for the hyperparameter. For example,LEARN_RATE = HPARAM_CANDIDATES([0, 0.001, 0.01, 0.1]).

When running hyperparameter tuning, the valid range is[0, 1.0], thedefault range is[0, 1.0], and the scale type isLOG.

OPTIMIZER

Syntax

OPTIMIZER = { { 'ADAGRAD' | 'ADAM' | 'FTRL' | 'RMSPROP' | 'SGD' } | HPARAM_CANDIDATES([candidates]) }

Description

The optimizer for training the model.

Arguments

This option accepts the following values:

• ADAM —Implements the Adam algorithm. This is the default.
• ADAGRAD —Implements the Adagrad algorithm
• FTRL —Implements the FTRL algorithm
• RMSPROP —Implements the RMSProp algorithm
• SGD —Implements the gradient descent algorithm

If you are running hyperparameter training, you can provide more than onevalue for this option by usingHPARAM_CANDIDATES and specifying an array.For example,OPTIMIZER = HPARAM_CANDIDATES(['ADAM', 'FTRL', 'SGD']).

ACTIVATION_FN

Syntax

ACTIVATION_FN = { { 'RELU' | 'RELU6' | 'ELU' | 'SELU' | 'SIGMOID' | 'TANH' } | HPARAM_CANDIDATES([candidates]) }

Description

The activation function of the neural network.

Arguments

This option accepts the following values:

• RELU —Rectified linear. This is the default.
• RELU6 —Rectified linear 6
• ELU —Exponential linear
• SELU —Scaled exponential linear
• SIGMOID —Sigmoid activation
• TANH —Tanh activation

If you are running hyperparameter training, then you can provide more than onevalue for this option by usingHPARAM_CANDIDATES and specifying an array.For example,ACTIVATION_FN = HPARAM_CANDIDATES(['RELU', 'RELU6', 'TANH']).

BATCH_SIZE

Syntax

BATCH_SIZE = {int64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) }

Description

The mini batch size of samples that are fed to the neural network.

Arguments

If you aren't running hyperparameter tuning, specify anINT64 value thatis positive and is less than or equal to8192. The default value is32or the number of samples, whichever is smaller.

If you are running hyperparameter tuning, use one of the following options:

• TheHPARAM_RANGE keyword and twoFLOAT64 values that definethe range to use for the hyperparameter. For example,BATCH_SIZE = HPARAM_RANGE(16, 64).
• TheHPARAM_CANDIDATES keyword and an array ofFLOAT64 valuesthat provide discrete values to use for the hyperparameter. For example,BATCH_SIZE = HPARAM_CANDIDATES([32, 64, 256, 1024]).

When running hyperparameter tuning, the valid range is(0, ∞),the default range is[16, 1024], and the scale type isLOG.

DROPOUT

Syntax

DROPOUT = {float64_value | HPARAM_RANGE(range) | HPARAM_CANDIDATES([candidates]) }

Description

The dropout rate of units in the neural network.

Arguments

If you aren't running hyperparameter tuning, then you can specify aFLOAT64value that is positive and is less than or equal to1.0. The default value is0.

If you are running hyperparameter tuning, then you must use one of thefollowing options:

• TheHPARAM_RANGE keyword and twoFLOAT64 values that definethe range to use for the hyperparameter. For example,DROPOUT = HPARAM_RANGE(0, 0.6).
• TheHPARAM_CANDIDATES keyword and an array ofFLOAT64 valuesthat provide discrete values to use for the hyperparameter. For example,DROPOUT = HPARAM_CANDIDATES([0.1, 0.3, 0.6]).

When running hyperparameter tuning, the valid range is[0, 1.0), thedefault range is[0, 0.8], and the scale type isLINEAR.

HIDDEN_UNITS

Syntax

HIDDEN_UNITS = {int_array | HPARAM_CANDIDATES([candidates]) }

Description

The hidden layers of the neural network.

Arguments

An array of integers that represents the architecture of the hidden layers. Ifnot specified, BigQuery ML applies a single hidden layer thatcontains no more than 128 units. The number of units is calculated as[min(128,num_samples / (10 * (num_input_units + num_output_units)))]. The upper bound ofthe rule ensures that the model isn't over fitting.

The number in the middle of the array defines the shape of the latent space. Forexample,hidden_units=[128, 64, 4, 64, 128] defines a four-dimensional latentspace.

The number of layers inhidden_units must be odd, and we recommend that thesequence be symmetrical.

The following example defines a model architecture that uses three hidden layerswith 256, 128, and 64 nodes, respectively.

HIDDEN_UNITS=[256,128,64]

If you are running hyperparameter tuning, then you must use theHPARAM_CANDIDATES keyword and specify an array in the formARRAY<STRUCT<ARRAY<INT64>>> to provide discrete values to use for thehyperparameter. Each struct value in the outer array represents a candidateneural architecture. The array ofINT64 values in each struct represents ahidden layer.

The following example represents a neural architecture search with threecandidates, which include a single layer of 8 neurons, two layers of neuronswith 8 and 16 in sequence, and three layers of neurons with 16, 32 and 64 insequence, respectively.

hidden_units=hparam_candidates([struct([8]),struct([8,16]),struct([16,32,64])])

The valid range for theINT64 arrays is[1, ∞).

TF_VERSION

Syntax

TF_VERSION={'1.15'|'2.8.0'}

Description

Specifies the TensorFlow version for model training. The defaultvalue is1.15.

SetTF_VERSION to2.8.0 to use TensorFlow2 with the Keras API.

EARLY_STOP

Syntax

EARLY_STOP={TRUE|FALSE}

Description

Determines whether training should stop after the first iteration in which therelative loss improvement is less than the value specified forMIN_REL_PROGRESS.

Arguments

ABOOL value. The default value isTRUE.

MIN_REL_PROGRESS

Syntax

MIN_REL_PROGRESS =float64_value

Description

The minimum relative loss improvement that is necessary to continue trainingwhenEARLY_STOP is set toTRUE. For example, a value of0.01 specifies that each iteration must reduce the loss by 1% for trainingto continue.

Arguments

AFLOAT64 value. The default value is0.01.

MAX_ITERATIONS

Syntax

MAX_ITERATIONS =int64_value

Description

The maximum number of training iterations, where one iteration representsa single pass of the entire training data.

Arguments

AnINT64 value. The default value is20.

WARM_START

Syntax

WARM_START={TRUE|FALSE}

Description

Determines whether to train a model with new training data, new model options,or both. Unless you explicitly override them, the initial options used to trainthe model are used for the warm start run.

In a warm start run, the iteration numbers are reset to start from zero. Usethe training run or iteration information returned by theML.TRAINING_INFO functionto distinguish the warm start run from the original run.

The values of theMODEL_TYPE, theHIDDEN_UNITS options, and the modelretraining data schema must all remain the same as they were in theprevious training job.

Arguments

ABOOL value. The default value isFALSE.

NUM_TRIALS

Syntax

NUM_TRIALS =int64_value

Description

The maximum number of submodels to train. The tuning stops whenNUM_TRIALSsubmodels are trained, or when the hyperparameter search space is exhausted.You must specify this option in order to use hyperparameter tuning.

Arguments

AnINT64 value between1 and100, inclusive.

The maximum number of trials to run at the same time. If you specify a valuefor this option, you must also specify a value forNUM_TRIALS.

Arguments

AnINT64 value between1 and5, inclusive. The default value is1.

HPARAM_TUNING_ALGORITHM

Syntax

HPARAM_TUNING_ALGORITHM={'VIZIER_DEFAULT'|'RANDOM_SEARCH'|'GRID_SEARCH'}

Description

The algorithm used to tune the hyperparameters. If you specify a valuefor this option, you must also specify a value forNUM_TRIALS.

Arguments

Specify one of the following values:

• VIZIER_DEFAULT: Use the default algorithm inVertex AI Vizier to tune hyperparameters. This algorithm is the mostpowerful algorithm of those offered. It performs a mixture of advancedsearch algorithms, includingBayesian optimizationwithGaussian processes. Italso usestransfer learningto take advantage of previously tuned models. This is the default, and alsothe recommended approach.

• RANDOM_SEARCH: Userandom searchto explore the search space.

• GRID_SEARCH: Usegrid searchto explore the search space. You can only use this algorithm when everyhyperparameter's search space is discrete.

HPARAM_TUNING_OBJECTIVES

Syntax

HPARAM_TUNING_OBJECTIVES={'MEAN_ABSOLUTE_ERROR'|'MEAN_SQUARED_ERROR'|'MEAN_SQUARED_LOG_ERROR'}

Description

The hyperparameter tuning objective for the model; only one objective issupported. If you specify a value for this option, you must also specify avalue forNUM_TRIALS.

Arguments

The possible objectives are a subset of themodel evaluation metricsfor the model type. If you aren't running hyperparameter tuning, or if you areand you don't specify an objective, theMEAN_SQUARED_ERROR objective isused.

KMS_KEY_NAME

Syntax

KMS_KEY_NAME =string_value

Description

The Cloud Key Management Servicecustomer-managed encryption key (CMEK) touse to encrypt the model.

Arguments

ASTRING value containing the fully-qualified name of the CMEK. For example,

'projects/my_project/locations/my_location/keyRings/my_ring/cryptoKeys/my_key'

Internal parameter defaults

BigQuery ML uses the following default values when buildingmodels:

loss_reduction=losses_utils.ReductionV2.SUM_OVER_BATCH_SIZEbatch_norm=False

query_statement

TheASquery_statement clause specifies theGoogleSQL query used to generate the training data. See theGoogleSQL query syntaxpage for the supported SQL syntax of thequery_statement clause.

Hyperparameter tuning

Autoencoder models supporthyperparameter tuning, which you can useto improve model performance for your data. To usehyperparameter tuning, set theNUM_TRIALs option to thenumber of trials that you want to run. BigQuery ML then trains themodel the number of times that you specify, using different hyperparametervalues, and returns the model that performs the best.

Hyperparameter tuning defaults to improving the key performance metric for thegiven model type. You can use theHPARAM_TUNING_OBJECTIVES option to tune fora different metric if you need to.

For more information about the training objectives and hyperparameterssupported for autoencoder models, seeAUTOENCODER.To try a tutorial that walks you through hyperparameter tuning, seeImprove model performance with hyperparameter tuning.

Supported machine learning functions

The following machine learning functions are supported for the Autoencoder model, including:

• ML.EVALUATE for evaluating model metrics.

• ML.FEATURE_INFO for reviewing information about the input features used to train a model.

• ML.PREDICT for dimensionality reduction.

• ML.RECONSTRUCTION_LOSS for anomaly detection and data sanitation purposes.

• ML.TRAINING_INFO for tracking information about the training iterations of a model.

Locations

For information about supported locations, seeLocations for non-remote models.

Example

The following example trains an autoencoder model against the tablemytable.

CREATEMODEL`project_id.mydataset.mymodel`OPTIONS(MODEL_TYPE='AUTOENCODER',ACTIVATION_FN='RELU',BATCH_SIZE=16,DROPOUT=0.1,EARLY_STOP=FALSE,HIDDEN_UNITS=[128,64,8,64,128],LEARN_RATE=0.001,MAX_ITERATIONS=50,OPTIMIZER='ADAGRAD')ASSELECT*FROM`project_id.mydataset.mytable`;

Pricing

Model training does not incur any charges if you are using on-demand pricing. If you are using capacity-based pricing, model training will use reserved slots. All prediction queries are billable, regardless of the pricing model.