Create Amazon S3 BigLake external tables

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, select the project where you want to create the dataset.
3. Expand the more_vert View actions option and click Create dataset.
4. On the Create dataset page, specify the following details:
1. For Dataset ID enter a unique dataset name.
2. For Data location choose a supported region.
3. Optional: To delete tables automatically, select the Enable table expiration checkbox and set the Default maximum table age in days. Data in Amazon S3 is not deleted when the table expires.
4. If you want to use default collation, expand the Advanced options section and then select the Enable default collation option.
5. Click Create dataset.

SQL

Use the CREATE SCHEMA DDL statement. The following example create a dataset in the aws-us-east-1 region:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE SCHEMA mydataset
   OPTIONS (
     location = 'aws-us-east-1');

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

bq

In a command-line environment, create a dataset using the bq mk command:

bq --location=LOCATION mk \
    --dataset \
PROJECT_ID:DATASET_NAME

The --project_id parameter overrides the default project.

Replace the following:

• LOCATION: the location of your dataset

  For information about supported regions, see Locations. After you create a dataset, you can't change its location. You can set a default value for the location by using the .bigqueryrc file.

• PROJECT_ID: your project ID

• DATASET_NAME: the name of the dataset that you want to create

  To create a dataset in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET_NAME.

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import com.google.cloud.bigquery.DatasetInfo;

// Sample to create a aws dataset
public class CreateDatasetAws {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    // Note: As of now location only supports aws-us-east-1
    String location = "aws-us-east-1";
    createDatasetAws(projectId, datasetName, location);
  }

  public static void createDatasetAws(String projectId, String datasetName, String location) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      DatasetInfo datasetInfo =
          DatasetInfo.newBuilder(projectId, datasetName).setLocation(location).build();

      Dataset dataset = bigquery.create(datasetInfo);
      System.out.println(
          "Aws dataset created successfully :" + dataset.getDatasetId().getDataset());
    } catch (BigQueryException e) {
      System.out.println("Aws dataset was not created. \n" + e.toString());
    }
  }
}

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project, and then select a dataset.

3. In the Dataset info section, click add_box Create table.

4. On the Create table page, in the Source section, do the following:

   1. For Create table from, select Amazon S3.
   2. For Select S3 path, enter a URI pointing to the Amazon S3 data in the format s3://BUCKET_NAME/PATH. Replace BUCKET_NAME with the name of the Amazon S3 bucket; the bucket's region should be the same as the dataset's region. Replace PATH with the path that you would like to write the exported file to; it can contain one wildcard *.
   3. For File format, select the data format in Amazon S3. Supported formats are AVRO, CSV, DELTA_LAKE, ICEBERG, JSONL, ORC, and PARQUET.

5. In the Destination section, specify the following details:

   1. For Dataset, choose the appropriate dataset.
   2. In the Table field, enter the name of the table.
   3. Verify that Table type is set to External table.
   4. For Connection ID, choose the appropriate connection ID from the drop-down. For information about connections, see Connect to Amazon S3.

6. In the Schema section, you can either enable schema auto-detection or manually specify a schema if you have a source file. If you don't have a source file, you must manually specify a schema.

   • To enable schema auto-detection, select the Auto-detect option.

   • To manually specify a schema, leave the Auto-detect option unchecked. Enable Edit as text and enter the table schema as a JSON array.

7. Click Create table.

SQL

