Google BigQuery API Client Example in PHP

Here is an example of how to use Google APIs Client Library for PHP in order for interact with Google BigQuery.
First you need to download Google BigQuery API client library for PHP (download from here).
Open it and use only the Google directory.
Before coding, please validate that you have a Google BigQuery account and that you have the credentials. In the following examples you need to supply:

      PROJECT_ID
      CLIENT_ID
      SERVICE_ACCOUNT_NAME

PHP class to interact with Google BigQuery API

<?php
 
require_once 'Google/Client.php';
require_once 'Google/Auth/AssertionCredentials.php';
require_once 'Google/Service/Bigquery.php';
 
class MyBigQueryClass {
	var $project_id;
	var $application_name;
	var $client_id;
	var $service_account_name;
	var $service;
 
	function MyBigQueryClass($project_id, $client_id, $service_account_name) {
		$this->project_id = $project_id;
		$this->application_name = "BigQuery PHP Example";
		$this->client_id = $client_id;
		$this->service_account_name = $service_account_name;
		$this->setGoogleServiceBigquery();
	}
 
	function getGoogleServiceBigquery(){
		return $this->service; // Google_Service_Bigquery
	}
 
	/* @throws Exception Google_Service_Exception if operation fail
	*/
	function loadFileToBigQuery($uri_array, $schema_array, $dataset, $table_name){
 
		$loadConfig = new Google_Service_Bigquery_JobConfigurationLoad();
		$loadConfig->setSchema($this->createSchema($schema_array));
		$loadConfig->setSourceUris($uri_array);
		$loadConfig->setDestinationTable($this->createDestinationTable($dataset, $table_name));
		$loadConfig->setCreateDisposition("CREATE_IF_NEEDED");
		$loadConfig->setWriteDisposition("WRITE_APPEND");
		$loadConfig->sourceFormat = 'CSV';
 
		$config = new Google_Service_Bigquery_JobConfiguration();
		$config->setDryRun(false);
		$config->setLoad($loadConfig);
 
		$job = new Google_Service_Bigquery_Job();
		$job->setConfiguration($config);
 
		// this may throw exception: Google_Service_Exception
		$job = $this->service->jobs->insert($this->project_id, $job);
 
		return $job;
	}
 
	function queryBigQuery($sql, $dataset, $table_name){
 
		$queryConfig = new Google_Service_Bigquery_JobConfigurationQuery();
		$queryConfig->setDestinationTable($this->createDestinationTable($dataset, $table_name));
		$queryConfig->setQuery($sql);
 
		$config = new Google_Service_Bigquery_JobConfiguration();
		$config->setDryRun(false);
		$config->setQuery($queryConfig);
 
		$job = new Google_Service_Bigquery_Job();
		$job->setConfiguration($config);
 
		$job = $this->service->jobs->insert($this->project_id, $job);
		return $job;
	}
 
	function requestBigQuery($sql){
		$query = new Google_Service_Bigquery_QueryRequest();
		$query->setQuery($sql);
		$query->setTimeoutMs(0);
		$response = $this->service->jobs->query($this->project_id, $query);
		$job_id = $response->getJobReference()->getJobId();
		$pageToken = null;
		do {
			$queryResults = $this->service->jobs->getQueryResults($this->project_id, $job_id);
			$queryResults->setPageToken($pageToken);
		} while (!$queryResults->getJobComplete());
		return $queryResults;
	}
 
 
	private function setGoogleServiceBigquery(){
		$client = new Google_Client();
		$client->setApplicationName($this->application_name);
		$client->setClientId($this->client_id);
		$key = file_get_contents('privatekey.p12');
		$cred = new Google_Auth_AssertionCredentials(
						$this->service_account_name,
						array('https://www.googleapis.com/auth/bigquery'),
						$key);
		$client->setAssertionCredentials($cred);
		if ($client->getAuth()->isAccessTokenExpired()) {
			$client->getAuth()->refreshTokenWithAssertion($cred);
			$service_token = $client->getAccessToken();
		}
		$this->service = new Google_Service_Bigquery($client); // Google_Service_Bigquery
	}
 
	private function createSchema($schema_array){
		$fields = array();
		foreach ($schema_array as $field){
			$table_field_schema = new Google_Service_Bigquery_TableFieldSchema;
			$table_field_schema->setName($field["name"]);
			$table_field_schema->setType($field["type"]);
			$fields[] = $table_field_schema;
		}
		// Create a tableschema
		$schema = new Google_Service_Bigquery_TableSchema();
		$schema->setFields($fields);
		return $schema;
	}
 
