在大查询中更改分区表架构 [英] change a partitioned table schema in big query

问题描述

我在大查询中有一个分区表，我想更改该表的架构.在通过Web UI使用以下sql之前，我已经更改了表的架构

I have a partitioned table in big query and I wanted to change the schema of that table. I have changed the schema of table before using the following sql from the web UI

'SELECT * REPLACE ((SELECT AS STRUCT whatever.* EXCEPT (columnName)) AS whatever) FROM `a:b.c`'

但这会导致所有先前的分区丢失，并且当我使用以下命令查找此新创建的表的分区时，它会给我今天的日期

but this causes all the previous partitions to be lost and when I look for the partitions of this newly created table using the following command it gives me today's date

SELECT _PARTITIONTIME as pt, FORMAT_TIMESTAMP("%Y%m%d", _PARTITIONTIME) as partition_id
FROM `a.b.c`
GROUP BY _PARTITIONTIME
ORDER BY _PARTITIONTIME

是否可以更改表的架构并将其分区保留在BigQuery中?

Is it possible to change the schema of a table and also keep its partitions in BigQuery?

推荐答案

当前，它在文档(链接2 )未列出所有可用的可能的修改，并且未将分区表更改为非分区表.但是，有一种解决方法.

Currently, it is stated in the documentation ( link 1, link 2) all the possible modifications that are available and changing a partitioned table to a non-partitioned table is not listed. However, there is a work around.

为了将分区表更改为非分区表，可以使用控制台查询数据并覆盖当前表或将其复制到新表.举例来说，我在BigQuery中有一个按 _PARTITIONTIME 分区的表.我使用以下查询创建了一个未分区的表

In order to change a Partitioned table to a Non-partitioned table, you can use the Console to query your data and overwrite your current table or copy to a new one. As an example, I have a table in BigQuery partitioned by _PARTITIONTIME. I used the following query to create a non-partitioned table,

SELECT *, _PARTITIONTIME as pt FROM `project.dataset.table` 

使用上面的代码，您将查询所有表分区之间的数据，并创建一个额外的列以显示其来自哪个分区.然后，在执行之前，有两个选项，将视图保存在新的未分区表中或覆盖当前表:

With the above code, you will query the data among all table's partitions and create an extra column to show which partition it came from. Then, before executing it, there are two options, save the view in a new non-partitioned table or overwrite the current table:

1. 创建新表，请转到:更多(在查询编辑器下)> 查询设置> 选中复选框"为查询结果设置目标表">> 选择项目，数据集并输入新表的名称> 在 目标表的写操作 检查 写为空.

1. Creating a new table go to: More(under the query editor) > Query Settings > Check the box "Set a destination table for query results" > Choose your project, dataset and write your new table's name > Under Destination table write preference check Write if empty.

覆盖当前表:更多(在查询编辑器下)> 查询设置> 选中该框"为查询结果设置目标表> 为当前表选择相同的项目和数据集> 为要覆盖的表写相同的表名> 下 目标表写入首选项 检查 覆盖表.

Overwriting the current table: More(under the query editor) > Query Settings > Check the box "Set a destination table for query results" > Choose the same project and dataset for your current table > Write the same table's name as the one you want to overwrite > Under Destination table write preference check Overwrite table.

这篇关于在大查询中更改分区表架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！