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
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]