The CREATE MODEL statement for importing ONNX models

This document describes the CREATE MODEL statement for importing Open Neural Network Exchange (ONNX) models into BigQuery. ONNX is an open standard format for representing machine learning models.

For information about the supported SQL statements and functions for each model type, see End-to-end user journey for each model.

CREATE MODEL syntax

{CREATE MODEL | CREATE MODEL IF NOT EXISTS | CREATE OR REPLACE MODEL}
model_name
OPTIONS(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 name exists, CREATE MODEL returns an error.

CREATE MODEL IF NOT EXISTS

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

CREATE OR REPLACE MODEL

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

model_name

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

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

model_name is not case-sensitive.

If you don't have a default project configured, then you must prepend the project 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 the Cloud Storage URI of the ONNX model to import. This option is required.

Arguments

A STRING value specifying the URI of a Cloud Storage bucket that contains the model to import.

BigQuery ML imports the model from Cloud Storage by using the credentials of the user who runs the CREATE 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 Service customer-managed encryption key (CMEK) to use to encrypt the model.

Arguments

A STRING 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 ONNX Tensor type as the type for a model input or output column. The following types aren't supported:

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

ONNX Tensor type Supported BigQuery type
ONNX_TENSOR_ELEMENT_DATA_TYPE_INT8
ONNX_TENSOR_ELEMENT_DATA_TYPE_INT16
ONNX_TENSOR_ELEMENT_DATA_TYPE_INT32
ONNX_TENSOR_ELEMENT_DATA_TYPE_INT64
ONNX_TENSOR_ELEMENT_DATA_TYPE_UINT8
ONNX_TENSOR_ELEMENT_DATA_TYPE_UINT16
ONNX_TENSOR_ELEMENT_DATA_TYPE_UINT32
ONNX_TENSOR_ELEMENT_DATA_TYPE_UINT64		 Supported INT64
ONNX_TENSOR_ELEMENT_DATA_TYPE_FLOAT16
ONNX_TENSOR_ELEMENT_DATA_TYPE_BFLOAT16
ONNX_TENSOR_ELEMENT_DATA_TYPE_FLOAT
ONNX_TENSOR_ELEMENT_DATA_TYPE_DOUBLE		 Supported FLOAT64
ONNX_TENSOR_ELEMENT_DATA_TYPE_BOOL Supported BOOL
ONNX_TENSOR_ELEMENT_DATA_TYPE_STRING Supported STRING
ONNX_TENSOR_ELEMENT_DATA_TYPE_COMPLEX64
ONNX_TENSOR_ELEMENT_DATA_TYPE_COMPLEX128		 Unsupported N/A

Limitations

Imported ONNX models have the following limitations:

  • ONNX models must be in .onnx format.
  • You can only use imported ONNX models with the ML.PREDICT function.
  • Model size is limited to 450 MB.
  • Among ONNX value types, only the Tensor type is supported in BigQuery ML.
  • BigQuery ML uses the ONNX Runtime 1.12.0 library to make predictions on ONNX models. Check ONNX Runtime compatibility for the ONNX version supported by the 1.12 library.
  • You can only use an imported ONNX model with an object table when you use capacity-based pricing through reservations. On-demand pricing isn't supported.

Example

The following example imports an ONNX model into BigQuery as a BigQuery model. The example assumes that there is an existing ONNX model located at gs://bucket/path/to/onnx_model/*.

CREATE MODEL `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 a scikit-learn classifier by using sklearn-onnx, set the converter option to zipmap=False or zipmap='columns' in order to not output a list of dictionaries for the probabilities. A list of dictionaries is converted to a sequence of map of tensors in ONNX, and BigQuery ML doesn't support sequences in ONNX. For more information, see Choose appropriate output of a classifier.

What's next

  • Check the ONNX tutorial on GitHub for converters you can use to convert your pre-trained models to ONNX format.