Create and use tables
This document describes how to create and use standard (built-in) tables in BigQuery. For information about creating other table types, see:
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 about managing tables including updating table properties, copying a table, and deleting a table, see Managing tables.
Before you begin
Before creating a table in BigQuery, first:
- Set up a project by following a BigQuery getting started guide.
- Create a BigQuery dataset.
- Optionally, read Introduction to tables to understand table limitations, quotas, and pricing.
Table naming
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 Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), Zs (space). For more information, see General Category.
The following are all examples of valid table names:
table 01
, ग्राहक
, 00_お客様
, étudiant-01
.
Caveats:
- Table names are case-sensitive by default.
mytable
andMyTable
can coexist in the same dataset, unless they are part of a dataset with case-sensitivity turned off. - Some table names and table name prefixes are reserved. If you receive an error saying that your table name or prefix is reserved, then select a different name and try again.
If you include multiple dot operators (
.
) in a sequence, the duplicate operators are implicitly stripped.For example, this:
project_name....datasest_name..table_name
Becomes this:
project_name.dataset_name.table_name
Create tables
You can create a table in BigQuery in the following ways:
- Manually using the Google Cloud console or the
bq
command-line toolbq mk
command. - Programmatically by calling the
tables.insert
API method. - By using the client libraries.
- From query results.
- By defining a table that references an external data source.
- When you load data.
- By using a
CREATE TABLE
data definition language (DDL) statement.
Required permissions
To create a table, you need the following IAM permissions:
bigquery.tables.create
bigquery.tables.updateData
bigquery.jobs.create
Additionally, you might require the bigquery.tables.getData
permission to
access the data that you write to the table.
Each of the following predefined IAM roles includes the permissions that you need in order to create a table:
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(includes thebigquery.jobs.create
permission)roles/bigquery.user
(includes thebigquery.jobs.create
permission)roles/bigquery.jobUser
(includes thebigquery.jobs.create
permission)
Additionally, if you have the bigquery.datasets.create
permission, you can
create and update tables in the datasets that you create.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Create an empty table with a schema definition
You can create an empty table with a schema definition in the following ways:
- Enter the schema using the Google Cloud console.
- Provide the schema inline using the
bq
command-line tool. - Submit a JSON schema file using the
bq
command-line tool. - Provide the schema in a table resource
when calling the API's
tables.insert
method.
For more information about 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
In the Google Cloud console, go to the BigQuery page.
- In the Explorer pane, expand your project, and then select a dataset.
- In the Dataset info section, click Create table.
- In the Create table panel, specify the following details:
- In the Source section, select Empty table in the Create table from list.
- In the Destination section, specify the following details:
- For Dataset, select the dataset in which you want to create the table.
- In the Table field, enter the name of the table that you want to create.
- Verify that the Table type field is set to Native table.
- In the Schema section, enter the schema
definition.
You can enter schema information manually by using one of
the following methods:
- Option 1: Click Edit as text and paste the schema in the form of a
JSON array. When you use a JSON array, you generate the schema using the
same process as creating a JSON schema file.
You can view the schema of an existing table in JSON format by entering the following
command:
bq show --format=prettyjson dataset.table
- Option 2: Click Type, and Mode. Add field and enter the table schema. Specify each field's Name,
- Option 1: Click Edit as text and paste the schema in the form of a
JSON array. When you use a JSON array, you generate the schema using the
same process as creating a JSON schema file.
You can view the schema of an existing table in JSON format by entering the following
command:
- Optional: Specify Partition and cluster settings. For more information, see Creating partitioned tables and Creating and using clustered tables.
- Optional: In the Advanced options section, if you want to use a customer-managed encryption key, then select the Use a customer-managed encryption key (CMEK) option. By default, BigQuery encrypts customer content stored at rest by using a Google-managed key.
- Click Create table.
SQL
The following example creates a table named newtable
that expires on
January 1, 2023:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE TABLE mydataset.newtable ( x INT64 OPTIONS (description = 'An optional INTEGER field'), y STRUCT < a ARRAY <STRING> OPTIONS (description = 'A repeated STRING field'), b BOOL > ) OPTIONS ( expiration_timestamp = TIMESTAMP '2023-01-01 00:00:00 UTC', description = 'a table that expires in 2023', labels = [('org_unit', 'development')]);
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Use the bq 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_field
--time_partitioning_type
--range_partitioning
--clustering_fields
--destination_kms_key
--label
--time_partitioning_field
, --time_partitioning_type
,
--range_partitioning
, --clustering_fields
, and --destination_kms_key
are not demonstrated here. Refer to the following links for more information
on these optional parameters:
- For more information about
--time_partitioning_field
,--time_partitioning_type
, and--range_partitioning
see partitioned tables. - For more information about
--clustering_fields
, see clustered tables. - For more information about
--destination_kms_key
, see customer-managed encryption keys.
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
.
To create an empty table in an existing dataset with a schema definition, enter the following:
bq mk \ --table \ --expiration integer \ --description description \ --label key_1:value_1 \ --label key_2:value_2 \ project_id:dataset.table \ schema
Replace the following:
- 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_1:value_1 and key_2:value_2 are key-value pairs that specify labels.
- 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.
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.
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.
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.
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.
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.
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.
Create an empty table without a schema definition
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.
Create a table from a query result
To create a table from a query result, write the results to a destination table.
Console
Open the BigQuery page in the Google Cloud console.
In the Explorer panel, expand your project and select a dataset.
Enter a valid SQL query.
Click More and then select Query settings.
Select the Set a destination table for query results option.
In the Destination section, select the Dataset in which you want to create the table, and then choose a Table Id.
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.
Optional: For Data location, choose your location.
To update the query settings, click Save.
Click Run. 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 above the editor.
SQL
The following example uses the
CREATE TABLE
statement
to create the trips
table from data in the public
bikeshare_trips
table:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE TABLE mydataset.trips AS ( SELECT bikeid, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips );
Click
Run.
For more information about how to run queries, see Running interactive queries.
For more information, see Creating a new table from an existing table.
bq
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 GoogleSQL 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
.
Optional: 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'
Replace the following:
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 toasia-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 GoogleSQL 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_table
flag to append
the query results to the destination table.
bq query \ --append_table \ --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'
The output for each of these examples looks like the following. For readability, some output is truncated.
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
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.
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.
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.
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.
Create a table that references an external data source
An external data source is a data source that you can query directly from BigQuery, even though the data is not stored in BigQuery storage. For example, you might have data in a different Google Cloud database, in files in Cloud Storage, or in a different cloud product altogether that you would like to analyze in BigQuery, but that you aren't prepared to migrate.
For more information, see Introduction to external data sources.
Create 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 about loading data, see Introduction to loading data into BigQuery.
Control access to tables
To configure access to tables and views, you can grant an IAM role to an entity at the following levels, listed in order of range of resources allowed (largest to smallest):
- a high level in the Google Cloud resource hierarchy such as the project, folder, or organization level
- the dataset level
- the table or view level
You can also restrict data access within tables, by using the following methods:
Access with any resource protected by IAM is additive. For example, if an entity does not have access at the high level such as a project, you could grant the entity access at the dataset level, and then the entity will have access to the tables and views in the dataset. Similarly, if the entity does not have access at the high level or the dataset level, you could grant the entity access at the table or view level.
Granting IAM roles at a higher level in the Google Cloud resource hierarchy such as the project, folder, or organization level gives the entity access to a broad set of resources. For example, granting a role to an entity at the project level gives that entity permissions that apply to all datasets throughout the project.
Granting a role at the dataset level specifies the operations an entity is allowed to perform on tables and views in that specific dataset, even if the entity does not have access at a higher level. For information on configuring dataset-level access controls, see Controlling access to datasets.
Granting a role at the table or view level specifies the operations an entity is allowed to perform on specific tables and views, even if the entity does not have access at a higher level. For information on configuring table-level access controls, see Controlling access to tables and views.
You can also create IAM custom roles. If you create a custom role, the permissions you grant depend on the specific operations you want the entity to be able to perform.
You can't set a "deny" permission on any resource protected by IAM.
For more information about roles and permissions, see Understanding roles in the IAM documentation and the BigQuery IAM roles and permissions.
Get information about tables
You can get information or metadata about tables in the following ways:
- Using the Google Cloud console.
- Using the
bq
command-line toolbq show
command. - Calling the
tables.get
API method. - Using the client libraries.
- Querying the
INFORMATION_SCHEMA
views (beta).
Required permissions
At a minimum, to get information about tables, you must be granted
bigquery.tables.get
permissions. The following predefined IAM
roles include bigquery.tables.get
permissions:
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
In addition, if a user has bigquery.datasets.create
permissions, when that
user creates a dataset, they are granted bigquery.dataOwner
access to it.
bigquery.dataOwner
access gives the user the ability to retrieve table
metadata.
For more information on IAM roles and permissions in BigQuery, see Access control.
Get table information
To get information about tables:
Console
In the navigation panel, in the Resources section, expand your project, and then select a dataset.
Click the dataset name to expand it. The tables and views in the dataset appear.
Click the table name.
In the Details panel, click Details to display the table's description and table information.
Optionally, switch to the Schema tab to view the table's schema definition.
bq
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.
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.
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.
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.
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.
Get table information using INFORMATION_SCHEMA
INFORMATION_SCHEMA
is a series of views that provide access to metadata
about datasets, routines, tables, views, jobs, reservations, and streaming data.
You can query the following views to get table information:
- Use the
INFORMATION_SCHEMA.TABLES
andINFORMATION_SCHEMA.TABLE_OPTIONS
views to retrieve metadata about tables and views in a project. - Use the
INFORMATION_SCHEMA.COLUMNS
andINFORMATION_SCHEMA.COLUMN_FIELD_PATHS
views to retrieve metadata about the columns (fields) in a table. - Use the
INFORMATION_SCHEMA.TABLE_STORAGE
views to retrieve metadata about current and historical storage usage by 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. For detailed information about
views, query the INFORMATION_SCHEMA.VIEWS
view instead.
The INFORMATION_SCHEMA.TABLES
view has the following schema:
Column name | Data type | Value |
---|---|---|
table_catalog |
STRING |
The project ID 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; one of the following:
|
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 |
ddl |
STRING |
The DDL statement
that can be used to recreate the table, such as
CREATE TABLE
or CREATE VIEW |
clone_time |
TIMESTAMP |
For table clones
(Preview),
the time when the base table was
cloned to create this
table. If
time travel was used, then this
field contains the time travel timestamp. Otherwise, the
clone_time field is the same as the
creation_time field. Applicable only to
tables with table_type set to CLONE .
|
base_table_catalog |
STRING |
For table clones
(Preview),
the base table's project. Applicable only to
tables with table_type set to CLONE .
|
base_table_schema |
STRING |
For table clones
(Preview),
the base table's dataset. Applicable only to tables with
table_type set to CLONE . |
base_table_name |
STRING |
For table clones
(Preview),
the base table's name. Applicable only to tables with
table_type set to CLONE . |
default_collation_name |
STRING |
The name of the default collation specification
if it exists; otherwise, NULL .
|
Examples
Example 1:
The following example retrieves table metadata for all of the tables in the
dataset named mydataset
. The query selects all of the columns from the
INFORMATION_SCHEMA.TABLES
view except for is_typed
, which is reserved for
future use. The metadata that's
returned is for all types of tables in mydataset
in your default project.
mydataset
contains the following tables:
mytable1
: a standard BigQuery tablemyview1
: a BigQuery view
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
.
SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES;
The result is similar to the following. For readability, some columns are excluded from the result.
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | ddl | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` | | | | | | | | ( | | | | | | | | id INT64 | | | | | | | | ); | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1` | | | | | | | | AS SELECT 100 as id; | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
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.
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
.
SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE';
The result is similar to the following. For readability, some columns are excluded from the result.
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | ddl |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1
|
| | | | | | | ( |
| | | | | | | id INT64 |
| | | | | | | ); |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
Example 3:
The following example retrieves table_name
and ddl
columns from the INFORMATION_SCHEMA.TABLES
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, you add the project ID to the dataset in
the following format:
`project_id`.dataset.INFORMATION_SCHEMA.view
.
In this example, the value is
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
.
SELECT table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_name = 'population_by_zip_2010';
The result is similar to the following:
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table_name | ddl | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010` | | | ( | | | geo_id STRING OPTIONS(description="Geo code"), | | | zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"), | | | population INT64 OPTIONS(description="The total count of the population for this segment."), | | | minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."), | | | maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), | | | gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.") | | | ) | | | OPTIONS( | | | labels=[("freebqcovid", "")] | | | ); | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
TABLE_OPTIONS
view
When you query the INFORMATION_SCHEMA.TABLE_OPTIONS
view, the query results
contain one row for each option, for each table or view in a dataset. For
detailed information about
views, query the INFORMATION_SCHEMA.VIEWS
view instead.
The INFORMATION_SCHEMA.TABLE_OPTIONS
view has the following schema:
Column name | Data type | Value |
---|---|---|
TABLE_CATALOG |
STRING |
The project ID 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 time when this table expires |
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 |
require_partition_filter |
BOOL |
Whether queries over the table require a partition filter |
enable_refresh |
BOOL |
Whether automatic refresh is enabled for a materialized view |
refresh_interval_minutes |
FLOAT64 |
How frequently a materialized view is refreshed |
For external tables, the following options are possible:
Options | |
---|---|
allow_jagged_rows |
If Applies to CSV data. |
allow_quoted_newlines |
If Applies to CSV data. |
bigtable_options |
Only required when creating a Bigtable external table. Specifies the schema of the Bigtable external table in JSON format. For a list of Bigtable table definition options, see
|
compression |
The compression type of the data source. Supported values include:
Applies to CSV and JSON data. |
decimal_target_types |
Determines how to convert a Example: |
description |
A description of this table. |
enable_list_inference |
If Applies to Parquet data. |
enable_logical_types |
If Applies to Avro data. |
encoding |
The character encoding of the data. Supported values include:
Applies to CSV data. |
enum_as_string |
If Applies to Parquet data. |
expiration_timestamp |
The time when this table expires. If not specified, the table does not expire. Example: |
field_delimiter |
The separator for fields in a CSV file. Applies to CSV data. |
format |
The format of the external data.
Supported values for
Supported values for
The value |
hive_partition_uri_prefix |
A common prefix for all source URIs before the partition key encoding begins. Applies only to hive-partitioned external tables. Applies to Avro, CSV, JSON, Parquet, and ORC data. Example: |
ignore_unknown_values |
If Applies to CSV and JSON data. |
|