BigQuery - Check if table already exists

activelearner picture activelearner · Feb 25, 2015 · Viewed 23k times · Source

I have a dataset in BigQuery. This dataset contains multiple tables.

I am doing the following steps programmatically using the BigQuery API:

  1. Querying the tables in the dataset - Since my response is too large, I am enabling allowLargeResults parameter and diverting my response to a destination table.

  2. I am then exporting the data from the destination table to a GCS bucket.

Requirements:

  • Suppose my process fails at Step 2, I would like to re-run this step.

  • But before I re-run, I would like to check/verify that the specific destination table named 'xyz' already exists in the dataset.

  • If it exists, I would like to re-run step 2.

  • If it does not exist, I would like to do foo.

How can I do this?

Thanks in advance.

Answer

tarheel picture tarheel · Jan 5, 2018

Alex F's solution works on v0.27, but will not work on later versions. In order to migrate to v0.28+, the below solution will work.

from google.cloud import bigquery

project_nm = 'gc_project_nm'
dataset_nm = 'ds_nm'
table_nm = 'tbl_nm'

client = bigquery.Client(project_nm)
dataset = client.dataset(dataset_nm)
table_ref = dataset.table(table_nm)

def if_tbl_exists(client, table_ref):
    from google.cloud.exceptions import NotFound
    try:
        client.get_table(table_ref)
        return True
    except NotFound:
        return False

if_tbl_exists(client, table_ref)