I have a text file with BigQuery tablenames. My requirement is to iterate through the list and search every dataset available in every Project environment (i.e. Project 'my-project-dev/test/prod') to check for the table's existence in each Project/DataSet, and output the results in a text file in the format shown below. Input File: TableA TableB TableC Output: tablename | exists_in_prod | exists_in_test | exists_in_dev | is_stg_table | action TableA | Y | N | Y | N | DELETE TableB | N | N | Y | N | DROP TableC | Y | Y | N | Y | DELETE Few considerations: There are 3 existing environments (dev/test/prod) that needs to be searched and the envs are determined by the Project Names - (E.g. 'my-project-dev', 'my-project-test', 'my-project-prod'). The dataset names are not provided in the input, hence all datasets in any specific env need to be searched to determine the table's existence. A dataset is identified as "staging" if the dataset-name ends with '_stg', in which case the tablename will also appear as 'tablename_stg' Logic for "action" column: If table found in Prod, then "action" = 'DELETE' irrespective of whether also found in dev and/or test If table found in Dev and/or Test but not in Prod, then "action" = 'DROP' If table found in any '_stg' dataset then always "action" = 'DELETE', irrespective of whether found in Prod/dev/test Can someone please help write the logic in Python or using BQ CLI script (whichever convenient) ? Thanks Continue reading...
