Export models

This page shows you how to export BigQuery ML models. You can export BigQuery ML models to Cloud Storage, and use them for online prediction, or edit them in Python. You can export a BigQuery ML model by:

• Using the Google Cloud console .
• Using the EXPORT MODEL statement.
• Using the bq extract command in the bq command-line tool.
• Submitting an extract job through the API or client libraries.

You can export the following model types:

• AUTOENCODER
• AUTOML_CLASSIFIER
• AUTOML_REGRESSOR
• BOOSTED_TREE_CLASSIFIER
• BOOSTED_TREE_REGRESSOR
• DNN_CLASSIFIER
• DNN_REGRESSOR
• DNN_LINEAR_COMBINED_CLASSIFIER
• DNN_LINEAR_COMBINED_REGRESSOR
• KMEANS
• LINEAR_REG
• LOGISTIC_REG
• MATRIX_FACTORIZATION
• RANDOM_FOREST_CLASSIFIER
• RANDOM_FOREST_REGRESSOR
• TENSORFLOW (imported TensorFlow models)
• PCA
• TRANSFORM_ONLY

Export model formats and samples

The following table shows the export destination formats for each BigQuery ML model type and provides a sample of files that get written in the Cloud Storage bucket.

Export model trained with TRANSFORM

If the model is trained with the TRANSFORM clause , then an additional preprocessing model performs the same logic in the TRANSFORM clause and is saved in the TensorFlow SavedModel format under the subdirectory transform . You can deploy a model trained with the TRANSFORM clause to Vertex AI as well as locally. For more information, see model deployment .

The model doesn't contain the information about the feature engineering performed outside the TRANSFORM clause during training. For example, anything in the SELECT statement . So you would need to manually convert the input data before feeding into the preprocessing model.

Supported data types

When exporting models trained with the TRANSFORM clause , the following data types are supported for feeding into the TRANSFORM clause .

Supported SQL functions

When exporting models trained with the TRANSFORM clause , you can use the following SQL functions inside the TRANSFORM clause .

• Operators
  • + , - , * , / , = , < , > , <= , >= , != , <> , [NOT] BETWEEN , [NOT] IN , IS [NOT] NULL , IS [NOT] TRUE , IS [NOT] FALSE , NOT , AND , OR .
• Conditional expressions
  • CASE expr , CASE , COALESCE , IF , IFNULL , NULLIF .
• Mathematical functions
  • ABS , ACOS , ACOSH , ASINH , ATAN , ATAN2 , ATANH , CBRT , CEIL , CEILING , COS , COSH , COT , COTH , CSC , CSCH , EXP , FLOOR , IS_INF , IS_NAN , LN , LOG , LOG10 , MOD , POW , POWER , SEC , SECH , SIGN , SIN , SINH , SQRT , TAN , TANH .
• Conversion functions
  • CAST AS INT64 , CAST AS FLOAT64 , CAST AS NUMERIC , CAST AS BIGNUMERIC , CAST AS STRING , SAFE_CAST AS INT64 , SAFE_CAST AS FLOAT64
• String functions
  • CONCAT , LEFT , LENGTH , LOWER , REGEXP_REPLACE , RIGHT , SPLIT , SUBSTR , SUBSTRING , TRIM , UPPER .
• Date functions
  • Date , DATE_ADD , DATE_SUB , DATE_DIFF , DATE_TRUNC , EXTRACT , FORMAT_DATE , PARSE_DATE , SAFE.PARSE_DATE .
• Datetime functions
  • DATETIME , DATETIME_ADD , DATETIME_SUB , DATETIME_DIFF , DATETIME_TRUNC , EXTRACT , PARSE_DATETIME , SAFE.PARSE_DATETIME .
• Time functions
  • TIME , TIME_ADD , TIME_SUB , TIME_DIFF , TIME_TRUNC , EXTRACT , FORMAT_TIME , PARSE_TIME , SAFE.PARSE_TIME .
