Google BigQuery: creating a view via Python google-cloud-bigquery version 0.27.0 vs. 0.28.0

Question

All,

I'm having trouble creating a Google BigQuery view in python with the version 0.28 of the bq library that has come out about two weeks ago. I'm quite certain the problem is on my side, something I'm missing, but I cannot find the issue.

Please be gentle, I don't ask lots of questions online but I'm quite stumped. I'm also not completely incompetent, here are some details:

1. I have my GOOGLE_APPLICATION_CREDENTIALS set correctly
2. All the other commands I've run against bq via python are fine

3. I've reviewed the https://cloud.google.com/bigquery/docs/python-client-migration

4. I think the issue is around the "fix" https://github.com/GoogleCloudPlatform/google-cloud-python/pull/4038 BigQuery: replaces table.create() with client.create_table() #4038

5. I've tried legacy vs. standard sql
6. I'm on python 2.7.12 (can't upgrade anytime soon, corporate version thing)

The issue? The code in the second block below creates a TABLE, with no schema and no records. It clearly should create a VIEW instead, right?

sudo pip install -Iv google-cloud-bigquery==0.27.0

from google.cloud import bigquery

project=None
dataset_name = 'my_dataset_id'
view_name = 'vw_dummy_data20'
sqlQuery = 'select record_id as id, UPPER(first_name) as first_name, UPPER(last_name) as last_name from [my_project_code:my_dataset_id.dummy_data13]'

bigquery_client = bigquery.Client(project=project)
dataset = bigquery_client.dataset(dataset_name)
table = dataset.table(view_name)
table.view_query = sqlQuery

table.create()

---

the above works fine, view created, great!

the below, only a table is created, no rows, no schema, yuck!

---

sudo pip uninstall google-cloud-bigquery

sudo pip install -Iv google-cloud-bigquery==0.28.0

from google.cloud import bigquery

project=None
dataset_name = 'my_dataset_id'
view_name = 'vw_dummy_data21'
sqlQuery = 'select record_id as id, UPPER(first_name) as first_name, UPPER(last_name) as last_name from [my_project_code:my_dataset_id.dummy_data13]'

bigquery_client = bigquery.Client(project=project)
dataset_ref = bigquery_client.dataset(dataset_name)
table_ref = dataset_ref.table(view_name)
table_ref.view_query = sqlQuery
table_ref.view_use_legacy_sql = True

table = bigquery.Table(table_ref)
bigquery_client.create_table(table)

Other links:

• How can I create a new view in bigquery using the python API?
• https://googlecloudplatform.github.io/google-cloud-python/latest/bigquery/usage.html

Any useful thoughts would be very much appreciated.

Thanks and best regards...Rich

Answer 1

You were so close!

The issue is with the lines

table_ref.view_query = sqlQuery
table_ref.view_use_legacy_sql = True

a TableReference class does not contain these properties. Instead, you must populate them on the Table class, as in

table = bigquery.Table(table_ref)
table.view_query = sqlQuery
table.view_use_legacy_sql = True

bigquery_client.create_table(table)

Answer 2

Tim's answer was perfect, thank you very much.

Here is the final code:

from google.cloud import bigquery
bigquery_client = bigquery.Client(project=project)
dataset_ref = bigquery_client.dataset(dataset_name)
table_ref = dataset_ref.table(view_name)
table = bigquery.Table(table_ref)
table.view_query = sqlQuery
table.view_use_legacy_sql = True

bigquery_client.create_table(table)