Creating and using tables

This document describes how to create and use tables in BigQuery. After creating a table, you can:

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

For more information on managing tables including updating table properties, copying a table, and deleting a table, see Managing tables.

Table limitations

BigQuery tables are subject to the following limitations:

  • Table names must be unique per dataset.
  • The console and the classic BigQuery web UI support copying only one table at a time.
  • When copying tables, the destination dataset must reside in the same location as the table being copied. For example, you cannot copy a table from an EU-based dataset to a US-based dataset.
  • When copying multiple source tables to a destination table by using the CLI or API, all source tables must have identical schemas.
  • You can only delete one table at a time by using the console, the classic BigQuery web UI, the command-line tool, or the API.
  • When exporting table data, the only supported destination is Cloud Storage.
  • As you approach 50,000 or more tables in a dataset, enumerating them becomes slower. Enumeration performance suffers whether you use an API call or the classic BigQuery web UI. Currently, the BigQuery web UI in the GCP console allows you to display only 50,000 tables per dataset.

    To improve classic BigQuery web UI performance, you can use the ?minimal parameter to limit the number of tables displayed to 30,000 tables per project. You add the parameter to the classic BigQuery web UI URL in the following format: https://bigquery.cloud.google.com/queries/[PROJECT_NAME]?minimal.

Creating a table

You can create a table in BigQuery:

  • Manually using the GCP Console, the classic BigQuery web UI, or the command-line tool's bq mk command
  • Programmatically by calling the tables.insert API method
  • From query results
  • By defining a table that references an external data source
  • When you load data

When you create a table in BigQuery, the table name must be unique per dataset. The table name can:

  • Contain up to 1,024 characters
  • Contain letters (upper or lower case), numbers, and underscores

Required permissions

To create a table, you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.create permissions. The following predefined, project-level IAM roles include bigquery.tables.create permissions:

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can create tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Creating an empty table with a schema definition

When you create an empty table with a schema definition, you can:

  • Enter the schema using the GCP Console or the classic BigQuery web UI
  • Provide the schema inline using the command-line tool
  • Submit a JSON schema file using the command-line tool
  • Provide the schema in a table resource when calling the API's tables.insert method.

For more information on specifying a table schema, see Specifying a schema.

After the table is created, you can load data into it or populate it by writing query results to it.

To create an empty table with a schema definition:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. In the navigation panel, in the Resources section, expand your project and select a dataset.

  3. On the right side of the window, in the details panel, click Create table. The process for loading data is the same as the process for creating an empty table.

    Create table

  4. On the Create table page, in the Source section, select Empty table.

  5. On the Create table page, in the Destination section:

    • For Dataset name, choose the appropriate dataset.

      Select dataset

    • In the Table name field, enter the name of the table you're creating in BigQuery.

    • Verify that Table type is set to Native table.

  6. In the Schema section, enter the schema definition.

    • Enter schema information manually by:

      • Enabling Edit as text and entering the table schema as a JSON array.

      • Using Add field to manually input the schema.

  7. In the Advanced options section, leave the default values.

  8. Select applicable items in the Advanced options section and then click Create table. For information on the available options, see JSON options.

  9. Click Create table.

Classic UI

  1. Click the down arrow icon down arrow icon next to your dataset name in the navigation and click Create new table.

  2. On the Create Table page, in the Source Data section, click Create empty table.

  3. On the Create Table page, in the Destination Table section:

    • For Table name, choose the appropriate dataset, and in the table name field, enter the name of the table you're creating.
    • Verify that Table type is set to Native table.
  4. In the Schema section, manually enter the schema definition.

    • You can enter schema information manually by:

      • Clicking Edit as text and entering the table schema as a JSON array:

        Add schema as JSON array

      • Using Add Field to input the schema:

        Add schema using add fields

  5. In the Options section, leave the default values.

  6. Click Create Table.

Command-line