• Timestamp functions
  • TIMESTAMP , TIMESTAMP_ADD , TIMESTAMP_SUB , TIMESTAMP_DIFF , TIMESTAMP_TRUNC , FORMAT_TIMESTAMP , PARSE_TIMESTAMP , SAFE.PARSE_TIMESTAMP , TIMESTAMP_MICROS , TIMESTAMP_MILLIS , TIMESTAMP_SECONDS , EXTRACT , STRING , UNIX_MICROS , UNIX_MILLIS , UNIX_SECONDS .
• Manual preprocessing functions
  • ML.IMPUTER , ML.HASH_BUCKETIZE , ML.LABEL_ENCODER , ML.MULTI_HOT_ENCODER , ML.NGRAMS , ML.ONE_HOT_ENCODER , ML.BUCKETIZE , ML.MAX_ABS_SCALER , ML.MIN_MAX_SCALER , ML.NORMALIZER , ML.QUANTILE_BUCKETIZE , ML.ROBUST_SCALER , ML.STANDARD_SCALER .

Limitations

The following limitations apply when exporting models:

• Model export is not supported if any of the following features were used during training:

  • ARRAY , TIMESTAMP , or GEOGRAPHY feature types were present in the input data.

• Exported models for model types AUTOML_REGRESSOR and AUTOML_CLASSIFIER do not support Vertex AI deployment for online prediction.

• The model size limit is 1 GB for matrix factorization model export. The model size is roughly proportional to num_factors , so you can reduce num_factors during training to shrink the model size if you reach the limit.

• For models trained with the BigQuery ML TRANSFORM clause for manual feature preprocessing , see the data types and functions supported for exporting.

• Models trained with the BigQuery ML TRANSFORM clause before 18 September 2023 must be re-trained before they can be deployed through Model Registry for online prediction.

• During model export, ARRAY<STRUCT<INT64, FLOAT64>> , ARRAY and TIMESTAMP are supported as pre-transformed data, but are not supported as post-transformed data.

Export BigQuery ML models

To export a model, select one of the following:

bq --location= location 
extract \
--destination_format format 
\
--model project_id:dataset.model 
\
gs:// bucket 
/ model_folder 

bq  
extract  
--model  
 \ 
 'mydataset.mymodel' 
  
 \ 
gs://example-bucket/mymodel_folder

bq  
extract  
--model  
 \ 
--destination_format  
ML_XGBOOST_BOOSTER  
 \ 
 'mydataset.mytable' 
  
 \ 