To create a BigLake table, use the CREATE EXTERNAL TABLE statement with the WITH CONNECTION clause:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE EXTERNAL TABLE DATASET_NAME.TABLE_NAME
     WITH CONNECTION `AWS_LOCATION.CONNECTION_NAME`
     OPTIONS (
       format = "DATA_FORMAT",
       uris = ["S3_URI"],
       max_staleness = STALENESS_INTERVAL,
       metadata_cache_mode = 'CACHE_MODE');

   Replace the following:

   • DATASET_NAME: the name of the dataset you created
   • TABLE_NAME: the name you want to give to this table
   • AWS_LOCATION: an AWS location in Google Cloud (for example, `aws-us-east-1`)
   • CONNECTION_NAME: the name of the connection you created
   • DATA_FORMAT: any of the supported BigQuery federated formats (such as AVRO, CSV, DELTA_LAKE, ICEBERG, or PARQUET (preview))
   • S3_URI: a URI pointing to the Amazon S3 data (for example, s3://bucket/path)
   • STALENESS_INTERVAL: specifies whether cached metadata is used by operations against the BigLake table, and how fresh the cached metadata must be in order for the operation to use it. For more information about metadata caching considerations, see Metadata caching for performance.

     To disable metadata caching, specify 0. This is the default.

     To enable metadata caching, specify an interval literal value between 30 minutes and 7 days. For example, specify INTERVAL 4 HOUR for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the cached metadata is older than that, the operation retrieves metadata from Amazon S3 instead.

   • CACHE_MODE: specifies whether the metadata cache is refreshed automatically or manually. For more information about metadata caching considerations, see Metadata caching for performance.

     Set to AUTOMATIC for the metadata cache to be refreshed at a system-defined interval, usually somewhere between 30 and 60 minutes.

     Set to MANUAL if you want to refresh the metadata cache on a schedule you determine. In this case, you can call the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the cache.

     You must set CACHE_MODE if STALENESS_INTERVAL is set to a value greater than 0.

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

Example:

CREATE EXTERNAL TABLE awsdataset.awstable
  WITH CONNECTION `aws-us-east-1.s3-read-connection`
  OPTIONS (
    format="CSV",
    uris=["s3://s3-bucket/path/file.csv"],
    max_staleness = INTERVAL 1 DAY,
    metadata_cache_mode = 'AUTOMATIC'
);

bq

Create a table definition file:

bq mkdef  \
--source_format=DATA_FORMAT \
--connection_id=AWS_LOCATION.CONNECTION_NAME \
--metadata_cache_mode=CACHE_MODE \
S3_URI > table_def

Replace the following:

• DATA_FORMAT: any of the supported BigQuery federated formats (such as AVRO, CSV, DELTA_LAKE, ICEBERG, or PARQUET).
• S3_URI: a URI pointing to the Amazon S3 data (for example, s3://bucket/path).
• AWS_LOCATION: an AWS location in Google Cloud (for example, aws-us-east-1).

• CONNECTION_NAME: the name of the connection you created.

• CACHE_MODE: specifies whether the metadata cache is refreshed automatically or manually. You only need to include this flag if you also plan to use the --max_staleness flag in the subsequent bq mk command to enable metadata caching. For more information about metadata caching considerations, see Metadata caching for performance.

  Set to AUTOMATIC for the metadata cache to be refreshed at a system-defined interval, usually somewhere between 30 and 60 minutes.

  Set to MANUAL if you want to refresh the metadata cache on a schedule you determine. In this case, you can call the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the cache. You must set CACHE_MODE if STALENESS_INTERVAL is set to a value greater than 0.

Next, create the BigLake table:

bq mk --max_staleness=STALENESS_INTERVAL --external_table_definition=table_def DATASET_NAME.TABLE_NAME

Replace the following:

• STALENESS_INTERVAL: specifies whether cached metadata is used by operations against the BigLake table, and how fresh the cached metadata must be in order for the operation to use it. For more information about metadata caching considerations, see Metadata caching for performance.

  To disable metadata caching, specify 0. This is the default.

  To enable metadata caching, specify an interval literal value between 30 minutes and 7 days. For example, specify INTERVAL 4 HOUR for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the cached metadata is older than that, the operation retrieves metadata from Amazon S3 instead.

• DATASET_NAME: the name of the dataset you created.

• TABLE_NAME: the name you want to give to this table.

For example, the following command creates a new BigLake table, awsdataset.awstable, which can query your Amazon S3 data that's stored at the path s3://s3-bucket/path/file.csv and has a read connection in the location aws-us-east-1:

bq mkdef  \
--autodetect \
--source_format=CSV \
--connection_id=aws-us-east-1.s3-read-connection \
--metadata_cache_mode=AUTOMATIC \
s3://s3-bucket/path/file.csv > table_def

bq mk --max_staleness=INTERVAL "1" HOUR \
--external_table_definition=table_def awsdataset.awstable

API

Call the tables.insert method API method, and create an ExternalDataConfiguration in the Table resource that you pass in.

Specify the schema property or set the autodetect property to true to enable schema auto detection for supported data sources.

Specify the connectionId property to identify the connection to use for connecting to Amazon S3.

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create an external aws table
public class CreateExternalTableAws {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String connectionId = "MY_CONNECTION_ID";
    String sourceUri = "s3://your-bucket-name/";
    CsvOptions options = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    ExternalTableDefinition externalTableDefinition =
        ExternalTableDefinition.newBuilder(sourceUri, options)
            .setConnectionId(connectionId)
            .setSchema(schema)
            .build();
    createExternalTableAws(projectId, datasetName, tableName, externalTableDefinition);
  }

  public static void createExternalTableAws(
      String projectId,
      String datasetName,
      String tableName,
      ExternalTableDefinition externalTableDefinition) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, externalTableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Aws external table created successfully");

      // Clean up
      bigquery.delete(TableId.of(projectId, datasetName, tableName));
    } catch (BigQueryException e) {
      System.out.println("Aws external was not created." + e.toString());
    }
  }
}

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and select a dataset.