Use the mk command with the --table or -t flag. You can supply table schema information inline or via a JSON schema file. Optional parameters include --expiration, --description, --time_partitioning_type, --destination_kms_key, and --label. If you are creating a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

--time_partitioning_type and --destination_kms_key are not demonstrated here. For more information on --time_partitioning_type, see ingestion-time partitioned tables or partitioned tables. For more information on --destination_kms_key, see customer-managed encryption keys.

To create an empty table in an existing dataset with a schema definition, enter:

bq mk --table --expiration [INTEGER] --description [DESCRIPTION] --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]

Where:

  • [INTEGER] is the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current UTC time plus the integer value. If you set the expiration time when you create a table, the dataset's default table expiration setting is ignored.
  • [DESCRIPTION] is a description of the table in quotes.
  • [KEY:VALUE] is the key:value pair that represents a label. You can enter multiple labels using a comma-separated list.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is a dataset in your project.
  • [TABLE] is the name of the table you're creating.
  • [SCHEMA] is an inline schema definition in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] or the path to the JSON schema file on your local machine.

When you specify the schema on the command line, you cannot include a RECORD (STRUCT) type, you cannot include a column description, and you cannot specify the column's mode. All modes default to NULLABLE. To include descriptions, modes, and RECORD types, supply a JSON schema file instead.

Examples:

Enter the following command to create a table using an inline schema definition. This command creates a table named mytable in mydataset in your default project. The table expiration is set to 3600 seconds (1 hour), the description is set to This is my table, and the label is set to organization:development. The command uses the -t shortcut instead of --table. The schema is specified inline as: qtr:STRING,sales:FLOAT,year:STRING.

bq mk -t --expiration 3600 --description "This is my table" --label organization:development mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Enter the following command to create a table using a JSON schema file. This command creates a table named mytable in mydataset in your default project. The table expiration is set to 3600 seconds (1 hour), the description is set to This is my table, and the label is set to organization:development. The path to the schema file is /tmp/myschema.json.

bq mk --table --expiration 3600 --description "This is my table" --label organization:development mydataset.mytable /tmp/myschema.json

Enter the following command to create a table using an JSON schema file. This command creates a table named mytable in mydataset in myotherproject. The table expiration is set to 3600 seconds (1 hour), the description is set to This is my table, and the label is set to organization:development. The path to the schema file is /tmp/myschema.json.

bq mk --table --expiration 3600 --description "This is my table" --label organization:development myotherproject:mydataset.mytable /tmp/myschema.json

After the table is created, you can update the table's expiration, description, and labels. You can also modify the schema definition.

API

Call the tables.insert method with a defined table resource.

C#

Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery C# API reference documentation .


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

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)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .

TableId tableId = TableId.of(datasetName, tableName);
// Table field definition
Field field = Field.of(fieldName, LegacySQLTypeName.STRING);
// Table schema definition
Schema schema = Schema.of(field);
TableDefinition tableDefinition = StandardTableDefinition.of(schema);
TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
Table table = bigquery.create(tableInfo);

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Node.js API reference documentation .

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

async function createTable() {
  // Creates a new table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const datasetId = "my_new_dataset";
  // const tableId = "my_new_table";
  // const schema = "Name:string, Age:integer, Weight:float, IsMagic:boolean";

  // Create a client
  const bigqueryClient = new BigQuery();

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigqueryClient
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}
createTable();

PHP

Before trying this sample, follow the PHP setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery PHP API reference documentation .

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);

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

from google.cloud import bigquery

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

# TODO(developer): 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"

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Ruby

Before trying this sample, follow the Ruby setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Ruby API reference documentation .

require "google/cloud/bigquery"

def create_table dataset_id = "my_dataset"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "my_table"

  table = dataset.create_table table_id do |updater|
    updater.string  "full_name", mode: :required
    updater.integer "age",       mode: :required
  end

  puts "Created table: #{table_id}"
end

Creating a table from a query result

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

