CODEWITHSUNDEEP
phpapiintegrationgoogle-bigquery
Amit Patange
Amit Patange

Reputation: 189

Google BigQuery integration with PHP

I need a help to integrate google bigquery code into the PHP. So i can execute query and other kind of operation from php code itself.

Need your help and suggest me some working examples link.

Thanks in advance.

Upvotes: 3

Views: 1820

Answers (2)

U.Malik
U.Malik

Reputation: 111

You can use the following steps

  1. In the Cloud Console, on the project selector page, select or create a Cloud project
  2. Enable the BigQuery API.
  3. Set up authentication
  4. In the Cloud Console, go to the Create service account key page.
  5. Set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of the JSON file that contains your service account key. This variable only applies to your current shell session, so if you open a new session, set the variable again

For full guide please see this doc here

Upvotes: 1

Pentium10
Pentium10

Reputation: 207830

Here is a code that

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

Upvotes: 5

Related Questions