Using Tables

This document describes how to create, update, and manage tables in Google BigQuery.

Overview

A BigQuery table contains individual records organized in rows, and a data type assigned to each column (also called a field). Individual fields within a record data type can contain nested and repeated fields.

Every table is defined by a schema that describes field names, types, and other information. You can specify the schema of a table during the initial table creation request, or you can create a table without a schema and declare the schema in the query or load job that first populates the table. If you need to change the schema later, you can update the schema.

For more information about data types and formats, see preparing data for BigQuery.

BigQuery supports the following table types:

  • Native tables: tables backed by native BigQuery storage.
  • External tables: tables backed by storage external to BigQuery. For more information, see Querying External Data Sources.
  • Views: Virtual tables defined by a SQL query. For more information, see Using views.

Creating a table

You can create a table in BigQuery:

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

Creating a table requires bigquery.tables.create permissions. The bigquery.dataEditor and bigquery.dataOwner predefined IAM roles include bigquery.tables.create permissions.

For more information on IAM roles in BigQuery, see Access Control.

Creating an empty table without a schema definition

When you create an empty table without a schema definition, you can define the schema when you load data into it. You can also update the schema manually before you load data.

To create an empty table without a schema definition:

Web UI

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

  2. On the Create table page:

    • For Source Data click Create empty table.
    • For Destination Table choose your dataset and enter the table name in the Destination table name field.
    • In the Schema section, delete the existing entry by clicking the delete (X) icon to the right of the entry. Deleting the entry creates an empty table with no schema definition.

    • Click Create Table.

      Create empty table

Command-line

Use the mk command with the --table or -t flag.

To create an empty table in an existing dataset, enter:

bq mk -t [DATASET].[TABLE]

Where:

  • [DATASET] is a dataset in your project.
  • [TABLE] is the name of the table you're creating.

API

Call tables.insert() with a table resource.

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 web UI
  • Provide the schema inline
  • Submit a JSON schema file using the command line tool
  • Provide the schema when calling the API's tables.insert() method.

After the table is created, you can load data into it by appending or overwriting it.

To create an empty table with a schema definition:

Web UI

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

  2. On the Create table page:

    • For Source Data, click Create empty table.
    • For Destination Table, choose your dataset and enter the table name in the Destination table name field.

    • For Schema, use Add Field to specify the schema manually or click Edit as Text and paste the schema in JSON format. When adding fields, consider the data types and modes.

      Create empty table schema

    • 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 file.

To create an empty table in an existing dataset using an inline schema definition, enter:

bq mk -t [DATASET].[TABLE] [SCHEMA]

Where:

  • [DATASET] is a dataset in your project.
  • [TABLE] is the name of the table you're creating.
  • [SCHEMA] is the schema definition (in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]).

For example:

bq mk -t mydataset.mytable qtr:STRING,sales:INTEGER,year:STRING

To create an empty table in an existing dataset using a JSON schema file stored on your local machine, enter:

bq mk -t [DATASET].[TABLE] [SCHEMA_FILE]

Where:

  • [DATASET] is a dataset in your project.
  • [TABLE] is the name of the table you're creating.
  • [SCHEMA_FILE] is the path to the JSON schema file on your local machine.

For example:

bq mk -t mydataset.mytable /tmp/myschema.json

API