gs://example-bucket/mymodel_folder

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. ExtractJobConfiguration 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Job 
 
 ; 
 import 
  
 com.google.cloud.bigquery. JobInfo 
 
 ; 
 import 
  
 com.google.cloud.bigquery. ModelId 
 
 ; 
 // Sample to extract model to GCS bucket 
 public 
  
 class 
 ExtractModel 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 throws 
  
 InterruptedException 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 projectName 
  
 = 
  
 "bigquery-public-data" 
 ; 
  
 String 
  
 datasetName 
  
 = 
  
 "samples" 
 ; 
  
 String 
  
 modelName 
  
 = 
  
 "model" 
 ; 
  
 String 
  
 bucketName 
  
 = 
  
 "MY-BUCKET-NAME" 
 ; 
  
 String 
  
 destinationUri 
  
 = 
  
 "gs://" 
  
 + 
  
 bucketName 
  
 + 
  
 "/path/to/file" 
 ; 
  
 extractModel 
 ( 
 projectName 
 , 
  
 datasetName 
 , 
  
 modelName 
 , 
  
 destinationUri 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 extractModel 
 ( 
  
 String 
  
 projectName 
 , 
  
 String 
  
 datasetName 
 , 
  
 String 
  
 modelName 
 , 
  
 String 
  
 destinationUri 
 ) 
  
 throws 
  
 InterruptedException 
  
 { 
  
 try 
  
 { 
  
 // Initialize client that will be used to send requests. This client only needs to be created 
  
 // once, and can be reused for multiple requests. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  BigQueryOptions 
 
 . 
 getDefaultInstance 
 (). 
 getService 
 (); 
  
  ModelId 
 
  
 modelId 
  
 = 
  
  ModelId 
 
 . 
 of 
 ( 
 projectName 
 , 
  
 datasetName 
 , 
  
 modelName 
 ); 
  
  ExtractJobConfiguration 
 
  
 extractConfig 
  
 = 
  
  ExtractJobConfiguration 
 
 . 
 newBuilder 
 ( 
 modelId 
 , 
  
 destinationUri 
 ). 
 build 
 (); 
  
  Job 
 
  
 job 
  
 = 
  
 bigquery 
 . 
  create 
 
 ( 
 JobInfo 
 . 
 of 
 ( 
 extractConfig 
 )); 
  
 // Blocks until this job completes its execution, either failing or succeeding. 
  
  Job 
 
  
 completedJob 
  
 = 
  
 job 
 . 
  waitFor 
 
 (); 
  
 if 
  
 ( 
 completedJob 
  
 == 
  
 null 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Job not executed since it no longer exists." 
 ); 
  
 return 
 ; 
  
 } 
  
 else 
  
 if 
  
 ( 
 completedJob 
 . 
 getStatus 
 (). 
 getError 
 () 
  
 != 
  
 null 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
  
 "BigQuery was unable to extract due to an error: \n" 
  
 + 
  
 job 
 . 
 getStatus 
 (). 
 getError 
 ()); 
  
 return 
 ; 
  
 } 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Model extract successful" 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 ex 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Model extraction job was interrupted. \n" 
  
 + 
  
 ex 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

Model deployment

You can deploy the exported model to Vertex AI as well as locally. If the model's TRANSFORM clause contains Date functions, Datetime functions, Time functions or Timestamp functions, you must use bigquery-ml-utils library in the container. The exception is if you are deploying through Model Registry , which does not need exported models or serving containers.

Vertex AI deployment

Local deployment

Prediction output format

This section provides the prediction output format of the exported models for each model type. All exported models support batch prediction; they can handle multiple input rows at a time. For example, there are two input rows in each of the following output format examples.

AUTOENCODER

+------------------------+------------------------+------------------------+
|      LATENT_COL_1      |      LATENT_COL_2      |           ...          |
+------------------------+------------------------+------------------------+
|       [FLOAT]          |         [FLOAT]        |           ...          |
+------------------------+------------------------+------------------------+

+------------------+------------------+------------------+------------------+
|   LATENT_COL_1   |   LATENT_COL_2   |   LATENT_COL_3   |   LATENT_COL_4   |
+------------------------+------------+------------------+------------------+
|    0.21384512    |    0.93457112    |    0.64978097    |    0.00480489    |
+------------------+------------------+------------------+------------------+

AUTOML_CLASSIFIER

+------------------------------------------+
| predictions                              |
+------------------------------------------+
| [{"scores":[FLOAT], "classes":[STRING]}] |
+------------------------------------------+

+---------------------------------------------+
| predictions                                 |
+---------------------------------------------+
| [{"scores":[1, 2], "classes":['a', 'b']},   |
|  {"scores":[3, 0.2], "classes":['a', 'b']}] |
+---------------------------------------------+

AUTOML_REGRESSOR

+-----------------+
| predictions     |
+-----------------+
| [FLOAT]         |
+-----------------+

+-----------------+
| predictions     |
+-----------------+
| [1.8, 2.46]     |
+-----------------+

BOOSTED_TREE_CLASSIFIER and RANDOM_FOREST_CLASSIFIER

+-------------+--------------+-----------------+
| LABEL_PROBS | LABEL_VALUES | PREDICTED_LABEL |
+-------------+--------------+-----------------+
| [FLOAT]     | [STRING]     | STRING          |
+-------------+--------------+-----------------+

+-------------+--------------+-----------------+
| LABEL_PROBS | LABEL_VALUES | PREDICTED_LABEL |
+-------------+--------------+-----------------+
| [0.1, 0.9]  | ['a', 'b']   | ['b']           |
+-------------+--------------+-----------------+
| [0.8, 0.2]  | ['a', 'b']   | ['a']           |
+-------------+--------------+-----------------+

BOOSTED_TREE_REGRESSOR AND RANDOM_FOREST_REGRESSOR

+-----------------+
| predicted_label |
+-----------------+
| FLOAT           |
+-----------------+

+-----------------+
| predicted_label |
+-----------------+
| [1.8]           |
+-----------------+
| [2.46]          |
+-----------------+

DNN_CLASSIFIER

+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| ALL_CLASS_IDS | ALL_CLASSES | CLASS_IDS | CLASSES | LOGISTIC (binary only) | LOGITS | PROBABILITIES |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [INT64]       | [STRING]    | INT64     | STRING  | FLOAT                  | [FLOAT]| [FLOAT]       |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+

+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| ALL_CLASS_IDS | ALL_CLASSES | CLASS_IDS | CLASSES | LOGISTIC (binary only) | LOGITS | PROBABILITIES |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [0, 1]        | ['a', 'b']  | [0]       | ['a']   | [0.36]                 | [-0.53]| [0.64, 0.36]  |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [0, 1]        | ['a', 'b']  | [0]       | ['a']   | [0.2]                  | [-1.38]| [0.8, 0.2]    |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+

DNN_REGRESSOR

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| FLOAT           |
+-----------------+

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| [1.8]           |
+-----------------+
| [2.46]          |
+-----------------+

DNN_LINEAR_COMBINED_CLASSIFIER

+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| ALL_CLASS_IDS | ALL_CLASSES | CLASS_IDS | CLASSES | LOGISTIC (binary only) | LOGITS | PROBABILITIES |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [INT64]       | [STRING]    | INT64     | STRING  | FLOAT                  | [FLOAT]| [FLOAT]       |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+

+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| ALL_CLASS_IDS | ALL_CLASSES | CLASS_IDS | CLASSES | LOGISTIC (binary only) | LOGITS | PROBABILITIES |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [0, 1]        | ['a', 'b']  | [0]       | ['a']   | [0.36]                 | [-0.53]| [0.64, 0.36]  |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+
| [0, 1]        | ['a', 'b']  | [0]       | ['a']   | [0.2]                  | [-1.38]| [0.8, 0.2]    |
+---------------+-------------+-----------+---------+------------------------+--------+---------------+

DNN_LINEAR_COMBINED_REGRESSOR

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| FLOAT           |
+-----------------+

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| [1.8]           |
+-----------------+
| [2.46]          |
+-----------------+

KMEANS

+--------------------+--------------+---------------------+
| CENTROID_DISTANCES | CENTROID_IDS | NEAREST_CENTROID_ID |
+--------------------+--------------+---------------------+
| [FLOAT]            | [INT64]      | INT64               |
+--------------------+--------------+---------------------+

+--------------------+--------------+---------------------+
| CENTROID_DISTANCES | CENTROID_IDS | NEAREST_CENTROID_ID |
+--------------------+--------------+---------------------+
| [1.2, 1.3]         | [1, 2]       | [1]                 |
+--------------------+--------------+---------------------+
| [0.4, 0.1]         | [1, 2]       | [2]                 |
+--------------------+--------------+---------------------+

LINEAR_REG

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| FLOAT           |
+-----------------+

+-----------------+
| PREDICTED_LABEL |
+-----------------+
| [1.8]           |
+-----------------+
| [2.46]          |
+-----------------+

LOGISTIC_REG

+-------------+--------------+-----------------+
| LABEL_PROBS | LABEL_VALUES | PREDICTED_LABEL |
+-------------+--------------+-----------------+
| [FLOAT]     | [STRING]     | STRING          |
+-------------+--------------+-----------------+

+-------------+--------------+-----------------+
| LABEL_PROBS | LABEL_VALUES | PREDICTED_LABEL |
+-------------+--------------+-----------------+
| [0.1, 0.9]  | ['a', 'b']   | ['b']           |
+-------------+--------------+-----------------+
| [0.8, 0.2]  | ['a', 'b']   | ['a']           |
+-------------+--------------+-----------------+

MATRIX_FACTORIZATION

Note: We currently only support taking an input user and output top 50 (predicted_rating, predicted_item) pairs sorted by predicted_rating in descending order.

+--------------------+--------------+
| PREDICTED_RATING | PREDICTED_ITEM |
+------------------+----------------+
| [FLOAT]          | [STRING]       |
+------------------+----------------+

+--------------------+--------------+
| PREDICTED_RATING | PREDICTED_ITEM |
+------------------+----------------+
| [5.5, 1.7]       | ['A', 'B']     |
+------------------+----------------+
| [7.2, 2.7]       | ['B', 'A']     |
+------------------+----------------+

TENSORFLOW (imported)

PCA

+-------------------------+---------------------------------+
| PRINCIPAL_COMPONENT_IDS | PRINCIPAL_COMPONENT_PROJECTIONS |
+-------------------------+---------------------------------+
|       [INT64]           |             [FLOAT]             |
+-------------------------+---------------------------------+

+-------------------------+---------------------------------+
| PRINCIPAL_COMPONENT_IDS | PRINCIPAL_COMPONENT_PROJECTIONS |
+-------------------------+---------------------------------+
|       [1, 2]            |             [1.2, 5.0]          |
+-------------------------+---------------------------------+

TRANSFORM_ONLY

XGBoost model visualization

You can visualize the boosted trees using the plot_tree Python API after model export. For example, you can leverage Colab without installing the dependencies:

1. Export the boosted tree model to a Cloud Storage bucket.
2. Download the model.bst file from the Cloud Storage bucket.
3. In a Colab noteboook , upload the model.bst file to Files .

4. Run the following code in the notebook:

     import 
     
    xgboost 
     
    as 
     
    xgb 
    import 
     
    matplotlib.pyplot 
     
    as 
     
    plt 
    model 
    = 
    xgb 
    . 
    Booster 
    ( 
    model_file 
    = 
    "model.bst" 
    ) 
    num_iterations 
    = 
   < iteration_number 
   > for 
    tree_num 
    in 
    range 
    ( 
    num_iterations 
    ): 
    xgb 
    . 
    plot_tree 
    ( 
    model 
    , 
    num_trees 
    = 
    tree_num 
    ) 
    plt 
    . 
    show 
    
   

This example plots multiple trees (one tree per iteration):

Currently, we don't save feature names in the model, so you will see names such as "f0", "f1", and so on. You can find the corresponding feature names in the assets/model_metadata.json exported file using these names (such as "f0") as indexes.

Required permissions

To export a BigQuery ML model to Cloud Storage, you need permissions to access the BigQuery ML model, permissions to run an extract job, and permissions to write the data to the Cloud Storage bucket.

BigQuery permissions

• At a minimum, to export model, you must be granted bigquery.models.export permissions. The following predefined Identity and Access Management (IAM) roles are granted bigquery.models.export permissions:

  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

• At a minimum, to run an export job , you must be granted bigquery.jobs.create permissions. The following predefined IAM roles are granted bigquery.jobs.create permissions:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Cloud Storage permissions

• To write the data to an existing Cloud Storage bucket, you must be granted storage.objects.create permissions. The following predefined IAM roles are granted storage.objects.create permissions:

  • storage.objectCreator
  • storage.objectAdmin
  • storage.admin

For more information on IAM roles and permissions in BigQuery ML, see Access control .

Move BigQuery data between locations

You cannot change the location of a dataset after it is created, but you can make a copy of the dataset .

Quota policy

For information on extract job quotas, see Extract jobs on the Quotas and limits page.

Pricing

There is no charge for exporting BigQuery ML models, but exports are subject to BigQuery's Quotas and limits . For more information on BigQuery pricing, see the Pricing page.

After the data is exported, you are charged for storing the data in Cloud Storage. For more information on Cloud Storage pricing, see the Cloud Storage Pricing page.

What's next

• Walk through the Export a BigQuery ML model for online prediction tutorial.