Object tables in BigQuery

An object table lists the files in a Cloud Storage bucket as rows, with their metadata, so you can filter them in SQL and send their contents to AI and ML functions. Access to a row means access to the file, inference needs Enterprise edition, and the table holds at most 300 million objects.

An object table is a read-only table over unstructured files in Cloud Storage: images, PDFs, audio, anything. Each row is one file, and the columns are the file’s metadata. It makes a bucket queryable with SQL (which files, how big, uploaded when) and gives AI and ML functions a way to read the file contents, so you can classify images, extract text from documents or transcribe audio in a query, and join the results with your other tables.

Creating one

CREATE EXTERNAL TABLE mydataset.product_images
WITH CONNECTION `myproject.us.lake-connection`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://my-bucket/images/*'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

object_metadata = 'SIMPLE' is what makes it an object table. Like a BigLake table, it reads the bucket through a connection’s service account, so users need access to the table, not the bucket.

The columns

There is also a hidden data pseudocolumn with the raw file bytes. You can’t select it; functions like ML.DECODE_IMAGE(data) read it. Querying the metadata columns is ordinary SQL:

SELECT content_type, COUNT(*) AS files, ROUND(SUM(size) / POW(1024, 3), 1) AS gib
FROM mydataset.product_images
GROUP BY content_type
ORDER BY gib DESC;

Working with the contents

The common ways to process the files from SQL:

• AI.GENERATE_TEXT, AI.GENERATE and related functions send each file to a Gemini model with a prompt, for example to describe an image or extract fields from a PDF. See Generative AI functions.
• AI.GENERATE_EMBEDDING turns files into embeddings for vector search.
• ML.ANNOTATE_IMAGE, ML.PROCESS_DOCUMENT and ML.TRANSCRIBE call Cloud Vision, Document AI and Speech-to-Text.
• ML.PREDICT with an imported TensorFlow, TensorFlow Lite or ONNX model runs your own model on images.
• A remote function runs any code you deploy on each file.

CREATE TABLE mydataset.image_labels AS
SELECT uri, content_type, vision_feature
FROM ML.PREDICT(
  MODEL mydataset.vision_model,
  (SELECT uri, content_type, ML.DECODE_IMAGE(data) AS vision_input
   FROM mydataset.product_images
   WHERE content_type = 'image/jpeg')
);

Filter the object table before calling a model: the model is billed per file it processes.

EXTERNAL_OBJECT_TRANSFORM(TABLE mydataset.product_images, ['SIGNED_URL']) adds a signed URL per row, to show the files in an application. The URLs expire after 6 hours.

Access to a row is access to the file

This is the security point to understand. Users can’t read the bucket, but anyone who can read a row of the object table can generate a signed URL for that file and open it. So row access is file access. Row-level security on an object table limits which files each user sees, for example by folder, upload date or a custom metadata tag:

CREATE ROW ACCESS POLICY no_pii
ON mydataset.product_images
GRANT TO ('group:[email protected]')
FILTER USING (NOT EXISTS (
  SELECT 1 FROM UNNEST(metadata) AS m WHERE m.name = 'contains_pii' AND m.value = 'true'
));

Freshness and cost

With metadata caching, the object list is refreshed in the background, usually within 30 to 60 minutes, so new files appear with that delay. BQ.REFRESH_EXTERNAL_METADATA_CACHE refreshes it on demand. Refreshes are billed like queries (bytes on on-demand, slots on capacity pricing); a BACKGROUND reservation keeps them from competing with user queries.

Querying the table is billed like any query. Model and AI calls are billed by the service that runs them (Vertex AI, Cloud Vision and so on), per file, on top of the query.

Limits

• Read-only: no DML, no ALTER of the data.
• At most 300 million objects per table.
• ML.PREDICT inference on object tables, and the image functions ML.RESIZE_IMAGE, ML.CONVERT_COLOR_SPACE and ML.CONVERT_IMAGE_TYPE, need Enterprise or Enterprise Plus edition; they don’t run on on-demand pricing or Standard edition.
• UNION ALL of an empty and a non-empty object table can fail.
• Cloud Storage only; not Amazon S3 or Azure.