Bigquery + PHP Examples

Bigquery + PHP examples

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>

BigQuery PHP simple query example

The library has lots of example and also the linked question has some answers. You can also check out the other questions on the php+google bigquery tag combo.

https://github.com/google/google-api-php-client

Authenticate and use Google's BigQuery in my php code

Here is the code snippet:

public function getServiceBuilder() {
            putenv('GOOGLE_APPLICATION_CREDENTIALS=path_to_service_account.json');

        $builder = new ServiceBuilder(array(
            'projectId' => PROJECT_ID
        ));

        return $builder;
}

then you can use like this

$builder = $this->getServiceBuilder();
    $bigQuery = $builder->bigQuery();
    $job = $bigQuery->runQueryAsJob('SELECT .......');

    $backoff = new ExponentialBackoff(8);
    $backoff->execute(function () use ($job) {
        $job->reload();
        $this->e('reloading job');
        if (!$job->isComplete()) {
            throw new \Exception();
        }
    });
    if (!$job->isComplete()) {
        $this->e('Job failed to complete within the allotted time.');
        return false;
    }

    $queryResults = $job->queryResults();

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

        foreach ($rows as $row) {

(edited)

you may need to add:

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\ServiceBuilder;
use Google\Cloud\ExponentialBackoff;

and composer.json (this is just an example it may differ the actual version)

{
    "require": {
        "google/cloud": "^0.99.0"
    }
}

Creating a table in BigQuery using the PHP api with schema

There is no such thing as $fieldString that is $fields array

like this:

 $fields = [
    [
        'name' => 'field1',
        'type' => 'string',
        'mode' => 'required'
    ],
    [
        'name' => 'field2',
        'type' => 'integer'
    ],
];

then

$schema = ['fields' => $fields];
$table = $dataset->createTable($tableId, ['schema' => $schema]);

See example here.

Google BigQuery integration with PHP

Here is a code that

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 ...@developer.gserviceaccount.com)
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

Use bigquery php client library to create an external table link to cloud storage

It is available through the client library. In the example snippet, you provided, the 'schema' is being specified as an 'option'. The external table definition is another option you can specify. Here is the underlying Table resource: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#resource

The externalDataConfiguration part is what you need to specify to create a external (aka Federated) BigQuery table.

Bigquery + PHP Examples