Analyze multimodal data in Python with BigQuery DataFrames

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This tutorial shows you how toanalyze multimodal data in aPython notebook by usingBigQuery DataFramesclasses and methods.

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

To upload a notebook already populated with the tasks covered in thistutorial, seeBigFrames Multimodal DataFrame.

Objectives

• Create multimodal DataFrames.
• Combine structured and unstructured data in a DataFrame.
• Transform images.
• Generate text and embeddings based on image data.
• Chunk PDFs for further analysis.

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.
• BigQuery Python UDFs: you incur costs for using BigQuery DataFrames image transformation and chunk PDF methods.
• Cloud Storage: you incur costs for the objects stored in Cloud Storage.
• Vertex AI: you incur costs for calls to Vertex AI models.

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

For more information about, see the following pricing pages:

• BigQuery pricing
• BigQuery Python UDF pricing
• Cloud Storage pricing
• Vertex AI 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 theresourcemanager.projects.create permission.Learn how to grant roles.
   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

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

3. Enable the BigQuery, BigQuery Connection, Cloud Storage, and Vertex AI APIs.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.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)
• Run BigQuery jobs:BigQuery User (roles/bigquery.user)
• Create and call Python UDFs:BigQuery Data Editor (roles/bigquery.dataEditor)
• Create URLs that let you read and modify Cloud Storage objects:BigQuery ObjectRef Admin (roles/bigquery.objectRefAdmin)
• Use notebooks:
  • BigQuery Read Session User (roles/bigquery.readSessionUser)
  • Notebook Runtime User (roles/aiplatform.notebookRuntimeUser)
  • Notebook Runtime User (roles/aiplatform.notebookRuntimeUser)
  • Code Creator (roles/dataform.codeCreator)

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

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Set up

In this section, you create the Cloud Storage bucket, connection, andnotebook used in this tutorial.

Create a bucket

Create a Cloud Storage bucket for storing transformed objects:

1. In the Google Cloud console, go to theBuckets page.

   Go to Buckets

2. Clickadd_boxCreate.

3. On theCreate a bucket page, in theGet started section, enter aglobally unique name that meets thebucket name requirements.

4. ClickCreate.

Create a connection

Create aCloud resource connectionand get the connection's service account. BigQuery uses theconnection to access objects in Cloud Storage.

1. Go to theBigQuery page.

   Go to BigQuery

2. In theExplorer pane, clickaddAdd data.

   TheAdd data dialog opens.

3. In theFilter By pane, in theData Source Type section, selectBusiness Applications.

   Alternatively, in theSearch for data sources field, you can enterVertex AI.

4. In theFeatured data sources section, clickVertex AI.

5. Click theVertex AI Models: BigQuery Federation solution card.

6. In theConnection type list, selectVertex AI remote models, remote functions, BigLake and Spanner (Cloud Resource).

7. In theConnection ID field, typebigframes-default-connection.

8. ClickCreate connection.

9. ClickGo to connection.

10. In theConnection info pane, copy the service account ID for use in alater step.

Grant permissions to the connection's service account

Grant the connection's service account the roles that it needs to accessCloud Storage and Vertex AI.You must grant these roles in the same project you created or selected in theBefore you begin section.

To grant the role, follow these steps:

1. Go to theIAM & Admin page.

   Go to IAM & Admin

2. Clickperson_addGrant access.

3. In theNew principals field, enter the service account ID that youcopied earlier.

4. In theSelect a role field, chooseCloud Storage, and thenselectStorage Object User.

5. ClickAdd another role.

6. In theSelect a role field, selectVertex AI, and then selectVertex AI User.

7. ClickSave.

Create a notebook

Create a notebook where you can run Python code:

1. Go to theBigQuery page.

   Go to BigQuery

2. In the tab bar of the editor pane, click thearrow_drop_downdrop-down arrow next toadd_boxSQL query,and then clickNotebook.

3. In theStart with a template pane, clickClose.

4. ClickConnect> Connect to a runtime.

5. If you have an existing runtime, accept the default settings and clickConnect. If you don't have an existing runtime, selectCreate new Runtime, and then clickConnect.

   It might take several minutes for the runtime to get set up.

Create a multimodal DataFrame

Create a multimodal DataFrame that integrates structured and unstructured databy using thefrom_glob_path methodof theSession class:

1. In the notebook, create a code cell and copy the following code into it:
   importbigframes# Flags to control preview image/video preview sizebigframes.options.display.blob_display_width=300importbigframes.pandasasbpd# Create blob columns from wildcard path.df_image=bpd.from_glob_path("gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*",name="image")# Other ways are: from string uri column# df = bpd.DataFrame({"uri": ["gs://<my_bucket>/<my_file_0>", "gs://<my_bucket>/<my_file_1>"]})# df["blob_col"] = df["uri"].str.to_blob()# From an existing object table# df = bpd.read_gbq_object_table("<my_object_table>", name="blob_col")# Take only the 5 images to deal with. Preview the content of the Mutimodal DataFramedf_image=df_image.head(5)df_image