Console

Option 1: Use a DDL statement

Data definition language (DDL) statements allow you to create and modify tables using standard SQL query syntax.

For more information, see the CREATE TABLE statement page and the CREATE TABLE example: Creating a new table from an existing table.

Option 2: Use the GCP Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. In the navigation panel, in the Resources section, expand your project and select a dataset.

  3. If the Query editor is hidden, click Show editor at the top right of the window.

  4. Enter a valid SQL query in the Query editor text area.

  5. Click More below the editor, then select Query settings.

    Query settings

  6. Check the box to Set a destination table for query results.

    Set destination

  7. In the Destination section, select the appropriate Project name and Dataset name where the table will be created, and choose a Table name.

  8. In the Destination table write preference section, choose one of the following:

    • Write if empty — Writes the query results to the table only if the table is empty.
    • Append to table — Appends the query results to an existing table.
    • Overwrite table — Overwrites an existing table with the same name using the query results.
  9. (Optional) For Processing location, click Auto-select and choose your location.

  10. Click Run query. This creates a query job that writes the query results to the table you specified.

Alternatively, if you forget to specify a destination table before running your query, you can copy the cached results table to a permanent table by clicking the Save Results button below the editor.

Classic UI

Option 1: Use a DDL statement

Data definition language (DDL) statements allow you to create and modify tables using standard SQL query syntax.

For more information, see the CREATE TABLE statement page and the CREATE TABLE example: Creating a new table from an existing table.

Option 2: Use the classic web UI

  1. Go to the classic BigQuery web UI.
    Go to the classic BigQuery web UI

  2. Click the Compose query button.

  3. Enter a valid SQL query in the New Query text area.

  4. Click Show Options.

  5. In the Destination Table section, click Select Table.

  6. In the Select Destination Table dialog:

    1. For Project, choose a project to store the dataset and table.

    2. For Dataset, choose the dataset to store the table.

    3. In the Table ID field, enter a table name. The name must be unique in the destination dataset. The table name can be up to 1024 characters long and can contain only a-z, A-Z, 0-9, or _ (the underscore character).

    4. Click OK.

  7. In the Destination Table section, for Write Preference, choose one of the following:

    • Write if empty — Writes the query results to the table only if the table is empty.
    • Append to table — Appends the query results to an existing table.
    • Overwrite table — Overwrites an existing table with the same name using the query results.
  8. (Optional) For Processing Location, click Unspecified and choose your data's location.

  9. Click Run query. This creates a query job that writes the query results to the table you specified.

Alternately, if you forget to specify a destination table before running your query, you can copy the temporary table to a permanent table by clicking the Save as Table button in the results window.

CLI

Enter the bq query command and specify the --destination_table flag to create a permanent table based on the query results. Specify the use_legacy_sql=false flag to use standard SQL syntax. To write the query results to a table that is not in your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

Supply the --location flag and set the value to your location.

To control the write disposition for an existing destination table, specify one of the following optional flags:

  • --append_table — If the destination table exists, the query results are appended to it.
  • --replace — If the destination table exists, it is overwritten with the query results.

    bq --location=[LOCATION] query \
    --destination_table [PROJECT_ID]:[DATASET].[TABLE] \
    --use_legacy_sql=false '[QUERY]'
    

Where:

  • [LOCATION] is the name of the location used to process the query. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location by using the .bigqueryrc file.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset that contains the table to which you are writing the query results.
  • [TABLE] is the name of the table to which you're writing the query results.
  • [QUERY] is a query in standard SQL syntax.

If no write disposition flag is specified, the default behavior is to write the results to the table only if it is empty. If the table exists and it is not empty, the following error is returned: BigQuery error in query operation: Error processing job '[PROJECT_ID]:bqjob_123abc456789_00000e1234f_1': Already Exists: Table [PROJECT_ID]:[DATASET].[TABLE].

Examples:

