CODEWITHSUNDEEP
phpgoogle-bigquery
Nilesh
Nilesh

Reputation: 2115

Bigquery + PHP examples

Can somebody provide working example of using the Bigquery API with PHP. I see there are examples for python and java but could not find anything for PHP.

Here is the bigquery browser https://bigquery.cloud.google.com/?pli=1

For e.g You can run this SQL in the browser

SELECT corpus,count(*) FROM publicdata:samples.shakespeare 
group by corpus limit 5;

I want to simulate similar call via PHP.

Even a rough example of how to use the PHP API will help a lot.

Upvotes: 6

Views: 18615

Answers (4)

dparks
dparks

Reputation: 51

The previous answers have outdated code. The following example should work with the more recent API (https://github.com/google/google-api-php-client/blob/master/src/Google/Service/Bigquery.php) :

require_once '../source/application/libraries/Google/autoload.php';

public function createGClient(){
  define("CLIENT_ID", "{PROJECT_ID}.apps.googleusercontent.com");
  define("SERVICE_ACCOUNT_NAME","{SERVICE_ACCOUNT EMAIL FROM CONSOLE}");
  define("KEY_FILE",'../{FILENAME}.p12');

  define("PROJECT_ID","{PROJECT_ID}");
  define("DATASET_ID","{DATASET_ID}");
  define("TABLE_ID","");

  $this->client = new Google_Client();
  $this->client->setApplicationName("{NAME}");

  $key = file_get_contents(KEY_FILE);
  $this->client->setAssertionCredentials(new Google_Auth_AssertionCredentials(SERVICE_ACCOUNT_NAME, array('https://www.googleapis.com/auth/bigquery'), $key, "notasecret"));
  $this->client->setClientId(CLIENT_ID);

  $this->service = new Google_Service_Bigquery($this->client);
}

public function runQuery(){
  // To see the a list of tables  
  print_r($this->service->tables->listTables(PROJECT_ID, DATASET_ID));

  // To see details of a table
  print_r($this->service->tables->get(PROJECT_ID, DATASET_ID, TABLE_ID));

  // To query a table
  $jobs = $this->service->jobs;
  $query = new Google_Service_Bigquery_QueryRequest();
  $query->setQuery("SELECT * FROM wherever;");
  $response = $jobs->query(PROJECT_ID, $query);
  print_r($response);
}

This is a modified version of the sample given at: http://michaelheap.com/using-the-php-sdk-with-google-bigquery/ for a service account. To use a client account you would need to use oauth2 and have a pingback address.

Upvotes: 3

Deepak Mittal
Deepak Mittal

Reputation: 199

/**
 * Executes and returns bigQuery response with 'INTERACTIVE' priority
 * $this->service is the object of Google_Service_Bigquery
 *          $this->service = new Google_Service_Bigquery($this->client);
 * @param String $sql
 * @return Google_Service_Bigquery_GetQueryResultsResponse
 */
public function execute($sql) {
    $job = new Google_Service_Bigquery_Job();
    $config = new Google_Service_Bigquery_JobConfiguration();
    $queryConfig = new Google_Service_Bigquery_JobConfigurationQuery();
    $queryConfig->setQuery($sql);
    /**
     * Priority is set to INTERACTIVE for faster response options are 'BATCH'/'INTERACTIVE' 
     */
    $queryConfig->setPriority("INTERACTIVE");
    $config->setQuery($queryConfig);
    $job->setId(md5("$sql_{microtime()}"));
    $job->setConfiguration($config);

    $running = $this->service->jobs->insert('divine-builder-586', $job);
    /* @var $running Google_Service_Bigquery_Job */
    $jr = $running->getJobReference();
    $jobId = $jr['jobId'];
    $res = $this->service->jobs->getQueryResults('divine-builder-586', $jobId);
    /* @var $res Google_Service_Bigquery_GetQueryResultsResponse */
    return $res;
}

Upvotes: 1

yoepus
yoepus

Reputation: 11

I had a lot of issues finding examples. This is a basic async query, but can demonstrate current PHP API usage, you can see the Python/Java example of the API for async queries here: https://developers.google.com/bigquery/querying-data

Please note, I am not referencing how to setup $client credentials, as it is well documented elsewhere.

    $bq = new Google_BigqueryService($client);

    //build query
    $sql = 'select * from example.table LIMIT 10';

    $job = new Google_Job();
    $config = new Google_JobConfiguration();
    $queryConfig = new Google_JobConfigurationQuery();
    $config->setQuery($queryConfig);

    $job->setConfiguration($config);
    $queryConfig->setQuery($sql);

    $insert = new Google_Job($bq->jobs->insert(PROJECT_ID,$job));
    $jr = $insert->getJobReference();
    $jobId = $jr['jobId'];

    $res = new Google_GetQueryResultsResponse($bq->jobs->getQueryResults(PROJECT_ID, $jobId));

//see the results made it as an object ok:
        var_dump($results);

Upvotes: 1

Michael Manoochehri
Michael Manoochehri

Reputation: 7877

Use the Google API Client for PHP. Here's a simple example of a script that does a single synchronous query job. This uses the class names found in the downloadable API client. Note: the source pulled from SVN features different class names. Note where you must add your own values for client secret, client id, redirect URI, and project id.

<?php

require_once 'google-api-php-client/src/apiClient.php';
require_once 'google-api-php-client/src/contrib/apiBigqueryService.php';

session_start();

$client = new apiClient();
// Visit https://developers.google.com/console to generate your
// oauth2_client_id, oauth2_client_secret, and to register your oauth2_redirect_uri.

$client->setClientId('XXXXXXXXXXXXXXX.apps.googleusercontent.com');
$client->setClientSecret('XXXXXXXXXXXXXXXXXXX');
$client->setRedirectUri('http://www_your_domain.com/somescript.php');

// Your project id
$project_id = 'XXXXXXXXXXXXXXXXXXXX';

// Instantiate a new BigQuery Client 
$bigqueryService = new apiBigqueryService($client);

if (isset($_REQUEST['logout'])) {
  unset($_SESSION['access_token']);
}

if (isset($_SESSION['access_token'])) {
  $client->setAccessToken($_SESSION['access_token']);
} else {
  $client->setAccessToken($client->authenticate());
  $_SESSION['access_token'] = $client->getAccessToken();
}

if (isset($_GET['code'])) {
  $redirect = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'];
  header('Location: ' . filter_var($redirect, FILTER_SANITIZE_URL));
}
?>
<!doctype html>
<html>
<head>
  <title>BigQuery API Sample</title>
</head>
<body>
<div id='container'>
  <div id='top'><h1>BigQuery API Sample</h1></div>
  <div id='main'>
<?php
  $query = new QueryRequest();
  $query->setQuery('SELECT TOP( title, 10) as title, COUNT(*) as revision_count FROM [publicdata:samples.wikipedia] WHERE wp_namespace = 0;');

  $jobs = $bigqueryService->jobs;
  $response = $jobs->query($project_id, $query);

  // Do something with the BigQuery API $response data
  print_r($response);

?>
  </div>
</div>
</body>
</html>

Upvotes: 13

Related Questions