2. Clickplay_circle_filledRun.

   The final call todf_image returns the images that have been added to theDataFrame. Alternatively, you could call the.display method.

Combine structured and unstructured data in the DataFrame

Combine text and image data in the multimodal DataFrame:

1. In the notebook, create a code cell and copy the following code into it:
   # Combine unstructured data with structured datadf_image["author"]=["alice","bob","bob","alice","bob"]# type: ignoredf_image["content_type"]=df_image["image"].blob.content_type()df_image["size"]=df_image["image"].blob.size()df_image["updated"]=df_image["image"].blob.updated()df_image

2. ClickRunplay_circle_filled.

   The code returns the DataFrame data.

3. In the notebook, create a code cell and copy the following code into it:

   # Filter images and display, you can also display audio and video types. Use width/height parameters to constrain window sizes.df_image[df_image["author"]=="alice"]["image"].blob.display()

4. ClickRunplay_circle_filled.

   The code returns images from the DataFrame where theauthor column value isalice.

Perform image transformations

Transform image data by using the following methods of theSeries.BlobAccessor class:

• image_blur
• image_normalize
• image_resize

The transformed images are written to Cloud Storage.

Transform images:

1. In the notebook, create a code cell and copy the following code into it:
   df_image["blurred"]=df_image["image"].blob.image_blur((20,20),dst=f"{dst_bucket}/image_blur_transformed/",engine="opencv")df_image["resized"]=df_image["image"].blob.image_resize((300,200),dst=f"{dst_bucket}/image_resize_transformed/",engine="opencv")df_image["normalized"]=df_image["image"].blob.image_normalize(alpha=50.0,beta=150.0,norm_type="minmax",dst=f"{dst_bucket}/image_normalize_transformed/",engine="opencv",)# You can also chain functions togetherdf_image["blur_resized"]=df_image["blurred"].blob.image_resize((300,200),dst=f"{dst_bucket}/image_blur_resize_transformed/",engine="opencv")df_image

2. Update all references to{dst_bucket} to refer to thebucket that you created, in the formatgs://mybucket.

3. ClickRunplay_circle_filled.

   The code returns the original images as well as all of their transformations.

Generate text

Generate text from multimodal data by using thepredict methodof theGeminiTextGenerator class:

1. In the notebook, create a code cell and copy the following code into it:
   frombigframes.mlimportllmgemini=llm.GeminiTextGenerator(model_name="gemini-2.0-flash-001")# Deal with first 2 images as exampledf_image=df_image.head(2)# Ask the same question on the imagesdf_image=df_image.head(2)answer=gemini.predict(df_image,prompt=["what item is it?",df_image["image"]])answer[["ml_generate_text_llm_result","image"]]

2. ClickRunplay_circle_filled.

   The code returns the first two images indf_image, along with textgenerated in response to the questionwhat item is it? for both images.

3. In the notebook, create a code cell and copy the following code into it:

   # Ask different questionsdf_image["question"]=[# type: ignore"what item is it?","what color is the picture?",]answer_alt=gemini.predict(df_image,prompt=[df_image["question"],df_image["image"]])answer_alt[["ml_generate_text_llm_result","image"]]

4. ClickRunplay_circle_filled.

   The code returns the first two images indf_image, with textgenerated in response to the questionwhat item is it? for the firstimage, and text generated in response to the questionwhat color is the picture? for the second image.

Generate embeddings

Generate embeddings for multimodal data by using thepredict methodof theMultimodalEmbeddingGenerator class:

1. In the notebook, create a code cell and copy the following code into it:
   # Generate embeddings on imagesembed_model=llm.MultimodalEmbeddingGenerator()embeddings=embed_model.predict(df_image["image"])embeddings

2. ClickRunplay_circle_filled.

   The code returns the embeddings generated by a call to an embedding model.

Chunk PDFs

Chunk PDF objects by using thepdf_chunk methodof theSeries.BlobAccessor class:

1. In the notebook, create a code cell and copy the following code into it:
   # PDF chunkingdf_pdf=bpd.from_glob_path("gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/documents/*",name="pdf")df_pdf["chunked"]=df_pdf["pdf"].blob.pdf_chunk(engine="pypdf")chunked=df_pdf["chunked"].explode()chunked

2. ClickRunplay_circle_filled.

   The code returns the chunked PDF data.

Clean up

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to theManage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then clickDelete.
3. In the dialog, type the project ID, and then clickShut down to delete the project.