[SOLVED] Mass update table and column descriptions in google big query

Mass update table and column descriptions in google big query

I need to update table and column description for more than 150 tables in BigQuery. Is there a way to write a procedure which can loop through each table and alter the table and update desc for columns that are missing description on the table.

I tried to loop through each table and each table has like more than 100 columns. As a result every time I am issuing hundreds of alters. Is there any way to solve this?

Solution

The following will work:

FOR row IN (
  select "street" as col, "street address" as description
  union all
  select "city" as col, "city" as description
  union all
  select "postal_code" as col, "postal code" as description
)
DO
  EXECUTE IMMEDIATE FORMAT("""
  ALTER TABLE <table_tef>
  ALTER COLUMN %s
  SET OPTIONS (
    description='%s'
  );
  """, row.col, row.description);
END FOR;

just adjust <table_ref> and preferably replace my nested sql query with single table reference (I kept it explicit - so it's crystal clear what goes in).

Then for multiple tables, using the same method:

FOR row IN (
  select col, description, table_to_update from <table_desc_ref>
)
DO
  EXECUTE IMMEDIATE FORMAT("""
  ALTER TABLE %s
  ALTER COLUMN %s
  SET OPTIONS (
    description='%s'
  );
  """, row.table_to_update, row.col, row.description);
END FOR;

where <table_desc_ref> should be a table containing columns, along with their refined descriptions and relevant table reference.