# Integrate with BigQuery

Experimental
BigQuery integration is experimental. Functionality and interfaces may change without notice, and it is not recommended for production use.

info
This feature is not enabled on accounts by default. Contact Technical Support or your Customer Success representative to enable it.

This guide explains how to integrate the Treasure AI-managed Glue HMS catalog and Iceberg tables with Google BigQuery using BigQuery Omni and an AWS Glue federated dataset.

## Overview

After provisioning Iceberg catalog resources for your Treasure AI account, you can expose the Glue HMS catalog to BigQuery as a federated dataset. This allows you to query Treasure AI-managed Iceberg tables directly from BigQuery without copying data.

The integration uses:

- A **BigQuery Omni AWS connection** to access the Glue HMS catalog and the S3 bucket via the Treasure AI reader IAM role
- A **federated dataset** that exposes the Treasure AI Glue database and its Iceberg tables in BigQuery


Unlike an IAM-role-based service, BigQuery authenticates to AWS with a **BigQuery Google identity** using AWS web identity federation (`sts:AssumeRoleWithWebIdentity`). Because of this, a single AWS connection provides both Glue catalog and S3 read access — you do not register separate service or storage credentials.

## Prerequisites

- Iceberg catalog resources provisioned and `active` for your account (see [Provision Resources](/ja/products/customer-data-platform/composable-publish/resource-provisioning))
- A Google Cloud project with BigQuery enabled, in a region where [BigQuery Omni supports the Treasure AI `aws_region`](https://docs.cloud.google.com/bigquery/docs/omni-introduction#locations)


### Authentication

For authentication details and API endpoints by region, see [Use Iceberg Catalog Management API](/ja/products/customer-data-platform/composable-publish/use-iceberg-catalog-api).

### Required Information

Retrieve these values from the [Get Resource Status](/ja/products/customer-data-platform/composable-publish/resource-provisioning#get-resource-status) endpoint:

```sh
curl "https://api-iceberg-mng.us01.treasuredata.com/v1/iceberg/catalog/resources" \
  -H "Authorization: TD1 <admin_api_key>" \
  -H "Accept: application/json"
```

| Response Field | Used As | Example |
|  --- | --- | --- |
| `aws_region` | Glue Region and BigQuery Omni location (`aws-<region>`) | `us-east-1` |
| `aws_account_id` | AWS Account ID in the Glue database ARN | `123456789012` |
| `iam_role_arn` | AWS role ID for the BigQuery connection | `arn:aws:iam::123456789012:role/zcpo-hms-tenant-us01_td10000` |
| `db_name` | Glue database name in the federated dataset source | `td10000_us01_export` |


The BigQuery Omni location is derived from `aws_region` by prefixing `aws-` — for example, `us-east-1` becomes `aws-us-east-1`. Use this same location for both the connection and the federated dataset.

## Setup

### Step 1 (BigQuery): Create connections

Create a **BigLake on AWS (via BigQuery Omni)** connection that uses the Treasure AI reader IAM role. Follow the **Create connections** section in [Connect to Amazon S3](https://docs.cloud.google.com/bigquery/docs/omni-aws-create-connection).

- For **AWS role ID**, use the `iam_role_arn` from the Treasure AI resource response.
- Set the connection **location** to the BigQuery Omni location that matches `aws_region` (for example, `aws-us-east-1`).


After creating the connection, open its **Connection info** and record the **BigQuery Google identity**. You will use it in the next step.

### Step 2 (Treasure AI): Update Trust Policy of IAM role for reading tables

Update the trust policy of the IAM role for reading tables so BigQuery can assume it via web identity federation.
This step corresponds to [Add a trust relationship to the AWS role](https://docs.cloud.google.com/bigquery/docs/omni-aws-create-connection#add-trust-policy) in the BigQuery documentation.

Call the trust policy update endpoint with `service` set to `bigquery` and the BigQuery Google identity from Step 1 as the `external_id`:

```sh
curl -X POST "https://api-iceberg-mng.us01.treasuredata.com/v1/iceberg/catalog/resources/reader_role/trust_policy" \
  -H "Authorization: TD1 <admin_api_key>" \
  -H "Accept: application/json" \
  -H "Content-Type: application/json" \
  -d '{
    "entries": [
      {
        "service": "bigquery",
        "external_id": "<bigquery_google_identity>"
      }
    ]
  }'
```

The `external_id` is matched against the `accounts.google.com:sub` condition in the IAM role trust policy, so only the specified BigQuery connection can assume the role.
This value is generated by BigQuery and is only known after creating the connection.

For BigQuery, `iam_principal_arns` is not used and must be omitted — BigQuery assumes the role through the fixed `accounts.google.com` federated principal, not an IAM role ARN.

Important
The federated dataset will not work until this step is completed. Poll the [Get Resource Status](/ja/products/customer-data-platform/composable-publish/resource-provisioning#get-resource-status) endpoint and wait for the status to return to `active` before proceeding.

### Step 3 (BigQuery): Create a federated dataset

Create a federated dataset that points at the Treasure AI Glue database. Follow: [Create a federated dataset for AWS Glue](https://docs.cloud.google.com/bigquery/docs/glue-federated-datasets).

Run the following in BigQuery, substituting the values from the Treasure AI resource response:

```sql
CREATE EXTERNAL SCHEMA my_dataset
WITH CONNECTION `<project_id>.aws-<aws_region>.<connection_name>`
  OPTIONS (
    external_source = 'aws-glue://arn:aws:glue:<aws_region>:<aws_account_id>:database/<db_name>',
    location = 'aws-<aws_region>');
```

- Use the connection created in Step 1 for the `WITH CONNECTION` clause.
- The dataset `location` must match the connection location (for example, `aws-us-east-1`) and cannot be changed after creation.
- Treasure AI's reader role already grants the Glue (`glue:GetDatabase`, `glue:GetTable`, `glue:GetTables`, `glue:GetPartitions`) and S3 read permissions that the federated dataset requires. No additional AWS permissions are needed.


## Validation

After creating the federated dataset, it appears in the BigQuery Explorer, and the Iceberg tables from the Treasure AI Glue database are exposed as tables you can query.

## Troubleshooting

| Symptom | Possible Cause |
|  --- | --- |
| Access denied when creating or querying | BigQuery Google identity mismatch — verify the `external_id` registered in Step 2 |
| Region or location mismatch error | The connection and dataset location must match the `aws_region` (`aws-<region>`) |
| Tables do not appear in the federated dataset | Confirm the resource status returned to `active` after Step 2 and that `db_name` is correct |


## Important Notes

- The trust policy update in Step 2 is a full replacement. If you need to add another integration later, include all existing entries along with the new one in the request.
- The reader IAM role is read-only. BigQuery can query tables but cannot modify data in the Treasure AI-managed S3 bucket.
- Databases created via the [Database Management API](/ja/products/customer-data-platform/composable-publish/database-management) are automatically accessible as federated datasets without additional configuration.