❌

BigQuery の列に格納済の XML データを Javascript UDF で簡単に参照させる

2024/04/25に公開

Google Cloud

桜も終わって、春というより初夏の暑さに汗ばんでいます。クラウドエースの亀梨です。
今回は、BigQuery の気が利いた機能、UDF (ユーザー定義関数)について紹介したいと思います。

BigQuery の対応しているデータ型

BigQuery では、多種多様なデータの格納と展開に対応しています。

たとえば JSON データにネイティブ対応しており、直接クエリすることができます。

しかし、残念なことに XML データを処理する方法は現時点で実装されておりません。

XML ノード内の値をクエリしたい！を叶える UDF

BigQuery には、存在しない機能をユーザー自身が Javascript で作ることのできる「UDF」という機能があります。
CREATE FUNCTION 文を実行することで、データセットに UDF を登録して、 SELECT 文で参照させることができます。

NPM ライブラリに登録されている from-xml モジュールを活用して、お手軽にクエリを実現してみましょう。

https://www.npmjs.com/package/from-xml

実装方法

ライブラリ JS ファイルを格納する GCS バケットを準備する

BigQuery 自身には、ライブラリファイルを格納できる場所がないため、ファイルの置き場所が必要になります。
GCS (Google Cloud Storage) バケットを作成し、ライブラリファイルを配置しましょう。
今回は、以下のようなパスになりました。(値は替えてあります)

gs://some-unique-bucket-name/from-xml.min.js

XML を参照する Javascript コードを書く

開発時は、基本的に Node.JS と同じ考えでよいのですが、 import 文が不要など一部異なります。
from-xml ライブラリを使うことで、XML を JSON に読み替えてアクセスできるようになります。
次項に出来上がったコードを示します。
コードは SQL 文である必要があるため、専用の記法で文字列として登録します。

CREATE FUNCTION 文で UDF を登録する

以下のようなコードを BigQuery のクエリエディタから実行します。
結果、エクスプローラのデータセットツリー配下に、「ルーティン」という項目が追加され、コードの中身を見ることが可能になります。

CREATE FUNCTION dataset_name.XmlQuery( query STRING, xmlstr STRING )
RETURNS STRING
LANGUAGE js
  OPTIONS (library=['gs://some-unique-bucket-name/from-xml.min.js'])
AS r"""

const data = fromXML(xmlstr);
let result = "";
try {
result = eval( 'data.' + query );
} catch(e){
  result = 'XMLParseError';
}
return JSON.stringify(result);

""";

引数として、ふたつの文字列 ( 1: JSON オブジェクトアクセス式 2: XMLの格納された列)を取り、文字列を返す関数を定義します。
「JSON オブジェクトアクセス式」とは、ドット区切りの以下のような文字列を意味します。

root-node.sub-node.child-node

これは、以下の XML の value を取り出す操作に対応します。

<root-node>
  <sub-node>
    <child-node>value</child-node>
  </sub-node>
</root-node>

また、コード中で try 〜 catch ブロックを付けていますが、これは例えば対象のXMLの中に、アクセスしたいノードがそもそも存在しなかった場合のエラーをケアするために入れています。

動作確認

クエリ

SELECT otherColumn, dataset_name.XmlQuery(
        'root-node.sub-node.child-node', xmlColumn
    ) as XML
FROM `PROJECT_NAME.dataset_name.table_name` LIMIT 100

結果

| otherColumn | XML   |
| some data   | value |
...(以下略)

期待通りにXMLの中の値を取り出すことができました。

UDF の弱点

簡単お手軽な UDF ですが、デバッグのためのインターフェイスが用意されていません。
また、エラーが発生して関数が停止した場合、クエリは失敗になります。そのため、エラー処理は必須です。
あまり込み入った処理を作り込むのには向いていないです。今回のような単機能ツールにしておくのがよいと思います。

まとめ

BigQuery に XML のインターフェイスが用意されていないというのは、私も意外でした。案外、近いうちに実装されて、この UDF が不要になるといいですね。
ちょっと Javascript が書ければ、簡単に BigQuery の機能を拡張できる UDF は便利なので、ぜひご活用ください。