How Can I Create A New View In Bigquery Using The Python Api?

I have some code that automatically generates a bunch of different SQL queries that I would like to insert into the bigquery to generate views, though one of the issues that I have

Solution 1:

Using https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/insert

Submit something like below, assuming you add the authorization

{"view":{"query":"select column1, count(1) `project.dataset.someTable` group by 1","useLegacySql":false},"tableReference":{"tableId":"viewName","projectId":"projectName","datasetId":"datasetName"}}

Alternatively in Python using, assuming you have a service key setup and the environmental variable GOOGLE_APPLICATION_CREDENTIALS=/path/to/my/key. The one caveat is that as far as I can tell this can only create views using legacy sql, and as an extension can only be queried using legacy sql, though the straight API method allows legacy or standard.

from google.cloud import bigquery

defcreate_view(dataset_name, view_name, project, viewSQL):

    bigquery_client = bigquery.Client(project=project)

    dataset = bigquery_client.dataset(dataset_name)
    table = dataset.table(view_name)

    table.view_query = viewSQL

    try:
        table.create()
        returnTrueexcept Exception as err:
        print(err)
        returnFalse

Solution 2:

Note: this changed a little bit with 0.28.0 of the library - see the following for further details: Google BigQuery: creating a view via Python google-cloud-bigquery version 0.27.0 vs. 0.28.0

my example function

# create a view via pythondefcreate_view(dataset_name, view_name, sqlQuery, project=None):
    try:

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

        returnTrueexcept Exception as e:
        errorStr = 'ERROR (create_view): ' + str(e)
        print(errorStr)
        raise

Solution 3:

Everything that web UI or the bq tool does is made through the BigQuery API, so don't give up yet :).

Creating a view is akin to creating a table, just be sure to have a table resource that contains a view property when you call tables.insert().

• https://cloud.google.com/bigquery/querying-data#views
• https://cloud.google.com/bigquery/docs/reference/v2/tables#resource

Solution 4:

bigquery.version -> '1.10.0'

defcreate_view(client, dataset_name, view_name, view_query):
    try:
        dataset_ref = client.dataset(dataset_name)
        view = dataset_ref.table(view_name)
        # view.table_type = 'VIEW'
        view.view_query = view_query
        view.view_query_legacy_sql  = False
        client.create_table(view)
        passexcept Exception as e:
        errorStr = 'ERROR (create_view): ' + str(e)
        print(errorStr)
        raise

create a table not a view !!!!

This is the right code to create a view:

defcreate_view(client, dataset_name, view_name, view_query):
    try:
        dataset_ref = client.dataset(dataset_name)
        view_ref = dataset_ref.table(view_name)
        table = bigquery.Table(view_ref)
        table.view_query = view_query
        table.view_use_legacy_sql = False
        client.create_table(table)
    except Exception as e:
        errorStr = 'ERROR (create_view): ' + str(e)
        print(errorStr)
        raise

---

Is necessary

table = bigquery.Table(view_ref)