Create and use tables

This document describes how to create and usestandard (built-in) tables inBigQuery. Forinformation about creating other table types, see the following:

• Creating partitioned tables
• Creating and using clustered tables

After creating a table, you can do the following:

• Control access to your table data.
• Get information about your tables.
• List the tables in a dataset.
• Get table metadata.

For more information about managing tables including updating table properties,copying a table, and deleting a table, seeManaging tables.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions toperform each task in this document.

Required roles

To get the permissions that you need to create a table, ask your administrator to grant you the following IAM roles:

• BigQuery Job User (roles/bigquery.jobUser) on the project if you're creating a table by loading data or by saving query results to a table.
• BigQuery Data Editor (roles/bigquery.dataEditor) on the dataset where you're creating the table.

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to create a table. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

• Contain characters with a total of up to 1,024 UTF-8 bytes.
• Contain Unicode characters in category L (letter), M (mark), N (number),Pc (connector, including underscore), Pd (dash), Zs (space). For moreinformation, seeGeneral Category.

The following are all examples of valid table names:table 01,ग्राहक,00_お客様,étudiant-01.

Caveats:

• Table names are case-sensitive by default.mytable andMyTable cancoexist in the same dataset, unless they are part of adataset withcase-sensitivity turned off.
• Some table names and table name prefixes are reserved. Ifyou receive an error saying that your table name or prefix isreserved, then select a different name and try again.

• If you include multiple dot operators (.) in a sequence, the duplicateoperators are implicitly stripped.

  For example, this:project_name....dataset_name..table_name

  Becomes this:project_name.dataset_name.table_name

Create tables

You can create a table in BigQuery in the following ways:

• Manually by using the Google Cloud console or the bq command-line toolbq mk command.
• Programmatically by calling thetables.insertAPI method.
• By using the client libraries.
• From query results.
• By defining a table that references an external data source.
• When you load data.
• By using aCREATE TABLEdata definition language (DDL) statement.

Create an empty table with a schema definition

You can create an empty table with a schema definition in the following ways:

• Enter the schema using the Google Cloud console.
• Provide the schema inline using the bq command-line tool.
• Submit a JSON schema file using the bq command-line tool.
• Provide the schema in atable resourcewhen calling the APIstables.insert method.

For more information about specifying a table schema, seeSpecifying a schema.

After the table is created, you canload datainto it or populate it bywriting query resultsto it.

To create an empty table with a schema definition:

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "mode": "NULLABLE",    "description": "Item ID"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF}

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "mode": "NULLABLE",    "description": "Item ID"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF}data "google_iam_policy" "default" {  binding {    role = "roles/bigquery.dataOwner"    members = [      "user:raha@altostrat.com",    ]  }}resource "google_bigquery_table_iam_policy" "policy" {  dataset_id  = google_bigquery_table.default.dataset_id  table_id    = google_bigquery_table.default.table_id  policy_data = data.google_iam_policy.default.policy_data}

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "mode": "NULLABLE",    "description": "Item ID"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF  encryption_configuration {    kms_key_name = google_kms_crypto_key.crypto_key.id  }  depends_on = [google_project_iam_member.service_account_access]}resource "google_kms_crypto_key" "crypto_key" {  name     = "example-key"  key_ring = google_kms_key_ring.key_ring.id}resource "random_id" "default" {  byte_length = 8}resource "google_kms_key_ring" "key_ring" {  name     = "${random_id.default.hex}-example-keyring"  location = "us"}# Enable the BigQuery service account to encrypt/decrypt Cloud KMS keysdata "google_project" "project" {}resource "google_project_iam_member" "service_account_access" {  project = data.google_project.project.project_id  role    = "roles/cloudkms.cryptoKeyEncrypterDecrypter"  member  = "serviceAccount:bq-${data.google_project.project.number}@bigquery-encryption.iam.gserviceaccount.com"}

