はじめに

この記事では、Qlik ReplicateのターゲットとしてSnowflakeを利用するための設定方法をご説明します。尚、ここでは設定手順の概要とポイントについて解説しますが、詳細につきましてはQlik ReplicateヘルプサイトにあるQlik Replicateの「Setup and User Guide」の「Using Snowflake on AWS/Azure/Google as a Target」の項をご参照ください。

【検証環境】

  • Qlik Replicate 6.6.0 SR1 on Windows
  • Snowflake  on Azure

Snowflake ODBCドライバの導入

以下のサイトよりSnowflake ODBCドライバをダウンロードしてQlik Replicateのサーバー上に導入します。

https://docs.snowflake.com/en/user-guide/odbc-download.html

Snowflakeのセットアップ

セットアップの実行

レプリケートの処理を行うSnowflakeユーザーには、テーブル操作(select,insert,update, truncate,delete,references)などの権限を付与する必要があります。ここでは例として、以下の処理を行うためのSQL文を実行します。

  • データロード処理を行うWarehouseをload_whという名称で作成
  • ODSという名称で新たなデータベースを作成
  • レプリケートの処理を行うためのユーザーをreplicate_userという名称で作成
  • replicateという名称でロールを作成してreplicate_userに割当て、load_wh利用の権限やODSデータベース(および含まれるスキーマやテーブル)に対する操作の権限をロールに付与
use role sysadmin;
//データロード用のWarehouseを作成
create or replace warehouse load_wh with warehouse_size='X-SMALL';

//ODSデータベースを作成
create database ODS;

use role accountadmin;
//replicateロールを作成
create role replicate;

//replicate_userを作成
CREATE USER replicate_user PASSWORD = 'replicate_password' DEFAULT_ROLE = 'REPLICATE' DEFAULT_WAREHOUSE = 'LOAD_WH' MUST_CHANGE_PASSWORD = FALSE; 

//replicate_userに対してreplicateロールを割り当て
grant role replicate to user replicate_user;

//replicateとsysadminロールに対して、load_wh利用の権限を付与
grant all on warehouse load_wh to sysadmin;
grant usage on warehouse load_wh to replicate;

//replicateとsysadminロールに対してODSデータベース操作の権限を付与
grant all on database ODS to role replicate;
grant all on all schemas in database ODS  to role replicate;
grant all on future schemas in database ODS  to role replicate;
grant select, insert, update, truncate, delete, references on all tables in database ODS to role replicate;
grant select, insert, update, truncate, delete, references on future tables in database ODS to role replicate;
grant all on database ODS to role sysadmin;
grant all on all schemas in database ODS  to role sysadmin;
grant all on future schemas in database ODS  to role sysadmin;
grant select, insert, update, truncate, delete, references on all tables in database ODS to role sysadmin;
grant select, insert, update, truncate, delete, references on future tables in database ODS to role sysadmin;

管理者アカウント(sysadmin)でSnowflakeの管理コンソールへログオンし、[Worksheets]を選択してSQL文をペーストし、[All Queries]をONにして[Run]をクリックします。

image

※検証実施後、上記で作成したリソースを全て削除したい場合には以下を実行することで削除することができます。(ここでは実行は不要です)

use role accountadmin;
drop user replicate_user;
drop role replicate;
use role sysadmin;
drop database ods;
drop warehouse load_wh;

セットアップの確認

[Warehouses]タブをクリックし、「LOAD_WH」が作成されていることを確認します。

image

[Databases]タブをクリックし、「ODS」が作成されていることを確認します。

image

右上のプロファイルから[Switch Role]>[ACCOUNTADMIN]を選択します。

image

[Users]タブをクリックし、「REPLICATE_USER」が作成されていることを確認します。

image

[Roles]タブをクリックし、「REPLICATE」が作成されていることを確認します。

image

Qlik Replicate上でのエンドポイント接続の作成

スタートメニューから[Qlik Replicate Console]を開きます。

image

[Manage Endpoint Connections]をクリックします。

image

