CODEWITHSUNDEEP
google-bigquery
Ravindra
Ravindra

Reputation: 2271

How to save the Bigquery result as the table from PHP Script?

I query the Big Query table from PHP script and get the result. I want to save the result in new resultant table for future need...

Upvotes: 3

Views: 1173

Answers (3)

Pentium10
Pentium10

Reputation: 207838

You need to set the destinationTable with the call, the results will be written to the table you set.

https://developers.google.com/bigquery/querying-data#asyncqueries

Upvotes: 2

user6392101
user6392101

Reputation: 638

Passing parameters to BQ call is tricky.

This should work in more recent cloud library versions:

public function runBigQueryJobIntoTable($query, $project, $dataset, $table)
{
    $bigQuery = new BigQueryClient(['projectId' => $project]);

    $destinationTable = $bigQuery->dataset($dataset)->table($table);
    $queryJobConfig = $bigQuery->query($query)->destinationTable($destinationTable);

    $job = $bigQuery->startQuery($queryJobConfig);
    $queryResults = $job->queryResults();

    while (!$queryResults->isComplete()) {
        sleep(1);
        $queryResults->reload();
    }

    return true;
}

For old versions:

public function runBigQueryJobIntoTable($query, $project, $dataset, $table)
{
    $bigQuery = new BigQueryClient(['projectId' => $project]);

    $jobConfig = [
        'destinationTable' => [
            'projectId' => $project,
            'datasetId' => $dataset,
            'tableId' => $table
        ]
    ];

    $job = $bigQuery->runQueryAsJob($query, ['jobConfig' => $jobConfig]);
    $queryResults = $job->queryResults();

    while (!$queryResults->isComplete()) {
        sleep(1);
        $queryResults->reload();
    }

    return true;
}

Upvotes: 2

Jordan Tigani
Jordan Tigani

Reputation: 26617

The option @Pentium10 mentioned works, but there is another way to do it, when you already have query results and you want to save them.

All queries in BigQuery generate output tables. If you don't specify your own destination table, the output table will be an automatically generated table that only sticks around for 24 hours. You can, however, copy that temporary table to a new destination table and it will stick around for as long as you like.

To get the destination table, you need to look up the job (if you use the jobs.query() api, the job id is in the jobReference field of the response (see here). To look up the job, you can use jobs.get() with that job id, and you'll get back the destination table information (datasetId and tableId) from the configuration.query.destinationTable (the job object is described here).

You can copy that destination table to your own table by using the jobs.insert() call with a copy configuration section filled out. Info on copying a table is here.

Upvotes: 2

Related Questions