SnowflakeとSaaS間のデータ連携を容易に実現!Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみた

SnowflakeとSaaS間のデータ連携を容易に実現!Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみた

Clock Icon2024.07.12

さがらです。

Omnataという、SnowflakeにNative Appsの形で特化した、各SaaSとのデータ連携に使用できるコネクタを提供している企業があります。

https://omnata.com/

今回、Snowflake Marketplaceで公開・販売されているNative AppsであるOmnata Google Sheets Pluginを用いて、「GoogleスプレッドシートからSnowflakeへのデータロード」と「SnowflakeからGoogleスプレッドシートへのReverse ETL」を行ってみたので、本記事でまとめてみます。

https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/google-sheets

Omnata Sync Engineのインストール

まず、どのOmnataのPlugin(コネクタ)を使うにあたっても、Omnata Sync Engineが必要となります。

Marketplaceで「Omnata Sync Engine」と検索するとヒットするため、これをインストールします。

2024-07-10_17h45_35

2024-07-10_17h46_18

インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。

2024-07-10_17h47_17

下図のようにポップアップが表示されます。私が行った際は約5分後、インストールされました。

2024-07-10_17h47_54

インストール後下図の画面となるので、Configureを押します。

2024-07-10_17h48_39

Streamlitのアプリケーションが立ち上がります。EXECUTE TASKの権限を求められるため、Grant Privilegesを押します。

2024-07-10_17h49_40

ログをOmnata社に共有してもよいか表示されるため、お好みのオプションを選択します。

2024-07-10_17h50_11

日次の管理用タスクのために、ウェアハウスの権限を与える必要があります。別途ワークシートを開いて、表示されたクエリを編集し、実行します。実行後はOmnataのアプリ上でCheckを押し、使用するウェアハウスを選択してSaveを押します。

2024-07-10_17h51_05

2024-07-10_17h51_18

下図の画面が出てくれば、Omnata Sync Engineの初期セットアップは完了です!

2024-07-10_17h51_49

Omnata Google Sheets Pluginのインストール

続いて、Googleスプレッドシート用のPluginをインストールします。Googleスプレッドシート用のPluginは無料で提供されているのが嬉しいですね!

Marketplaceで「Omnata Google」と検索するとヒットするため、これをインストールします。

2024-07-10_17h52_36

2024-07-10_17h52_49

インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。

2024-07-10_17h53_31

下図のようにポップアップが表示されます。私が行った際は約1分後、インストールされました。

2024-07-10_17h54_05

インストール後下図の画面となるので、Openを押します。

2024-07-10_17h54_56

Streamlitのアプリケーションが立ち上がりPluginのConfigurationが始まります。

Omnata Sync Engineをインストールしているか聞かれるため、Yesを押します。

2024-07-10_17h57_27

Omnata Sync Engineのアプリケーションに対してGoogle Sheets Pluginのアプリケーションの権限を付与する必要があります。

Omnata App nameを修正してEnterを押すと下部のクエリもその名前で更新されますので、別途ワークシートを開いて生成されたクエリを実行します。

2024-07-10_17h58_19

2024-07-10_17h58_32

Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。

2024-07-10_17h59_12

続いて、Pluginに対してapplication roleを付与するため、表示されたクエリをワークシートで実行します。

2024-07-10_17h59_55

2024-07-10_18h00_09

Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。

2024-07-10_18h01_04

下図のように表示されれば、Google Sheets Pluginのセットアップは完了です!

2024-07-10_18h01_30

Google SheetsのConnectionのセットアップ

次に、OmnataとGoogle Sheets間の認証を行うためにConnectionのセットアップを行っていきます。

事前準備:サービスアカウントの作成

後述するセットアップではOmnataとGoogle Sheetsの認証でサービスアカウントを使用するため、Google側でサービスアカウントの作成を行います。(Google Cloudプロジェクトが必要となる点だけご注意ください。)

Google CloudのIAMと管理サービスアカウントから、サービスアカウントを作成を押します。

2024-07-10_18h02_47

サービスアカウント名などを入力し、作成して続行を押します。

2024-07-10_18h03_38

2番目と3番目のステップはスキップして問題ないため、このまま完了を押します。

2024-07-10_18h04_18

作成されたサービスアカウントの詳細画面に移動し、キータブから鍵を追加新しい鍵を作成を押します。キーのタイプはJSON作成を押します。

2024-07-10_18h05_24

