[codeigniter] 使用Google Service Account & Google Bigquery API 下載資料

21二月 2017 by Bryce No Comments

在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這支程式：

<?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();
        }
    }

<?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中先宣告即可

<?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();

    }

<?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，寫法如下：

    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;

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;

922 全部 3 今日

Step1

Step2

Step3

發表迴響 取消回覆

發表迴響取消回覆