🍑

[PHP x BigQuery] パラメータ化されたクエリ（名前付きパラメータ）を実行する

2022/06/27に公開

PHP

PHPで、パラメータ化されたクエリ（名前付きクエリ）を実行する方法です

PHPから、BigQueryにてクエリ実行する時、値をバインド（公式ドキュメントでいうところの「パラメータ化されたクエリ」（parameterized queries） or 「名前付きパラメータ」（named parameters）or 「クエリパラメータ」（query with timestamp parameters））したいケースって多々あると思うのですが、

公式ドキュメント

を見ると、どのページにもPHPのサンプルコードは載っていません。。

とはいえ、ベタ書きするのはちょっと、、と思っていたら、

BigQueryClient.php | googleapis/google-cloud-php

にありましたので、覚書きを兼ねてサンプルコードを書いておきます

前提条件

googleapis/google-cloud-php-bigqueryに従って必要なライブラリがインストールされていること
→ 特別な理由がなければ、全コンポーネントを含む「google/cloud」をチョイスするとよいでしょう
→ READMEのPHP Versions Supportedによれば、PHPのバージョンは5.5以上（Google Cloud Computeは7.0以上）必要なので要件を満たしているかも確認しておきましょう（2022年7月15日現在）

一応、コマンドも載せておきます

$ composer require google/cloud

基本的な使い方

以下のような構成のテーブルから指定したカテゴリIDに紐づくアイテムを全件取り出す場合を考えてみます

+-------------------+--------------------+-------------------+
|    item_id(int64) | item_name(string)  |cagegory_id(int64)|
+-------------------+--------------------+-------------------+
|                 1 |   チョコレートケーキ |                1 |
|                 2 |              豆大福 |                2 |
|                 3 |       シュークリーム |                1 |
|                 4 |           水ようかん |                2 |
|                 5 |                月餅 |                3 |
+-------------------+--------------------+-------------------+

プログラムは以下の通り
カテゴリIDは、コマンドライン引数の第1引数に入力するものとします

getCategoryItems.php

<?php

require 'vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

// BigQuery接続用の秘密鍵
$keyfile = './xxxxx.json';

$bigQuery = new BigQueryClient([
    'keyFile' => json_decode(file_get_contents($keyfile), true),
]);

$query = 'select * from `sample1.items` where category_id = @categoryId';

// パラメータの設定
$queryJobConfig = $bigQuery->query($query)
    ->parameters([
	// そのままだとstring型なので、int型に変換
        'categoryId' => (int)$argv[1]
    ]);
$queryResults = $bigQuery->runQuery($queryJobConfig);
/* オプションを指定する場合: 以下はタイムアウトまでの時間30,000ミリ秒、最大リトライ回数3回
$options = ['timeoutMs'=>30000, 'maxRetries'=>3]; 
$queryResults = $bigQuery->runQuery($queryJobConfig, $options);
*/

// 結果を全行取得
$rows = $queryResults->rows();

foreach ($rows as $row) {
    echo $row['itemname'].PHP_EOL;
}

お試しとして、category_idが2のアイテムを取得してみます

実行コマンド例

php getCategoryItems.php 2
# 実行結果
豆大福
水ようかん

→ 結果の取り出しは、
QueryResults.php | googleapis/google-cloud-php-bigquery
を参考にしました

地理関数を使う場合

オマケとして、地理関数を使う場合も載せておきます

getGeogDiff.php

<?php

require 'vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

// BigQuery接続用の秘密鍵
$keyfile = './xxxxx.json';

$bigQuery = new BigQueryClient([
    'keyFile' => json_decode(file_get_contents($keyfile), true),
]);

$lng = "139.75848551546315"; // 経度
$lat = "35.6549177467895"; // 緯度

$wkt = "POINT($lng $lat)";

$from = 0; // 開始地点
$to = 50;  // 末端

$query = "select st_difference(
            st_buffer(ST_GeogFromText(@wkt), @to),
            st_buffer(ST_GeogFromText(@wkt), @from)
) as diff";

$queryJobConfig = $bigQuery->query($query)
    ->parameters([
        'wkt' => $wkt,
        'from' => (int)$from,
        'to' => (int)$to
    ]);
$queryResults = $bigQuery->runQuery($queryJobConfig);

foreach($queryResults as $row){
    echo $row['diff'].PHP_EOL;
}

実行コマンド

$ php getGeogDiff.php
# 実行結果
POLYGON((139.75793217444 35.6549112440862, 139.757944368312 35.6548236539013, 139.757977358093 35.6547396796948, 139.758029875948 35.654662548532, 139.758099903608 35.6545952244965, 139.758184749939 35.6545402947853, 139.758281154358 35.6544998702893, 139.758385412138 35.654475504476, 139.75849351677 35.6544681336952, 139.758601313921 35.6544780411972, 139.758704661075 35.6545048462485, 139.758799586715 35.6545475187622, 139.758882442938 35.6546044188814, 139.758950045638 35.6546733599942, 139.758999796867 35.6547516927596, 139.75902978467 35.6548364069163, 139.759038856577 35.6549242469618, 139.75902666389 35.6550118372569, 139.758993675099 35.6550958117496, 139.75894115789 35.6551729433308, 139.758871130432 35.6552402678533, 139.75878628383 35.6552951980459, 139.758689878707 35.6553356229445, 139.758585619897 35.6553599890201, 139.758477514066 35.6553673598832, 139.75836971573 35.6553574522709, 139.758266367586 35.6553306469335, 139.7581714413 35.6552879740014, 139.758088584874 35.6552310733952, 139.758020982445 35.655162131801, 139.757971231921 35.655083798633, 139.757941245147 35.6549990842139, 139.75793217444 35.6549112440862))

終わりに

自分もなかなか見つけられず、職場の人に教えてもらいました。

日本語で検索して見つからない場合は、関連ドキュメントを英語表示にして、そのワードで検索してみるとよいかもですね・・。
その場合は、Google USAを使用するのがベター。
（「名前付きパラメータ」を英語にして、「named parameter bigquery php」とかで検索してみたのですが、検索結果の5,6番目くらいにしれっとあって見逃してたりしたので）

・・まぁ、Google CloudさんがPHPのサンプルコードも冒頭で記載の3ページに載せてくれるのがイチバンなんですけどね（今後に期待しておこう、、

前提条件

基本的な使い方

地理関数を使う場合

終わりに

Discussion