CODEWITHSUNDEEP
google-bigquerybigdataavro
Austin Mahan
Austin Mahan

Reputation: 1

How to rename column with path in BigQuery?

I am looking for a way to rename a column that is nested from my avro schema. I tried the options google has on their docs (https://cloud.google.com/bigquery/docs/manually-changing-schemas) but any time I try to alias or cast as a nested structure it doesn't work.

For example:

   SELECT
     * EXCEPT(user.name.first, user.name.last),
     user.name.first AS user.name.firstName,
     user.name.last AS user.name.lastName
   FROM
     mydataset.mytable

However this doesn't like aliasing with paths. Another option I am trying to avoid is pulling in all my previous avro files in and converting them using dataflow. I am hoping for a more elegant solution than that. Thanks.

Upvotes: 0

Views: 1443

Answers (2)

saifuddin778
saifuddin778

Reputation: 7277

You have to re-build those structs. You can do something like this:

select 
  struct(
    struct(
      user.name.first as firstName, 
      user.name.last as lastName
    ) as name,
    user.height as height
  ) as user,
  address,
  age
from mydataset.mytable

Once you can verify the results, you can go about either creating a new table from these results or over-writing the existing table (which is essentially a workaround for renaming columns, but with caution). Hope it helps.

Upvotes: 0

Elliott Brossard
Elliott Brossard

Reputation: 33755

You need to rebuild the structure at each level. Here's an example over some sample data:

SELECT
  * REPLACE(
    (SELECT AS STRUCT user.* REPLACE (
       (SELECT AS STRUCT user.name.* EXCEPT (first, last),
          user.name.first AS firstName,
          user.name.last AS lastName
       ) AS name)
    ) AS user)
FROM (
  SELECT
    STRUCT(
      STRUCT('elliott' AS first, '???' AS middle, 'brossard' AS last) AS name,
      'Software Engineer' AS occupation
    ) AS user
)

The idea is to replace the user struct with a new one where name has the desired struct type using the nested replacement/struct construction syntax.

Upvotes: 1

Related Questions