How to get all results from multiple queries from big query?

user_7468597 0
Viewed 900

I use the firebase cloud function and I have a function that gets an SQL request and calls bigquery and returns the results to my iOS/Android app. but if I want to send multiple requests I just get 1 result. I read about that and I found that I need to do it with jobs, somebody can help me with that?

exports.callBigQuery = async (data, context) => {
    const queryFrom = data.text;
    const [rows] = [];
    const options = {
        query: queryFrom,
    };
    const [jobs] = await bigqueryClient.createQueryJob(options);
    jobs.forEach(job => { 
        const item = job.getQueryResults();
        rows.push(item);
        console.log(item); 
    }); 
    console.log(rows);
    return rows;
};

This is the query that i send to the "callBigQuery" function(if I run it on the bigquery console I get 2 results):

 let str = "SELECT * FROM 'table_name_1' where isWorking = 'true' limit 1; SELECT * FROM `table_name_2` where isWorking = 'true'"
google-bigquery node.js nodes
1 Answers

Your query is interpreted as a script. Considering that BigQuery UI shows results of a script as link to the single queries. There is probably a way to search in the logs for the job ID of each query and then get the results. I think that the easies solution would be to run one query read the results and then repeat for the second query. You could even run the 2 queries in parallel instead of sequentially as they are executed in the script.

Edited:

From the BigQuery scripting docs

Scripts are executed in BigQuery using jobs.insert, similar to any other query, with the multi-statement script specified as the query text. When a script executes, additional jobs, known as child jobs, are created for each statement in the script. You can enumerate the child jobs of a script by calling jobs.list, passing in the script’s job ID as the parentJobId parameter.

When jobs.getQueryResults is invoked on a script, it will return the query results for the last SELECT, DML, or DDL statement to execute in the script, with no query results if none of the above statements have executed. To obtain the results of all statements in the script, enumerate the child jobs and call jobs.getQueryResults on each of them.

So the only way to do it is by listing all the jobs that the script triggers and get the results one-by-one.

In my humble opinion, it is much more complicated than run the queries separately. But if you really need to do it, the solution is explained in the docs.

user_40180470
Related