SnowflakeでXML形式のデータを扱ってみた

概要

この記事では、SnowflakeでXML形式のデータを扱う方法を紹介します。Snowflakeは、様々な形式のデータをそのままロードして、Snowflake内でデータ変換をすることができますが、XMLを扱う機能は日本語の情報が少ないです。（Public Preview機能であるためでしょうか。。）
XMLの取り扱いを丁寧に紹介し、読めば誰でもXMLの取り扱いができるようなるような記事を目指します！

利用するXMLサンプル

今回は、以下のサンプルを利用します。ある学校の、学年毎、クラス毎、生徒毎の情報を保持するデータのイメージです。

最終的なデータ変換の結果

今回は上記データの生徒毎情報を、テーブル形式に変換することを目標とします。変換後の最終的なデータのイメージは以下のような形です。

XMLファイルのロード

まずはXML形式のファイルをロードします。今回紹介する方法は１例ですので、以下の手順以外の方法でも問題ありません。

1. テーブル生成

2. 内部ステージ作成

3. ローカルファイルのアップロード

4. ロード

5. データ確認

以下で詳細を見ていきます。

• テーブル生成
  まずはテーブルを作成します。Snowflakeで半構造化データを取り扱う際は、VARIANT型を用います。

• 内部ステージ作成
  XMLファイル用のステージを作成します。今回は、内部ステージでファイルフォーマットを指定します。（copy into 文のオプションで指定した場合は、そちらが優先されます。詳しくはこちら）
• ローカルファイルのアップロード
  SnowSQLを利用して、ローカルPC上のXMLファイルをステージにアップロードします。
• ロード

  copy into コマンドで、XMLファイルをロードします。
• データの確認

  ロードができたので、テーブルの中身を確認してみます。

  上手く取り込めていそうです。１番上の階層のXML要素を１レコードとして取り込んでいます。今回は元データに１番上の階層の要素が２つあるので、２レコード生成されています。

  SnowflakeのVARIANT型は、内部でデータ型を保持しています。内部で保持している型は、typeof関数で確認できますので、今回ロードしたデータも確認してみましょう。

  
  
  しっかりXML型として認識されていますね！Snowflake賢い！

XMLの要素取り扱いの基礎を知る

XMLデータをロードできたので、XML形式のデータを扱うための基礎を知りましょう。

• 要素名を取り出す

  要素名を取り出すには、”@”オペレータを利用します。ドット表記を用いた半構造化データのクエリの場合、以下のように指定します。

   

  クエリ結果は、以下の通りです。一番上の階層の要素名が取得できています。

   半構造化データのクエリは、GET関数を使用して抽出することもできます。

  結果はもちろん、ドット表記を用いた場合と同様になります。

• 属性値を取り出す

  属性値を取り出すには、”@属性名”オペレータ利用します。同様に、ドット表記でもGET関数でもどちらでも取り出すことができます。

   

• 値（子要素）を取り出す

  値（子要素）を取り出す方法は主に２つ存在します。

  • XMLGET関数を用いる方法

    値（子要素）がXML形式の場合、XMLGET関数を用いることができます。以下は、学年要素から、クラス要素を抜き出すクエリです。

    戻り値として、指定した子要素のXMLオブジェクトがリターンされています。 ただし、XMLGET関数の利用には、以下の注意事項があります。

    • 指定した要素が複数ある場合、先頭の要素のみリターンされる

    例えば、以下のクエリでクラス要素の子要素である、生徒要素をクエリしてみます。

    クエリ結果は以下のようになります。

    このように、元データの１年１組には、３人の生徒がいましたが、１人分の生徒のデータのみ抽出しています。XMLGET関数は、子要素が１つのみと確定しているときにのみ、利用した方が良さそうです。 子要素が複数ある場合には、次に紹介する”$”オペレータを利用する方法を利用する必要があります。

  • “$”オペレータを用いる方法

    ”@”オペレータなどと同様に、ドット表記もしくはGET関数を用いて、”$”オペレータにアクセスすることで、子要素にアクセスすることができます。まずは、学年要素の子要素にアクセスしてみます。

    子要素が１つのXML要素のみだったので、XMLGET関数を用いた場合と同様となりました。
    
    次に、子要素が複数ある場合の動作を見てみます。学年の孫要素（クラスの子要素）にアクセスします。

    なんだかややこしそうなデータです。1レコードの全容を詳しくみてみます。

    
    結果は、XML形式を再現可能なARRAY型で保持されているようです。一応、typeof関数で型を確認してみます。

    あっています。子要素が複数ある場合はSnowflake側で、ARRAY型として保持してくれるようです。

    当然ですが、子要素が文字列の場合は、きちんと文字列として保持してくれますし、数値の場合もnumber型にしてくれます。Snowflake、賢い！！！

複数ある子要素をテーブル形式に展開する

“$”オペレータを利用することで、複数ある要素にアクセスすると、ARRAY型で取得できます。Snowflakeでこのような複数あるデータをテーブル形式に展開するのは、lateral flattenを用いるのが常套手段です。

LATERALはLateral Join (ラテラル結合)のことで、CROSS JOINのように左側のテーブルから 1 行ずつ取り出して、その行を使ってサブクエリを実行し、そのサブクエリの結果行を右側として、左側の行と結合していく、ということを行います。flattenは、半構造化データを行列の形に展開します。セットで利用することで、複数要素を別々の行にばらして展開できます。

実際のデータを見た方がピンとくると思います。次のクエリで、各クラスの情報を取り出してみます。

VALUE列に、クラスの子要素が格納されています。 （※SEQ, KEY, PATH, INDEXは、元要素からのアクセスパスを表すものになります。)

ここまでくれば、あとは各要素から、個別の情報を抽出すれば完成です！
今回は生徒の情報のみクエリしてみます！

無事に生徒毎の情報を、テーブル形式で表示することができました！！

まとめ

いかがでしたでしょうか。今回はSnowflakeでXML形式のデータを取り扱う方法を紹介いたしました。Snowflakeは様々な形式のデータを扱うことができますね！