Python BigQuery API - get table schema/header

Question

Given a query example like

import uuid

from google.cloud import bigquery


def query_shakespeare():
    client = bigquery.Client()
    query_job = client.run_async_query(str(uuid.uuid4()), """
        #standardSQL
        SELECT corpus AS title, COUNT(*) AS unique_words
        FROM `publicdata.samples.shakespeare`
        GROUP BY title
        ORDER BY unique_words DESC
        LIMIT 10""")

    query_job.begin()
    query_job.result()  # Wait for job to complete.

    destination_table = query_job.destination
    destination_table.reload()
    for row in destination_table.fetch_data():
        print(row)


if __name__ == '__main__':
    query_shakespeare()

how can I get the schema of the table? row, in the previous example has the form

Row(('august', -1, 'aaa', 333), {'col1': 0, 'col2': 1, 'col3': 2})

but I can't find, for the package google-cloud-bigquery==0.28.0 the way to extract the header JSON. Of course, also the extraction of the table schema would be fine for me, but the current Google documentation looks not to work for the last version...

Willian Fuks · Accepted Answer · 2017-11-08 20:18:54Z

5

If you need the schema of the table you just queried, you can get it from the result method from the QueryJob:

client = bq.Client()
query = """
         #standardSQL
         SELECT corpus AS title, COUNT(*) AS unique_words
         FROM `publicdata.samples.shakespeare`
         GROUP BY title
         ORDER BY unique_words DESC
         LIMIT 10"""
query_job = client.query(query)
result = query_job.result()

schema = result.schema

Result:

[SchemaField(u'title', u'string', u'NULLABLE', None, ()),
 SchemaField(u'unique_words', u'integer', u'NULLABLE', None, ())]

(The code you presented in your question is related to version 0.27).

As for the question of getting the header JSON, not sure if I understood it correctly but as it seems you need the schema to find where the json is located (I'm guessing here).

answered Nov 8, 2017 at 20:18

Willian Fuks

11.9k10 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Illegal Operator Over a year ago

Very interesting, because the documentation says table.schema

Shriganesh Kolhe · Accepted Answer · 2019-07-07 18:02:47Z

1

table_ref = client.dataset(dataset_id).table(table_id)
table = client.get_table(table_ref)
original_schema = table.schema

this code will print table schema. In my case, it was following output

[SchemaField('guidislink', 'STRING', 'NULLABLE', None, ()),
 SchemaField('id', 'STRING', 'NULLABLE', None, ()),
 SchemaField('link', 'STRING', 'NULLABLE', None, ())]

answered Jul 7, 2019 at 18:02

Shriganesh Kolhe

2534 silver badges5 bronze badges

Collectives™ on Stack Overflow

Python BigQuery API - get table schema/header

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related