Creating a temporary table

The following example creates a temporary table named Example and inserts values into it.

CREATE TEMP TABLE Example
(
  x INT64,
  y STRING
);

INSERT INTO Example
VALUES (5, 'foo');
INSERT INTO Example
VALUES (6, 'bar');

SELECT *
FROM Example;

This script returns the following output:

+-----+---+-----+
| Row | x | y   |
+-----+---|-----+
| 1   | 5 | foo |
| 2   | 6 | bar |
+-----+---|-----+

CREATE TABLE LIKE statement

Creates a new table with all of the same metadata of another table.

Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ]

table_name

LIKE [[project_name.]dataset_name.]source_table_name

...

[OPTIONS(table_option_list)]

Details

This statement is a variant of the CREATE TABLE statement and has the same limitations. Other than the use of the LIKE clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

The CREATE TABLE LIKE statement copies only the metadata of the source table. You can use the as query_statement clause to include data into the new table.

The new table has no relationship to the source table after creation; thus modifications to the source table will not propagate to the new table.

By default, the new table inherits partitioning, clustering, and options metadata from the source table. You can customize metadata in the new table by using the optional clauses in the SQL statement. For example, if you want to specify a different set of options for the new table, then include the OPTIONS clause with a list of options and values. This behavior matches that of ALTER TABLE SET OPTIONS.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then bigquery.tables.delete permission is also required.

Examples

Example 1

The following example creates a new table named newtable in mydataset with the same metadata as sourcetable:

CREATE TABLE mydataset.newtable
LIKE mydataset.sourcetable

Example 2

The following example creates a new table named newtable in mydataset with the same metadata as sourcetable and the data from the SELECT statement:

CREATE TABLE mydataset.newtable
LIKE mydataset.sourcetable
AS SELECT * FROM mydataset.myothertable

CREATE TABLE COPY statement

Creates a table that has the same metadata and data as another table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name

COPY source_table_name

...

[OPTIONS(table_option_list)]

Details

This statement is a variant of the CREATE TABLE statement and has the same limitations. Other than the use of the COPY clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

The CREATE TABLE COPY statement copies both the metadata and data from the source table.

The new table inherits partitioning and clustering from the source table. By default, the table options metadata from the source table are also inherited, but you can override table options by using the OPTIONS clause. The behavior is equivalent to running ALTER TABLE SET OPTIONS after the table is copied.

The new table has no relationship to the source table after creation; modifications to the source table are not propagated to the new table.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then bigquery.tables.delete permission is also required.

CREATE SNAPSHOT TABLE statement

Creates a table snapshot based on a source table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATE SNAPSHOT TABLE [ IF NOT EXISTS ] table_snapshot_name

CLONE source_table_name

[FOR SYSTEM_TIME AS OF time_expression]

[OPTIONS(snapshot_option_list)]

Arguments

• IF NOT EXISTS: If a table snapshot or other table resource exists with the same name, the CREATE statement has no effect.

• table_snapshot_name: The name of the table snapshot that you want to create. The table snapshot name must be unique per dataset. See Table path syntax.

• source_table_name: The name of the table that you want to snapshot or the table snapshot that you want to copy. See Table path syntax.

  If the source table is a standard table, then BigQuery creates a table snapshot of the source table. If the source table is a table snapshot, then BigQuery creates a copy of the table snapshot.

• FOR SYSTEM_TIME AS OF: Lets you select the version of the table that was current at the time specified by timestamp_expression. It can only be used when creating a snapshot of a table; it can't be used when making a copy of a table snapshot.

• snapshot_option_list: Additional table snapshot creation options such as a label and an expiration time.

Details

CREATE SNAPSHOT TABLE statements must comply with the following rules:

• Only one CREATE statement is allowed.
• The source table must be one of the following:
  • A table
  • A table clone
  • A table snapshot
• The FOR SYSTEM_TIME AS OF clause can only be used when creating a snapshot of a table or table clone; it can't be used when making a copy of a table snapshot.

snapshot_option_list

The option list lets you set table snapshot options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a table snapshot option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression that contains only literals, query parameters, and scalar functions.

The constant expression cannot contain:

• A reference to a table
• Subqueries or SQL statements such as SELECT, CREATE, and UPDATE
• User-defined functions, aggregate functions, or analytic functions
• The following scalar functions:
  • ARRAY_TO_STRING
  • REPLACE
  • REGEXP_REPLACE
  • RAND
  • FORMAT
  • LPAD
  • RPAD
  • REPEAT
  • SESSION_USER
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY

If VALUE evaluates to NULL, the corresponding option NAME in the CREATE SNAPSHOT TABLE statement is ignored.

Required permissions

This statement requires the following IAM permissions:

Examples

Create a table snapshot: fail if it already exists