Enter the following command to write query results to a destination table named mytable in mydataset. The dataset is in your default project. Since no write disposition flag is specified in the command, the table must be new or empty. Otherwise, an Already exists error is returned. The query retrieves data from the USA Name Data public dataset.

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data`.usa_names.usa_1910_current
WHERE
  gender = "M"
ORDER BY
  number DESC'

Enter the following command to use query results to overwrite a destination table named mytable in mydataset. The dataset is in your default project. The command uses the --replace flag to overwrite the destination table.

bq query \
--destination_table mydataset.mytable --replace \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

Enter the following command to append query results to a destination table named mytable in mydataset. The dataset is in my-other-project, not your default project. The command uses the --append flag to append the query results to the destination table.

bq query \
--append --use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

API

To save query results to a permanent table, call the jobs.insert method, configure a query job, and include a value for the destinationTable property. To control the write disposition for an existing destination table, configure the writeDisposition property.

To control the processing location for the query job, specify the location property in the jobReference section of the job resource.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

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)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .

To save query results to a permanent table, set the destination table to the desired TableId in a QueryJobConfiguration.

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

To save query results to a permanent table, create a QueryJobConfig and set the destination to the desired TableReference. Pass the job configuration to the query method.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
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,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

Creating a table that references an external data source

An external data source (also known as a federated data source) is a data source that you can query directly even though the data is not stored in BigQuery. Instead of loading or streaming the data, you create a table that references the external data source.

BigQuery offers support for querying data directly from:

You can query data in a supported external data source by creating a temporary or permanent table that references data stored in the external data source. For more information on working with external data sources, see:

Creating a table when you load data

When you load data into BigQuery, you can load data into a new table or partition, you can append data to an existing table or partition, or you can overwrite a table or partition. You do not need to create an empty table before loading data into it. You can create the new table and load your data at the same time.

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

For more information on loading data, see Introduction to loading data into BigQuery.

Controlling access to tables

You cannot assign access controls directly to tables. You can control table access by configuring access controls at the dataset level or at the project level.

Dataset-level access controls specify the operations users, groups, and service accounts are allowed to perform on tables in that specific dataset. If you assign only dataset-level permissions, you must also assign a primitive or predefined, project-level role that provides access to the project, for example, bigquery.user.

Instead of granting access to individual datasets, you can assign predefined, project-level IAM roles that grant permissions to all table data in all datasets in a project.

You can also create IAM custom roles. If you create a custom role, the permissions you grant depend on the table operations you want the user, group, or service account to be able to perform.

For more information on roles and permissions, see:

For information on working with table data, see Managing table data.

Using tables

Getting information about tables

You can get information or metadata about tables by:

  • Using the GCP Console or the classic BigQuery web UI
  • Using the bq show CLI command
  • Calling the tables.get API method.
  • Querying the INFORMATION_SCHEMA views (beta)

Required permissions

To get information about tables, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.get permissions. If you are granted bigquery.tables.get permissions at the project level, you can get information about all tables in the project. All predefined, project-level IAM roles include bigquery.tables.get permissions except for bigquery.jobUser and bigquery.user.

In addition, a user assigned the bigquery.user role has bigquery.datasets.create permissions. This allows a user assigned to the bigquery.user role to get information about tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Getting table information

To get information about tables:

Console

  1. In the navigation panel, in the Resources section, expand your project and select a dataset. Click the dataset name to expand it. This displays the tables and views in the dataset.

  2. Click the table name.

  3. Below the editor, click Details. This page displays the table's description and table information.

  4. Click the Schema tab to view the table's schema definition.

Classic UI

  1. In the navigation pane, click the down arrow icon down arrow icon to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

  2. Click the table name.

  3. Click Details. The Table Details page displays the table's description and table information.

    View table details

  4. Click the Schema tab to view the table's schema definition.

CLI

Issue the bq show command to display all table information. Use the --schema flag to display only table schema information. The --format flag can be used to control the output.

If you are getting information about a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Where:

  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.
  • [TABLE] is the name of the table.

Examples:

Enter the following command to display all information about mytable in mydataset. mydataset is in your default project.

bq show --format=prettyjson mydataset.mytable

Enter the following command to display all information about mytable in mydataset. mydataset is in myotherproject, not your default project.

bq show --format=prettyjson myotherproject:mydataset.mytable

Enter the following command to display only schema information about mytable in mydataset. mydataset is in myotherproject, not your default project.

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

Call the tables.get method and provide any relevant parameters.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

meta, err := client.Dataset(datasetID).Table(tableID).Metadata(ctx)
if err != nil {
	return err
}
// Print basic information about the table.
fmt.Printf("Schema has %d top-level fields\n", len(meta.Schema))
fmt.Printf("Description: %s\n", meta.Description)
fmt.Printf("Row in managed storage: %d\n", meta.NumRows)

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .

TableId tableId = TableId.of(projectId, datasetName, tableName);
Table table = bigquery.getTable(tableId);

PHP

Before trying this sample, follow the PHP setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery PHP API reference documentation .

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);

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

from google.cloud import bigquery

# TODO(developer): 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)

print(
    "Got table '{}.{}.{}'.".format(table.project, table.dataset_id, table.table_id)
)

# View table properties
print("Table schema: {}".format(table.schema))
print("Table description: {}".format(table.description))
print("Table has {} rows".format(table.num_rows))

Getting table information using INFORMATION_SCHEMA (beta)

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, tables, and views.

You can query the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_OPTIONS views to retrieve metadata about tables and views in a project. You can also query the INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.COLUMN_FIELD_PATHS views to retrieve metadata about the columns (fields) in a table.

The TABLES and TABLE_OPTIONS views also contain high-level information about views. For detailed information, query the INFORMATION_SCHEMA.VIEWS view instead.

TABLES view

When you query the INFORMATION_SCHEMA.TABLES view, the query results contain one row for each table or view in a dataset.

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables or views.

The INFORMATION_SCHEMA.TABLES view has the following schema:

Column name Data type Value
TABLE_CATALOG STRING The name of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table or view also referred to as the datasetId
TABLE_NAME STRING The name of the table or view also referred to as the tableId
TABLE_TYPE STRING The table type:
IS_INSERTABLE_INTO STRING YES or NO depending on whether the table supports DML INSERT statements
IS_TYPED STRING The value is always NO
CREATION_TIME TIMESTAMP The table's creation time

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.TABLES view except for is_typed which is reserved for future use. The metadata returned is for all tables in mydataset in your default project — myproject.

mydataset contains the following tables:

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

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES'

The results should look like the following:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

Example 2:

The following example retrieves all tables of type BASE TABLE from the INFORMATION_SCHEMA.TABLES view. The is_typed column is excluded. The metadata returned is for tables in mydataset in your default project — myproject.

Queries against the INFORMATION_SCHEMA.TABLES view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

The results should look like the following:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS view

When you query the INFORMATION_SCHEMA.TABLE_OPTIONS view, the query results contain one row for each table or view in a dataset.

Queries against the INFORMATION_SCHEMA.TABLE_OPTIONS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables or views.

The INFORMATION_SCHEMA.TABLE_OPTIONS view has the following schema:

Column name Data type Value
TABLE_CATALOG STRING The name of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table or view also referred to as the datasetId
TABLE_NAME STRING The name of the table or view also referred to as the tableId
OPTION_NAME STRING One of the name values in the options table
OPTION_TYPE STRING One of the data type values in the options table
OPTION_VALUE STRING One of the value options in the options table
Options table
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 The default lifetime, in days, of all partitions in a partitioned table
expiration_timestamp FLOAT64 The default lifetime, in days, of the table
kms_key_name STRING The name of the Cloud KMS key used to encrypt the table
friendly_name STRING The table's descriptive name
description STRING A description of the table
labels ARRAY<STRUCT<STRING, STRING>> An array of STRUCT's that represent the labels on the table

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

Example 1:

The following example retrieves the default table expiration times for all tables in mydataset in your default project (myproject) by querying the INFORMATION_SCHEMATA.TABLE_OPTIONS view.

Queries against the INFORMATION_SCHEMA.TABLE_OPTIONS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="expiration_timestamp"'

The results should look like the following:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | 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" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Example 2:

The following example retrieves metadata about all tables in mydataset that contain test data. The query uses the values in the description option to find tables that contain "test" anywhere in the description. mydataset is in your default project — myproject.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="description" AND option_value LIKE "%test%"'

The results should look like the following:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | 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"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS view

When you query the INFORMATION_SCHEMA.COLUMNS view, the query results contain one row for each column (field) in a table.

Queries against the INFORMATION_SCHEMA.COLUMNS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

The INFORMATION_SCHEMA.COLUMNS view has the following schema:

Column name Data type Value
TABLE_CATALOG STRING The name of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table also referred to as the datasetId
TABLE_NAME STRING The name of the table or view also referred to as the tableId
COLUMN_NAME STRING The name of the column
ORDINAL_POSITION INT64 The 1-indexed offset of the column within the table; if it's a pseudo column such as _PARTITIONTIME or _PARTITIONDATE, the value is NULL
IS_NULLABLE STRING YES or NO depending on whether the column's mode allows NULL values
DATA_TYPE STRING The column's standard SQL data type
IS_GENERATED STRING The value is always NEVER
GENERATION_EXPRESSION STRING The value is always NULL
IS_STORED STRING The value is always NULL
IS_HIDDEN STRING YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_UPDATABLE STRING The value is always NULL
IS_SYSTEM_DEFINED STRING YES or NO depending on whether the column is a pseudo column such as _PARTITIONTIME or _PARTITIONDATE
IS_PARTITIONING_COLUMN STRING YES or NO depending on whether the column is a partitioning column
CLUSTERING_ORDINAL_POSITION STRING The 1-indexed offset of the column within the table's clustering columns; the value is NULL if the table is not a clustered table

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMNS view for the population_by_zip_2010 table in the census_bureau_usa dataset. This dataset is part of the BigQuery public dataset program.

Because the table you're querying is in another project, the bigquery-public-data project, you add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

The following columns are excluded from the query results because they are currently reserved for future use:

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

Queries against the INFORMATION_SCHEMA.COLUMNS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
 FROM
   `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
 WHERE
   table_name="population_by_zip_2010"'

