× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

Google BigQuery integration with PHP

Marton
Nov 18, 2015
<p>Here is a code that </p> <ul> <li>properly creates a <code>Google_Client</code> using <a href="https://github.com/google/google-api-php-client" rel="nofollow">https://github.com/google/google-api-php-client</a></li> <li>runs a job async</li> <li>displays the running job ID and status</li> </ul> <p>You need to have:</p> <ul> <li>service account created (something like <code>...@developer.gserviceaccount.com</code>)</li> <li>your key file (<code>.p12</code>)</li> <li>service_token_file_location (writable path to store the JSON from the handshake, it will be valid for 1h)</li> </ul> <p>code sample:</p> <pre><code>function getGoogleClient($data = null) { global $service_token_file_location, $key_file_location, $service_account_name; $client = new Google_Client(); $client-&gt;setApplicationName("Client_Library_Examples"); $old_service_token = null; $service_token = @file_get_contents($service_token_file_location); $client-&gt;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-&gt;setAssertionCredentials($cred); if ($client-&gt;getAuth()-&gt;isAccessTokenExpired()) { $client-&gt;getAuth()-&gt;refreshTokenWithAssertion($cred); $service_token = $client-&gt;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-&gt;setDryRun(false); $queryConfig = new Google_Service_Bigquery_JobConfigurationQuery(); $config-&gt;setQuery($queryConfig); $job-&gt;setConfiguration($config); $destinationTable = new Google_Service_Bigquery_TableReference(); $destinationTable-&gt;setDatasetId(DATASET_ID); $destinationTable-&gt;setProjectId(PROJECT_ID); $destinationTable-&gt;setTableId('table1'); $queryConfig-&gt;setDestinationTable($destinationTable); $sql = "select * from publicdata:samples.github_timeline limit 10"; $queryConfig-&gt;setQuery($sql); try { // print_r($job); // exit; $job = $bq-&gt;jobs-&gt;insert(PROJECT_ID, $job); $status = new Google_Service_Bigquery_JobStatus(); $status = $job-&gt;getStatus(); // print_r($status); if ($status-&gt;count() != 0) { $err_res = $status-&gt;getErrorResult(); die($err_res-&gt;getMessage()); } } catch (Google_Service_Exception $e) { echo $e-&gt;getMessage(); exit; } //print_r($job); $jr = $job-&gt;getJobReference(); //var_dump($jr); $jobId = $jr['jobId']; if ($status) $state = $status['state']; echo 'JOBID:' . $jobId . " "; echo 'STATUS:' . $state; </code></pre> <p>You can grab the results with:</p> <pre><code>$res = $bq-&gt;jobs-&gt;getQueryResults(PROJECT_ID, $_GET['jobId'], array('timeoutMs' =&gt; 1000)); if (!$res-&gt;jobComplete) { echo "Job not yet complete"; exit; } echo "&lt;p&gt;Total rows: " . $res-&gt;totalRows . "&lt;/p&gt;\r\n"; //see the results made it as an object ok //print_r($res); $rows = $res-&gt;getRows(); $r = new Google_Service_Bigquery_TableRow(); $a = array(); foreach ($rows as $r) { $r = $r-&gt;getF(); $temp = array(); foreach ($r as $v) { $temp[] = $v-&gt;v; } $a[] = $temp; } print_r($a); </code></pre> <p>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.</p> <p><a href="https://github.com/google/google-api-php-client/blob/master/src/Google/Service/Bigquery.php" rel="nofollow">https://github.com/google/google-api-php-client/blob/master/src/Google/Service/Bigquery.php</a></p> <p>like:</p> <ul> <li>Google_Service_Bigquery_TableRow</li> </ul> <p>Also check out the questions tagged with [php] and [google-bigquery] <a href="http://stackoverflow.com/questions/tagged/google-bigquery+php">http://stackoverflow.com/questions/tagged/google-bigquery+php</a></p> <p>This tip was originally posted on <a href="http://stackoverflow.com/questions/30477858/Google%20BigQuery%20integration%20with%20PHP/30478266">Stack Overflow</a>.</p>
comments powered by Disqus