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

问题描述

在我的BigQuery表中有一个整型类型列，现在我需要将它转换为 float 列。我也必须保留所有记录。我想要做的是 更改列类型 。没有铸造。

我读过，可以通过将表格上的查询结果导出到自身来完成。

如何做到这一点？

解决方案

table

  SELECT 
 CAST（int_field AS FLOAT）AS float_field，
< all_other_fields> 
 FROM YourTable 
  

这种方法可以扫描整个表格

要执行此操作，您应该使用 BQ Web UI 中的显示选项按钮并正确设置选项，如下例所示。在你运行这个之后 - 你的表将会有你想要的float和原始整数数据类型的列。注意：您应该为int_field和float_field使用适当的/相同的字段名称

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

使用作业：插入 EXTRACT，然后LOAD

在这里，您可以将表格提取到GCS，然后将其重新加载到GBQ通过调整模式

以上方法将a）消除查询（扫描）表的成本成本，并且b）可以帮助限制y如果你有复杂的schame（带有记录/重复/嵌套，等等/类型），那么你可以出现。

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屋！