The following example creates a table snapshot of the table myproject.mydataset.mytable. The table snapshot is created in the dataset mydataset and is named mytablesnapshot:

CREATE SNAPSHOT TABLE `myproject.mydataset.mytablesnapshot`
CLONE `myproject.mydataset.mytable`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="my_table_snapshot",
  description="A table snapshot that expires in 2 days",
  labels=[("org_unit", "development")]
)

If the table snapshot name already exists in the dataset, then the following error is returned:

Already Exists: myproject.mydataset.mytablesnapshot

The table snapshot option list specifies the following:

• Expiration time: 48 hours after the time the table snapshot is created
• Friendly name: my_table_snapshot
• Description: A table snapshot that expires in 2 days
• Label: org_unit = development

Create a table snapshot: ignore if it already exists

The following example creates a table snapshot of the table myproject.mydataset.mytable. The table snapshot is created in the dataset mydataset and is named mytablesnapshot:

CREATE SNAPSHOT TABLE IF NOT EXISTS `myproject.mydataset.mytablesnapshot`
CLONE `myproject.mydataset.mytable`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="my_table_snapshot",
  description="A table snapshot that expires in 2 days"
  labels=[("org_unit", "development")]
)

The table snapshot option list specifies the following:

• Expiration time: 48 hours after the time the table snapshot is created
• Friendly name: my_table_snapshot
• Description: A table snapshot that expires in 2 days
• Label: org_unit = development

If the table snapshot name already exists in the dataset, then no action is taken, and no error is returned.

For information about restoring table snapshots, see CREATE TABLE CLONE.

For information about removing table snapshots, see DROP SNAPSHOT TABLE.

CREATE TABLE CLONE statement

Creates a table clone based on a source table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ]

destination_table_name

CLONE source_table_name [FOR SYSTEM_TIME AS OF time_expression]

...

[OPTIONS(table_option_list)]

Details

Other than the use of the CLONE clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

Arguments

• OR REPLACE: Replaces a table with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If the specified destination table name already exists, the CREATE statement has no effect. Cannot appear with OR REPLACE.

destination_table_name is the name of the table that you want to create. The table name must be unique per dataset. The table name can contain the following:

• Up to 1,024 characters
• Letters (upper or lower case), numbers, and underscores

OPTIONS(table_option_list) lets you specify additional table creation options such as a label and an expiration time.

source_table_name is the name of the source table.

CREATE TABLE CLONE statements must comply with the following rules:

• Only one CREATE statement is allowed.
• The table that is being cloned must be a table, a table clone, or a table snapshot.

OPTIONS

CREATE TABLE CLONE options are the same as CREATE TABLE options.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then bigquery.tables.delete permission is also required.

Examples

Restore a table snapshot: fail if destination table already exists

The following example creates the table myproject.mydataset.mytable from the table snapshot myproject.mydataset.mytablesnapshot:

CREATE TABLE `myproject.mydataset.mytable`
CLONE `myproject.mydataset.mytablesnapshot`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY),
  friendly_name="my_table",
  description="A table that expires in 1 year",
  labels=[("org_unit", "development")]
)

If the table name exists in the dataset, then the following error is returned:

Already Exists: myproject.mydataset.mytable.

The table option list specifies the following:

• Expiration time: 365 days after the time that the table is created
• Friendly name: my_table
• Description: A table that expires in 1 year
• Label: org_unit = development

Create a clone of a table: ignore if the destination table already exists

The following example creates the table clone myproject.mydataset.mytableclone based on the table myproject.mydataset.mytable:

CREATE TABLE IF NOT EXISTS `myproject.mydataset.mytableclone`
CLONE `myproject.mydataset.mytable`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY),
  friendly_name="my_table",
  description="A table that expires in 1 year",
  labels=[("org_unit", "development")]
)

The table option list specifies the following:

• Expiration time: 365 days after the time the table is created
• Friendly name: my_table
• Description: A table that expires in 1 year
• Label: org_unit = development

If the table name exists in the dataset, then no action is taken, and no error is returned.

For information about creating a copy of a table, see CREATE TABLE COPY.

For information about creating a snapshot of a table, see CREATE SNAPSHOT TABLE.

CREATE VIEW statement

Creates a new view.

Syntax

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name

[(view_column_name_list)]

[OPTIONS(view_option_list)]

AS query_expression

Arguments

• OR REPLACE: Replaces any view with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If a view or other table resource exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• view_name: The name of the view you're creating. See Table path syntax.

• view_column_name_list: Lets you explicitly specify the column names of the view, which may be aliases to the column names in the underlying SQL query.

• view_option_list: Additional view creation options such as a label and an expiration time.

• query_expression: The Google Standard SQL query expression used to define the view.

Details

CREATE VIEW statements must comply with the following rules:

• Only one CREATE statement is allowed.

view_column_name_list

