残念なことにDBにJSONなどではなくPHPでシリアライズされた形式でデータが保存されていることがあります
この中身をBigQuery側から参照したかったので調べてみました
軽く検索したところJavaScriptでPHPのシリアライズされたデータをもとに戻せるライブラリはいくつもありそうなのでこれをUDFから呼び出すことにします
GitHub - naholyr/js-php-unserialize: JavaScript tool to unserialize data taken from PHP. It can parse "serialize()" output, or even serialized sessions data.
GitHub - bd808/php-unserialize-js: Convert serialized PHP data to a javascript object graph.
GitHub - steelbrain/php-serialize: PHP Serialize/Unserialize in Javascript
とりあえず試してみたかったので一番上に貼ったリポジトリのunserialize()関数をそのまま貼り付けて使ってみました
UDFでは戻り値の型を指定する必要があるのでSTRINGにしておいてJSONを返すことにします
CREATE TEMPORARY FUNCTION unserialize(x STRING)
RETURNS STRING
LANGUAGE js AS """
# ここにライブラリの中身を直に書くか、もしくは外部ライブラリのインクルードをこのUDFの定義の外側に書く
return JSON.stringify(unserialize(x)); # unserialize() という関数がライブラリで定義されているものとします
""";
SELECT unserialize('a:3:{s:1:"a";s:1:"1";s:1:"b";i:2;s:1:"c";a:3:{i:0;i:2;i:1;i:4;i:2;i:6;}}')というわけでPHPのシリアライズされた形式からJSONに変更できました。あとはBigQueryのJSON関係の関数を使えば中身が取り出せます
これで解決と思ったのですが、よくみたら[2, 4, 6]の配列が{"0":2,"1":4,"2":6}と連想配列扱いされていました
よく読むとこれはシリアライズされた状態から戻すのに使ったライブラリの仕様のようなので、配列が含まれるデータの時には自分でパッチを当てるか別のライブラリを使うしかなさそうです。もしくはSQL側で工夫すれば取ってこれるのでとりあえずは使えそうです
