如何通过查询和导出到自身来更改 BigQuery 表的列类型? [英] How to change a column type of a BigQuery table by querying and exporting to itself?

问题描述

我的 BigQuery 表中有一个 integer 类型的列，现在我需要将它转换为 float 列.我还必须保留所有记录.我想要做的是更改列类型.不投射.

我了解到，只需将表上的查询结果导出到自身即可.

怎么做?

解决方案

使用SELECT将结果写回表

SELECTCAST(int_field AS FLOAT) AS float_field，<all_other_fields>从你的表

这种方法会让你扫描整个表格

要执行此操作 - 您应该使用 BQ Web UI 中的 显示选项 按钮并正确设置选项，如下例所示.运行此操作后 - 您的表将根据需要具有带有浮点数与原始整数数据类型的列.注意:您应该为 int_field 和 float_field 使用正确/相同的字段名称

如果您只需要添加新列，我会指向您表:补丁 GBQ API.
我不确定这个 API 是否允许更改列的类型 - 我怀疑 - 但很容易检查

<块引用>

使用作业:插入 EXTRACT 然后 LOAD

在这里您可以将表提取到 GCS，然后使用调整后的架构将其加载回 GBQ

上述方法将 a) 消除查询(扫描)表的成本成本，并且 b) 可以帮助解决限制，如果您有复杂的方案(带有记录/重复/嵌套等类型/模式)

I have a integer type column in my BigQuery table and now I need to convert it to a float column. I also have to keep all records. What I want to do is changing the column type. Not casting.

I've read that it's possible to do it just by exporting results of a query on a table to itself.

How to do it?

解决方案

Using SELECT with writing result back to table

SELECT 
  CAST(int_field AS FLOAT) AS float_field, 
  <all_other_fields> 
FROM YourTable

This approach will co$t you scan of whole table

To execute this - you should use Show Option button in BQ Web UI and properly set options as in below example. After you run this - your table will have that column with float vs. original integer data type as you wanted. Note: You should use proper/same field name for both int_field and float_field

if you would just needed to add new column I would point you to Tables: patch GBQ API.
I am not sure if this API allows to change type of column - I doubt - but it is easy to check

Using Jobs: insert EXTRACT and then LOAD

Here you can extract table to GCS and then load it back to GBQ with adjusted schema

Above approach will a) eliminate cost cost of querying (scan) tables and b) can help with limitations that you can come up if you have complex schame (with records/repeated/nested, etc. type /mode)

这篇关于如何通过查询和导出到自身来更改 BigQuery 表的列类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！