blocks|key|4080205|text|2018更新-使用DDL的明确答案|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4080206|有了BigQuery的DDL支持，您可以从查询结果创建表，并在创建时指定其过期时间。例如，对于3天：|4080207|#standardSQL
CREATE+TABLE+`fh-bigquery.public_dump.vtemp`
OPTIONS(
++expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),+INTERVAL+3+DAY)
)+AS
SELECT+corpus,+COUNT(*)+c
FROM+`bigquery-public-data.samples.shakespeare`
GROUP+BY+corpus|code-block|syntax|javascript|4080208|https://cloud.google.com/bigquery/docs/data-definition-language+|4080209|文档|unordered-list-item|4080210|4080211|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/docs/data-definition-language^0|0|C|0|0|0|0|1R|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|D|@$9|16|A|17|1|18]]|E|$]]|$1|O|3|P|5|Q|7|19|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

2018 update - definitive answer with DDL

With BigQuery's DDL support you can create a table from the results a query - and specify its expiration at creation time. For example, for 3 days:

<pre><code>#standardSQL
CREATE TABLE `fh-bigquery.public_dump.vtemp`
OPTIONS(
 expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
) AS
SELECT corpus, COUNT(*) c
FROM `bigquery-public-data.samples.shakespeare`
GROUP BY corpus
</code></pre>

<ul>
<li>Docs: <a href="https://cloud.google.com/bigquery/docs/data-definition-language" rel="noreferrer">https://cloud.google.com/bigquery/docs/data-definition-language</a></li>
</ul>

blocks|key|1969904|text|2019更新-使用BigQuery+scripting+(测试版)，CREATE+TEMP+TABLE正式支持。请参阅公共文档here。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1969905|2018更新：+|1969906|bigquery中的每个查询都会创建一个包含结果的临时表。临时的，除非您为目标表指定了名称，否则您可以控制它的生命周期。|1969907|使用api查看临时表名称，或在查询时命名您的表。|1969908|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting|1|https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#temporary_tables^0|0|6|Y|L|I|9|0|1R|4|1|0|0|7|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@$9|10|A|11|1|12]|$9|13|A|14|1|15]]|E|$]]|$1|F|3|G|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|19|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|1A|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]|T|$5|O|P|Q|E|$R|U]]]]

2019 update -- With BigQuery <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting" rel="noreferrer">scripting</a> (Beta now), CREATE TEMP TABLE is officially supported. See public documentation <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#temporary_tables" rel="noreferrer">here</a>. 

2018 update: <a href="https://stackoverflow.com/a/50227484/132438">https://stackoverflow.com/a/50227484/132438</a>

Every query in bigquery creates a temporary table with the results. Temporary unless you give a name to the destination table, then you are in control of its lifecycle.

Use the api to see the temporary table name, or name your tables when querying.

blocks|key|1969936|text|可以在"New+Standard+SQL"中使用WITH创建临时表。参见WITH+clause。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1969937|谷歌给出了一个例子：|1969938|WITH+subQ1+AS+(SELECT+SchoolID+FROM+Roster),
+++++subQ2+AS+(SELECT+OpponentID+FROM+PlayerStats)
SELECT+*+FROM+subQ1
UNION+ALL
SELECT+*+FROM+subQ2;|code-block|syntax|javascript|1969939|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/sql-reference/|1|https://cloud.google.com/bigquery/sql-reference/query-syntax#with-clause^0|O|4|3|I|0|10|B|1|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]|U|$5|P|Q|R|E|$S|V]]]]

A temporary table can be created with <code>WITH</code> in the <a href="https://cloud.google.com/bigquery/sql-reference/" rel="noreferrer" title="new standard sql">"New Standard SQL"</a>. See <a href="https://cloud.google.com/bigquery/sql-reference/query-syntax#with-clause" rel="noreferrer">WITH clause</a>.

An example given by Google:

<pre><code>WITH subQ1 AS (SELECT SchoolID FROM Roster),
 subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
</code></pre>

blocks|key|5498753|text|2019更新+--使用BigQuery+scripting，CREATE+TEMP+TABLE正式支持。请参阅公共文档here。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|5498754|CREATE+TEMP+TABLE+Example
(
++x+INT64,
++y+STRING
);

INSERT+INTO+Example
VALUES+(5,+'foo');

INSERT+INTO+Example
VALUES+(6,+'bar');

SELECT+*
FROM+Example;|code-block|syntax|javascript|5498755|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting|1|https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#temporary_tables^0|0|6|U|L|K|9|0|1N|4|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]|S|$5|N|O|P|E|$Q|T]]]]

2019 update -- With BigQuery <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting" rel="nofollow noreferrer">scripting</a>, CREATE TEMP TABLE is officially supported. See public documentation <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#temporary_tables" rel="nofollow noreferrer">here</a>.
<pre class="lang-sql prettyprint-override"><code>CREATE TEMP TABLE Example
(
 x INT64,
 y STRING
);

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

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

