Snowflake Knowledge - INSIGHT LAB

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

作成者: Osyou|2022年10月11日

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

概要

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

利用するXMLサンプル

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

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

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

XMLファイルのロード

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

  1. テーブル生成
  1. 内部ステージ作成
  1. ローカルファイルのアップロード
  1. ロード
  1. データ確認

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

  • テーブル生成
    まずはテーブルを作成します。Snowflakeで半構造化データを取り扱う際は、VARIANT型を用います。
  • 内部ステージ作成
    XMLファイル用のステージを作成します。今回は、内部ステージでファイルフォーマットを指定します。(copy into 文のオプションで指定した場合は、そちらが優先されます。詳しくはこちら
  • ローカルファイルのアップロード
    SnowSQLを利用して、ローカルPC上のXMLファイルをステージにアップロードします。
  • ロード

    copy into コマンドで、XMLファイルをロードします。

  • データの確認

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

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

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



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



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

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

  • 要素名を取り出す

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

     

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

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

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

  • 属性値を取り出す

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

     

  • 値(子要素)を取り出す

    値(子要素)を取り出す方法は主に2つ存在します。

    • XMLGET関数を用いる方法

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

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

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

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

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

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

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

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

      子要素が1つの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は様々な形式のデータを扱うことができますね!