3. Click more_vert View actions, and then click Create table. This opens the Create table pane.

4. In the Source section, specify the following details:

   1. For Create table from, select Amazon S3.

   2. Provide the path to the folder, using wildcards. For example, s3://mybucket/*.

      The folder must be in the same location as the dataset that contains the table you want to create, append, or overwrite.

   3. From the File format list, select the file type.

   4. Select the Source data partitioning checkbox, and then specify the following details:

      1. For Select Source URI Prefix, enter the URI prefix. For example, s3://mybucket/my_files.
      2. Optional: To require a partition filter on all queries for this table, select the Require partition filter checkbox. Requiring a partition filter can reduce cost and improve performance. For more information, see Requiring predicate filters on partition keys in queries.

      3. In the Partition inference mode section, select one of the following options:

         • Automatically infer types: set the partition schema detection mode to AUTO.
         • All columns are strings: set the partition schema detection mode to STRINGS.
         • Provide my own: set the partition schema detection mode to CUSTOM and manually enter the schema information for the partition keys. For more information, see Provide a custom partition key schema.

5. In the Destination section, specify the following details:

   1. For Project, select the project in which you want to create the table.
   2. For Dataset, select the dataset in which you want to create the table.
   3. For Table, enter the name of the table that you want to create.
   4. For Table type, verify that External table is selected.
   5. For Connection ID, select the connection that you created earlier.

6. In the Schema section, you can either enable schema auto-detection or manually specify a schema if you have a source file. If you don't have a source file, you must manually specify a schema.

   • To enable schema auto-detection, select the Auto-detect option.

   • To manually specify a schema, leave the Auto-detect option unchecked. Enable Edit as text and enter the table schema as a JSON array.

7. To ignore rows with extra column values that don't match the schema, expand the Advanced options section and select Unknown values.

8. Click Create table.

SQL

Use the CREATE EXTERNAL TABLE DDL statement:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
   WITH PARTITION COLUMNS
   (
     PARTITION_COLUMN PARTITION_COLUMN_TYPE,
   )
   WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
   OPTIONS (
     hive_partition_uri_prefix = "HIVE_PARTITION_URI_PREFIX",
     uris=['FILE_PATH'],
     format ="TABLE_FORMAT"
     max_staleness = STALENESS_INTERVAL,
     metadata_cache_mode = 'CACHE_MODE'
   );

   Replace the following:

   • PROJECT_ID: the name of your project in which you want to create the table—for example, myproject
   • DATASET: the name of the BigQuery dataset that you want to create the table in—for example, mydataset
   • EXTERNAL_TABLE_NAME: the name of the table that you want to create—for example, mytable
   • PARTITION_COLUMN: the name of the partitioning column
   • PARTITION_COLUMN_TYPE: the type of the partitioning column
   • REGION: the region that contains the connection—for example, us
   • CONNECTION_ID: the name of the connection—for example, myconnection
   • HIVE_PARTITION_URI_PREFIX: hive partitioning uri prefix–for example: s3://mybucket/
   • FILE_PATH: path to the data source for the external table that you want to create—for example: s3://mybucket/*.parquet
   • TABLE_FORMAT: the format of the table that you want to create—for example, PARQUET
   • STALENESS_INTERVAL: specifies whether cached metadata is used by operations against the BigLake table, and how fresh the cached metadata must be in order for the operation to use it. For more information about metadata caching considerations, see Metadata caching for performance.

     To disable metadata caching, specify 0. This is the default.

     To enable metadata caching, specify an interval literal value between 30 minutes and 7 days. For example, specify INTERVAL 4 HOUR for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the cached metadata is older than that, the operation retrieves metadata from Amazon S3 instead.

   • CACHE_MODE: specifies whether the metadata cache is refreshed automatically or manually. For more information about metadata caching considerations, see Metadata caching for performance.

     Set to AUTOMATIC for the metadata cache to be refreshed at a system-defined interval, usually somewhere between 30 and 60 minutes.

     Set to MANUAL if you want to refresh the metadata cache on a schedule you determine. In this case, you can call the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the cache.

     You must set CACHE_MODE if STALENESS_INTERVAL is set to a value greater than 0.

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

