# 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.

Source: https://docs.querylab.io/bigquery/object-tables/

Updated: 2026-10-06

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

```sql
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](https://docs.querylab.io/bigquery/biglake-and-iceberg/), it reads the bucket through a connection's service account, so users need access to the table, not the bucket.

## The columns

| Column         | Type                         | Contents                                         |
| -------------- | ---------------------------- | ------------------------------------------------ |
| `uri`          | `STRING`                     | `gs://bucket/path/name`                          |
| `generation`   | `INT64`                      | the object version                               |
| `content_type` | `STRING`                     | the MIME type, such as `image/jpeg`              |
| `size`         | `INT64`                      | bytes                                            |
| `md5_hash`     | `STRING`                     | base64-encoded MD5                               |
| `updated`      | `TIMESTAMP`                  | when the object's metadata last changed          |
| `metadata`     | `ARRAY<STRUCT<name, value>>` | the object's custom metadata                     |
| `ref`          | `STRUCT` (`ObjectRef`)       | a reference AI functions use to read the content |

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:

```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](https://docs.querylab.io/bigquery/ai-functions/).
- `AI.GENERATE_EMBEDDING` turns files into embeddings for [vector search](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/remote-functions/) runs any code you deploy on each file.

```sql
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](https://docs.querylab.io/bigquery/row-level-security/) on an object table limits which files each user sees, for example by folder, upload date or a custom metadata tag:

```sql
CREATE ROW ACCESS POLICY no_pii
ON mydataset.product_images
GRANT TO ('group:analysts@example.com')
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.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to object tables](https://cloud.google.com/bigquery/docs/object-table-introduction)

- [Create object tables](https://cloud.google.com/bigquery/docs/object-tables)

- [Built-in table functions, EXTERNAL_OBJECT_TRANSFORM](https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions-built-in#external_object_transform)