Change bigquery view definition with python

Question

I'm trying to update a view in bigquery via python. I've been able to create the view using the following approach;

def createView(client):
    viewDataset = 'dataset'
    viewName = 'name'
    view_ref = client.dataset(viewDataset).table(viewName)
    view = bigquery.Table(view_ref)
    view_sql = """
    select * from '{}.{}' where thing = 2
    """".format(viewDataSet, viewName)
    view.view_query = view_sql
    client.create_table(view)

(Code for explanation purposes)

This worked fine and created the view. I then wanted to run a function that updates the view definition. I reused the same code and it failed with an error saying the view exists already - this makes sense. I then followed this example here; https://cloud.google.com/bigquery/docs/managing-views

Using the code to update a views SQL query. Basically I swapped the line

client.create_table(view)

for

client.update_table(view)

I get an error saying I have not added the fields attribute... Being a view, I though I wouldn't have to do this.

Can anyone tell me the correct way to use python to update an existing bigquery view?

Cheers

Answer 1

Look! You are using:

"select * from '{}.{}' where thing = 2"

Notice this:

from '{}.{}'

But a table should be referenced as:

from '{}.{}.{}'

This piece of code works to me:

from google.cloud import bigquery

if __name__ == "__main__":
    client = bigquery.Client()

    dataset_view_id= 'dataset_name'
    table_view_id = 'view_name'

    view = bigquery.Table(client.dataset(dataset_view_id).table(table_view_id))


    ##############        
    ###what was in that table? request table info
    ##############
    get_view = client.get_table(view)  # API Request

    # Display OLD view properties
    print('View at {}'.format(get_view.full_table_id))
    print('View Query:\n{}'.format(get_view.view_query))


    ##############
    #update the table:
    ##############
    sql_template = (
        'SELECT * FROM `{}.{}.{}` where disease="POLIO" ')

    source_project_id = "project_from_the_query"
    source_dataset_id = "dataset_from_the_query"
    source_table_id = "table_from_the_query"

    view.view_query = sql_template.format(source_project_id, source_dataset_id, source_table_id)

    view = client.update_table(view, ['view_query'])  # API request


    ##############
    #Now print the view query to be sure it's been updated:
    ##############    
    get_view = client.get_table(view)  # API Request

    # Display view properties
    print('\n\n NEW View at {}'.format(get_view.full_table_id))
    print('View Query:\n{}'.format(get_view.view_query))
    # [END bigquery_get_view]