PHPer だって、 BigQuery を使いたい！
はじめに
いや、とうぜん使えるんですが、ほとんどの PHPキッズ たちは BigQuery が使えないと思います。日本において、 PHPキッズ たちが気軽に BigQuery をつかったら GDP は跳ねる訳です。 日本政府 や Google日本 はその点勉強が足りない訳です。電車に乗ってたらとなりのおじさんがそのように言ってました。さだかではありませんが、では私が一役買いましょう。
BigQuery どころじゃない
流行らない理由１は、Google Cloud Platform (GCP) が複雑でITバブルの黎明期みたいな状態であることが原因です。でも、これも要はどうするの？だけを最初に教えてくれたらいいんですよね。まず BigQuery にはjsonキーを発行するんですが、権限が付与されたキーでないとダメです。たとえばオーナーならなんでもできるだろ！っていうのは勘違いです。 BigQuery管理者のロールを与えてから、キーを発行してください。
できましたか？ここらへんはIAM権限がーとか、どのプロジェクトがーで、そのあとにサービスキーでjsonをDLします。
いよいよコーディング。サンプルよこせ！
ここがクリアできると、次はPHPサンプルよこせってなります。一応ありますが、Googleドキュメント内は少々蚊帳の外現象で、PHPサンプルをちょいちょい削ってきます。しぶしぶ英語圏のgithubリンクを踏んで、ソースを眺めることになります。
ライブラリを composer で落とします。 composer?どこでどうするの？って方は、今後のために composer の学習を先に優先させましょう、今後のために。
で、autoload読み込んでuseするっていう流れです。
require '/...../vendor/autoload.php';
use Google\Cloud\BigQuery\BigQueryClient;
putenv('GOOGLE_APPLICATION_CREDENTIALS=/..../.....json');
composerでDLすると、そのディレクトリに vendor とかできてその配下にライブラリが設置されているんで、そのライブラリ作成者のドキュメントにしたがってuseします。
GOOGLE_APPLICATION_CREDENTIALSは今回のライブラリではお約束の環境変数として github に英語で記述されています。
Authentication
Please see our Authentication guide for more information on authenticating your client. Once authenticated, you'll be ready to start making requests.
こういうの英語だと読み飛ばしちゃって、なんかうごかねーってなりますよね(・ω・;)
データセット作成
$this->bigQuery = new BigQueryClient([
'projectId' => 'GCPのプロジェクトID'
]);
$this->dataset = $this->bigQuery->createDataset("DB名的");
データセットは create database です。プロジェクトIDはjsonファイル内にも記述があります。
テーブル作成
$this->bigQuery = new BigQueryClient([
'projectId' => 'GCPのプロジェクトID'
]);
$dataset = $this->bigQuery->dataset("DB名");
$fields = [
[
'name' => 'field1',
'type' => 'string',
'mode' => 'required'
],
[
'name' => 'field2',
'type' => 'integer'
],
];
$schema = ['fields' => $fields];
$this->table = $dataset->createTable("テーブル名", ['schema' => $schema]);
new BigQueryClientは共通なんで、上で一発newしとけばいいですが、一応明示的に書きました。あと、DB,TableはGCP管理画面上で事前にセットしておけばいい話ではあります。SQLワークスペースってページが、GCPの管理画面のどこかにあるんで探してみてください。そこでポチポチと作成できます。
テーブルにデータいれる
$this->bigQuery = new BigQueryClient([
'projectId' => 'GCPのプロジェクトID'
]);
$query = "insert into PJT名.DB名.TBL名(field1, field2)values('aaa', 1)";
$jobConfig = $this->bigQuery->query($query);
$queryResults = $this->bigQuery->runQuery($jobConfig);
ここが真骨頂といいますか、 BigQuery を PHPer 達に流行らすトリガーです。そう、csvとかjsonとか、データ取り込む際にあまりなんだかなー的なところだったんですが、普通にSQLでいけます。なので、自社サービスのDBからセレクトしたものを bigQuery で insert 普通にできるんです。それの、 PHPライブラリ版 の サンプル をよこせって思うんですが、ググってもないんですね(笑)ないというかインデックスされてないのかな。それか順位が低い。需要がなくて。なんかPHPララベル版は見かけました。でもララベル使ってない人口の方がキッズ多いんで...
テーブルのデータを書き換える
$this->bigQuery = new BigQueryClient([
'projectId' => 'GCPのプロジェクトID'
]);
$query = "update PJT名.DB名.TBL名 set field1 = 'abc' where field2 = 1";
$jobConfig = $this->bigQuery->query($query);
$queryResults = $this->bigQuery->runQuery($jobConfig);
こんな感じで、定番SQLは普通に動きます。処理時間は自前のDBサーバの方が速いですね。統計データとかを数十万レコード以上とかいれて、where句とかで検索する時とか、サーバ肥大化、バックアップ面も考えて BigQuery を候補に入れるんと思いますよ。
テーブル取得
$this->bigQuery = new BigQueryClient([
'projectId' => 'GCPのプロジェクトID'
]);
$query = "select * from PJT名.DB名.TBL名";
$jobConfig = $this->bigQuery->query($query);
$queryResults = $this->bigQuery->runQuery($jobConfig);
$this->record = [];
$i = 0;
foreach ($queryResults as $row) {
foreach ($row as $column => $value) {
$this->record[$i][$column] = $value;
}
$i++;
}
#print("<pre>");var_dump($this->record);print("</pre>");
ちょいちょい $this があるのはクラス内でテストした切り抜きのためです。ペラPHPなら省いて問題ありません。
BigQuery PHP とかの似たような単語でググっても、select, insert, update がなかったり、PHP用じゃなかったりとかしたんで、動作テストして記事にしました。日本のキッズたちが BigQuery を気軽に楽しめる世界を夢見て――。
alter tableはない
削除はdelete fromという標準的なやつでいけます。いよいよ網羅してきましたが、カラム追加をSQLワークスペースでできても、削除はできません。これは作り直しをしないといけないためです。
CREATE OR REPLACE TABLE `pjt.dataset.table` AS
SELECT * EXCEPT(消したいカラム１, 消したいカラム２) FROM `pjt.dataset.table`
上記でいけます。変更したいとなると、カラムを追加して、そっちにデータをコピーして、削除したいカラムを消すという手順が必要そうです。
参考
Google 標準 SQL のデータ操作言語（DML）ステートメント
Discussion