Google BigQuery - Automating a Cron Job

Question

Using PHP, I am able to run BigQuery manually but I need BigQuery to run as an automatic cron job (without Gmail login). How would I accomplish this? Thanks.

Answer 1

You need to create a service account in the developer console, than you will be able to use from code. Here is a code that we have in our cron files.

• properly creates a Google_Client using https://github.com/google/google-api-php-client
• runs a job async
• displays the running job ID and status

You need to have:

• service account created (something like [email protected])
• your key file (.p12)
• service_token_file_location (writable path to store the JSON from the handshake, it will be valid for 1h)

code sample:

function getGoogleClient($data = null) {
    global $service_token_file_location, $key_file_location, $service_account_name;
    $client = new Google_Client();
    $client->setApplicationName("Client_Library_Examples");

    $old_service_token = null;
    $service_token = @file_get_contents($service_token_file_location);
    $client->setAccessToken($service_token);
    $key = file_get_contents($key_file_location);
    $cred = new Google_Auth_AssertionCredentials(
            $service_account_name, array(
        'https://www.googleapis.com/auth/bigquery',
        'https://www.googleapis.com/auth/devstorage.full_control'
            ), $key
    );
    $client->setAssertionCredentials($cred);
    if ($client->getAuth()->isAccessTokenExpired()) {
        $client->getAuth()->refreshTokenWithAssertion($cred);
        $service_token = $client->getAccessToken();
    }
    return $client;
}

$client = getGoogleClient();
$bq = new Google_Service_Bigquery($client);

/**
 * @see https://developers.google.com/bigquery/docs/reference/v2/jobs#resource
 */
$job = new Google_Service_Bigquery_Job();
$config = new Google_Service_Bigquery_JobConfiguration();
$config->setDryRun(false);
$queryConfig = new Google_Service_Bigquery_JobConfigurationQuery();
$config->setQuery($queryConfig);

$job->setConfiguration($config);

$destinationTable = new Google_Service_Bigquery_TableReference();
$destinationTable->setDatasetId(DATASET_ID);
$destinationTable->setProjectId(PROJECT_ID);
$destinationTable->setTableId('table1');

$queryConfig->setDestinationTable($destinationTable);

$sql = "select * from publicdata:samples.github_timeline limit 10";
$queryConfig->setQuery($sql);

try {
//    print_r($job);
//    exit;
    $job = $bq->jobs->insert(PROJECT_ID, $job);

    $status = new Google_Service_Bigquery_JobStatus();
    $status = $job->getStatus();
//    print_r($status);
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
        die($err_res->getMessage());
    }
} catch (Google_Service_Exception $e) {
    echo $e->getMessage();
    exit;
}
//print_r($job);
$jr = $job->getJobReference();
//var_dump($jr);
$jobId = $jr['jobId'];
if ($status)
    $state = $status['state'];

echo 'JOBID:' . $jobId . " ";
echo 'STATUS:' . $state;

You can grab the results with:

$res = $bq->jobs->getQueryResults(PROJECT_ID, $_GET['jobId'], array('timeoutMs' => 1000));

if (!$res->jobComplete) {
    echo "Job not yet complete";
    exit;
}
echo "<p>Total rows: " . $res->totalRows . "</p>\r\n";
//see the results made it as an object ok
//print_r($res);
$rows = $res->getRows();
$r = new Google_Service_Bigquery_TableRow();
$a = array();
foreach ($rows as $r) {
    $r = $r->getF();
    $temp = array();
    foreach ($r as $v) {
        $temp[] = $v->v;
    }
    $a[] = $temp;
}
print_r($a);

You can see here the classes that you can use for your other BigQuery calls. When you read the file, please know that file is being generated from other sources, hence it looks strange for PHP, and you need to learn reading it in order to be able to use the methods from it.

https://github.com/google/google-api-php-client/blob/master/src/Google/Service/Bigquery.php

like:

• Google_Service_Bigquery_TableRow

Also check out the questions tagged with [php] and [google-bigquery] https://stackoverflow.com/questions/tagged/google-bigquery+php