Analyze multimodal data with SQL and BigQuery DataFrames

This tutorial shows you how to analyze multimodal data by using SQL queries and BigQuery DataFrames .

This tutorial uses the product catalog from the public Cymbal pet store dataset.

Objectives

• Use ObjectRef values to store image data alongside structured data in a BigQuery standard table .
• Enrich your data with image descriptions, keywords, and animal types, and subcategories by using the AI.GENERATE function .
• Generate embeddings based on image data by using the AI.EMBED function .
• Find similar images by using the VECTOR_SEARCH function.
• Summarize user manuals by processing ordered multimodal data using arrays of ObjectRef values.

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery : you incur costs for the data that you process in BigQuery.
• Cloud Storage : you incur costs for the objects stored in Cloud Storage.
• Gemini Enterprise Agent Platform : you incur costs for calls to Agent Platform models.

To generate a cost estimate based on your projected usage, use the pricing calculator .

For more information about, see the following pricing pages:

• BigQuery pricing
• Cloud Storage pricing
• Agent Platform pricing

Before you begin

1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Roles required to select or create a project

   • Select a project : Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
   • Create a project : To create a project, you need the Project Creator role ( roles/resourcemanager.projectCreator ), which contains the resourcemanager.projects.create permission. Learn how to grant roles .

   Go to project selector

2. Verify that billing is enabled for your Google Cloud project .

3. Enable the BigQuery, BigQuery Connection, Cloud Storage, and Agent Platform API APIs.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role ( roles/serviceusage.serviceUsageAdmin ), which contains the serviceusage.services.enable permission. Learn how to grant roles .

   Enable the APIs

Required roles

To get the permissions that you need to complete this tutorial, ask your administrator to grant you the following IAM roles:

• Create a connection: BigQuery Connection Admin ( roles/bigquery.connectionAdmin )
• Grant permissions to the connection's service account: Project IAM Admin ( roles/resourcemanager.projectIamAdmin )
• Create a Cloud Storage bucket: Storage Admin ( roles/storage.admin )
• Create datasets, models, UDFs, and tables, and run BigQuery jobs: BigQuery Admin ( roles/bigquery.admin )
• Create URLs that let you read and modify Cloud Storage objects: BigQuery ObjectRef Admin ( roles/bigquery.objectRefAdmin )

For more information about granting roles, see Manage access to projects, folders, and organizations .

You might also be able to get the required permissions through custom roles or other predefined roles .

Set up

In this section, you create the dataset, connection, tables, and models used in this tutorial.

Create a dataset

Create a BigQuery dataset to contain the objects you create in this tutorial:

1. In the Google Cloud console, go to the BigQuerypage.

   Go to BigQuery

2. In the left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left paneto open the pane.

3. In the Explorerpane, select your project.

4. Click more_vert View actions, and then click Create dataset. The Create datasetpane opens.

5. For Dataset ID, type cymbal_pets .

6. Click Create dataset.

Create a connection

Create a Cloud resource connection and get the connection's service account. BigQuery uses the connection to access objects in Cloud Storage:

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the left pane, click explore Explorer:

   

3. In the Explorerpane, click add Add data.

   The Add datadialog opens.

4. In the Filter Bypane, in the Data Source Typesection, select Business Applications.

   Alternatively, in the Search for data sourcesfield, you can enter Vertex AI .

5. In the Featured data sourcessection, click Vertex AI.

6. Click the Vertex AI Models: BigQuery Federationsolution card.

7. In the Connection typelist, select Vertex AI remote models, remote functions, BigLake and Spanner (Cloud Resource).

8. In the Connection IDfield, type cymbal_conn .

9. Click Create connection.

10. Click Go to connection.

11. In the Connection infopane, copy the service account ID for use in a following step.

Grant permissions to the connection's service account

Grant the connection's service account the appropriate roles to access other services. You must grant these roles in the same project you created or selected in the Before you begin section. Granting the roles in a different project results in the error bqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource .

Create a bucket

Create a Cloud Storage bucket for storing transformed objects:

1. Go to the Bucketspage.

   Go to Buckets

2. Click add_box Create.

3. On the Create a bucketpage, in the Get startedsection, enter a globally unique name that meets the bucket name requirements .

4. Click Create.

Grant permissions on the Cloud Storage bucket

Give the service account access to use objects in the bucket you created:

