Can 2 result sets be viewed from bigquery.client.query()?

user_12560657 0
Viewed 398

I would like to run 2 Select parameters using the bigquery API. for example, if I run the below query

SELECT 1;
SELECT 2;

When I run this using the following python script I only obtain the result of 2nd query. 

def runquery();
    bqclient = bigquery.client()
    query = """ SELECT 1; 
                SELECT 2;"""
    query_job = bqclient.query(query)
    data = query_job.result()
    rows = list(data)
    print(rows)

Result:

[Row((2,), {'f0_': 0})]

But if I run the same query in bigquery query composer I would be able to view both the result.

Image from BQ Query Editor

How would I be able to get both the query results in Bigquery API resultset? Would I need to add a jobconfig to client.query() statement?

google-bigquery python-3.x python
1 Answers

Here's a quick example of walking a script. In this example you parent job is of type script, which is composed of two child jobs that are both select statements. Once the parent is complete, you can invoke list_jobs with a parent filter to find the child jobs and interrogate them for results. Child jobs don't nest, so you only have to worry about one level of children below the parent job.

def multi_statement_script():

    from google.cloud import bigquery

    bqclient = bigquery.Client()
    query = """ SELECT 1; 
                SELECT 2;
            """

    parent_query = bqclient.query(query)
    # wait for parent job to finish (which completes when all children are done)
    parent_query.result()

    print("parent job {}".format(parent_query.job_id))
    children = bqclient.list_jobs(parent_job=parent_query.job_id)

    # note the jobs are enumerated newest->oldest, so the reverse 
    # ordering specified in the script
    for child in children:
        print("job {}".format(child.job_id))
        rows = list(child.result())
        print(rows)
user_2124350
Related