[Python] Checking for Table Existence in BigQuery using Python or BQ CLI (shell-script)

I have a simple text file containing a list of BigQuery tables (TableA, TableB etc), for each of which the following is required -

• Search the table in all datasets present in all envs (projects)
• If a table is present in Prod (determined by the project-name) in any dataset, and is also present (in any dataset) in Dev and Test, then truncate/delete its contents in Dev and Test env - Do not DROP it
• If the table is not present in Prod, but present in Dev and Test, then DROP the table from Dev and Test env
• However, if the table belongs to any staging dataset (identified as '*_stg') irrespective of the env, then just delete/truncate it in dev and Test - do not DROP it

Assume the following examples-
Project Names: 'my-project-dev', 'my-project-test', 'my-project-prod'
Dataset Names: 'my_dataset_hist', 'my_dataset_curr', 'my_dataset_stg'

Please note that the datasets the tables belong to are not specified in the input list and hence the tables need to be searched in ALL datasets (for any specific project env)
Currently the above is performed manually in BigQuery, however, I am trying to automate it in Python or by shell-script using bq-cli.

Can someone please help write the code in Python or BQ CLI ?

Thanks.

Continue reading...