BigQuery ObjectRef: A Practical Guide to Multimodal Data Analytics

A Practical Guide to Multimodal Data Analytics with BigQuery

This article provides a comprehensive guide to leveraging BigQuery's ObjectRef feature for multimodal data analytics, enabling the unified analysis of structured and unstructured data using SQL and Python.

Introduction

Modern enterprises deal with a mix of structured data (tables) and unstructured data (images, audio, documents). Traditionally, analyzing these together has been complex due to data silos and the need for separate tools. BigQuery's ObjectRef aims to solve this by allowing direct references to unstructured data stored in Google Cloud Storage (GCS) within BigQuery tables.

Part 1: ObjectRef - The Key to Unifying Multimodal Data

ObjectRef Structure and Function

ObjectRef is a BigQuery STRUCT data type that points to unstructured data in GCS without storing the data itself. Its key fields include:

uri: GCS path to the object.
authorizer: Specifies how BigQuery securely accesses GCS objects.
version: Locks the reference to a specific GCS object version for reproducibility.
details: A JSON element containing GCS metadata like contentType or size.

A JSON example of an ObjectRef:

{
  "uri": "gs://cymbal-support/calls/ticket-83729.mp3",
  "version": 1742790939895861,
  "authorizer": "my-project.us-central1.conn",
  "details": {
    "gcs_metadata": {
      "content_type": "audio/mp3",
      "md5_hash": "a1b2c3d5g5f67890a1b2c3d4e5e47890",
      "size": 5120000,
      "updated": 1742790939903000
    }
  }
}

This structure enables BigQuery to locate, access, and understand unstructured files, forming the basis for multimodal tables.

Create Multimodal Tables

A multimodal table is a BigQuery table with one or more ObjectRef columns. These can be created by:

Creating an ObjectRef Column with Object Tables: Use CREATE EXTERNAL TABLE with uris pointing to GCS files. This creates a table with a ref column of type ObjectRef. This is efficient for many files.
```
CREATE EXTERNAL TABLE `project_id.dataset_id.my_table`
WITH CONNECTION `project_id.region.connection_id`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket-name/path/*.jpg']
);
```

Programmatically Constructing ObjectRefs: Use the OBJ.MAKE_REF() function, often wrapped in OBJ.FETCH_METADATA() to populate the details field. This is useful for dynamic workflows.

SELECT
OBJ.FETCH_METADATA(OBJ.MAKE_REF('gs://my-bucket/path/image.jpg', 'us-central1.conn')) AS customer_image_ref,
OBJ.FETCH_METADATA(OBJ.MAKE_REF('gs://my-bucket/path/call.mp3', 'us-central1.conn')) AS support_call_ref

Part 2: Multimodal Tables with SQL

Secure and Governed Access

ObjectRef integrates with BigQuery's security features. Access to GCS objects is delegated through a BigQuery connection resource specified in the ObjectRef's authorizer field, not granted directly to users. This allows for:

Column-level security: Restrict access to entire ObjectRef columns.
Row-level security: Filter which rows users can see based on defined policies.
Multiple Authorizers: Use different connections for different GCS buckets or permissions, managing access centrally.

AI-Driven Inference with SQL

The AI.GENERATE_TABLE function applies generative AI models to multimodal data for enrichment. For example, to create SEO keywords and product descriptions from product names and images:

SELECT
  product_id,
  seo_keywords,
  product_description
FROM AI.GENERATE_TABLE(
  MODEL `dataset_id.gemini`, (
    SELECT (
		'For the image of a pet product, generate:'
        '1) 5 SEO search keywords and' 
        '2) A one sentence product description',
        product_name, image_ref) AS prompt,
        product_id
    FROM `dataset_id.products_multimodal_table`
  ),
  STRUCT(
     "seo_keywords ARRAY, product_description STRING" AS output_schema
  )
);

This automates tasks and produces ready-to-use data.

Part 3: Multimodal DataFrames with Python

Bridging Python and BigQuery for Multimodal Inference

BigQuery DataFrames (using the bigframes library) offers a pandas-like API to interact with BigQuery data without pulling it into local memory. This extends to multimodal analytics, creating multimodal dataframes that combine structured data with references to unstructured files (blob columns).

Create Multimodal DataFrames

Blob columns, the Python equivalent of ObjectRef, can be created in three ways:

From a GCS location: Use bpd.from_glob_path() to scan a GCS bucket, creating a temporary object table as a DataFrame.

import bigframes.pandas as bpd
df = bpd.from_glob_path("gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*", name="image")

From an existing object table: Use bpd.read_gbq_object_table().
From a DataFrame with a URI field: Use .str.to_blob() on a URI column.
```
df["blob_col"] = df["uri"].str.to_blob()
```

AI-Driven Inference with Python

Multimodal dataframes enable AI-driven analysis on unstructured data. The process involves:

Creating a multimodal DataFrame with a blob column.
Loading a BigQuery ML model into a BigFrames model object.
Calling .predict() on the model with the DataFrame.

Example using Gemini to describe pet product images:

import bigframes.pandas as bpd
from bigframes.ml import llm

df = bpd.from_glob_path("gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*", name="image_blob")
df = df.head(2) # Limit for example

model = llm.GeminiTextGenerator(model_name="gemini-2.5-flash-preview-05-20")

answer = model.predict(df, prompt=["Write a 1 sentence product description for the image.", df["image_blob"]])

print(answer[["ml_generate_text_llm_result", "image_blob"]])

This integrates BigQuery ML functions like ML.GENERATE_TEXT for scalable multimodal analysis.

Going Deeper with Multimodal DataFrames

The bigframes library offers tools for unstructured data processing:

Built-in Transformations: Image operations like blur, normalize, and resize.
Embedding Generation: Create semantic search capabilities using Vertex AI models.
PDF Chunking: Streamline RAG workflows by segmenting document content.

These features position BigQuery DataFrames as an end-to-end solution for multimodal analytics.

Conclusion

BigQuery's multimodal capabilities, through ObjectRef and BigQuery DataFrames, break down data silos, allowing seamless analysis of structured and unstructured data. Whether using SQL or Python, users can now perform sophisticated multimodal analysis with ease.

Resources:

Author: Jeff Nelson, Google Cloud