Zenn
⛷️

ExcelのPower QueryでSnowflakeにETLする

2024/11/29に公開
Excel
Snowflake
Power Query
tech

SnowflakeのProfessional ServicesチームでSolutions Architectをしております後藤です。

📖はじめに

  • 「Microsfot ExcelのデータをSnowflakeに取り込めないか」というのはお客様と会話していてよくでてくる話題ですが、残念ながら現在ネイティブにExcelを扱える機能は存在しません。(2024-11-29)
  • SharePoint上のファイルを取り込む機能は現在プレビューででてきていますが、取り扱い可能なフォーマットはpptx, pdf, docxと非構造化データに限られています。
  • そこで、ExcelにビルトインされているPower QueryでSnowflakeへETLを行う力技を紹介します。
  • Power Queryは基本的にExcelブック外部からのデータを取り込み、結合や加工などを行なってシートにテーブルやピボットテーブルとして読み込む機能[1]になりますが、これを応用した形になります。
  • ただしExcelの接続の自動更新(=Power Queryの自動実行)はPower Automateでもできないため、このソリューションは基本的に手動実行する必要があります。2024-12-01追記: PowerShellを用いて自動実行する方法があるようです。[2]

❗本記事で行うこと

  • ExcelのテーブルをPower Queryに取り込み、ごにょごにょしてSnowflakeへクエリを発行してテーブル化する

✅Prerequisites

  • ODBCのセットアップ
  • ODBC経由でアクセスするロールへの適切な権限の付与
  • MFAを設定されている場合、Power Queryを使っていると大量に認証が飛んでくることがあります。その場合はMFAトークンのキャッシングを検討ください。

💻手順

Step 1: Power Queryからのクエリを受けるプロシージャをSnowflake上に作成

CREATE OR REPLACE PROCEDURE <db>.<schema>.create_table_procedure(
    location VARCHAR,
    cols VARCHAR,
    element VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS 
BEGIN
 
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRANSIENT TABLE ' || :location || ' AS
        SELECT * FROM (VALUES ' || :element || ') AS t ' || :cols || '';
 
    RETURN 'Table "' || :location || '" successfully refreshed.';
 
END;

これは何をしているのかというと、:locationにテーブルを作成する（既に同名のテーブルが存在している場合は再作成する）という操作です。この:location, :cols, :elementにPower Queryで値を埋め込んで発行させようということです。EXECUTE IMMEDIATEは文字列リテラルに記載されたクエリを実行するというコマンドです。

Step 2: Power Queryにテーブルを取り込み、整形する


テーブルを取り込み、'を付加してリストへ整形する

この例では簡便的にExcel上のテーブルをPower Queryに取り込み、リストに整形しています。Power Queryには多数コネクタがあり、以下のようなことも可能です。

  • SharePoint上のとあるフォルダ内に格納されているExcelファイルたちから、特定のファイル名パターンに合致した上で更に最新の日付のファイルからテーブルを読み込む
  • SharePoint上のフォルダ内にある複数のエクセル内のテーブルを全て縦に結合して読み込む
  • 各種データベースへクエリを発行し、データを取り込む
  • Website上のHTMLで作成されたテーブルのデータを読み込む 等

プロセスをクエリエディタでみてみると、次になります。

この画面は「詳細エディター」ボタンで確認ができます。

以下プロセスの解説です。

  1. source: 現在のブックからdummyという名前のテーブルを読み込み
  2. changed_type: 前列をtextタイプに変換[3]
  3. combined_col: 多数列をひとつの列に結合。delimiterとして '','' を付与。シングルクオテーションを2つ付与しているのはエスケープのため
  4. combined_col1: 最後にリストに変換

Step 3: クエリを作成する

次に、Snowflakeへ発行するクエリを作成します。

完成した形

この例ではc1,c2,c3という列があるtemp.public.tmp_tableというテーブルを作成するものになります。クエリエディタでみてみましょう。

以下プロセスの解説です。

  1. element: Stored ProceduresでVALUES以下に投入する部分の形成を行なっています。前項で作成したリストをさらに結合して一つのStringに変換し、先頭と最後に括弧を追加しています。
  2. query: Stored Procedureの文を作成。テーブル名や列名はここではハードコードしていますが、勿論パラメータ[4]を埋め込むことも可能です。

Step 4: クエリを発行する

前項までのスクショにはでてきていないのですが（撮り忘れ）、最後にSnowflakeへクエリを発行するクエリを作成します。


この例では新しいブランククエリを作成


Odbc.Queryメソッドにて作成したStringを埋め込む

Odbc.Queryについてはこちらを参照ください。
dsnを指定し、次のパラメータでクエリを記載します。

Step 5: クエリを実行する

  • Power Query画面を終わり（閉じて読み込み）、上書き保存し、エクセル画面上の「データ」リボン→「すべて更新」を押下すると規定のプロセスがワンクリックで実行されます。
  • このブックを週次や月次に開いて「すべて更新」すれば、手動にはなってしまうものの定期的にテーブルの洗い替えが可能になります🔅

Step 6: 結果


実行後、Snowsight上でテーブルを確認。無事作成された（データは適当に作成）

手順としては以上になります。ExcelだけでSnowflakeへのETLができました。

☝️補足

  • 今回ご紹介したのは単純なCTASですが、工夫すればDELETE/INSERT、MERGEなども十分可能かと思われます。
  • 実はステップ1のプロシージャの作成は必須ではありません。なぜなら、Step 3のクエリに直接CREATE OR REPLACE ... と記述すれば良いからです。ただ、Snowflake側にテンプレのプロシージャを登録しておくことで使い回しがやりやすくなること、EXECUTE AS OWNERとしておくことで当該プロシージャを保有するロールの権限にて実行できるという利点があります。※Excelから接続するロールは概してUSAGEのみの権限しかもってないことがままあるため
  • どこまでデータを投入できるのか？という点においては、Stored Procedureに渡すパラメータの値は今回VARCHARとして渡しているので、最大 16,777,216 byte (16MB)まで可能と思われますが未検証です
  • データにシングルクオテーション(例: "Let's go!")を含む場合は上記のやり方はエラーとなりますので、リストを作成するプロセスで値を置換で更にエスケープを追加する必要があります。[5] 
例
Table.ReplaceValue(<source_table>, "'", "''''", Replacer.ReplaceText, {<columns_to_search>})

❄️ Happy BUILDing on Snowflake! ❄️

脚注

  1. ピボットテーブルで返せば、Excelの限界(104万行)を超えることが可能。64bit版のExcelであれば経験上1000万行レベルまで処理可能 ↩︎

  2. タスクスケジューラでエクセルのパワークエリを自動更新させる方法 ↩︎

  3. 列を結合する上で、全ての列でtextに変換が必要となります ↩︎

  4. Excelシートのセルの値を埋め込むなど ↩︎

  5. この変換はPower Query上の変換タブの「値の置換」でマウスクリックで簡単に実行可能 ↩︎

Discussion