在codeigniter中使用google client library倒不是什麼難事，但使用Google Service Account比較少人提到，多數人都是撈回GA；剛好最近有機會實作取得Bigquery，順手寫一下筆記。
Step1
先下載Google APIs Client Library for PHP。這邊都是以codeigniter這個Framework為主，其他應該也差不多；我把他放在「application/third_party/Google」下
Step2
在「application/third_party/」先新增一個init程式(google_init.php)，在controller中我會習慣call這支程式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php set_include_path(get_include_path() . PATH_SEPARATOR . APPPATH.'third_party/'); require_once(APPPATH . 'third_party/Google/Client.php'); require_once(APPPATH . 'third_party/Google/Service/Bigquery.php'); function google_init() { $PATH_TO_API = ''; $PATH_TO_PRIVATE_KEY_FILE = './assets/yourCertificate.p12'; $APP_NAME = 'Your APP Name'; $APP_EMAIL ='Your Google Service Account Id'; $CLIENT_ID = 'Your Client ID'; $client = new Google_Client(); $client->setApplicationName($APP_NAME); try { $cred = new Google_Auth_AssertionCredentials( $APP_EMAIL, // email array('https://www.googleapis.com/auth/bigquery'), file_get_contents( $PATH_TO_PRIVATE_KEY_FILE ) // keyfile you downloaded ); $client->setAssertionCredentials($cred); if($client->getAuth()->isAccessTokenExpired()) { $client->getAuth()->refreshTokenWithAssertion($cred); } $bigquery = new Google_Service_Bigquery($client); return $bigquery; } catch (Exception $e) { echo 'There was an error: ' . $e->getMessage(); } } |
其中Service Account Id是需要進到Google Cloud PlatForm中的「Iam & Admin」去新增
然後要把憑證檔下載回來！先create key或是新增account時直接下載p12的檔案
下載p12檔案
這個步驟極為重要，用於網頁自動驗證，不然的話就只能走Oauth的流程。這個檔案下載回來就是上述程式中的「yourCertificate.p12」
Step3
使用時在controller中先宣告即可
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); use Google\Cloud\BigQuery\BigQueryClient; class Bigquery extends CI_Controller { public function __construct() { parent::__construct(); require_once(APPPATH . 'third_party/google_init.php'); $this->bigquery = google_init(); } |
$this->bigquery 就可以直接執行需要運算了。
實際運用上的話，拿一段要在BigQuery上執行的sql，寫法如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
public function runSql($str_sql) { $project_id = $this->config->item("project_id"); $obj_query = new Google_Service_Bigquery_QueryRequest(); $obj_query->setQuery($str_sql); $obj_bigquery = $this->bigquery; $obj_result = $obj_bigquery->jobs->query($project_id, $obj_query); $isComplete = $obj_result->jobComplete; while (!$isComplete) { sleep(1); // let's wait for a moment... $obj_result = $obj_bigquery->jobs->getQueryResults($project_id, $obj_result->jobReference->jobId); $isComplete = $obj_result->jobComplete; } return $obj_result; |