	private function createDestinationTable($dataset, $table_name){
		$destinationTable = new Google_Service_Bigquery_TableReference();
		$destinationTable->setProjectId($this->project_id);
		$destinationTable->setDatasetId($dataset);
		$destinationTable->setTableId($table_name);
		return $destinationTable;
	}
}
?>

PHP example code for querying bigquery and get results

<?php
 
$PROJECT_ID = "0123456789";
$CLIENT_ID = "0123456789-xxx.apps.googleusercontent.com";
$SERVICE_ACCOUNT_NAME = "0123456789-xxx@developer.gserviceaccount.com";
 
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
$sql = "select * from publicdata:samples.github_timeline limit 10";
try{
	$queryResults = $myBigQueryObject->requestBigQuery($sql);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
}
 
 
	$fields = $queryResults->getSchema()->getFields();
	$fieldNames = array();
	foreach ($fields as $field) {
		$fieldNames[] = $field['name'];
	}
	$row = $queryResults['rows'][0];
	//var_dump($fieldNames);
	var_dump($row);
?>

PHP example code for querying bigquery and insert result to new table

<?php
 
$PROJECT_ID = "0123456789";
$CLIENT_ID = "0123456789-xxx.apps.googleusercontent.com";
$SERVICE_ACCOUNT_NAME = "0123456789-xxx@developer.gserviceaccount.com";
 
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
$dataset = "bigquery_dataset";
$table_name = "php_example";
$sql = "select * from publicdata:samples.github_timeline limit 10";
 
try{
	$job = $myBigQueryObject->queryBigQuery($sql, $dataset, $table_name);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
}
 
    $status = new Google_Service_Bigquery_JobStatus();
    $status = $job->getStatus();
    //    print_r($status);
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
        die($err_res->getMessage());
    }
 
$jr = $job->getJobReference();
//var_dump($jr);
$jobId = $jr['jobId'];
if ($status)
    $state = $status['state'];
 
echo 'JOBID:' . $jobId . " ";
echo 'STATUS:' . $state;
exit;
?>

PHP example code for uploading a file from Google cloud to bigquery

<?php
$PROJECT_ID = "0123456789";
$CLIENT_ID = "0123456789-xxx.apps.googleusercontent.com";
$SERVICE_ACCOUNT_NAME = "0123456789-xxx@developer.gserviceaccount.com";
 
$SCHEMA = array(array('name' => 'firstField', 'type' => 'string'),
				array('name' => 'secondField', 'type' => 'string')
				);
 
$myBigQueryObject = new MyBigQueryClass($PROJECT_ID, $CLIENT_ID, $SERVICE_ACCOUNT_NAME);
 
$uri_array = array(
    // File paths here
    "gs://file/path/here/some_file.csv"
);
$dataset = "bigquery_dataset";
$table_name = "php_example";
try{
	$job = $myBigQueryObject->loadFileToBigQuery($uri_array, $SCHEMA, $dataset, $table_name);
}catch (Google_Service_Exception $e) {
    echo "Google Service Exception: " . $e->getMessage();
}
 
    $status = new Google_Service_Bigquery_JobStatus();
    $status = $job->getStatus();
//    print_r($status);
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
        die($err_res->getMessage());
    }
 
$jr = $job->getJobReference();
//var_dump($jr);
$jobId = $jr['jobId'];
if ($status)
    $state = $status['state'];
 
echo 'JOBID:' . $jobId . " ";
echo 'STATUS:' . $state;
exit;
 
?>

Useful links:

3 Comments
  1. Mic says:

    Hi I was running your code and it returned this error.

    PHP Fatal error: Uncaught exception ‘Google_Auth_Exception’ with message ‘Unable to parse the p12 file. Is this a .p12 file? Is the password correct? OpenSSL error: error:0D06B08E:asn1 encoding routines:ASN1_D2I_READ_BIO:not enough data’ in /home/michael/bigquery/google-api-php-client-master/src/Google/Signer/P12.php:55

    Where do I get the private key?

    I did generate an API key from the developer console page under APIs & Auth > credentials > API keys but maybe there is not where you got the private key?

  2. stetra says:

    Thank you. Very useful.

Leave a Reply

*