2024-07-10_18h05_43

下図のように表示されJSONファイルがダウンロードされます。あとでOmnataでの設定で使用しますので、大切に保存しておきましょう。

2024-07-10_18h06_25

次に対象のサービスアカウントのアドレスに対して、Omnataで連携対象のドライブに対して編集者権限を付与します。これで、サービスアカウントの準備は完了です。

2024-07-10_18h06_59

2024-07-10_18h07_11

事前準備:各種APIの有効化

OmnataはGoogle Drive APIとGoogle Sheets APIを使用しているため、サービスアカウントを作成したGoogle Cloudプロジェクトで有効化しておきます。

2024-07-10_18h08_42

2024-07-10_18h08_56

Google SheetsのConnectionのセットアップ

Omnata Sync Engineのアプリケーションを起動し、左のConnectionsを押します。

2024-07-10_18h09_42

Create your first connectionを押します。

2024-07-10_18h10_23

Google Sheetsが表示されているため、Selectを押します。

2024-07-10_18h11_09

新しいConnectionの設定を行う画面が表示されますので、それぞれ入力していきます。Connection MethodOAuthService Accountが選択可能ですが、今回は事前準備もしていたのでService Accountにします。

2024-07-10_18h12_02

EXTERNAL ACCESS INTEGRATIONに関するクエリが表示されるため、コピーしてワークシートに移動し、ACCOUNTADMINで実行します。クエリ実行後、末尾のCheckを押します。

2024-07-10_18h12_42

事前に作成したサービスアカウントのJSONキーの内容をコピーし、Service Account JSONに貼り付けます。その後、Connectを押します。

2024-07-10_18h13_52

Connectionの設定のために許可が必要なアドレス一覧が表示されます。問題なければConfirmを押します。

2024-07-10_18h14_39

数十秒後、設定が完了したら下図のように表示されます。これでConnectionの設定は完了です。

2024-07-10_18h15_17

Googleスプレッドシート→Snowflakeのデータロード

準備は整ったので、実際にGoogleスプレッドシート→Snowflakeのデータ連携をやってみます。

検証内容

jaffle_shopというファイルを用意し、customersとordersというシートを用意しておきます。これらのデータをSnowflakeにロードすることを行ってみます。

2024-07-12_12h50_12
2024-07-12_12h50_32

Sync設定手順

Omnataの画面で、左のメニューからNew Syncを押します。

2024-07-12_12h51_06

Get startedを押します。

2024-07-12_12h51_44

Google Sheetsの下部のSelectを押します。

2024-07-12_12h52_10

Select Connectionで、先程作成したConnectionを選択して、Continueを押します。

2024-07-12_12h52_41

次にsync directionを選択します。GoogleスプレッドシートからSnowflakeへのロードのため、Inboundを選択します。

2024-07-12_12h53_09

Select Objectsが表示されるため、Spreadsheet IDから同期したいスプレッドシートであるjaffle_shopを選択します。

2024-07-12_12h53_42

連携可能なシートの一覧が表示されるため、連携したいシートの左横をチェックします。その後、Continueを押します。

(今回は検証しませんが、Set new object behaviourYesにすると、新しいシートが追加されたときにも自動で追加してくれるようです。)

2024-07-12_12h54_14

Set the sync strategyが表示されます。公式Docによると、Google Sheetsの場合はFull Refreshしかできない仕様となっています。

画面の左側に、各オプションの説明も記載されています。今回はmergeで進めます。(画面左の説明と合致していないですが、mergeappendが選択できるのが正しいようです。左側の表記については次回のアップデートで修正されるとのこと。)

2024-07-12_12h54_41

mergeの場合、各Objectに対してID Field(s)を入力し、Continueを押します。

2024-07-12_12h56_57

Choose deployment methodが表示されます。スケジュールや使用するウェアハウスを設定する画面となっています。Dependentでは、別のSyncに併せて実行するということも可能のようです。

2024-07-12_15h25_08

2024-07-12_15h25_22

2024-07-12_15h25_36

設定が確認できたら、Start Syncを押します。

2024-07-12_15h26_13

下図のように表示されたら、Syncの設定は完了です!

2024-07-12_15h26_36

View Syncから作成したSyncを見ると、Snowflakeの出力先となるデータベース・スキーマ・テーブルも定義されていました。(ここはOmnataが自動で指定した内容で定義されるようです。)

2024-07-12_15h30_29