Examples

The following example creates a BigLake table over partitioned data in Amazon S3. The schema is autodetected.

CREATE EXTERNAL TABLE `my_dataset.my_table`
WITH PARTITION COLUMNS
(
  sku STRING,
)
WITH CONNECTION `us.my-connection`
OPTIONS(
  hive_partition_uri_prefix = "s3://mybucket/products",
  uris = ['s3://mybucket/products/*']
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

bq

First, use the bq mkdef command to create a table definition file:

bq mkdef \
--source_format=SOURCE_FORMAT \
--connection_id=REGION.CONNECTION_ID \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
--metadata_cache_mode=CACHE_MODE \
 URIS > DEFINITION_FILE

Replace the following:

• SOURCE_FORMAT: the format of the external data source. For example, CSV.
• REGION: the region that contains the connection—for example, us.
• CONNECTION_ID: the name of the connection—for example, myconnection.
• PARTITIONING_MODE: the Hive partitioning mode. Use one of the following values:
  • AUTO: Automatically detect the key names and types.
  • STRINGS: Automatically convert the key names to strings.
  • CUSTOM: Encode the key schema in the source URI prefix.
• URI_SHARED_PREFIX: the source URI prefix.

• BOOLEAN: specifies whether to require a predicate filter at query time. This flag is optional. The default value is false.

• CACHE_MODE: specifies whether the metadata cache is refreshed automatically or manually. You only need to include this flag if you also plan to use the --max_staleness flag in the subsequent bq mk command to enable metadata caching. For more information about metadata caching considerations, see Metadata caching for performance.

  Set to AUTOMATIC for the metadata cache to be refreshed at a system-defined interval, usually somewhere between 30 and 60 minutes.

  Set to MANUAL if you want to refresh the metadata cache on a schedule you determine. In this case, you can call the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the cache. You must set CACHE_MODE if STALENESS_INTERVAL is set to a value greater than 0.

• URIS: the path to the Amazon S3 folder, using wildcard format.

• DEFINITION_FILE: the path to the table definition file on your local machine.

If PARTITIONING_MODE is CUSTOM, include the partition key schema in the source URI prefix, using the following format:

--hive_partitioning_source_uri_prefix=URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

After you create the table definition file, use the bq mk command to create the BigLake table:

bq mk --max_staleness=STALENESS_INTERVAL \
--external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Replace the following:

• STALENESS_INTERVAL: specifies whether cached metadata is used by operations against the BigLake table, and how fresh the cached metadata must be in order for the operation to use it. For more information about metadata caching considerations, see Metadata caching for performance.

  To disable metadata caching, specify 0. This is the default.

  To enable metadata caching, specify an interval literal value between 30 minutes and 7 days. For example, specify INTERVAL 4 HOUR for a 4 hour staleness interval. With this value, operations against the table use cached metadata if it has been refreshed within the past 4 hours. If the cached metadata is older than that, the operation retrieves metadata from Amazon S3 instead.

• DEFINITION_FILE: the path to the table definition file.

• DATASET_NAME: the name of the dataset that contains the table.

• TABLE_NAME: the name of the table you're creating.

• SCHEMA: specifies a path to a JSON schema file, or specifies the schema in the form field:data_type,field:data_type,.... To use schema auto-detection, omit this argument.

Examples

The following example uses AUTO Hive partitioning mode for Amazon S3 data:

bq mkdef --source_format=CSV \
  --connection_id=us.my-connection \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=s3://mybucket/myTable \
  --metadata_cache_mode=AUTOMATIC \
  s3://mybucket/* > mytable_def

bq mk --max_staleness=INTERVAL "1" HOUR \
  --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

The following example uses STRING Hive partitioning mode for Amazon S3 data:

bq mkdef --source_format=CSV \
  --connection_id=us.my-connection \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=s3://mybucket/myTable \
  --metadata_cache_mode=AUTOMATIC \
  s3://mybucket/myTable/* > mytable_def

bq mk --max_staleness=INTERVAL "1" HOUR \
  --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

To set Hive partitioning using the BigQuery API, include the hivePartitioningOptions object in the ExternalDataConfiguration object when you create the table definition file. To create a BigLake table, you must also specify a value for the connectionId field.

If you set the hivePartitioningOptions.mode field to CUSTOM, you must encode the partition key schema in the hivePartitioningOptions.sourceUriPrefix field as follows: s3://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

To enforce the use of a predicate filter at query time, set the hivePartitioningOptions.requirePartitionFilter field to true.

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. If the Editor tab isn't visible, then click add_boxCompose new query.

3. Click More > Query settings. The Query settings dialog opens.

4. In the Query settings dialog, for Additional settings > Data location, select the BigQuery region that is collocated with the BigQuery Omni region. For example, if your BigQuery Omni region is aws-us-east-1, specify us-east4.

5. Select the remaining fields and click Save.

bq

Use the --location flag to set the job's processing location to the BigQuery region that is collocated with the BigQuery Omni region. For example, if your BigQuery Omni region is aws-us-east-1, specify us-east4.

Example

bq query --use_legacy_sql=false --location=us-east4 \
"SELECT * FROM region-aws-us-east-1.INFORMATION_SCHEMA.JOBS limit 10;"

bq query --use_legacy_sql=false --location=asia-northeast3 \
"SELECT * FROM region-aws-ap-northeast-2.INFORMATION_SCHEMA.JOBS limit 10;"

API

If you are running jobs programmatically, set the location argument to the BigQuery region that is collocated with the BigQuery Omni region. For example, if your BigQuery Omni region is aws-us-east-1, specify us-east4.

AWS CLI

Run the following command to apply an S3 bucket policy that includes a condition using the aws:SourceVpc attribute:

  aws s3api put-bucket-policy \
    --bucket=BUCKET_NAME \
    --policy "{
      \"Version\": \"2012-10-17\",
      \"Id\": \"RestrictBucketReads\",
      \"Statement\": [
          {
              \"Sid\": \"AccessOnlyToOmniVPC\",
              \"Principal\": \"*\",
              \"Action\": [\"s3:ListBucket\", \"s3:GetObject\"],
              \"Effect\": \"Allow\",
              \"Resource\": [\"arn:aws:s3:::BUCKET_NAME\",
                             \"arn:aws:s3:::BUCKET_NAME/*\"],
              \"Condition\": {
                  \"StringEquals\": {
                    \"aws:SourceVpc\": \"VPC_ID\"
                  }
              }
          }
      ]
    }"

