A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://developers.google.com/bigquery/docs/object-tables below:

Create object tables | BigQuery

Skip to main content

Stay organized with collections Save and categorize content based on your preferences.

Create object tables

This document describes how to access unstructured data in BigQuery by creating an object table.

To create an object table, you must complete the following tasks:

  1. Create a connection to read object information from Cloud Storage.
  2. Grant the Storage Object Viewer (roles/storage.objectViewer) role to the service account associated with the connection.
  3. Create the object table and associate it with the connection by using the CREATE EXTERNAL TABLE statement.
Before you begin
  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  3. Verify that billing is enabled for your Google Cloud project.

  4. Enable the BigQuery and BigQuery Connection API APIs.

    Enable the APIs

  5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  6. Verify that billing is enabled for your Google Cloud project.

  7. Enable the BigQuery and BigQuery Connection API APIs.

    Enable the APIs

  8. Ensure that your BigQuery administrator has created a connection and set up access to Cloud Storage.
Required roles

To work with object tables, your users need the following IAM permissions based on their role in your organization. For more information on user roles, see Security model. For more information about giving permissions, see Viewing the grantable roles on resources.

Caution: Data analysts should not have the following: Create object tables

Before you create an object table, you must have an existing dataset to contain it. For more information, see Creating datasets.

To create an object table:

SQL

Use the CREATE EXTERNAL TABLE 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_ID.TABLE_NAME`
WITH CONNECTION {`PROJECT_ID.REGION.CONNECTION_ID`| DEFAULT}
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['BUCKET_PATH'[,...]],
  max_staleness = STALENESS_INTERVAL,
  metadata_cache_mode = 'CACHE_MODE');

    Replace the following:

  3. Click play_circle Run.

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

Examples

The following example creates an object table with a metadata cache staleness interval of 1 day:

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://mybucket/*'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

The following example creates an object table over the objects in three Cloud Storage buckets:

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*','gs://bucket2/folder1/*','gs://bucket3/*']
);

The following example creates an object table over just the PDF objects in a Cloud Storage bucket:

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*.pdf']
);
bq

Use the bq mk command.

bq mk --table \
--external_table_definition=BUCKET_PATH@REGION.CONNECTION_ID \
--object_metadata=SIMPLE \
--max_staleness=STALENESS_INTERVAL \
--metadata_cache_mode=CACHE_MODE \
PROJECT_ID:DATASET_ID.TABLE_NAME

Replace the following:

Examples

The following example creates an object table with a metadata cache staleness interval of 1 day:

bq mk --table \
--external_table_definition=gs://mybucket/*@us.my-connection \
--object_metadata=SIMPLE \
--max_staleness=0-0 1 0:0:0 \
--metadata_cache_mode=AUTOMATIC \
my_dataset.object_table

The following example creates an object table over the objects in three Cloud Storage buckets:

bq mk --table \
--external_table_definition=gs://bucket1/*,gs://bucket2/folder1/*,gs://bucket3/*@us.my-connection \
--object_metadata=SIMPLE \
my_dataset.object_table

The following example creates an object table over just the PDF objects in a Cloud Storage bucket:

bq mk --table \
--external_table_definition=gs://bucket1/*.pdf@us.my-connection \
--object_metadata=SIMPLE \
my_dataset.object_table
API

Call the tables.insert method. Include an ExternalDataConfiguration object with the objectMetadata field set to SIMPLE in the Table resource that you pass in.

The following example shows how to call this method by using curl:

ACCESS_TOKEN=$(gcloud auth print-access-token) curl \
-H "Authorization: Bearer ${ACCESS_TOKEN}" \
-H "Content-Type: application/json" \
-X POST \
-d '{"tableReference": {"projectId": "my_project", "datasetId": "my_dataset", "tableId": "object_table_name"}, "externalDataConfiguration": {"objectMetadata": "SIMPLE", "sourceUris": ["gs://mybucket/*"]}}' \
https://www.googleapis.com/bigquery/v2/projects/my_project/datasets/my_dataset/tables
Terraform

This example creates an object table with metadata caching enabled with manual refresh.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The key fields to specify for an object table are google_bigquery_table.external_data_configuration.object_metadata, google_bigquery_table.external_data_configuration.metadata_cache_mode, and google_bigquery_table.max_staleness. For more information on each resource, see the Terraform BigQuery documentation.

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell
  1. Launch Cloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly created main.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory. 
    terraform init

    Optionally, to use the latest Google provider version, include the -upgrade option:

    terraform init -upgrade
Apply the changes
  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations: 
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project. Query object tables

You can query an object table like any other BigQuery, for example:

SELECT *
FROM mydataset.myobjecttable;

Querying an object table returns metadata for the underlying objects. For more information, see Object table schema.

What's next

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["This document guides you through creating object tables in BigQuery to access unstructured data in Cloud Storage."],["Creating an object table requires setting up a connection to Cloud Storage, granting the connection's service account the appropriate permissions, and using the `CREATE EXTERNAL TABLE` statement."],["Different user roles, such as Data lake administrator, Data warehouse administrator, and Data analyst, have different required IAM permissions for working with object tables."],["When creating an object table, you can configure metadata caching by setting a staleness interval and choosing between automatic or manual cache refresh modes to optimize performance."],["You can create object tables using the SQL `CREATE EXTERNAL TABLE` statement, the `bq mk` command, API `tables.insert` method, or Terraform, with examples provided for each."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4