BigQuery + PHP Query Select Get Temporary Table

Question

I a little confuse because I hard to find detail example of documentation how to this

I want to query data at temporary table from previous select query in BigQuery using API on PHP

function run_query($query, $useLegacySql, &$result)
{ 
        $builder = new ServiceBuilder([
                'projectId' => 'PROJECTID',
        ]);

        $job = new Google_Job();

        $bigQuery = $builder->bigQuery();

        $queryResults = $bigQuery->runQuery(
            $query,
            ['useLegacySql' => $useLegacySql]);

        if ($queryResults->isComplete()) 
        {
            $i = 0;
            $rows = $queryResults->rows();

            foreach ($rows as $row) 
            {
                $i++;

                $result[$i] = $row;
            }
        } 
        else 
        {
            throw new Exception('The query failed to complete');
        }
}

So my question is if I run this function to execute my select query for example "SELECT username FROM user_activities", how can I get the temporary table from this select query and execute the next query from this temporary table?

I know there is Jobs API on BigQuery but I still confuse how to implement it?

Thanks

Pentium10 Pentium10 · Accepted Answer · 2016-11-18T16:44:17

You need to replace this section of the code:

 $queryResults = $bigQuery->runQuery(
            $query,
            ['useLegacySql' => $useLegacySql]);

with:

$job = $bigQuery->runQueryAsJob($query);
$isComplete = false;
$queryResults = $job->queryResults();

while (!$isComplete) {
    sleep(1); // let's wait for a moment...
    $queryResults->reload(); // trigger a network request
    $isComplete = $queryResults->isComplete(); // check the query's status
}

!!! You also need to poll now until a job is finished, the documenation link is included in the bottom, then you can use:

$info=$job->info();
print_r($info);

and this returns you a bunch of info about your job such as the destinationTable

Array
(
    [kind] => bigquery#job
    [etag] => "<edited>"
    [id] => <edited>
    [selfLink] => https://www.googleapis.com/bigquery/v2/projects/<edited>
    [jobReference] => Array
        (
            [projectId] => <edited>
            [jobId] => <edited>
        )

    [configuration] => Array
        (
            [query] => Array
                (
                    [query] => SELECT repository_url, 
       repository_has_downloads 
FROM   [publicdata:samples.github_timeline]
LIMIT  10
                    [destinationTable] => Array
                        (
                            [projectId] => <edited>
                            [datasetId] => <edited>
                            [tableId] => anon734f2eebd13d01c49c65dce7359aeab1df285473
                        )

                    [createDisposition] => CREATE_IF_NEEDED
                    [writeDisposition] => WRITE_TRUNCATE
                )

        )

    [status] => Array
        (
            [state] => RUNNING
        )

    [statistics] => Array
        (
            [creationTime] => 1479487171996
            [startTime] => 1479487172576
        )

    [user_email] => <edited>
)

see more documentation here

BigQuery + PHP Query Select Get Temporary Table

1 Answers