Bigquery + PHP examples - Bigquery + PHP examples

有人能提供在PHP中使用Bigquery API的工作示例吗。我看到有python和java的例子，但找不到任何PHP的例子。

这是bigquery浏览器https://bigquery.cloud.google.com/?pli=1

例如，您可以在浏览器中运行此SQL

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

我想通过PHP模拟类似的调用。

即使是一个如何使用PHP API的粗略例子也会有很大帮助。

使用Google API客户端PHP。下面是一个执行单个同步查询作业的脚本的简单示例。这使用在可下载的API客户端中找到的类名称。注意：从SVN中提取的源具有不同的类名。请注意，您必须为客户端机密、客户端id、重定向URI和项目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>

前面的答案有过时的代码。以下示例应与更新的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);
}

这是样本的修改版本，提供于：http://michaelheap.com/using-the-php-sdk-with-google-bigquery/服务帐户。要使用客户端帐户，您需要使用oauth2并有一个ping back地址。

我在查找示例时遇到了很多问题。这是一个基本的异步查询，但可以演示当前PHP API的使用，您可以在这里看到API异步查询的Python/Java示例：https://developers.google.com/bigquery/querying-data

请注意，我并不是在参考如何设置$client凭据，因为它在其他地方有很好的文档记录。

    $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);

/**
 * 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;
}