Qlikにおけるデータの差分取り込み

初めに

前回、AmazonS3を例にQlikへのデータ取り込みについて、記載しました。
今回は、QVDを活用した差分取り込みについて、記載しようと思います。

QVDファイルの活用

QVDとは、Qlik senseで使われる独自の高速データファイル形式です。圧縮率が高く、読み込みが高速であるため、データの保存・再利用に有効です。
QVDはロードスクリプトで読み書き可能です。

// 売上テーブルをロード
売上:
LOAD
	売上No,
    売上日,
    データ登録日時,
    データ更新日時,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額
FROM [テーブル名];

// 売上テーブルをqvd形式でAmazonS3へ保存
STORE [売上] INTO [lib://Amazon_S3_V2/test/sales_20250101.qvd](qvd);

ロードした売上テーブルを『sales_20250101』という名前のQVD形式で保存しました。
Store文は一番右のアイコン『Storeステートメントを挿入』アイコンを選択することでも作成できます。

データ登録日時を使ったQlik差分ロード

次に保存したQVDを活用した差分取り込みを考えます。
方法は様々ありますが、まず簡単なものとして、既存のQVDから既存データの最終データ登録日時を取得し、それ以降に登録のあったデータをデータベースから取得することを考えます。

// (2章で作成した)既存のqvdからデータ登録日時のMAXを取得
TBL_temp:
LAOD
	Max(データ登録日時) as date_max
FROM [lib:// Amazon_S3_V2/test/sales_20250101.qvd](qvd);

// MAXのデータ登録日時をタイムスタンプ形式で変数に格納
LET vDate_max = Timestamp(FieldValue(‘date_max’,1),’YYYY/MM/DD hh:mm:ss’);

Drop Table TBL_temp;

// (2章で作成した)既存のqvdから売上テーブルをロード
売上:
LOAD * FROM [lib:// Amazon_S3_V2/test/sales_20250101.qvd](qvd);

// Where句にて既存のMAXデータ登録日時より新しいデータをデータベースから取得し、売上テーブルに連結
// ※castが使えないデータベースの場合は別の関数が必要
Concatenate(売上)
    売上No,
    売上日,
    データ登録日時,
    データ更新日時,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額
FROM [テーブル名];
Where データ登録日時 > Cast('$(vDate_Max)' as timestamp);

QVDのデータ登録日時を参照し、既存レコードをQVDから、新たに登録されたレコードをデータベースから取得することができました。

データ更新日時を使ったQlik差分ロード

次にデータ更新日時から、データが更新されているものを洗い替えるという処理について考えます。やり方については、先ほどのデータ登録日時の場合と似ていますが、データの更新がある物のみを洗い替えるという処理が必要になります。

// (2章で作成した)既存のqvdからデータ更新日時のMAXを取得
TBL_temp2:
LAOD
	Max(データ更新日時) as date_max2
FROM [lib:// Amazon_S3_V2/test/sales_20250101.qvd](qvd);

// MAXのデータ更新日時をタイムスタンプ形式で変数に格納(データ形式はデータベース側に従う)
LET vDate_max2 = Timestamp(FieldValue(‘date_max2’,1),’YYYY/MM/DD hh:mm:ss’);

Drop Table TBL_temp2;

// Where句にて、既存のMAXデータ更新日時より新しいデータをデータベースから取得
// フラグとして1をふる
売上:
    売上No,
    売上日,
    データ登録日時,
    データ更新日時,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額,
    1 as update_flg
FROM [テーブル名]
Where データ更新日時 > Cast('$(vDate_Max2)' as timestamp);

// 既存の売上テーブルをデータ更新側の売上テーブルと連結せずにロード
NoConcatenate
売上_temp:
    売上No,
    売上日,
    データ登録日時,
    データ更新日時,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額
FROM [lib:// Amazon_S3_V2/test/sales_20250101.qvd](qvd);

// 既存の売上テーブルにおいて、洗い替えが必要なレコードにフラグをたてる
// 今回の場合、ユニークな売上Noを結合キーとし
// 既存の売上テーブル側に対し、更新側にも売上Noがあるものに、update_flgを結合する
// update_flg=1が振られたレコードは洗い替えが必要なレコードということ
Left Join(売上_temp)
    売上No,
    update_flg
Resident 売上;

// Where句にてupdate_flg=1ではないものを、洗い替えが必要ないものとして
// 既存の売上テーブルからデータ更新側の売上テーブルに連結
Concatenate(売上)
    売上No,
    売上日,
    データ登録日時,
    データ更新日時,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額
Resident 売上_temp
Where update_flg <> 1;

Drop Table 売上_temp;

新たに更新されたレコードをデータベースから、更新のなかったレコードをQVDから取得することができました。

本日日付でのQVDファイルの保存

最後に、更新したテーブルを再度QVDとして保存することで、次回の差分ロード時に活用します。
名前にロードした日の日付を付け、保存することとします。

// 変数にロードした日本時間での日付を格納
Let varToday = Date(ConvertToLocalTime(Now(),'GMT+09:00'));

// 『/』を削除し『YYYYMMDD』の形へ（Dateの形式による）
Let varTodayFile = Replace(varToday,'/','');

// 売上テーブルをqvd形式で再度保存
STORE [売上] INTO [lib://Amazon_S3_V2/test/sales_$(varTodayFile).qvd](qvd);

新たに作成した売上テーブルを『sales_YYYYMMDD』の形で再度QVDとして保存することができました。

よかったらシェアしてね！