手動でSync実行してみる

作成したSyncを用いて、手動でSyncを実行してみたいと思います。

Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。

2024-07-12_15h46_51

Controlsタブから、Run nowを押します。

2024-07-12_15h47_28

Historyタブを見ると、新しくSyncが開始されていることがわかります。

2024-07-12_15h55_16

無事に完了すると、Health StateHEALTHYとなります。Viewを押すと、どのテーブルが何レコードロードされたのかも見ることが出来ます。

2024-07-12_16h01_45

2024-07-12_16h02_35

実際にデータを見てみると、INBOUND_RAWというスキーマには抽出したJSON形でそのままロードされたテーブルが作られており、INBOUND_NORMALIZEDというスキーマにはINBOUND_RAWで作られたテーブルからJSON内の値を抽出してカラムとして追加したビューが作られています。

2024-07-12_16h07_46

2024-07-12_16h08_12

Snowflake⇢GoogleスプレッドシートのReverseETL

続いて、OmnataではDWH⇢各アプリケーションのReverseETLもできるため、SnowflakeからGoogleスプレッドシートへのReverseETLを行ってみます。

検証内容

事前に下図のようなスプレッドシートを用意しておき、このスプレッドシートにCUSTOMERSというSnowflake上でdbtを介して加工処理を行ったテーブルの内容をエクスポートしてみます。

2024-07-12_16h15_56

2024-07-12_16h20_14

Sync設定手順

まず画面左のNew Syncを押し、Get startedを押します。

2024-07-12_16h22_36

Google Sheetsの下にあるSelectを押します。

2024-07-12_16h23_48

事前に作成済のConnectionを選択し、Continueを押します。

2024-07-12_16h24_39

Snowflakeから外部への連携のため、Outboundを押します。

2024-07-12_16h25_44

すると、今回ReverseETLの対象であるデータベース・スキーマ・テーブルへの権限の付与を求められるため、表示されたクエリの内容を対象のデータベース・スキーマ・テーブルに変更し、ワークシートで実行します。

2024-07-12_16h27_33

権限の付与を行ったあと、Omnataの画面に戻りRefreshを押すとリストから対象のデータベース・スキーマ・テーブルが選択できるため、選択します。ここでは、IDに該当するカラムも選択します。

その後、選択したIDに該当するカラムが重複なしであることが確認できたら、Continuteを押します。

2024-07-12_16h31_48

Set a sync strategyの画面になるので、Replaceを選択します。

2024-07-12_16h33_14

Spreadsheed IDでReverseETLの宛先となるスプレッドシート、Sheet Nameを選択します。

その後、各オプションは必要なところにチェックを入れて、Continueを押します。

2024-07-12_16h39_34

INBOUNDの設定のときと同様にスケジュールの設定画面となりますので、任意のスケジュール設定を行い、Start Syncを押します。

2024-07-12_16h42_18

これでReverseETLのSyncの設定は完了です!

手動でSync実行してみる

作成したSyncを用いて、手動でSyncを実行してみたいと思います。

Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。

2024-07-12_16h44_28

Controlsタブから、Run nowを押します。

2024-07-12_16h45_14

一定時間経過後、Historyタブを見るとRunが無事に完了していました。Viewから対象のRunの詳細も確認できます。

2024-07-12_16h48_09

2024-07-12_16h48_55

この上で、宛先に設定していたスプレッドシートを見ると、無事にSyncが出来ていました!

2024-07-12_16h50_20

おまけ:Omnataの裏側の仕組み

OmnataはStreamlitで動作するアプリケーションですが、バックエンドとして多くのUDFやストアドプロシージャが使われています。

2024-07-12_16h57_09

試しにREAD_SHEETというUDFを使ってみると、対象のスプレッドシートのデータを得ることが出来ます。

2024-07-12_16h58_50

最後に

Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみました。
Omnataだけで、GoogleスプレッドシートとSnowflake双方向の同期ができるのはとても便利だと感じました!

これは余談なのですが、Omnataの検証を始めたのは6月中旬で、INBOUNDのSyncがうまくいかない事象が発生して数十回ほどやりとりをOmnata社の方とさせて頂き、最終的に無事にSyncができるようになったという背景があります。事象が解決するまで何度も解決策を提示して頂いたりアプリケーションのアップデートも行って頂いたことには感謝しかありません!Omnata社の対応いただいた皆様、ありがとうございました!

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.