I have a huge BQ table with a complex schema (lots of repeated and record fields). Is there a way for me to add more columns to this table and/or create a select that would copy the entire table into a new one with the addition of one (or more) columns? It appears as if copying a table requires flattening of repeated columns (not good). I need an exact copy of the original table with some new columns.
我有一个庞大的BQ表,具有复杂的模式(许多重复和记录字段)。有没有办法让我在这个表中添加更多列和/或创建一个select,它会将整个表复制到一个新表中,并添加一个(或多个)列?看起来好像复制表需要对重复列进行展平(不好)。我需要一些新列的原始表的精确副本。
I found a way to Update Table Schema but it looks rather limited as I can only seem to add nullable or repeated columns. I can't add record columns or remove anything.
我找到了一种更新表架构的方法,但它看起来相当有限,因为我似乎只能添加可空或重复的列。我无法添加记录列或删除任何内容。
If I were to modify my import JSON data (and schema) I could import anything. But my import data is huge and conveniently already in a denormalized gzipped JSON so changing that seems like a huge effort.
如果我要修改我的导入JSON数据(和架构),我可以导入任何东西。但是我的导入数据非常庞大且已经非常方便地使用了非规范化的gzip压缩JSON,所以改变它似乎是一项巨大的努力。
2 个解决方案
#1
If you want to use a query to copy the table, but don't want nested and repeated fields to be flattened, you can set the flattenResults parameter to false to preserve the structure of your output schema.
如果要使用查询来复制表,但不希望展平嵌套和重复字段,可以将flattenResults参数设置为false以保留输出模式的结构。
#2
I think you can add fields of type RECORD.
我想你可以添加RECORD类型的字段。
Nullable and repeated refer to field's mode, not type. So you can add a Nullable record or a Repeated record, but cannot add a Required record.
可空和重复是指字段的模式,而不是类型。因此,您可以添加Nullable记录或重复记录,但不能添加Required记录。
https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
You are correct that you cannot delete anything.
你是不对的,你不能删除任何东西。
#1
If you want to use a query to copy the table, but don't want nested and repeated fields to be flattened, you can set the flattenResults parameter to false to preserve the structure of your output schema.
如果要使用查询来复制表,但不希望展平嵌套和重复字段,可以将flattenResults参数设置为false以保留输出模式的结构。
#2
I think you can add fields of type RECORD.
我想你可以添加RECORD类型的字段。
Nullable and repeated refer to field's mode, not type. So you can add a Nullable record or a Repeated record, but cannot add a Required record.
可空和重复是指字段的模式,而不是类型。因此,您可以添加Nullable记录或重复记录,但不能添加Required记录。
https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
You are correct that you cannot delete anything.
你是不对的,你不能删除任何东西。