Call tables.insert() with a table resource.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public void CreateTable(string datasetId, string tableId, BigQueryClient client)
{
    var dataset = client.GetDataset(datasetId);
    // Create schema for new table.
    var schema = new TableSchemaBuilder
    {
        { "title", BigQueryDbType.String },
        { "unique_words", BigQueryDbType.Int64 }
    }.Build();
    // Create the table if it doesn't exist.
    BigQueryTable table = dataset.GetOrCreateTable(tableId, schema);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

// Item represents a row item.
type Item struct {
	Name  string
	Count int
}

// Save implements the ValueSaver interface.
func (i *Item) Save() (map[string]bigquery.Value, string, error) {
	return map[string]bigquery.Value{
		"Name":  i.Name,
		"Count": i.Count,
	}, "", nil
}

	schema, err := bigquery.InferSchema(Item{})
	if err != nil {
		return err
	}
	table := client.Dataset(datasetID).Table(tableID)
	if err := table.Create(ctx, schema); err != nil {
		return err
	}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

TableId tableId = TableId.of(datasetName, tableName);
// Table field definition
Field field = Field.of(fieldName, Field.Type.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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset in which to create the table, e.g. "my_dataset"
// const datasetId = "my_new_dataset";

// The ID for the new table, e.g. "my_new_table"
// const tableId = "my_new_table";

// The schema of the new table, e.g. "Name:string, Age:integer, Weight:float, IsMagic:boolean"
// const schema = "Name:string, Age:integer, Weight:float, IsMagic:boolean";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

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

// Create a new table in the dataset
bigquery
  .dataset(datasetId)
  .createTable(tableId, options)
  .then((results) => {
    const table = results[0];
    console.log(`Table ${table.id} created.`);
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Example:
 * ```
 * $fields = [
 *     [
 *         'name' => 'field1',
 *         'type' => 'string',
 *         'mode' => 'required'
 *     ],
 *     [
 *         'name' => 'field2',
 *         'type' => 'integer'
 *     ],
 * ];
 * $schema = ['fields' => $fields];
 * create_table($projectId, $datasetId, $tableId, $schema);
 * ```
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId   The BigQuery table ID.
 * @param array  $schema    The BigQuery table schema.
 */
function create_table($projectId, $datasetId, $tableId, $schema)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $options = ['schema' => $schema];
    $table = $dataset->createTable($tableId, $options);
    return $table;
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def create_table(dataset_name, table_name, project=None):
    """Creates a simple table in the given dataset.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)

    if not dataset.exists():
        print('Dataset {} does not exist.'.format(dataset_name))
        return

    table = dataset.table(table_name)

    # Set the table schema
    table.schema = (
        bigquery.SchemaField('Name', 'STRING'),
        bigquery.SchemaField('Age', 'INTEGER'),
        bigquery.SchemaField('Weight', 'FLOAT'),
    )

    table.create()

    print('Created table {} in dataset {}.'.format(table_name, dataset_name))

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset to create table in"
# table_id   = "ID of the table to create"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id

dataset.create_table table_id

puts "Created table: #{table_id}"

Getting information about tables

You can get information about tables to which you have access using the web UI, the CLI, and the API.

Getting information about tables requires bigquery.tables.get permissions. The following BigQuery IAM roles include bigquery.tables.get permissions:

For more information on IAM roles and permissions in BigQuery, see Access control.

To get information about tables:

Web UI

  1. In the navigation pane, click the blue arrow 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

CLI

Issue the bq show command:

bq show --format=prettyjson [DATASET].[TABLE]

Where:

  • [DATASET] is the name of the dataset containing the table.
  • [TABLE] is the name of the table.

For example, the following command displays information about mytable in dataset mydataset.

bq show --format=prettyjson mydataset.mytable

API

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

Listing tables in a dataset

You can list tables to which you have access using the web UI, the CLI, and the API.

Listing tables requires bigquery.tables.list permissions. All BigQuery IAM roles include bigquery.tables.list permissions except for bigquery.jobUser.

For more information about IAM roles and permissions in BigQuery, see Access control.

To list the tables in a dataset:

Web UI

  1. In the web UI, in the navigation pane, click the blue arrow 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 to list the tables and views in a dataset:

bq ls --format=prettyjson [DATASET]

Where:

  • [DATASET] is the name of the dataset that contains the table.

API

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

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

var tables = _client.ListTables(datasetId).ToList();

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset to list tables in, e.g. "my_dataset"
// const datasetId = "my_dataset";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

// Lists all tables in the dataset
bigquery
  .dataset(datasetId)
  .getTables()
  .then((results) => {
    const tables = results[0];
    console.log('Tables:');
    tables.forEach((table) => console.log(table.id));
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 */
function list_tables($projectId, $datasetId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $tables = $dataset->tables();
    foreach ($tables as $table) {
        print($table->id() . PHP_EOL);
    }
}

/**
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId   The BigQuery table ID.
 */
function get_table($projectId, $datasetId, $tableId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    return $dataset->table($tableId);
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def list_tables(dataset_name, project=None):
    """Lists all of the tables in a given dataset.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)

    if not dataset.exists():
        print('Dataset {} does not exist.'.format(dataset_name))
        return

    for table in dataset.list_tables():
        print(table.name)

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset to create table in"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id

dataset.tables.each do |table|
  puts table.table_id
end

Updating table properties

You can update table properties using the web UI, CLI, or API. You can change:

  • The table's description
  • The table's schema definition
  • The table's labels

Updating tables requires bigquery.tables.update permissions. The following predefined IAM roles include bigquery.tables.update permissions:

For more information on IAM roles and permissions in BigQuery, see: access control.

Updating a table's description

To update a table's description:

Web UI

You cannot add a description when you create a table using the web UI. After the table is created, you can add a description on the Table Details page.

  1. In the navigation pane, select your table.

  2. On the Table Details page, click Details.

  3. In the Description section, click Describe this table to open the description box.

  4. Enter a description in the box. When you click away from the box, the text is saved.

    Table description

CLI

Issue the bq update command with the --description flag:

bq update --description "[DESCRIPTION]" [DATASET].[TABLE]

Where:

  • [DESCRIPTION] is the text describing the dataset in quotes.
  • [DATASET] is the name of the dataset that contains the table you're updating.
  • [TABLE] is the name of the table you're updating.

For example, the following command changes the description of mytable to "Description of mytable."

bq update --description "Description of mytable" mydataset.mytable

API

Call tables.update and use the description property to apply your table description.

Adding a schema to an empty table

If you create an empty table without a schema definition, you can update it by adding a schema definition. You can specify the schema definition when you load data into the empty table, or you can add the schema before you load data.

To add a schema definition to an empty table:

Web UI

  1. In the navigation pane, select your table.

  2. On the Table Details page, click Add New Fields.

  3. Click Edit as Text to add the schema in JSON format, or in the New Fields section:

    • For Name, type the field name.
    • For Type, choose the data type.

    • For Mode, choose NULLABLE or REPEATED.

      Update table schema

  4. When you are done adding fields, click Add to Table.

CLI

Issue the bq update command and provide the schema definition:

  bq update [DATASET].[TABLE] [SCHEMA]

Where:

  • [DATASET] is the name of the dataset that contains the table you're updating.
  • [TABLE] is the name of the table you're updating.
  • [SCHEMA] is the schema definition (in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]).

To add a schema definition to an empty table using a JSON schema file:

  1. Create a JSON file containing an array of field definitions: 

        [
     {
       "name": "[NAME]",
       "type": "[TYPE]",
       "mode": "[MODE]"
     },
     {
       "name": "[NAME]",
       "type": "[TYPE]",
       "mode": "[MODE]"
     },
     {
       "name": "[NAME]",
       "type": "[TYPE]",
       "mode": "[MODE]"
     }
    ]

    Where:

    • [NAME] is the field name.
    • [TYPE] is the data type.

    • [MODE] is NULLABLE or REPEATED.

  2. After creating your schema file, issue the following command to update the table's schema definition:

    bq update [DATASET].[TABLE] [SCHEMA_FILE]

    Where:

    • [DATASET] is the name of the dataset that contains the table you're updating.
    • [TABLE] is the name of the table you're updating.
    • [SCHEMA_FILE] is the schema definition file on your local machine.

    For example:

    bq update mydataset.mytable /tmp/mytable.json

API

Call tables.update or tables.patch. Use the schema property to apply your schema update.

Updating a table's schema definition

You can update the schema for an existing table by adding new fields to it. The new fields must be NULLABLE or REPEATED. You cannot add a REQUIRED field to an existing table schema.

Modifying fields is not permitted aside from making REQUIRED fields NULLABLE.

Web UI

In the web UI, you cannot modify or delete existing fields in the schema definition. However, you can add new fields. To add a field to a table's schema:

  1. In the navigation pane, select your table.

  2. On the Table Details page, click Add New Fields.

  3. In the New Fields section:

    • For Name, type the field name.
    • For Type, choose the data type.

    • For Mode, choose NULLABLE or REPEATED.

      Update table schema

  4. When you are done adding fields, click Add to Table.

CLI

To update a table schema inline, issue the bq update command and provide the schema definition:

bq update [DATASET].[TABLE] [SCHEMA]

Where:

  • [DATASET] is the name of the dataset that contains the table you're updating.
  • [TABLE] is the name of the table you're updating.
  • [SCHEMA] is the schema definition (in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]). When updating a table schema inline, you must provide the entire schema including your new field. For complex schemas, consider using the show command to pipe the schema to a file.

To update a table's schema using a schema file:

  1. Issue the bq show command and pipe the output to a file:

    bq --format=prettyjson show [DATASET].[TABLE] > [SCHEMA_FILE]

    Where:

    • [DATASET] is the name of the dataset that contains the table you're updating.
    • [TABLE] is the name of the table you're updating.
    • [SCHEMA_FILE] is the schema definition file written to your local machine.

    For example:

    bq --format=prettyjson show mydataset.mytable > myschema.json

  2. Delete everything from the file except the JSON array of field definitions. Then, add your new field to the array:

    [
 {
   "name": "[NAME]",
   "type": "[TYPE]"
 },
 {
   "name": "[NAME]",
   "type": "[TYPE]"
 },
 {
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 }
]

    Where:

    • [NAME] is the field name.
    • [TYPE] is the data type.
    • [MODE] is NULLABLE or REPEATED.

  3. After updating your schema file, issue the following command to update the table's schema:

    bq update [DATASET].[TABLE] [SCHEMA_FILE]

    Where:

    • [DATASET] is the name of the dataset that contains the table that you're updating.
    • [TABLE] is the name of the table that you're updating.
    • [SCHEMA_FILE] is the schema definition file on your local machine.

    For example:

    bq update mydataset.mytable myschema.json

API

Call tables.update or tables.patch. Use the schema property to apply your schema update.

Loading data into a table

You can load data when you create a table, or you can create an empty table and load the data later. When you load data, you can use schema auto-detect for supported data formats.

To load additional data into an existing table, append or overwrite the table, or use the Data Manipulation Language to perform bulk inserts or updates.

Browsing table data

Web UI

  1. In the web UI, in the navigation pane, click the blue arrow 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 to browse.

  3. Click Preview. A sample set of data is displayed.

    Table preview

Command-line

Issue the bq head command with the --max_rows flag to list a particular number of rows in the table. If --max_rows is not specified, the default is 100.

bq head --max_rows=[INTEGER] [DATASET].[TABLE]

Where:

  • [INTEGER] is the number of rows to display.
  • [DATASET] is the name of the dataset containing the table.
  • [TABLE] is the name of the table to browse.

Because the bq head command does not create a query job, bq head commands do not appear in your query history, and you are not charged for them.

For example, the following command lists all fields in the first 10 rows in mydataset.mytable.

bq head --max_rows=10 mydataset.mytable

To list a subset of fields (including nested fields) when you display table data, use the --selected_fields flag:

bq head --selected_fields=[FIELDS] [DATASET].[TABLE]

Where:

  • [FIELDS] is a comma-separated list of fields.
  • [DATASET] is the name of the dataset containing the table.
  • [TABLE] is the name of the table to browse.

For example, the following command lists field1 and field2 in the first 10 rows in mydataset.mytable.

bq head --max_rows=10 --selected_fields=field1,field2 mydataset.mytable

API

Browse through a table's data by calling bigquery.tabledata.list, specifying the name of the table and an optional row offset. This method lets you specify the maximum number of rows to return per page. If you request a row index beyond the last row, the method will return successfully, but without a rows property.

Values are returned wrapped in a JSON object that you must parse, as described in the reference documentation.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public int TableDataList(
    string datasetId, string tableId, int pageSize, BigQueryClient client)
{
    int recordCount = 0;
    var result = client.ListRows(datasetId, tableId, null,
        new ListRowsOptions { PageSize = pageSize });
    // If there are more rows than were returned in the first page of results, 
    // iterating over the rows will lazily evaluate the results each time, 
    // making further requests as necessary.
    foreach (var row in result)
    {
        Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
        recordCount++;
    }
    return recordCount;
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

The Google Cloud Client Library for Go automatically paginates by default, so you do not need to implement pagination yourself, for example: 

table := client.Dataset(datasetID).Table(tableID)
it := table.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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

Page<List<FieldValue>> tableData =
    bigquery.listTableData(datasetName, tableName, TableDataListOption.pageSize(100));
for (List<FieldValue> row : tableData.iterateAll()) {
  // do something with the row
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

The Google Cloud Client Library for Node.js automatically paginates by default, so you do not need to implement pagination yourself, for example: 

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

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset of the table to browse, e.g. "my_dataset"
// const datasetId = "my_dataset";

// The ID of the table to browse, e.g. "my_table"
// const tableId = "my_table";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

// Lists rows in the table
bigquery
  .dataset(datasetId)
  .table(tableId)
  .getRows()
  .then((results) => {
    const rows = results[0];
    console.log('Rows:');
    rows.forEach((row) => console.log(row));
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

Pagination happens automatically in the Google Cloud Client Library for PHP using the generator function rows, which fetches the next page of results during iteration. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Browse a bigquery table.
 * Example:
 * ```
 * browse_table($projectId, $datasetId, $tableId);
 * ```
 *
 * @param string $projectId  The Google project ID.
 * @param string $datasetId  The BigQuery dataset ID.
 * @param string $tableId    The BigQuery table ID.
 * @param string $maxResults The number of results to return at a time.
 * @param string $startIndex The row index to start on.
 *
 * @return int number of rows returned
 */
function browse_table($projectId, $datasetId, $tableId, $maxResults = 10, $startIndex = 0)
{
    $options = [
        'maxResults' => $maxResults,
        'startIndex' => $startIndex
    ];
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    $numRows = 0;
    foreach ($table->rows($options) as $row) {
        print('---');
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, $value);
        }
        $numRows++;
    }

    return $numRows;
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def list_rows(dataset_name, table_name, project=None):
    """Prints rows in the given table.

    Will print 25 rows at most for brevity as tables can contain large amounts
    of rows.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)
    table = dataset.table(table_name)

    if not table.exists():
        print('Table {}:{} does not exist.'.format(dataset_name, table_name))
        return

    # Reload the table so that the schema is available.
    table.reload()

    # Load at most 25 results.
    rows = itertools.islice(table.fetch_data(), 25)

    # Use format to create a simple table.
    format_string = '{!s:<16} ' * len(table.schema)

    # Print schema field names
    field_names = [field.name for field in table.schema]
    print(format_string.format(*field_names))

    for row in rows:
        print(format_string.format(*row))

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

Pagination happens automatically in the Google Cloud Client Library for Ruby using Table#data and Data#next. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset containing table"
# table_id   = "ID of the table to display data for"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id
table    = dataset.table table_id

table.data.each do |row|
  row.each      do |column_name, value|
    puts "#{column_name} = #{value}"
  end
end

Copying a table

You can copy a table using the web UI, the command-line tool, or the API.

Copying tables requires bigquery.tables.create permissions. The following predefined IAM roles include bigquery.tables.create permissions:

In addition, you must have READ permission (bigquery.dataViewer) to the source dataset of the table being copied, and WRITE permission (bigquery.dataEditor) to the destination dataset.

For more information on IAM roles and permissions in BigQuery, see access control.

Table copies are subject to the following limits:

  • Copy table jobs are subject to BigQuery's quota policies.
  • 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.

Web UI

When you use the BigQuery web UI to copy a table, you cannot overwrite an existing table in the destination dataset. You can, however, use the command-line tool to overwrite a destination table during a table copy.

To copy a table:

  1. Click the down arrow display menu icon next to the table you want to copy and click Copy Table.

  2. In the Copy Table dialog:

    • For Destination project, choose the project that will store the copied table.
    • For Destination dataset, select the dataset where you want to store the copied table.

    • For Destination table, enter a name for the new table. The name must be unique in the dataset where the table will belong. The table name can be up to 1024 characters long and can contain only a-z, A-Z, 0-9, or _ (the underscore character).

      Table copy

  3. Click OK to start the copy job.

Command-line

To copy a table, issue the bq cp command.

bq cp [SOURCE_TABLE] [DESTINATION_TABLE]

Where:

  • [SOURCE_TABLE] is the table you're copying.
  • [DESTINATION_TABLE] is the name of the table in the destination dataset.

For example, the following command copies mydataset.mytable to mydataset2.mytable2:

bq cp mydataset.mytable mydataset2.mytable2

You can use additional flags to control the write disposition of the destination table:

  • -a appends the table to an existing table
  • -f overwrites existing destination tables and doesn't prompt you
  • -n prevents overwriting an existing table

To copy a table and to overwrite a destination table with the same name, enter:

bq cp -f mydataset.mytable mydataset2.mytable

You can also copy tables across different projects by specifying the project id in the source and destination path:

bq cp 123456789123:mydataset.mytable 0987654321098:mydataset2.mytable2

API

To copy a table using the API:

You can copy an existing table through the API by calling the bigquery.jobs.insert method, and configuring a copy job. You must specify the following values in your job configuration:

"copy": {
      "sourceTable": {       // Required
        "projectId": string, // Required
        "datasetId": string, // Required
        "tableId": string    // Required
      },
      "destinationTable": {  // Required
        "projectId": string, // Required
        "datasetId": string, // Required
        "tableId": string    // Required
      },
      "createDisposition": string,  // Optional
      "writeDisposition": string,   // Optional
    },

Where sourceTable provides information about the table to be copied, destinationTable provides information about the new table, createDisposition specifies whether to create the table if it doesn't exist, and writeDisposition specifies whether to overwrite or append to an existing table.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public void CopyTable(
    string datasetId, string tableIdToBeCopied, string newTableId, BigQueryClient client)
{
    var table = client.GetTable(datasetId, tableIdToBeCopied);
    string query = $"SELECT * FROM {table}";
    var destination = client.GetTableReference(datasetId, newTableId);
    BigQueryJob job = client.CreateQueryJob(query,
        new QueryOptions { DestinationTable = destination });
    // Wait for the job to complete.
    job.GetQueryResults();
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

dataset := client.Dataset(datasetID)
copier := dataset.Table(dstID).CopierFrom(dataset.Table(srcID))
copier.WriteDisposition = bigquery.WriteTruncate
job, err := copier.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
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

Job job = table.copy(datasetName, tableName);
// Wait for the job to complete.
try {
  Job completedJob = job.waitFor(WaitForOption.checkEvery(1, TimeUnit.SECONDS),
      WaitForOption.timeout(3, TimeUnit.MINUTES));
  if (completedJob != null && completedJob.getStatus().getError() == null) {
    // Job completed successfully
  } else {
    // Handle error case
  }
} catch (InterruptedException | TimeoutException e) {
  // Handle interrupted wait
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset of the table to copy, e.g. "my_src_dataset"
// const srcDatasetId = "my_src_dataset";

// The ID of the table to copy, e.g. "my_src_table"
// const srcTableId = "my_src_table";

// The ID of the destination dataset, e.g. "my_dest_dataset"
// const destDatasetId = "my_dest_dataset";

// The ID of the destination table, e.g. "my_dest_table"
// const destTableId = "my_dest_table";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

let job;

// Copies the table contents into another table
bigquery
  .dataset(srcDatasetId)
  .table(srcTableId)
  .copy(bigquery.dataset(destDatasetId).table(destTableId))
  .then((results) => {
    job = results[0];
    console.log(`Job ${job.id} started.`);

    // Wait for the job to finish
    return job.promise();
  })
  .then((results) => {
    // Get the job's status
    return job.getMetadata();
  }).then((metadata) => {
    // Check the job's status for errors
    const errors = metadata[0].status.errors;
    if (errors && errors.length > 0) {
      throw errors;
    }
  }).then(() => {
    console.log(`Job ${job.id} completed.`);
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/**
 * @param string $projectId             The Google project ID.
 * @param string $datasetId             The BigQuery dataset ID.
 * @param string $sourceTableId         The BigQuery table ID to copy from.
 * @param string $destinationTableId    The BigQuery table ID to copy to.
 */
function copy_table($projectId, $datasetId, $sourceTableId, $destinationTableId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $sourceTable = $dataset->table($sourceTableId);
    $destinationTable = $dataset->table($destinationTableId);
    $job = $sourceTable->copy($destinationTable);
    // poll the job until it is complete
    $backoff = new ExponentialBackoff(10);
    $backoff->execute(function () use ($job) {
        print('Waiting for job to complete' . PHP_EOL);
        $job->reload();
        if (!$job->isComplete()) {
            throw new Exception('Job has not yet completed', 500);
        }
    });
    // check if the job has errors
    if (isset($job->info()['status']['errorResult'])) {
        $error = $job->info()['status']['errorResult']['message'];
        printf('Error running job: %s' . PHP_EOL, $error);
    } else {
        print('Table copied successfully' . PHP_EOL);
    }
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def copy_table(dataset_name, table_name, new_table_name, project=None):
    """Copies a table.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)
    table = dataset.table(table_name)

    # This sample shows the destination table in the same dataset and project,
    # however, it's possible to copy across datasets and projects. You can
    # also copy muliple source tables into a single destination table by
    # providing addtional arguments to `copy_table`.
    destination_table = dataset.table(new_table_name)

    # Create a job to copy the table to the destination table.
    job_id = str(uuid.uuid4())
    job = bigquery_client.copy_table(
        job_id, destination_table, table)

    # Create the table if it doesn't exist.
    job.create_disposition = (
        google.cloud.bigquery.job.CreateDisposition.CREATE_IF_NEEDED)

    job.begin()  # Start the job.
    print('Waiting for job to finish...')
    job.result()

    print('Table {} copied to {}.'.format(table_name, new_table_name))

Appending and overwriting table data

You can load additional data into a table either from source files or by appending query results. The schema of the loaded data must match the schema of the existing table, but you can update the schema before appending or you can use the bq query command with the --schema_update_option flag.

You use the Write preference option to specify what action to take when you load data from a source file or from a query result. Options include:

  • Write if empty: Writes the data only if the table is empty.
  • Append to table: Appends the data to the end of the table.
  • Overwrite table: Replaces all existing data in a table.

BigQuery allows you to load data in the following formats:

  • Avro
  • CSV
  • JSON
  • Cloud Datastore backups

For more information, see data formats.

Using query results

To write to a table using query results:

Web UI

  1. Click Compose Query.

  2. Compose your query in the New Query box.

  3. Click Show Options.

  4. For Destination Table, click Select Table.

  5. In the Select Destination Table dialog:

    • For Project, select the appropriate project.
    • For Dataset, choose the dataset containing the table you are writing the query results to.
    • For Table ID type the name of the table you are writing the data to. If the table does not exist, BigQuery creates it for you.
    • Click OK.

  6. For Write Preference, choose one of the following:

    • Click Write if empty to overwrite the table using query results only if the destination table is empty.
    • Click Append to table to append the results to an existing table.

    • Click Overwrite table to replace the table data with the query results.

      Write query results

  7. Click Run Query.

CLI

Issue the bq query command with the --destination_table flag and one of the following flags:

  • --append_table: When a destination table is specified, append the query result to the table.
  • --replace: If true, erase the contents of the table before writing the query results.

The following command issues a standard SQL query and overwrites the destination table with the query results:

bq query --use_legacy_sql=false --replace=true --destination_table=[DATASET].[TABLE] '[QUERY]'

The following command issues a standard SQL query and appends the destination table with the query results:

bq query --use_legacy_sql=false --append_table=true --destination_table=[DATASET].[TABLE] '[QUERY]'

Where:

  • [DATASET] is the name of the dataset that contains the table you're overwriting.
  • [TABLE] is the name of the table you're overwriting with the query results.
  • [QUERY] is a standard SQL query.

For example, the following command queries mytable2 and overwrites mydataset.mytable with the query results:

bq query --use_legacy_sql=false --replace=true --destination_table=mydataset.mytable 'SELECT field1,field2 FROM `mydataset.mytable2`'

The following command queries mytable2 and appends the results to mydataset.mytable:

bq query --use_legacy_sql=false --append_table=true --destination_table=mydataset.mytable 'SELECT field1,field2 FROM `mydataset.mytable2`'

You can also use the --schema_update_option flag when you append to a table or overwrite a table. When specified, the schema of the destination table is updated with the schema of the new data. The --schema_update_option flag supports the following options:

  • ALLOW_FIELD_ADDITION: Allows new fields to be added to the schema definition.
  • ALLOW_FIELD_RELAXATION: Allows changing REQUIRED fields to NULLABLE.

For more information about updating a table schema, see Updating a table's schema definition.

API

Call jobs.insert using the query child property and specify the query configuration options.

Using data from a source file

When you write data from a source file, you can use the data to:

  • Load data into an empty table
  • Append data to an existing table
  • Overwrite the contents of an existing table

The source file can be:

  • A Cloud Datastore backup stored in Google Cloud Storage
  • CSV, JSON (newline delimited), or AVRO files stored in Google Cloud Storage
  • A CSV, JSON, or Avro file upload (from a readable data source such as your local machine)

To append or overwrite table data using a source file:

Web UI

  1. Click the down arrow display menu icon next to the dataset containing the table you want to update and click Create new table.

  2. On the Create Table page, in the Source Data section:

    • For Source Data, leave Create from source selected.

    • For Location, choose the appropriate option:

      • Choose File upload, click Choose file, and select your file.
      • Choose Google Cloud Storage and then enter the Cloud Storage URI in the following format: gs://[BUCKET]/[OBJECT]. If you are loading multiple files that share a common base-name, you can use a wildcard in the Cloud Storage URI.

    • For File format, choose the appropriate option:

      • For Google Cloud Storage, choose CSV, JSON (newline delimited), Avro, or Cloud Datastore backup.
      • For File upload, choose CSV, JSON (newline delimited), or Avro.

  3. In the Destination Table section:

    • For Table name, choose the appropriate dataset and enter the name of the table you're overwriting or appending.
    • For Table type, leave Native table selected.

  4. In the Schema section, use Add Field to add the schema definition manually, or click Edit as Text to paste a schema definition in JSON format. For supported data formats, you can also choose Automatically detect to enable schema auto-detection.

  5. In the Options section, for Write preference choose:

    • Write if empty to overwrite the table only if the table is empty. If the table is not empty, an error is returned.
    • Append to table to append the data to the table.

    • Overwrite table to overwrite the existing data.

      Append file to table

  6. Click Create Table.

CLI

Issue the bq load command. If the table already exists, the default is to append the data. You can specify the --replace flag to overwrite the table.

The following command loads data from a source file and overwrites the destination table:

bq load --replace=true [DATASET].[TABLE] [SOURCE] [SCHEMA]

The following command appends the destination table with data from a source file:

bq load [DATASET].[TABLE] [SOURCE] [SCHEMA]

Where:

  • [DATASET] is the name of the dataset that contains the table you're overwriting.
  • [TABLE] is the name of the table you're overwriting with the source file.
  • [SOURCE] is the name of a local file or a comma-separated list of Cloud Storage URI paths. If you are loading multiple files that share a common base-name, you can use a wildcard in the Cloud Storage URI.
  • [SCHEMA] is a schema file on your local machine or an inline schema definition (in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]). For supported data formats, you can also use schema auto-detection.

For example, the following command overwrites mydataset.mytable with a source file (source_data.csv) in Cloud Storage and uses a JSON schema file named schema.json:

bq load --replace=true mydataset.mytable gs://bucket/source_data.csv ./schema.json

The following command overwrites mydataset.mytable with a local file and uses schema auto-detection:

bq load --replace=true --autodetect=true mydataset.mytable ./source_data.csv

The following command appends mydataset.mytable with data from a file in Cloud Storage (source_data.json) using an inline schema definition:

bq load mydataset.mytable gs://bucket/source_data.json field1:STRING,field2:INTEGER

You can also use the --schema_update_option flag when you append to a table or overwrite a table. When specified, the schema of the destination table is updated with the schema of the new data. The --schema_update_option flag supports the following options:

  • ALLOW_FIELD_ADDITION: Allows new fields to be added to the schema definition.
  • ALLOW_FIELD_RELAXATION: Allows changing REQUIRED fields to NULLABLE.

For more information on updating a table schema, see Updating a table's schema definition.

API

Call jobs.insert using the load child property and specify the load configuration options.

Modifying table data

Using the standard SQL dialect, you can modify data in a table using DML statements. The legacy SQL dialect does not support either updating or deleting data.

To update or delete data using legacy SQL, you must delete the table and then recreate the table with new data. Alternatively, you could write a query that modifies the data and write the results to a destination table.

Currently, you cannot delete fields from an existing table. To work around this limitation, you can run a query that excludes the fields you want to remove and overwrite the table with the query result.

Exporting table data

You can export table data in CSV, JSON, or Avro format. Currently, you must export data into a Cloud Storage bucket. Exporting to your local machine is not supported; however, you can download a query result in JSON or CSV format using the web UI.

For more information, see exporting data from BigQuery.

Assigning access controls to tables

You share access to BigQuery data using project-level IAM roles and dataset-level access controls. Currently, you cannot apply access controls to a table or a view. For more information, see Access Control.

You use the dataset's access controls to determine the users and groups allowed to access the tables in the dataset. For example, if you assign the bigquery.dataOwner role to a user at the project level, that user can create, update, get, and delete tables in all of the project's datasets. If you assign the bigquery.dataOwner role at the dataset level, the user can create, update, get, and delete tables only in that dataset. For more information about project- level IAM roles and dataset access controls, see Access Control.

For more information on configuring dataset access controls, see Assigning access controls to datasets.

Deleting a table

You can delete a table using the web UI, CLI, or API. When you delete a table, any data in the table is also deleted. To automatically delete tables after a specified period of time, set the default table expiration for the dataset.

Deleting a table requires bigquery.tables.delete permissions. The following predefined IAM roles include bigquery.tables.delete permissions:

For more information on IAM roles and permissions in BigQuery, see: access control.

To delete a table:

Web UI

  1. Click the down arrow icon down arrow icon next to your table name in the navigation, then click Delete table.

  2. When prompted, click OK to confirm.

Command-line

Use the bq rm command with the -t flag to delete a table. When you use the CLI to remove a table, you must confirm the command. You can use the -f flag to skip confirmation:

bq rm -f -t [DATASET].[TABLE]

Where:

  • [DATASET] is the name of the dataset.
  • [TABLE] is the name of the table you're deleting.

For example, the following command removes mytable in mydataset:

bq rm -t mydataset.mytable

API

Call the tables.delete() method and specify the appropriate parameters.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
{
    client.DeleteTable(_projectId, datasetId, tableId);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

table := client.Dataset(datasetID).Table(tableID)
if err := table.Delete(ctx); err != nil {
	return err
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

Boolean deleted = bigquery.delete(datasetName, tableName);
if (deleted) {
  // the table was deleted
} else {
  // the table was not found
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

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

// The project ID to use, e.g. "your-project-id"
// const projectId = "your-project-id";

// The ID of the dataset of the table to delete, e.g. "my_dataset"
// const datasetId = "my_dataset";

// The ID of the table to delete, e.g. "my_table"
// const tableId = "my_table";

// Instantiates a client
const bigquery = BigQuery({
  projectId: projectId
});

// Deletes the table
bigquery
  .dataset(datasetId)
  .table(tableId)
  .delete()
  .then(() => {
    console.log(`Table ${tableId} deleted.`);
  })
  .catch((err) => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId   The BigQuery table ID.
 */
function delete_table($projectId, $datasetId, $tableId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    $table->delete();
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def delete_table(dataset_name, table_name, project=None):
    """Deletes a table in a given dataset.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)
    table = dataset.table(table_name)

    table.delete()

    print('Table {}:{} deleted.'.format(dataset_name, table_name))

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset delete table from"
# table_id   = "ID of the table to delete"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id
table    = dataset.table table_id

table.delete

puts "Deleted table: #{table_id}"

Undeleting a table

It's possible to undelete a table within two days of table deletion by using table decorators and the copy command. Undeletions are performed on a best-effort basis and are not guaranteed. Tables from deleted datasets and deleted tables replaced with a new table of the same name cannot be undeleted.

To undelete a table, determine a UNIX timestamp of when the table existed (in milliseconds). Then, copy the table at that time to a new table. The new table must have a different name than the deleted table.

For example, the following statement copies mydataset.mytable at the time 1418864998000 into a new table mydataset.newtable:

bq cp mydataset.mytable@1418864998000 mydataset.newtable

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Not now Use the app

Send feedback about...

This page
Documentation feedback
BigQuery
Product feedback