The results should look like the following. For readability, table_catalog and table_schema are excluded from the results:

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

COLUMN_FIELD_PATHS view

When you query the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view, the query results contain one row for each column nested within a RECORD (or STRUCT) column.

Queries against the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

The INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view has the following schema:

Column name Data type Value
TABLE_CATALOG >STRING The name of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table also referred to as the datasetId
TABLE_NAME STRING The name of the table or view also referred to as the tableId
COLUMN_NAME STRING The name of the column
FIELD_PATH STRING The path to a column nested within a `RECORD` or `STRUCT` column
DATA_TYPE STRING The column's standard SQL data type
DESCRIPTION STRING The column's description

For more information on dataset properties, see the dataset resource page in the REST API documentation. For more information on table and view properties, see the table resource page in the REST API documentation.

Examples

The following example retrieves metadata from the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view for the commits table in the github_repos dataset. This dataset is part of the BigQuery public dataset program.

Because the table you're querying is in another project, the bigquery-public-data project, you add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW]; for example, `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

The commits table contains the following nested and nested and repeated columns:

  • author: nested RECORD column
  • committer: nested RECORD column
  • trailer: nested and repeated RECORD column
  • difference: nested and repeated RECORD column

Your query will retrieve metadata about the author and difference columns.

Queries against the INFORMATION_SCHEMA.COLUMN_FIELD_PATHS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the tables.

