The CREATE MODEL statement for importing ONNX models

This document describes theCREATE MODEL statement for importingOpen Neural Network Exchange (ONNX) models intoBigQuery 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. ONNX is an open standard format for representing machinelearning models.

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

CREATE MODEL syntax

{CREATE MODEL |CREATE MODEL IF NOT EXISTS |CREATE OR REPLACE MODEL}model_nameOPTIONS(MODEL_TYPE = 'ONNX', MODEL_PATH =string_value  [, 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='ONNX'

Description

Specifies the model type. This option is required.

MODEL_PATH

Syntax

MODEL_PATH=string_value

Description

Specifies theCloud Storage URIof the ONNX model to import. This option is required.

Arguments

ASTRING value specifying the URI of a Cloud Storage bucket that containsthe model to import.

BigQuery ML imports the model from Cloud Storage by using thecredentials of the user who runs theCREATE MODEL statement.

Examples

MODEL_PATH='gs://bucket/path/to/onnx_model/*'

MODEL_PATH='gs://bucket/path/to/onnx_model/model.onnx'

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'

Supported data types for input and output columns

BigQuery ML supports the ONNXTensor typeas the type for a model input or output column. The following types aren'tsupported:

• Map type
• Opaque type
• Sequence type
• Optional type
• Sparse tensor type

BigQuery ML converts some ONNX model inputand output columns to BigQuery data types, and someONNX tensor element typesaren't supported. Supported data types for input and outputcolumns include the following:

Locations

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

Limitations

Imported ONNX models have the following limitations:

• ONNX models must be in.onnx format.
• You can only use imported ONNX models with theML.PREDICT function.
• Model size is limited to 450 MB.
• AmongONNX value types,only theTensor typeis supported in BigQuery ML.
• BigQuery ML uses theONNX Runtime 1.12.0library to make predictions on ONNX models. CheckONNX Runtime compatibilityfor theONNX versionsupported by the 1.12 library.
• You can only use an imported ONNX model with an object table when youuse capacity-based pricing through reservations. On-demand pricing isn'tsupported.

Example

The following example imports an ONNX model into BigQuery as aBigQuery model. The example assumes that there is an existingONNX model located atgs://bucket/path/to/onnx_model/*.

CREATEMODEL`project_id.mydataset.mymodel`OPTIONS(MODEL_TYPE='ONNX',MODEL_PATH="gs://bucket/path/to/onnx_model/*")

Troubleshooting

Error:ONNX model output 'output_probability' has unsupported ONNX type: ONNX_TYPE_SEQUENCE.

Error:ONNX model output 'output_probability' is a list of dictionaries, which is not supported in BigQuery ML.

Resolution: If you convert the ONNX model from ascikit-learn classifier by usingsklearn-onnx, set theconverter option tozipmap=False orzipmap='columns' in order to not outputa list of dictionaries for the probabilities. A list of dictionaries isconverted to a sequence of map of tensors in ONNX, and BigQuery MLdoesn't support sequences in ONNX. For more information, seeChoose appropriate output of a classifier.

What's next

• Check theONNX tutorial on GitHub forconverters you can use to convert your pre-trained models to ONNX format.