How to remove unused column names from Bigquery Schema-CodePudding

Using Bigquery I was trying to remove nested schmea like below, but couldn't able to do so. Can anyone please let me know

Table:

FiledName          Type       Mode
 Person            RECORD   NULLABLE
 Person.Name       STRING   NULLABLE
 Person.Add        RECORD   NULLABLE
 Person.Add.line   STRING   NULLABLE

Code:

ALTER TABLE `<project_id.dataset.table_name` DROP COLUMN 'Person.Add'

I have to remove entire record.

Expected output:

FiledName          Type       Mode
 Person            RECORD   NULLABLE
 Person.Name       STRING   NULLABLE

CodePudding user response：

You can use below approach - it selects all except of person.add and inserts into new table

create table `project_id.dataset.new_table_name` as 
select * replace(
    (select as struct person.* except(add)) as person
  )
from `project_id.dataset.table_name`;

CodePudding user response：

Unfortunately per the documentation this cannot be done right now: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#details_14

The alternative would be to add a new Person record column with your desired fields, copy the data into it, then drop the old Person record.