To run the query:

Console

  1. Open the BigQuery web UI in the GCP Console.

    Go to the BigQuery web UI

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. Click Run.

CLI

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
 WHERE
   table_name="commits"
   AND column_name="author"
   OR column_name="difference"'

The results should look like the following. For readability, table_catalog and table_schema are excluded from the results.

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

Listing tables in a dataset

You can list tables in datasets by:

  • Using the GCP Console or the classic BigQuery web UI
  • Using the bq ls CLI command
  • Calling the tables.list API method

Required permissions

To list tables in a dataset, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.list permissions. If you are granted bigquery.tables.list permissions at the project level, you can list tables in any dataset in the project. All predefined, project-level IAM roles include bigquery.tables.list permissions except for bigquery.jobUser.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Listing tables

To list the tables in a dataset:

Console

  1. In the web UI, in the navigation pane, click the down arrow icon down arrow icon to the left of your dataset to expand it, or click the dataset name. This displays the tables and views in the dataset.

  2. Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.

Classic UI

  1. In the web UI, in the navigation pane, click the down arrow icon down arrow icon to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

  2. Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.

    View tables

Command-line

Issue the bq ls command. The --format flag can be used to control the output. If you are listing tables in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

Additional flags include:

  • --max_results or -n: An integer indicating the maximum number of results. The default value is 50.

    bq ls --format=pretty --max_results [INTEGER] [PROJECT_ID]:[DATASET]
    

