Search code examples
sqlgoogle-bigqueryddl

For loop in Bigquery

The following SQL in Bigquery:

FOR tableId IN (
  select table_id
  from `misys-db-replication-nutracap.hevo_dataset_misys_db_replication_nutracap_LPj9.__TABLES__`
  WHERE NOT STARTS_WITH(table_id, "mi")
)
DO
  DROP TABLE `misys-db-replication-nutracap.hevo_dataset_misys_db_replication_nutracap_LPj9`.tableId.table_id;
END FOR;

Throughs an error:

Invalid value: Invalid project ID 'misys-db-replication-nutracap.hevo_dataset_misys_db_replication_nutracap_LPj9'. Project IDs must contain 6-63 lowercase letters, digits, or dashes. Some project IDs also include domain name separated by a colon. IDs must start with a letter and may not end with a dash. at [7:3]

It seems to me that the tableId.table_id dot is being considered as part of the path to the table, but I'm not sure how to fix it.

Solution

  • You need to do something like:

    FOR item IN (
  select table_id
  from `dataset.__TABLES__`
  WHERE STARTS_WITH(table_id, "Table")
)
DO
  EXECUTE IMMEDIATE concat("DROP TABLE `dataset`.",item.table_id);
END FOR;