そろそろ、QlikからDBに直接書き込んでみようか

こんにちは！YOKOです。
Qlikでデータロードして、結果を直接データベースに書き込みたい！
って思ったことないですか？
QlikViewの時はVBScriptで書き込みできたのに…
ちょっと古いか😅
そんなことやっていた人も少ないとは思いますが…
まあ、できないことはないので、やってみることにします。

Qlikからデータロードした結果をデータベースに書き込むいくつかの方法

Qlik Senseからデータベースに書き込むようなロードスクリプトは僕が知る限り、ありません！
ですので、考えられる方法としては、次の方法かなーと思っていました。

① Qlik SenseからCSVファイルで出力して、CSVデータをQlik Automateで読み込んで1行ずつDBに書き込む
② Qlik Senseでストレートテーブルを作成し、Qlik Automateでストレートテーブルから読み込んでDBに書き込む

①はストレージにCSVファイルで一旦 Storeして、Qlik Automateで読み込んでくるわけだけど、ストレージを準備しないといけないし、1行ずつDBに書き込むのは時間かかりそうだなあと思う。
②はわざわざストレートテーブルを作って、それをQlik Automateで指定して読み込むって何だか邪道な気がする。
一番いいのはQlik内のテーブルから直接データを取り出して、DBに書き込むことなんだろうけど、そんなことできるんだろうか？
そして、1行ずつなんて回りくどいことせずにガツンと書き込みたいんだけどなあ…
なんて思っていましたが、何とかなりますよ！
さっそく、やってみます。

Qlik Automateに立ちはだかる２つの壁

これまでQlik Automateは、Qlik Cloudの契約さえあれば使えるとても便利なツールなのですが、これまで使ってきて、困ったことがいくつかあり、今回は次の２つが引っかかりそうです。

Qlik Automateのリソース問題
Qlik Automateは色んなコネクタがあって、データベースからデータベースへの直接連携ができます。
これは便利！と思って、MySQLからSnowflakeにデータを追加する処理を作ったのですが、MySQLから数万行SELECTしてSnowflakeにINSERTしようとしたら、メモリ不足（？）で動作しませんでした😢
もっとリソース増やしてほしいと思うんだけど、おまけで付いてくるツールなので、そんなワガママ言えないですよねえ。
なので、Qlik Automateでデータ連携させるには、大量データでAbortしないように、分割して実行させるような工夫をしておいたほうが良いです。

Qlik Senseのテーブルを直接取り込めない
僕が調べた限りでは、Qlik AutomateでQlik内のテーブルを直接読み込む方法はないような…
以前、QlikからHubSpot（MAツール）に連携する時に、ものすごく時間がかかるので、Qlik Sense内でJSON形式のテーブルを作成したら、バッチモードで書き込むことができたというありがたいことがありました。バッチなら100レコード単位で書き込めるので、時間が100分の1に短縮できたんですよね。
実は、これがヒントになります！

Qlikでデータベースに直接連携するには

今回は、Qlik Sense内のデータをMySQLのテーブルに直接INSERTしてみます。
上で書いた知見を基に、なるべくリソースを使わずにJSONテーブルから連携するというアプローチで攻めてみますね。
なるべくリソースを使わないようにするには、INSERT処理を分割しておきます。そのためには連携データであるJSON自体を分割しておく必要があります。そして、Qlik Sense内にJSONテーブルを作成することで、ストレージを介することなく直接連携ができるようになるというわけです。

Qlik Sense内にJSONテーブルを作成する

まず、JSON形式のデータをおさらいしておきます。
CSVがこんな形だとすると

id,name
1,yamada
2,yamakawa

JSONはこんな形です。

[
  {"id" : "1", "name" : "yamada"},
  {"id" : "2", "name" : "yamakawa"}
]

こんな形式に変換できるの？と思うかもしれませんが、至って簡単です。
（これから解説するロードスクリプトをコピペするだけでできます）

今回は、Qlik Sense内に以下のようなテーブルがあるとして、これをJSON形式に変換していきます。因みにこのテーブルには約12,000行のデータが格納されています。

上記のdataテーブルをJSON化するには以下のようなスクリプトを追加します。

//key-valueに変換
data_cross:
CrossTable('key','value',1)
Load
	RowNo() as row_no,
	* 
Resident data;

//レコード単位にまとめる
data_concat:
Load 
	row_no,
	'{' & Concat(Distinct key_value,',') & '}' as concat_key_value
Group By row_no;
Load 
	row_no,
	'"' & key & '"' & ':' & '"' & value & '"' as key_value
Resident data_cross
;

//JSON化（分割）
Let vSplit = 10000;	//分割するレコード単位
Let vRow_total = NoOfRows('data_concat');
Let vRow_split = Floor($(vRow_total) / $(vSplit));	//分割回数

For i = 0 to  $(vRow_split)

  data_json:
  Load 
      '[' & Concat(concat_key_value,',') & ']' as json_field
  Resident data_concat
  Where row_no > $(i)*$(vSplit) and row_no <= ($(i)+1)*$(vSplit);

Next i

Drop Table data_cross,data_concat;

ロースクリプトを補足しますね。

key-valueに変換　1〜7行目　→data_crossテーブル
CrossTableを使って、key-value型に変換します。変換した結果は以下のような形式になります。

レコード単位にまとめる　9〜19行目　→data_concatテーブル
data_crossテーブルをJSON形式に変換して、レコード1行になるようにまとめます。

JSON化（分割）21〜34行目　→data_jsonテーブル
data_concatテーブルを[ ]でくくります。ここでは10,000レコードを1かたまりのJSONとしてまとめます。今回のデータは12,000行ありますので、1レコード目が10,000個、2レコード目が2,000個のデータになっています。
実行時にエラーになった場合は、Let vSplit = 10000;の値を小さくするなどして調整してみて下さい。

Qlik Automateの設定

Qlik Automateで以下のようなフローを作成します。

Do Reload
App Id: リロードするアプリIDを指定
このリロードで、アプリ内にJSONテーブルが作成されます。JSONフィールドはjson_fieldという名称です。

Do Query
Query: TRUNCATE TABLE `table name`
今回は洗替するので、一旦テーブルをTRUNCATE（またはDELETE）します。

List Values Of Field
App Id: リロードしたアプリID
Field Name: json_field
JSONテーブルのJSONフィールド（ここではjson_field）を指定します。

Insert Bulk
Table: データベースのテーブル名
Values: List Values Of Field > Item > Q Text
ここでJSONの行単位でMySQLのテーブルにInsertします。ここでは2回の書き込みになる想定です。

Insert Bulkは初めて使いましたが、まさにJSONからバッチで実行するためのブロックのようですね。
これはいい！
以下、実行結果です。Qlikのリロードが完了してから、DBへのINSERTが完了するまで、12,000レコードの処理であっても、7秒程度で完了していました。