Where:

  • [INTEGER] is an integer representing the number of tables to list.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.

When you run the command, the Type field displays either TABLE or VIEW. For example:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

Examples:

Enter the following command to list tables in dataset mydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to return more than the default output of 50 tables from mydataset. mydataset is in your default project.

bq ls --format=pretty --max_results 60 mydataset

Enter the following command to list tables in dataset mydataset in myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

To list tables using the API, call the tables.list method.

C#

Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery C# API reference documentation .


using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryListTables
{
    public void ListTables(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Retrieve list of tables in the dataset
        List<BigQueryTable> tables = client.ListTables(datasetId).ToList();
        // Display the results
        if (tables.Count > 0)
        {
            Console.WriteLine($"Tables in dataset {datasetId}:");
            foreach (var table in tables)
            {
                Console.WriteLine($"\t{table.Reference.TableId}");
            }
        }
        else
        {
            Console.WriteLine($"{datasetId} does not contain any tables.");
        }
    }
}

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

ts := client.Dataset(datasetID).Tables(ctx)
for {
	t, err := ts.Next()
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "Table: %q\n", t.TableID)
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .

DatasetId datasetId = DatasetId.of(projectId, datasetName);
Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
for (Table table : tables.iterateAll()) {
  // do something with the table
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Node.js API reference documentation .

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const datasetId = "my_dataset";

// Create a client
const bigqueryClient = new BigQuery();

async function listTables() {
  // Lists tables in "my_dataset".

  // List all tables in the dataset
  const [tables] = await bigqueryClient.dataset(datasetId).getTables();

  console.log('Tables:');
  tables.forEach(table => console.log(table.id));
}
listTables();

PHP

Before trying this sample, follow the PHP setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery PHP API reference documentation .

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);
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

from google.cloud import bigquery

# TODO(developer): 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)

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

Ruby

Before trying this sample, follow the Ruby setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Ruby API reference documentation .

require "google/cloud/bigquery"

def list_tables dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id

  puts "Tables in dataset #{dataset_id}:"
  dataset.tables.each do |table|
    puts "\t#{table.table_id}"
  end
end

Next steps

Bu sayfayı yararlı buldunuz mu? Lütfen görüşünüzü bildirin:

Şunun hakkında geri bildirim gönderin...

Yardım mı gerekiyor? Destek sayfamızı ziyaret edin.