The view's column name list is optional. The names must be unique but do not have to be the same as the column names of the underlying SQL query. For example, if your view is created with the following statement:

CREATE VIEW mydataset.age_groups(age, count) AS SELECT age, COUNT(*)
FROM mydataset.people
group by age;

Then you can query it with:

SELECT age, count from mydataset.age_groups;

The number of columns in the column name list must match the number of columns in the underlying SQL query. If the columns in the table of the underlying SQL query is added or dropped, the view becomes invalid and must be recreated. For example, if the age column is dropped from the mydataset.people table, then the view created in the previous example becomes invalid.

view_option_list

The option list allows you to set view options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a view option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannot contain:

• A reference to a table
• Subqueries or SQL statements such as SELECT, CREATE, or UPDATE
• User-defined functions, aggregate functions, or analytic functions
• The following scalar functions:
  • ARRAY_TO_STRING
  • REPLACE
  • REGEXP_REPLACE
  • RAND
  • FORMAT
  • LPAD
  • RPAD
  • REPEAT
  • SESSION_USER
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY

If VALUE evaluates to NULL, the corresponding option NAME in the CREATE VIEW statement is ignored.

Default project in view body

If the view is created in the same project used to run the CREATE VIEW statement, the view body query_expression can reference entities without specifying the project; the default project is the project which owns the view. Consider the sample query below.

CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;

After running the above CREATE VIEW query in the project myProject, you can run the query SELECT * FROM myProject.myDataset.myView. Regardless of the project you choose to run this SELECT query, the referenced table anotherDataset.myTable is always resolved against project myProject.

If the view is not created in the same project used to run the CREATE VIEW statement, then all references in the view body query_expression must be qualified with project IDs. For instance, the preceding sample CREATE VIEW query is invalid if it runs in a project different from myProject.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update permission.

If the OPTIONS clause includes an expiration time, then bigquery.tables.delete permission is also required.

Examples

Creating a new view

The following example creates a view named newview in mydataset:

CREATE VIEW `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

If the view name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

The view is defined using the following Google Standard SQL query:

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

The view option list specifies the:

• Expiration time: 48 hours from the time the view is created
• Friendly name: newview
• Description: A view that expires in 2 days
• Label: org_unit = development

Creating a view only if the view doesn't exist

The following example creates a view named newview in mydataset only if no view named newview exists in mydataset. If the view name exists in the dataset, no error is returned, and no action is taken.

CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

The view is defined using the following Google Standard SQL query:

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

The view option list specifies the:

• Expiration time: 48 hours from the time the view is created
• Friendly name: newview
• Description: A view that expires in 2 days
• Label: org_unit = development

Creating or replacing a view

The following example creates a view named newview in mydataset, and if newview exists in mydataset, it is overwritten using the specified query expression.

CREATE OR REPLACE VIEW `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

The view is defined using the following Google Standard SQL query:

SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

The view option list specifies the:

• Expiration time: 48 hours from the time the view is created
• Friendly name: newview
• Description: A view that expires in 2 days
• Label: org_unit = development

ref：Data definition language (DDL) statements in Google Standard SQL  |  BigQuery  |  Google Cloud

最新文章

1. 龙之谷手游WebVR技术分享
2. ibatis order by 防止sql注入
3. priority_queue 示例
4. FPGA学习笔记之格雷码、边沿检测、门控时钟
5. ACM竞赛高手比其他程序员水平高很多吗？
6. jquery给元素添加样式表的方法
7. Linux命令详解：[7]获得命令帮助
8. js init : function ()
9. Tabbar视图切换，返回上一视图，添加item
10. 荷兰国旗，三类数字分离 nyoj
11. Python学习之路——字符处理(二)
12. 一、spark入门之spark shell：wordcount
13. 关于 Go 中 Map 类型和 Slice 类型的传递
14. 爬取朋友圈，Get年度关键词
15. CSS中可以继承和不可继承的常见属性
16. docker学习-常用命令1
17. PL/SQL学习笔记之条件控制语句
18. java操作数据库的基本方法
19. BZOJ5281: [Usaco2018 Open]Talent Show 01分数规划+01背包
20. 关于帝国CMS迁移到新服务器上出现问题的处理办法

热门文章

1. PostgreSQL性能优化综合案例 - 1
2. PACS系统各个功能介绍--九五小庞
3. oracle to mogdb 迁移---mtk工具
4. Intellij IDEA 通过数据库表生成带注解的实体类Generate MyPOJOs.groovy脚本的编写
5. Ubuntu离线安装 MySQL 5.7
6. 98、TypeError: f.upload.addEventListener is not a function
7. docker 安装 jFrog
8. mybatis_pagehelper_selectOne的SQL语句被莫名的增加分页相关设置，暂定解决办法
9. 中间件Middleware 使用及相关概念
10. 创建异步倒计时触发Task