如何删除BigQuery中属于嵌套列的列 [英] How to delete a column in BigQuery that is part of a nested column

问题描述

我要删除BigQuery表中属于记录或嵌套列的列。我在他们的documentation中找到了这个命令。遗憾的是，此命令不适用于现有记录字段中的嵌套列。 是否有解决此问题的方法？

例如，如果我有这个模式，我想删除Address字段中的Address2字段。因此，从这个开始：

[
    {
        "name": "name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "address1",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address2",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "country",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

至此：

[
    {
        "name": "name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "address1",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "country",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

推荐答案

在下面使用

select * replace(
  array(select as struct * except(address2) from t.addresses) 
  as addresses)
from `project.dataset.table` t       

如果要永久删除该字段-请使用，如下例所示

create or replace table `project.dataset.new_table` as 
select * replace(
  array(select as struct * except(address2) from t.addresses) 
  as addresses)
from `project.dataset.table` t

这篇关于如何删除BigQuery中属于嵌套列的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！