[New Endpoint Connections]をクリックします。

image

以下の形で接続情報などの入力を行い[Test  Connection]をクリックして接続に問題が無いか確認します。ここでは[Staging]にSnowflake内部のStage領域を利用しますが、外部のStagingオプションとして、Snowflake on AzureではAzure Blob storage、Snowflake on AWSではS3、Snowflake on GoogleでGoogle storageを利用することができます。

image

[Advanced]タブを開きます。[Max file size (MB)]はデータをSnowflakeに転送される際のファイルの最大サイズとなり、デフォルト値は 100 MB です。より大きなファイルサイズを指定すると、データ量が多い場合に転送のレイテンシーは長くなる可能性がありますが、一度に転送されるデータ量が大きくなるためQlik Replicateが Snowflakeと対話する回数を減らすことができます。対話数(主にSQLのDML処理)が増えるとSnowflakeクラウドサービスの課金が増えるため、必要に応じてこの値をチューニングを行います。(課金の詳細についてはSnowflakeのサイトをご参照ください。)ここでは設定の変更を行わず、[Save]をクリックして保存して[Close]をクリックします。

image

Qlik Replicateタスクの作成

[New Task….]をクリックします。

image

以下の設定で[OK]をクリックします。ここでは[Replicate Profile]として[Unidirectional](ソースからターゲットへの一方向のレプリケーション)を選択し、[Task Options]では[Full Load](全てのデータを初期ロード)と、[Apply Changes](初期ロード完了後、ソースに対する変更キャプチャーしてターゲットに適用)を選択しています。[Store Changes]を選択すると、変更データをターゲットに適用して最新データに更新するだけでなく、過去の変更履歴を別テーブルに保存することができます。

image

ここでは別途作成したOracle_SRCをソース、今回作成したSnowflake_TGTをターゲットにドラッグ&ドロップで追加しています。

image

[Table Selection…]をクリックします。

image

[Search]をクリックし(条件を指定しない場合には全てのテーブルが表示されます)、レプリケーションの対象とするテーブルを選択します。ここではHRスキーマのCOUNTRIES、DEPARTMENTSなどの全てのテーブルを選択し、[>]ボタンをクリックしてレプリケーションの対象に追加しています。設定が完了したら[OK]をクリックします。

image

[Task Setting…]をクリックします。

image

Snowflakeをターゲットに利用する場合、タスクの[Apply Changes] が有効となり、かつ[Change Processing Mode]の設定が[Batch optimized apply]の設定の場合には、 [Apply batched changes to multiple tables concurrently option]を有効化することが推奨されています。この設定は一つのトランザクションで複数テーブルの変更処理を同時に適用するためのオプションで、パフォーマンス及びSnowflakeの課金の最適化に影響します。このオプションを有効化した場合には[Error Handling]の初期設定が変更され、また[Global Error Handling policy ]が利用できなくなります。[Batch Tuning]の設定はSnowflakeにデータを纏めて適用するために、Qlik Replicateが変更データをメモリにため込む時間や量を設定します。こちらもパフォーマンス及びSnowflakeの課金の観点から必要に応じて設定を変更します。設定が完了後、[OK]をクリックします。

image

[Save]をクリックして保存し、[Run]をクリックしてタスク実行します。

image

Full Load(初期のフルロード)のMonitor画面が表示され(表示されない場合には[Monitor]>[Full Load]を選択します)、ロードの状況が確認できます。全てロードが完了すると[Completed]のステータスに表示されます。

image

次に、ソースデータに変更を行い、その変更の適用状況を確認します。ここではOracle SQL Developer上でレコードの変更(Update)と追加(Insert)を行ってコミットしています。

image

Monitor上で[Change Processing]タブを開くと、変更の適用情報が確認できます。以下ではUpdatesが2件、Insertが1件適用されていることが確認できます。

image

Snowflakeコンソール上で[Worksheet]を開いて対象のテーブルを右クリックして[Preview Data]を選択すると、データの内容を参照し変更が適用されているかを確認することができます。

image