SELECT *
FROM Example;
</code></pre>

blocks|key|4080285|text|要创建临时表，请在使用create+table语句时使用TEMP或temporary关键字，而使用CREATE+TEMPORARY+TABLE需要脚本，因此最好从begin语句开始。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4080286|Begin+CREATE+TEMP+TABLE+<table_name>+as+select+*+from+<table_name>+where+<condition>;+End+;|offset|length|style|CODE|4080287|entityMap^0|0|0|2J|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

To create a temporary table, use the TEMP or TEMPORARY keyword when you use the CREATE TABLE statement and use of CREATE TEMPORARY TABLE requires a script , so its better to start with begin statement.

<code>Begin
CREATE TEMP TABLE &lt;table_name&gt; as select * from &lt;table_name&gt; where &lt;condition&gt;;
End ;</code>

blocks|key|1605830|text|在GCP+bigquery中创建临时表的示例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1605831|CREATE+TABLE++`project_ID_XXXX.Sales.superStore2011`+
OPTIONS(
++expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),+INTERVAL+1+DAY)
)+AS
SELECT+
++Product_Name,Product_Category,+SUM(profit)+Total_Profit,++FORMAT_DATE("%25Y",Order_Date)+AS+Year
FROM+
++`project_ID_XXXX.Sales.superStore`+
WHERE
++FORMAT_DATE("%25Y",Order_Date)="2011"
GROUP+BY+
++Product_Name,Product_Category,Order_Date
ORDER+BY+
+++Year,+Total_Profit+DESC
LIMIT+5+|code-block|syntax|javascript|1605832|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Example of creating temp tables in GCP bigquery

<pre><code>CREATE TABLE `project_ID_XXXX.Sales.superStore2011` 
OPTIONS(
 expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
) AS
SELECT 
 Product_Name,Product_Category, SUM(profit) Total_Profit, FORMAT_DATE("%Y",Order_Date) AS Year
FROM 
 `project_ID_XXXX.Sales.superStore` 
WHERE
 FORMAT_DATE("%Y",Order_Date)="2011"
GROUP BY 
 Product_Name,Product_Category,Order_Date
ORDER BY 
 Year, Total_Profit DESC
LIMIT 5 
</code></pre>

blocks|key|1969926|text|取以下SQL示例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1969927|SELECT+name,count+FROM+mydataset.babynames+
++WHERE+gender+=+'M'+ORDER+BY+count+DESC+LIMIT+6+INTO+mydataset.happyhalloween;|code-block|syntax|javascript|1969928|最简单的命令行等效项是|1969929|bq+query+--destination_table=mydataset.happyhalloween+\+
"SELECT+name,count+FROM+mydataset.babynames+WHERE+gender+=+'M'+\
ORDER+BY+count+DESC+LIMIT+6"|1969930|请参阅此处的文档：https://cloud.google.com/bigquery/bq-command-line-tool#createtablequery|offset|length|1969931|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/bq-command-line-tool#createtablequery^0|0|0|0|0|9|1Z|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Take the SQL sample of

<pre><code>SELECT name,count FROM mydataset.babynames 
 WHERE gender = 'M' ORDER BY count DESC LIMIT 6 INTO mydataset.happyhalloween;
</code></pre>

The easiest command line equivalent is 

<pre><code>bq query --destination_table=mydataset.happyhalloween \ 
"SELECT name,count FROM mydataset.babynames WHERE gender = 'M' \
ORDER BY count DESC LIMIT 6"
</code></pre>

See the documentation here:
<a href="https://cloud.google.com/bigquery/bq-command-line-tool#createtablequery" rel="nofollow noreferrer">https://cloud.google.com/bigquery/bq-command-line-tool#createtablequery</a>

Is there any way to create a temporary table in Google BigQuery through:

<pre><code>SELECT * INTO &lt;temp table&gt; 
FROM &lt;table name&gt; 
</code></pre>

same as we can create in SQL?

For complex queries, I need to create temporary tables to store my data.

How to create temporary table in Google BigQuery

5位重磅专家聚焦 AI 时代开发者核心成长需求，带你深入解锁全栈进化、人机协作关键路径！点击报名到场：<a href="https://cloud.tencent.com/developer/salon/salon-2403/signup" target="_blank">https://cloud.tencent.com/developer/salon/salon-2403/signup</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/487c82639d2338df3ef66dfb1450601a.png"/>

腾讯技术创作特训营「年度论坛」正在报名中！

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

有没有办法通过以下方式在谷歌BigQuery中创建临时表：SELECT * INTO <temp table> FROM <table name> 和我们可以在SQL中创建的一样吗？对于复杂的查询，我需要创建临时表来存储数据。

问如何在BigQuery中创建临时表
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在BigQuery中创建临时表EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在BigQuery中创建临时表
EN