SnowflakeとSaaS間のデータ連携を容易に実現！Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみた
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
さがらです。
Omnataという、SnowflakeにNative Appsの形で特化した、各SaaSとのデータ連携に使用できるコネクタを提供している企業があります。
今回、Snowflake Marketplaceで公開・販売されているNative AppsであるOmnata Google Sheets Pluginを用いて、「GoogleスプレッドシートからSnowflakeへのデータロード」と「SnowflakeからGoogleスプレッドシートへのReverse ETL」を行ってみたので、本記事でまとめてみます。
Omnata Sync Engineのインストール
まず、どのOmnataのPlugin(コネクタ)を使うにあたっても、Omnata Sync Engineが必要となります。
Marketplaceで「Omnata Sync Engine」と検索するとヒットするため、これをインストールします。
インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。
下図のようにポップアップが表示されます。私が行った際は約5分後、インストールされました。
インストール後下図の画面となるので、Configureを押します。
Streamlitのアプリケーションが立ち上がります。EXECUTE TASKの権限を求められるため、Grant Privilegesを押します。
ログをOmnata社に共有してもよいか表示されるため、お好みのオプションを選択します。
日次の管理用タスクのために、ウェアハウスの権限を与える必要があります。別途ワークシートを開いて、表示されたクエリを編集し、実行します。実行後はOmnataのアプリ上でCheckを押し、使用するウェアハウスを選択してSaveを押します。
下図の画面が出てくれば、Omnata Sync Engineの初期セットアップは完了です！
Omnata Google Sheets Pluginのインストール
続いて、Googleスプレッドシート用のPluginをインストールします。Googleスプレッドシート用のPluginは無料で提供されているのが嬉しいですね！
Marketplaceで「Omnata Google」と検索するとヒットするため、これをインストールします。
インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。
下図のようにポップアップが表示されます。私が行った際は約1分後、インストールされました。
インストール後下図の画面となるので、Openを押します。
Streamlitのアプリケーションが立ち上がりPluginのConfigurationが始まります。
Omnata Sync Engineをインストールしているか聞かれるため、Yesを押します。
Omnata Sync Engineのアプリケーションに対してGoogle Sheets Pluginのアプリケーションの権限を付与する必要があります。
Omnata App nameを修正してEnterを押すと下部のクエリもその名前で更新されますので、別途ワークシートを開いて生成されたクエリを実行します。
Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。
続いて、Pluginに対してapplication roleを付与するため、表示されたクエリをワークシートで実行します。
Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。
下図のように表示されれば、Google Sheets Pluginのセットアップは完了です！
Google SheetsのConnectionのセットアップ
次に、OmnataとGoogle Sheets間の認証を行うためにConnectionのセットアップを行っていきます。
事前準備：サービスアカウントの作成
後述するセットアップではOmnataとGoogle Sheetsの認証でサービスアカウントを使用するため、Google側でサービスアカウントの作成を行います。（Google Cloudプロジェクトが必要となる点だけご注意ください。）
Google CloudのIAMと管理のサービスアカウントから、サービスアカウントを作成を押します。
サービスアカウント名などを入力し、作成して続行を押します。
2番目と3番目のステップはスキップして問題ないため、このまま完了を押します。
作成されたサービスアカウントの詳細画面に移動し、キータブから鍵を追加→新しい鍵を作成を押します。キーのタイプはJSONで作成を押します。
下図のように表示されJSONファイルがダウンロードされます。あとでOmnataでの設定で使用しますので、大切に保存しておきましょう。
次に対象のサービスアカウントのアドレスに対して、Omnataで連携対象のドライブに対して編集者権限を付与します。これで、サービスアカウントの準備は完了です。
事前準備：各種APIの有効化
OmnataはGoogle Drive APIとGoogle Sheets APIを使用しているため、サービスアカウントを作成したGoogle Cloudプロジェクトで有効化しておきます。
Google SheetsのConnectionのセットアップ
Omnata Sync Engineのアプリケーションを起動し、左のConnectionsを押します。
Create your first connectionを押します。
Google Sheetsが表示されているため、Selectを押します。
新しいConnectionの設定を行う画面が表示されますので、それぞれ入力していきます。Connection MethodはOAuthとService Accountが選択可能ですが、今回は事前準備もしていたのでService Accountにします。
EXTERNAL ACCESS INTEGRATIONに関するクエリが表示されるため、コピーしてワークシートに移動し、ACCOUNTADMINで実行します。クエリ実行後、末尾のCheckを押します。
事前に作成したサービスアカウントのJSONキーの内容をコピーし、Service Account JSONに貼り付けます。その後、Connectを押します。
Connectionの設定のために許可が必要なアドレス一覧が表示されます。問題なければConfirmを押します。
数十秒後、設定が完了したら下図のように表示されます。これでConnectionの設定は完了です。
Googleスプレッドシート→Snowflakeのデータロード
準備は整ったので、実際にGoogleスプレッドシート→Snowflakeのデータ連携をやってみます。
検証内容
jaffle_shopというファイルを用意し、customersとordersというシートを用意しておきます。これらのデータをSnowflakeにロードすることを行ってみます。
Sync設定手順
Omnataの画面で、左のメニューからNew Syncを押します。
Get startedを押します。
Google Sheetsの下部のSelectを押します。
Select Connectionで、先程作成したConnectionを選択して、Continueを押します。
次にsync directionを選択します。GoogleスプレッドシートからSnowflakeへのロードのため、Inboundを選択します。
Select Objectsが表示されるため、Spreadsheet IDから同期したいスプレッドシートであるjaffle_shopを選択します。
連携可能なシートの一覧が表示されるため、連携したいシートの左横をチェックします。その後、Continueを押します。
（今回は検証しませんが、Set new object behaviourをYesにすると、新しいシートが追加されたときにも自動で追加してくれるようです。）
Set the sync strategyが表示されます。公式Docによると、Google Sheetsの場合はFull Refreshしかできない仕様となっています。
画面の左側に、各オプションの説明も記載されています。今回はmergeで進めます。（画面左の説明と合致していないですが、mergeとappendが選択できるのが正しいようです。左側の表記については次回のアップデートで修正されるとのこと。）
mergeの場合、各Objectに対してID Field(s)を入力し、Continueを押します。
Choose deployment methodが表示されます。スケジュールや使用するウェアハウスを設定する画面となっています。Dependentでは、別のSyncに併せて実行するということも可能のようです。
設定が確認できたら、Start Syncを押します。
下図のように表示されたら、Syncの設定は完了です！
View Syncから作成したSyncを見ると、Snowflakeの出力先となるデータベース・スキーマ・テーブルも定義されていました。（ここはOmnataが自動で指定した内容で定義されるようです。）
手動でSync実行してみる
作成したSyncを用いて、手動でSyncを実行してみたいと思います。
Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。
Controlsタブから、Run nowを押します。
Historyタブを見ると、新しくSyncが開始されていることがわかります。
無事に完了すると、Health StateがHEALTHYとなります。Viewを押すと、どのテーブルが何レコードロードされたのかも見ることが出来ます。
実際にデータを見てみると、INBOUND_RAWというスキーマには抽出したJSON形でそのままロードされたテーブルが作られており、INBOUND_NORMALIZEDというスキーマにはINBOUND_RAWで作られたテーブルからJSON内の値を抽出してカラムとして追加したビューが作られています。
Snowflake⇢GoogleスプレッドシートのReverseETL
続いて、OmnataではDWH⇢各アプリケーションのReverseETLもできるため、SnowflakeからGoogleスプレッドシートへのReverseETLを行ってみます。
検証内容
事前に下図のようなスプレッドシートを用意しておき、このスプレッドシートにCUSTOMERSというSnowflake上でdbtを介して加工処理を行ったテーブルの内容をエクスポートしてみます。
Sync設定手順
まず画面左のNew Syncを押し、Get startedを押します。
Google Sheetsの下にあるSelectを押します。
事前に作成済のConnectionを選択し、Continueを押します。
Snowflakeから外部への連携のため、Outboundを押します。
すると、今回ReverseETLの対象であるデータベース・スキーマ・テーブルへの権限の付与を求められるため、表示されたクエリの内容を対象のデータベース・スキーマ・テーブルに変更し、ワークシートで実行します。
権限の付与を行ったあと、Omnataの画面に戻りRefreshを押すとリストから対象のデータベース・スキーマ・テーブルが選択できるため、選択します。ここでは、IDに該当するカラムも選択します。
その後、選択したIDに該当するカラムが重複なしであることが確認できたら、Continuteを押します。
Set a sync strategyの画面になるので、Replaceを選択します。
Spreadsheed IDでReverseETLの宛先となるスプレッドシート、Sheet Nameを選択します。
その後、各オプションは必要なところにチェックを入れて、Continueを押します。
INBOUNDの設定のときと同様にスケジュールの設定画面となりますので、任意のスケジュール設定を行い、Start Syncを押します。
これでReverseETLのSyncの設定は完了です！
手動でSync実行してみる
作成したSyncを用いて、手動でSyncを実行してみたいと思います。
Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。
Controlsタブから、Run nowを押します。
一定時間経過後、Historyタブを見るとRunが無事に完了していました。Viewから対象のRunの詳細も確認できます。
この上で、宛先に設定していたスプレッドシートを見ると、無事にSyncが出来ていました！
おまけ：Omnataの裏側の仕組み
OmnataはStreamlitで動作するアプリケーションですが、バックエンドとして多くのUDFやストアドプロシージャが使われています。
試しにREAD_SHEETというUDFを使ってみると、対象のスプレッドシートのデータを得ることが出来ます。
最後に
Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみました。
Omnataだけで、GoogleスプレッドシートとSnowflake双方向の同期ができるのはとても便利だと感じました！
これは余談なのですが、Omnataの検証を始めたのは６月中旬で、INBOUNDのSyncがうまくいかない事象が発生して数十回ほどやりとりをOmnata社の方とさせて頂き、最終的に無事にSyncができるようになったという背景があります。事象が解決するまで何度も解決策を提示して頂いたりアプリケーションのアップデートも行って頂いたことには感謝しかありません！Omnata社の対応いただいた皆様、ありがとうございました！