usingGoogle.Cloud.BigQuery.V2;publicclassBigQueryCreateTable{publicBigQueryTableCreateTable(stringprojectId="your-project-id",stringdatasetId="your_dataset_id"){BigQueryClientclient=BigQueryClient.Create(projectId);vardataset=client.GetDataset(datasetId);// Create schema for new table.varschema=newTableSchemaBuilder{{"full_name",BigQueryDbType.String},{"age",BigQueryDbType.Int64}}.Build();// Create the tablereturndataset.CreateTable(tableId:"your_table_id",schema:schema);}}

import("context""fmt""time""cloud.google.com/go/bigquery")// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.funccreateTableExplicitSchema(projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydatasetid"// tableID := "mytableid"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()sampleSchema:=bigquery.Schema{{Name:"full_name",Type:bigquery.StringFieldType},{Name:"age",Type:bigquery.IntegerFieldType},}metaData:=&bigquery.TableMetadata{Schema:sampleSchema,ExpirationTime:time.Now().AddDate(1,0,0),// Table will be automatically deleted in 1 year.}tableRef:=client.Dataset(datasetID).Table(tableID)iferr:=tableRef.Create(ctx,metaData);err!=nil{returnerr}returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.StandardTableDefinition;importcom.google.cloud.bigquery.TableDefinition;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;publicclassCreateTable{publicstaticvoidrunCreateTable(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";Schemaschema=Schema.of(Field.of("stringField",StandardSQLTypeName.STRING),Field.of("booleanField",StandardSQLTypeName.BOOL));createTable(datasetName,tableName,schema);}publicstaticvoidcreateTable(StringdatasetName,StringtableName,Schemaschema){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,tableName);TableDefinitiontableDefinition=StandardTableDefinition.of(schema);TableInfotableInfo=TableInfo.newBuilder(tableId,tableDefinition).build();bigquery.create(tableInfo);System.out.println("Table created successfully");}catch(BigQueryExceptione){System.out.println("Table was not created. \n"+e.toString());}}}

// Import the Google Cloud client library and create a clientconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctioncreateTable(){// Creates a new table named "my_table" in "my_dataset"./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";// const schema = 'Name:string, Age:integer, Weight:float, IsMagic:boolean';// For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resourceconstoptions={schema:schema,location:'US',};// Create a new table in the datasetconst[table]=awaitbigquery.dataset(datasetId).createTable(tableId,options);console.log(`Table${table.id} created.`);}

use Google\Cloud\BigQuery\BigQueryClient;/** Uncomment and populate these variables in your code */// $projectId = 'The Google project ID';// $datasetId = 'The BigQuery dataset ID';// $tableId = 'The BigQuery table ID';// $fields = [//    [//        'name' => 'field1',//        'type' => 'string',//        'mode' => 'required'//    ],//    [//        'name' => 'field2',//        'type' => 'integer'//    ],//];$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$dataset = $bigQuery->dataset($datasetId);$schema = ['fields' => $fields];$table = $dataset->createTable($tableId, ['schema' => $schema]);printf('Created table %s' . PHP_EOL, $tableId);

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the table to create.# table_id = "your-project.your_dataset.your_table_name"schema=[bigquery.SchemaField("full_name","STRING",mode="REQUIRED"),bigquery.SchemaField("age","INTEGER",mode="REQUIRED"),]table=bigquery.Table(table_id,schema=schema)table=client.create_table(table)# Make an API request.print("Created table{}.{}.{}".format(table.project,table.dataset_id,table.table_id))

require"google/cloud/bigquery"defcreate_tabledataset_id="my_dataset"bigquery=Google::Cloud::Bigquery.newdataset=bigquery.datasetdataset_idtable_id="my_table"table=dataset.create_tabletable_iddo|updater|updater.string"full_name",mode::requiredupdater.integer"age",mode::requiredendputs"Created table:#{table_id}"end

Create an empty table without a schema definition

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardTableDefinition;importcom.google.cloud.bigquery.TableDefinition;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;// Sample to create a table without schemapublicclassCreateTableWithoutSchema{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";createTableWithoutSchema(datasetName,tableName);}publicstaticvoidcreateTableWithoutSchema(StringdatasetName,StringtableName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,tableName);TableDefinitiontableDefinition=StandardTableDefinition.of(Schema.of());TableInfotableInfo=TableInfo.newBuilder(tableId,tableDefinition).build();bigquery.create(tableInfo);System.out.println("Table created successfully");}catch(BigQueryExceptione){System.out.println("Table was not created. \n"+e.toString());}}}

Create a table from a query result

To create a table from a query result, write the results to a destination table.

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryWithDestination demonstrates saving the results of a query to a specific table by setting the destination// via the API properties.funcqueryWithDestination(wio.Writer,projectID,destDatasetID,destTableIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.Query("SELECT 17 as my_col")q.Location="US"// Location must match the dataset(s) referenced in query.q.QueryConfig.Dst=client.Dataset(destDatasetID).Table(destTableID)// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.TableId;publicclassSaveQueryToTable{publicstaticvoidrunSaveQueryToTable(){// TODO(developer): Replace these variables before running the sample.Stringquery="SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";StringdestinationTable="MY_TABLE";StringdestinationDataset="MY_DATASET";saveQueryToTable(destinationDataset,destinationTable,query);}publicstaticvoidsaveQueryToTable(StringdestinationDataset,StringdestinationTableId,Stringquery){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// Identify the destination tableTableIddestinationTable=TableId.of(destinationDataset,destinationTableId);// Build the query jobQueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).setDestinationTable(destinationTable).build();// Execute the query.bigquery.query(queryConfig);// The results are now saved in the destination table.System.out.println("Saved query ran successfully");}catch(BigQueryException|InterruptedExceptione){System.out.println("Saved query did not run \n"+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionqueryDestinationTable(){// Queries the U.S. given names dataset for the state of Texas// and saves results to permanent table./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = 'my_dataset';// const tableId = 'my_table';// Create destination table referenceconstdataset=bigquery.dataset(datasetId);constdestinationTable=dataset.table(tableId);constquery=`SELECT name    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`    WHERE state = 'TX'    LIMIT 100`;// For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resourceconstoptions={query:query,// Location must match that of the dataset(s) referenced in the query.location:'US',destination:destinationTable,};// Run the query as a jobconst[job]=awaitbigquery.createQueryJob(options);console.log(`Job${job.id} started.`);console.log(`Query results loaded to table${destinationTable.id}`);}

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the destination table.# table_id = "your-project.your_dataset.your_table_name"job_config=bigquery.QueryJobConfig(destination=table_id)sql="""    SELECT corpus    FROM `bigquery-public-data.samples.shakespeare`    GROUP BY corpus;"""# Start the query, passing in the extra configuration.query_job=client.query(sql,job_config=job_config)# Make an API request.query_job.result()# Wait for the job to complete.print("Query results loaded to the table{}".format(table_id))

Create a table that references an external data source

An external data source is a data source that you can query directly fromBigQuery, even though the data is not stored inBigQuery storage. For example, you might have data in adifferent Google Cloud database, in files in Cloud Storage, or in adifferent cloud product altogether that you would like to analyze inBigQuery, but that you aren't prepared to migrate.

For more information, seeIntroduction to external data sources.

Create a table when you load data

When you load data into BigQuery, you can load data into a newtable or partition, you can append data to an existing table or partition, oryou can overwrite a table or partition. You don't need to create an emptytable before loading data into it. You can create the new table and load yourdata at the same time.

When you load data into BigQuery, you can supply the tableor partition schema, or for supported data formats, you can use schemaauto-detection.

For more information about loading data, seeIntroduction to loading data into BigQuery.

Create a multimodal table

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

You can create a table with one or moreObjectRef columns in order to store metadataabout unstructured data that is related to the other structured data in thetable. For example, in a products table, you could create anObjectRef columnto store product image information along with the other product data. Theunstructured data itself is stored in Cloud Storage, and is made availablein BigQuery by using anobject table.

To learn how to create a multimodal table, seeAnalyze multimodal data with SQL and Python UDFs.

Control access to tables

To configure access to tables and views, you can grant anIAM role to an entity at the following levels, listed inorder of range of resources allowed (largest to smallest):

• a high level in theGoogle Cloud resource hierarchysuch as the project, folder, or organization level
• the dataset level
• the table or view level

You can also restrict data access within tables, by using the followingmethods:

• column-level security
• column data masking
• row-level security

Access with any resource protected by IAM is additive. Forexample, if an entity does not have access at the high level such as a project,you could grant the entity access at the dataset level, and then the entity willhave access to the tables and views in the dataset. Similarly, if the entitydoes not have access at the high level or the dataset level, you could grant theentity access at the table or view level.

Granting IAM roles at a higher level in theGoogle Cloudresource hierarchysuch as the project, folder, or organization level gives the entity access to abroad set of resources. For example, granting a role to an entity at the projectlevel gives that entity permissions that apply to all datasets throughout theproject.

Granting a role at the dataset level specifies the operations an entity isallowed to perform on tables and views in that specific dataset, even if theentity does not have access at a higher level. For information on configuringdataset-level access controls, seeControlling access to datasets.

Granting a role at the table or view level specifies the operations an entity isallowed to perform on specific tables and views, even if the entity does nothave access at a higher level. For information on configuring table-level accesscontrols, seeControlling access to tables and views.

You can also createIAM custom roles.If you create a custom role, the permissions you grant depend on the specificoperations you want the entity to be able to perform.

You can't set a "deny" permission on any resource protected byIAM.

For more information about roles and permissions, seeUnderstanding rolesin the IAM documentation and the BigQueryIAM roles and permissions.

Get information about tables

You can get information or metadata about tables in the following ways:

• Using the Google Cloud console.
• Using the bq command-line toolbq show command.
• Calling thetables.getAPI method.
• Using the client libraries.
• Querying theINFORMATION_SCHEMA.VIEWS view.

Required permissions

At a minimum, to get information about tables, you must be grantedbigquery.tables.get permissions. The following predefined IAMroles includebigquery.tables.get permissions:

• bigquery.metadataViewer
• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

In addition, if a user hasbigquery.datasets.create permissions, when thatuser creates a dataset, they are grantedbigquery.dataOwner access to it.bigquery.dataOwner access gives the user the ability to retrieve tablemetadata.

For more information on IAM roles and permissions inBigQuery, seeAccess control.

Get table information

To get information about tables:

import("context""fmt""io""cloud.google.com/go/bigquery")// printTableInfo demonstrates fetching metadata from a table and printing some basic information// to an io.Writer.funcprintTableInfo(wio.Writer,projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()meta,err:=client.Dataset(datasetID).Table(tableID).Metadata(ctx)iferr!=nil{returnerr}// Print basic information about the table.fmt.Fprintf(w,"Schema has %d top-level fields\n",len(meta.Schema))fmt.Fprintf(w,"Description: %s\n",meta.Description)fmt.Fprintf(w,"Rows in managed storage: %d\n",meta.NumRows)returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Table;importcom.google.cloud.bigquery.TableId;publicclassGetTable{publicstaticvoidrunGetTable(){// TODO(developer): Replace these variables before running the sample.StringprojectId="bigquery_public_data";StringdatasetName="samples";StringtableName="shakespeare";getTable(projectId,datasetName,tableName);}publicstaticvoidgetTable(StringprojectId,StringdatasetName,StringtableName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(projectId,datasetName,tableName);Tabletable=bigquery.getTable(tableId);System.out.println("Table info: "+table.getDescription());}catch(BigQueryExceptione){System.out.println("Table not retrieved. \n"+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctiongetTable(){// Retrieves table named "my_table" in "my_dataset"./**   * TODO(developer): Uncomment the following lines before running the sample   */// const datasetId = "my_dataset";// const tableId = "my_table";// Retrieve table referenceconstdataset=bigquery.dataset(datasetId);const[table]=awaitdataset.table(tableId).get();console.log('Table:');console.log(table.metadata.tableReference);}getTable();

use Google\Cloud\BigQuery\BigQueryClient;/** Uncomment and populate these variables in your code *///$projectId = 'The Google project ID';//$datasetId = 'The BigQuery dataset ID';//$tableId   = 'The BigQuery table ID';$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$dataset = $bigQuery->dataset($datasetId);$table = $dataset->table($tableId);

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the model to fetch.# table_id = 'your-project.your_dataset.your_table'table=client.get_table(table_id)# Make an API request.# View table propertiesprint("Got table '{}.{}.{}'.".format(table.project,table.dataset_id,table.table_id))print("Table schema:{}".format(table.schema))print("Table description:{}".format(table.description))print("Table has{} rows".format(table.num_rows))

Get table information usingINFORMATION_SCHEMA

INFORMATION_SCHEMA is a series of views that provide access to metadataabout datasets, routines, tables, views, jobs, reservations, and streaming data.

You can query the following views to get table information:

• Use theINFORMATION_SCHEMA.TABLES andINFORMATION_SCHEMA.TABLE_OPTIONSviews to retrieve metadata about tables and views in a project.
• Use theINFORMATION_SCHEMA.COLUMNS andINFORMATION_SCHEMA.COLUMN_FIELD_PATHS views to retrieve metadata about thecolumns (fields) in a table.
• Use theINFORMATION_SCHEMA.TABLE_STORAGE views to retrieve metadataabout current and historical storage usage by a table.

TheTABLES andTABLE_OPTIONS views also contain high-levelinformation about views. For detailed information, query theINFORMATION_SCHEMA.VIEWS viewinstead.

TABLES view

When you query theINFORMATION_SCHEMA.TABLES view, the query results containone row for each table or view in a dataset. For detailed information aboutviews, query theINFORMATION_SCHEMA.VIEWSview instead.

TheINFORMATION_SCHEMA.TABLES view has the following schema:

Examples

Example 1:

The following example retrieves table metadata for all of the tables in thedataset namedmydataset. The metadata that'sreturned is for all types of tables inmydataset in your default project.

mydataset contains the following tables:

• mytable1: a standard BigQuery table
• myview1: a BigQuery view

To run the query against a project other than your default project, add theproject ID to the dataset in the following format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

SELECTtable_catalog,table_schema,table_name,table_type,is_insertable_into,creation_time,ddlFROMmydataset.INFORMATION_SCHEMA.TABLES;

+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+| table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |                     ddl                     |+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+| myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` ||                |               |                |            |                    |                     | (                                           ||                |               |                |            |                    |                     |   id INT64                                  ||                |               |                |            |                    |                     | );                                          || myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1`   ||                |               |                |            |                    |                     | AS SELECT 100 as id;                        |+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+

SELECTtable_name,table_type,base_table_catalog,base_table_schema,base_table_name,snapshot_time_msFROMmydataset.INFORMATION_SCHEMA.TABLESWHEREtable_type='CLONE'ORtable_type='SNAPSHOT';

  +--------------+------------+--------------------+-------------------+-----------------+---------------------+  | table_name   | table_type | base_table_catalog | base_table_schema | base_table_name | snapshot_time_ms    |  +--------------+------------+--------------------+-------------------+-----------------+---------------------+  | items_clone  | CLONE      | myproject          | mydataset         | items           | 2018-10-31 22:40:05 |  | orders_bk    | SNAPSHOT   | myproject          | mydataset         | orders          | 2018-11-01 08:22:39 |  +--------------+------------+--------------------+-------------------+-----------------+---------------------+

The following example retrievestable_name andddl columns from theINFORMATION_SCHEMA.TABLESview for thepopulation_by_zip_2010 table in thecensus_bureau_usadataset. This dataset is part of the BigQuerypublic dataset program.

Because the table you're querying is in another project, you add the project ID to the dataset inthe following format:`project_id`.dataset.INFORMATION_SCHEMA.view.In this example, the value is`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

SELECTtable_name,ddlFROM`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLESWHEREtable_name='population_by_zip_2010';

The result is similar to the following:

+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|       table_name       |                                                                                                            ddl                                                                                                             |+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010`                                                                                                                                               ||                        | (                                                                                                                                                                                                                          ||                        |   geo_id STRING OPTIONS(description="Geo code"),                                                                                                                                                                           ||                        |   zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"),                                                                                                                          ||                        |   population INT64 OPTIONS(description="The total count of the population for this segment."),                                                                                                                             ||                        |   minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."),                                                          ||                        |   maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), ||                        |   gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.")                                                                                                                    ||                        | )                                                                                                                                                                                                                          ||                        | OPTIONS(                                                                                                                                                                                                                   ||                        |   labels=[("freebqcovid", "")]                                                                                                                                                                                             ||                        | );                                                                                                                                                                                                                         |+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

TABLE_OPTIONS view

When you query theINFORMATION_SCHEMA.TABLE_OPTIONS view, the query resultscontain one row for each option, for each table or view in a dataset. Fordetailed information aboutviews, query theINFORMATION_SCHEMA.VIEWS viewinstead.

TheINFORMATION_SCHEMA.TABLE_OPTIONS view has the following schema:

Options table

For external tables, the following options are possible:

Examples

Example 1:

The following example retrieves the default table expiration times for alltables inmydataset in your default project (myproject) by querying theINFORMATION_SCHEMA.TABLE_OPTIONS view.

To run the query against a project other than your default project, add theproject ID to the dataset in the following format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

SELECT*FROMmydataset.INFORMATION_SCHEMA.TABLE_OPTIONSWHEREoption_name='expiration_timestamp';

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+

SELECT*FROMmydataset.INFORMATION_SCHEMA.TABLE_OPTIONSWHEREoption_name='description'ANDoption_valueLIKE'%test%';

  +----------------+---------------+------------+-------------+-------------+--------------+  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |  +----------------+---------------+------------+-------------+-------------+--------------+  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |  +----------------+---------------+------------+-------------+-------------+--------------+

The following example retrieves metadata from theINFORMATION_SCHEMA.COLUMNSview for thepopulation_by_zip_2010 table in thecensus_bureau_usadataset. This dataset is part of the BigQuerypublic dataset program.

Because the table you're querying is in another project, thebigquery-public-data project, you add the project ID to the dataset in thefollowing format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

The following column is excluded from the query results:

• IS_UPDATABLE

SELECT*EXCEPT(is_updatable)FROM`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNSWHEREtable_name='population_by_zip_2010';

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | policy_tags |+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      || population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      || population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      || population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      || population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL | 0 rows      || population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL | 0 rows      |+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+-------------+

When you query theINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, the queryresults contain one row for each columnnested within aRECORD(orSTRUCT) column.

TheINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view has the following schema:

Examples

The following example retrieves metadata from theINFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for thecommits table in thegithub_repos dataset.This dataset is part of the BigQuerypublic dataset program.

Because the table you're querying is in another project, thebigquery-public-data project, you add the project ID to the dataset in thefollowing format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

Thecommits table contains the following nested and nested and repeatedcolumns:

• author: nestedRECORD column
• committer: nestedRECORD column
• trailer: nested and repeatedRECORD column
• difference: nested and repeatedRECORD column

To view metadata about theauthor anddifference columns, run the following query.

SELECT*FROM`bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHSWHEREtable_name='commits'AND(column_name='author'ORcolumn_name='difference');

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description | policy_tags |  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        | 0 rows      |  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        | 0 rows      |  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        | 0 rows      |  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        | 0 rows      |  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        | 0 rows      |  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        | 0 rows      |  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+

SELECTSUM(total_logical_bytes)AStotal_logical_bytesFROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

+---------------------+| total_logical_bytes |+---------------------+| 971329178274633     |+---------------------+

SELECTtable_schemaASdataset_name,-- LogicalSUM(total_logical_bytes)/power(1024,3)AStotal_logical_gib,SUM(active_logical_bytes)/power(1024,3)ASactive_logical_gib,SUM(long_term_logical_bytes)/power(1024,3)ASlong_term_logical_gib,-- PhysicalSUM(total_physical_bytes)/power(1024,3)AStotal_physical_gib,SUM(active_physical_bytes)/power(1024,3)ASactive_physical_gib,SUM(active_physical_bytes-time_travel_physical_bytes)/power(1024,3)ASactive_no_tt_physical_gib,SUM(long_term_physical_bytes)/power(1024,3)ASlong_term_physical_gib,SUM(time_travel_physical_bytes)/power(1024,3)AStime_travel_physical_gib,SUM(fail_safe_physical_bytes)/power(1024,3)ASfail_safe_physical_gibFROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGEWHEREtable_type='BASE TABLE'GROUPBYtable_schemaORDERBYdataset_name

The prices used in the pricing variables for this query are fortheus-central1 region. If you want to run this query for a different region,update the pricing variables appropriately. SeeStorage pricing for pricing information.

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. Enter the following GoogleSQL query in theQuery editor box.INFORMATION_SCHEMA requires GoogleSQL syntax. GoogleSQLis the default syntax in the Google Cloud console.

   DECLAREactive_logical_gib_priceFLOAT64DEFAULT0.02;DECLARElong_term_logical_gib_priceFLOAT64DEFAULT0.01;DECLAREactive_physical_gib_priceFLOAT64DEFAULT0.04;DECLARElong_term_physical_gib_priceFLOAT64DEFAULT0.02;WITHstorage_sizesAS(SELECTtable_schemaASdataset_name,-- LogicalSUM(IF(deleted=false,active_logical_bytes,0))/power(1024,3)ASactive_logical_gib,SUM(IF(deleted=false,long_term_logical_bytes,0))/power(1024,3)ASlong_term_logical_gib,-- PhysicalSUM(active_physical_bytes)/power(1024,3)ASactive_physical_gib,SUM(active_physical_bytes-time_travel_physical_bytes)/power(1024,3)ASactive_no_tt_physical_gib,SUM(long_term_physical_bytes)/power(1024,3)ASlong_term_physical_gib,-- Restorable previously deleted physicalSUM(time_travel_physical_bytes)/power(1024,3)AStime_travel_physical_gib,SUM(fail_safe_physical_bytes)/power(1024,3)ASfail_safe_physical_gib,FROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECTWHEREtotal_physical_bytes+fail_safe_physical_bytes>0-- Base the forecast on base tables only for highest precision resultsANDtable_type='BASE TABLE'GROUPBY1)SELECTdataset_name,-- LogicalROUND(active_logical_gib,2)ASactive_logical_gib,ROUND(long_term_logical_gib,2)ASlong_term_logical_gib,-- PhysicalROUND(active_physical_gib,2)ASactive_physical_gib,ROUND(long_term_physical_gib,2)ASlong_term_physical_gib,ROUND(time_travel_physical_gib,2)AStime_travel_physical_gib,ROUND(fail_safe_physical_gib,2)ASfail_safe_physical_gib,-- Compression ratioROUND(SAFE_DIVIDE(active_logical_gib,active_no_tt_physical_gib),2)ASactive_compression_ratio,ROUND(SAFE_DIVIDE(long_term_logical_gib,long_term_physical_gib),2)ASlong_term_compression_ratio,-- Forecast costs logicalROUND(active_logical_gib*active_logical_gib_price,2)ASforecast_active_logical_cost,ROUND(long_term_logical_gib*long_term_logical_gib_price,2)ASforecast_long_term_logical_cost,-- Forecast costs physicalROUND((active_no_tt_physical_gib+time_travel_physical_gib+fail_safe_physical_gib)*active_physical_gib_price,2)ASforecast_active_physical_cost,ROUND(long_term_physical_gib*long_term_physical_gib_price,2)ASforecast_long_term_physical_cost,-- Forecast costs totalROUND(((active_logical_gib*active_logical_gib_price)+(long_term_logical_gib*long_term_logical_gib_price))-(((active_no_tt_physical_gib+time_travel_physical_gib+fail_safe_physical_gib)*active_physical_gib_price)+(long_term_physical_gib*long_term_physical_gib_price)),2)ASforecast_total_cost_differenceFROMstorage_sizesORDERBY(forecast_active_logical_cost+forecast_active_physical_cost)DESC;

   Note:INFORMATION_SCHEMA view names are case-sensitive.

3. ClickRun.

The result is similar to following:

+--------------+--------------------+-----------------------+---------------------+------------------------+--------------------------+-----------------------------+------------------------------+----------------------------------+-------------------------------+----------------------------------+--------------------------------+| dataset_name | active_logical_gib | long_term_logical_gib | active_physical_gib | long_term_physical_gib | active_compression_ratio | long_term_compression_ratio | forecast_active_logical_cost | forecaset_long_term_logical_cost | forecast_active_physical_cost | forecast_long_term_physical_cost | forecast_total_cost_difference |+--------------+--------------------+-----------------------+---------------------+------------------------+--------------------------+-----------------------------+------------------------------+----------------------------------+-------------------------------+----------------------------------+--------------------------------+| dataset1     |               10.0 |                  10.0 |                 1.0 |                    1.0 |                     10.0 |                        10.0 |                          0.2 |                              0.1 |                          0.04 |                             0.02 |                           0.24 |

• Using the Google Cloud console.
• Using the bq command-line toolbq ls command.
• Calling thetables.listAPI method.
• Using the client libraries.

Required permissions

At a minimum, to list tables in a dataset, you must be grantedbigquery.tables.list permissions. The following predefined IAMroles includebigquery.tables.list permissions:

• bigquery.user
• bigquery.metadataViewer
• bigquery.dataViewer
• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

For more information on IAM roles and permissions inBigQuery, seeAccess control.

List tables

To list the tables in a dataset:

usingGoogle.Cloud.BigQuery.V2;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;publicclassBigQueryListTables{publicvoidListTables(stringprojectId="your-project-id",stringdatasetId="your_dataset_id"){BigQueryClientclient=BigQueryClient.Create(projectId);// Retrieve list of tables in the datasetList<BigQueryTable>tables=client.ListTables(datasetId).ToList();// Display the resultsif(tables.Count >0){Console.WriteLine($"Tables in dataset {datasetId}:");foreach(vartableintables){Console.WriteLine($"\t{table.Reference.TableId}");}}else{Console.WriteLine($"{datasetId} does not contain any tables.");}}}

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// listTables demonstrates iterating through the collection of tables in a given dataset.funclistTables(wio.Writer,projectID,datasetIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()ts:=client.Dataset(datasetID).Tables(ctx)for{t,err:=ts.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintf(w,"Table: %q\n",t.TableID)}returnnil}

importcom.google.api.gax.paging.Page;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQuery.TableListOption;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.DatasetId;importcom.google.cloud.bigquery.Table;publicclassListTables{publicstaticvoidrunListTables(){// TODO(developer): Replace these variables before running the sample.StringprojectId="bigquery-public-data";StringdatasetName="samples";listTables(projectId,datasetName);}publicstaticvoidlistTables(StringprojectId,StringdatasetName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();DatasetIddatasetId=DatasetId.of(projectId,datasetName);Page<Table>tables=bigquery.listTables(datasetId,TableListOption.pageSize(100));tables.iterateAll().forEach(table->System.out.print(table.getTableId().getTable()+"\n"));System.out.println("Tables listed successfully.");}catch(BigQueryExceptione){System.out.println("Tables were not listed. Error occurred: "+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionlistTables(){// Lists tables in 'my_dataset'./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = 'my_dataset';// List all tables in the datasetconst[tables]=awaitbigquery.dataset(datasetId).getTables();console.log('Tables:');tables.forEach(table=>console.log(table.id));}

use Google\Cloud\BigQuery\BigQueryClient;/** Uncomment and populate these variables in your code */// $projectId  = 'The Google project ID';// $datasetId  = 'The BigQuery dataset ID';$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$dataset = $bigQuery->dataset($datasetId);$tables = $dataset->tables();foreach ($tables as $table) {    print($table->id() . PHP_EOL);}

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set dataset_id to the ID of the dataset that contains#                  the tables you are listing.# dataset_id = 'your-project.your_dataset'tables=client.list_tables(dataset_id)# Make an API request.print("Tables contained in '{}':".format(dataset_id))fortableintables:print("{}.{}.{}".format(table.project,table.dataset_id,table.table_id))

require"google/cloud/bigquery"deflist_tablesdataset_id="your_dataset_id"bigquery=Google::Cloud::Bigquery.newdataset=bigquery.datasetdataset_idputs"Tables in dataset#{dataset_id}:"dataset.tables.eachdo|table|puts"\t#{table.table_id}"endend

Audit table history

You can audit the history of BigQuery tables by queryingCloud Audit Logs in Logs Explorer. These logs help you track when tables werecreated, updated, or deleted, and identify the user or service account that madethe changes.

Required permissions

To browse audit logs, you need theroles/logging.privateLogViewerrole. For more information on IAM roles and permissions inCloud Logging, seeAccess control with IAM.

Get audit data

You can access audit information from the Google Cloud console,gcloud commandline, REST API, and all supported languages using client libraries. The loggingfilter shown in the following example can be used regardless of method used.

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

   Go to Logging

2. Use the following query to access the audit data:

   logName = "projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity"AND resource.type = "bigquery_dataset"AND timestamp >= "STARTING_TIMESTAMP"AND protoPayload.@type = "type.googleapis.com/google.cloud.audit.AuditLog"AND (  protoPayload.metadata.tableCreation :*  OR protoPayload.metadata.tableChange :*  OR protoPayload.metadata.tableDeletion :*)AND protoPayload.resourceName : "projects/PROJECT_ID/datasets/DATASET_ID/tables/"

Replace the following:

• PROJECT_ID: the project that contains datasets and tablesyou are interested in.
• STARTING_TIMESTAMP: the oldest logs that you want to see.Use ISO 8601 format, such as2025-01-01 or2025-02-03T04:05:06Z.
• DATASET_ID: the dataset that you want to filter by.

Interpret the results

In the Logs Explorer result pane, expand the entry you're interested in,and then clickExpand nested fields to show the whole message.

The logging entry contains only one of the following objects to indicatethe operation performed:

• protoPayload.metadata.tableCreation: a table was created.
• protoPayload.metadata.tableChange: table metadata was changed, such asschema update, description change, or table replacement.
• protoPayload.metadata.tableDeletion: a table was deleted.

The content of these objects describes the requested action.For a detailed description, seeBigQueryAuditMetadata.

Explanation of the query

• logName = "projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity":This line filters for Admin Activity audit logs within your Google Cloudproject. These logs record API calls and actions that modify theconfiguration or metadata of your resources.
• resource.type = "bigquery_dataset": This narrows the search to eventsrelated to BigQuery datasets, where table operations arelogged.
• timestamp >= "STARTING_TIMESTAMP": Filters log entries toonly show those created on or after the specified timestamp.
• protoPayload.@type = "type.googleapis.com/google.cloud.audit.AuditLog":Ensures the log message conforms to the standard Cloud Audit Log structure.
• ( ... ): This block groups conditions to find different types of tableevents, as outlined in the previous section. The:* operator indicatesthat the key must be present. If you are interested in only one event,such as table creation, remove unnecessary conditions from this block.

• protoPayload.resourceName : "projects/PROJECT_ID/datasets/DATASET_ID/tables/":Selects log entries matching tables contained in the specified dataset. Thecolon (:) operator performs a substring search.

  • To filter entries for a single table, replace the condition with thefollowing one:protoPayload.resourceName = "projects/PROJECT_ID/datasets/DATASET_ID/tables/TABLE_NAME".
  • To include all tables in all datasets in the specific project, remove thiscondition.

For more information on log filtering, seelogging query language.

Table security

To control access to tables in BigQuery, seeControl access to resources with IAM.

What's next

• For more information about datasets, seeIntroduction to datasets.
• For more information about handling table data, seeManaging table data.
• For more information about specifying table schemas, seeSpecifying a schema.
• For more information about modifying table schemas, seeModifying table schemas.
• For more information about managing tables, seeManaging tables.
• To see an overview ofINFORMATION_SCHEMA, go toIntroduction to BigQueryINFORMATION_SCHEMA.