SnowflakeとSaaS間のデータ連携を容易に実現!Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみた
さがらです。
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の検証を始めたのは6月中旬で、INBOUNDのSyncがうまくいかない事象が発生して数十回ほどやりとりをOmnata社の方とさせて頂き、最終的に無事にSyncができるようになったという背景があります。事象が解決するまで何度も解決策を提示して頂いたりアプリケーションのアップデートも行って頂いたことには感謝しかありません!Omnata社の対応いただいた皆様、ありがとうございました!