Replace the following:

• BUCKET_NAME: the Amazon S3 bucket that you want BigQuery to access.
• VPC_ID: the BigQuery Omni VPC ID of the BigQuery Omni region collocated with the Amazon S3 bucket. You can find this information in the table on this page.

Terraform

Add the following to your Terraform configuration file:

  resource "aws_s3_bucket" "example" {
    bucket = "BUCKET_NAME"
  }

  resource "aws_s3_bucket_policy" "example" {
    bucket = aws_s3_bucket.example.id
    policy = jsonencode({
      Version = "2012-10-17"
      Id      = "RestrictBucketReads"
      Statement = [
          {
              Sid       = "AccessOnlyToOmniVPC"
              Effect    = "Allow"
              Principal = "*"
              Action    = ["s3:GetObject", "s3:ListBucket"]
              Resource  = [
                  aws_s3_bucket.example.arn,
                  "${aws_s3_bucket.example.arn}/*"
                  ]
              Condition = {
                  StringEquals = {
                      "aws:SourceVpc": "VPC_ID"
                  }
              }
          },
      ]
    })
  }

Replace the following:

• BUCKET_NAME: the Amazon S3 bucket that you want BigQuery to access.
• VPC_ID: the BigQuery Omni VPC ID of the BigQuery Omni region collocated with the Amazon S3 bucket.