1. Go to the Bucketspage.

   Go to Buckets

2. Click the name of the bucket you created.

3. Click Permissions.

4. Click person_add Grant access. The Grant accessdialog opens.

5. In the New principalsfield, enter the service account ID that you copied earlier.

6. In the Select a rolefield, choose Cloud Storage, and then select Storage Object User.

7. Click Save.

Grant permissions on to use Agent Platform models

Give the service account access to use Agent Platform models:

1. Go to the IAM & Adminpage.

   Go to IAM & Admin

2. Click person_add Grant access. The Grant accessdialog opens.

3. In the New principalsfield, enter the service account ID that you copied earlier.

4. In the Select a rolefield, enter Agent Platform User.

5. Click Save.

Create the tables of example data

Create tables to store the Cymbal pets product information.

Create the products table

Create a standard table that contains the Cymbal pets product information:

1. In the Google Cloud console, go to the BigQuerypage.

   Go to BigQuery

2. Run the following to create the products table:

   SQL

    LOAD 
     
    DATA 
     
    OVERWRITE 
     
    cymbal_pets 
    . 
    products 
    FROM 
     
    FILES 
    ( 
     
    format 
     
    = 
     
    'avro' 
    , 
     
    uris 
     
    = 
     
    [ 
     
    'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro' 
    ] 
    ); 
   

   BigQuery DataFrames

   Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames . For more information, see the BigQuery DataFrames reference documentation .

   To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment .

     import 
     
    bigframes.bigquery 
     
    as 
     
    bbq 
    import 
     
    bigframes.pandas 
     
    as 
     
    bpd 
    bbq 
    . 
    load_data 
    ( 
    "cymbal_pets.products" 
    , 
    write_disposition 
    = 
    "OVERWRITE" 
    , 
    from_files_options 
    = 
    { 
    "format" 
    : 
    "avro" 
    , 
    "uris" 
    : 
    [ 
    "gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro" 
    ], 
    }, 
    ) 
    
   

Create the product_images table

Create an object table that contains the Cymbal pets product images:

• Run the following to create the product_images table:

  SQL

   CREATE 
    
   OR 
    
   REPLACE 
    
   EXTERNAL 
    
   TABLE 
    
   cymbal_pets 
   . 
   product_images 
    
   WITH 
    
   CONNECTION 
    
   `us.cymbal_conn` 
    
   OPTIONS 
    
   ( 
    
   object_metadata 
    
   = 
    
   'SIMPLE' 
   , 
    
   uris 
    
   = 
    
   [ 
   'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png' 
   ] 
   , 
    
   max_staleness 
    
   = 
    
   INTERVAL 
    
   30 
    
   MINUTE 
   , 
    
   metadata_cache_mode 
    
   = 
    
   AUTOMATIC 
   ); 
  

  BigQuery DataFrames

  Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames . For more information, see the BigQuery DataFrames reference documentation .

  To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment .

    bbq 
   . 
   create_external_table 
   ( 
   "cymbal_pets.product_images" 
   , 
   replace 
   = 
   True 
   , 
   connection_name 
   = 
   "us.cymbal_conn" 
   , 
   options 
   = 
   { 
   "object_metadata" 
   : 
   "SIMPLE" 
   , 
   "uris" 
   : 
   [ 
   "gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png" 
   ], 
   }, 
   ) 
   
  

Create models

The SQL instructions in this tutorial show how to call AI functions that don't require you to create a model. If you're following the BigQuery DataFrames instructions, select that option to create remote models that represent a Gemini model and a multimodal embedding model.

  gemini_model 
 = 
 bbq 
 . 
 ml 
 . 
 create_model 
 ( 
 "cymbal_pets.gemini" 
 , 
 replace 
 = 
 True 
 , 
 connection_name 
 = 
 "us.cymbal_conn" 
 , 
 options 
 = 
 { 
 "endpoint" 
 : 
 "gemini-2.5-flash" 
 }, 
 ) 
 

  embedding_model 
 = 
 bbq 
 . 
 ml 
 . 
 create_model 
 ( 
 "cymbal_pets.embedding_model" 
 , 
 replace 
 = 
 True 
 , 
 connection_name 
 = 
 "us.cymbal_conn" 
 , 
 options 
 = 
 { 
 "endpoint" 
 : 
 "multimodalembedding@001" 
 }, 
 )