続！Qlik SenseでKintoneからデータを読み込み！
更新日：2023年9月26日
Kintone APIの制限「1クエリー最大500件」の壁を超える
kintoneからの単純なデータ取得はRESTコネクターを使って比較的かんたんにできました。
ただ、
実際にプロトタイプを作成して運用を始めてみるとデータの更新が面倒です・・・。
というのも、マスタ系は良いとして、日々データが記録されていくトランザクション系のデータに関しては500件ではとても間に合いません。。。仕方なく、kintoneのエクスポート機能を利用してCSVに出力していたのですが、どうしても手動になってしまうのでだんだん苦しくなってしまいました。
そこで何とか自動で更新できる方法はないかと挑戦してみた結果、ひとつの手法にたどりついたので記録しておきます。
※500件までの取得については前回記事 を参照ください。
500件の壁を超えるアイディア
できるだけ、スクリプトを考えずに実現できて、無理矢理にならないような感じの方法を考えた結果、下図のような案でやってみることにしました。
取得対象のkintoneアプリの要件は次の二点です。
★既に約12,000件のデータが記録されている。
★1週間に約400件のデータが追記されていく。
★頻度は少ないが、レコードの修正（Update）もあり得る。※kintoneアプリから500件以上のレコードを自動的にQlik Senseへリロードする計画図
▼処理のポイント
① まずは、既に対象kintoneアプリに記録されている12,000件はCSVに出力しておきます。
この12,000件は更新されないものとして割り切ります！（4月30日時点で出力しました）
⇒ 記録_20210430.csv
② ①を出力した後に記録されたデータについては、RESTコネクタを使用してQlik Senseで
ロードします。
・kintone クエリーで更新日付が5月1日以降のデータのみロードします。
・ロードしたデータは、そのままQVDファイルとして出力し保持しておきます。
・出力するQVDのファイル名は、リロード実行時の週番号とします。
⇒ 記録_2021_n週.qvd
⇒ 記録_2021_n+1週.qvd
⇒ 記録_2021_n+2週.qvd・・・・・
上記の処理なら、週ごとのQVDファイルが生成されていくので、毎日リロードすれば１週間分についてはレコードが修正されても対応できるはず！
▼Qlik Senseの良いところ
Qlik Senseはレイアウトが同じデータであれば、ファイルが分割されていてもリロード時にマージしてひとつのテーブルとして読み込んでくれます。さらに、読み取り対象のファイル名はワイルドカード「*」指定が使えるので、週ごとに生成されるQVDはまとめて自動でリロードすることができます。
これならQVD出力の部分だけうまくできれば、ほとんどロードスクリプトも書くことなくやれるはず。1年で52ファイル増えるけど適当なタイミングで手動でマージすればいっか。
週ごとのQVDを自動生成する
前述の構想でうまくいけそうな気がして喜んだところでふと思いました。kintoneアプリからの読み取りはkintoneアプリの項目ごとにQlik Senseへロードするため、Qlik Sense内では複数テーブルになってしまいます・・・。
QVDファイルへの出力はこのQlik Sense内のテーブル単位になるため、すべてのテーブルをQVDファイルにするとさすがにファイルが多くなりすぎて管理が難しい。（下図のようなイメージです）
図の場合はkintoneアプリの項目が4つとして、1週間ごとに4つのQVDファイルが出力される。図のサンプルはマスタですが、実際にQVDファイル化したいのはトランザクションなので、10項目くらいあります。「52週×10＝520」1年間に520もファイルができてしまうと大変です。ここは対策が必要です。
というわけで、もうひと手間かけてがんばってみることにしました。
何とかロードスクリプトを触らずにGUIでやれないものかと「連結または結合」の機能に辿り着きました。これで、Qlik Sense内にロードしたテーブルを結合（JOIN）しました。
「・・・」から「連結または結合」メニューをクリックすると、
「テーブルを2つ選択して連結または結合します。」
と表示されています。
結合したい2つのテーブルを選択すると画面が下図のように変わりました。選択した時点で左下の画面でキーになる項目がセットされていました。（今回はキーとなる項目の修正は不要でしたが、修正が必要な場合は右側にある「マッピングの編集」で対応できます）
「アクションを選択」から「Inner Join」を選択して、↑図では見切れていますが「適用」ボタンを押すと2つのテーブルが結合されます。
結合された2つのテーブルは、ひとつにまとめて表示され、↓図のように結合マークのアイコンが表示されていることが確認できました。
あとは連結されたテーブルに対して、残りのテーブルを同様の手順でひとつずつ結合していくことで最終的にすべてのテーブルを結合することができました。
結合されるテーブルが増えていくごとに左図のように項目が増えていくイメージです。
最終的に「テーブル名」を任意の ”トレーニング記録” という名称にしています。
これで、QVDに出力するためのトランザクションテーブルが作成できました！
QVDファイル生成用のスクリプト編集
あとは、上述までの手順で生成したテーブル「トレーニング記録」をQVDに出力するだけです。（※参考 QVDファイルを出力する構文 ）
今回は、毎週ごとにファイル名を変えながら出力することがポイントなので、ファイル名に変数を使用することにしました。このQVDファイル出力とファイル名の動的な制御は、データマネージャのGUIではできなかったので、最後の最後に思い切ってロードスクリプトでの対応に踏み切りました。
▼GUIで設定した結合処理のロードスクリプトの確認
結合処理のロードスクリプトの最後の部分のキャプチャーです。
いくつかの「INNER JOIN」と最終行に「RENAME TABLE」にて”トレーニング記録”という名称が設定されているのが確認できます。
▼QVD生成処理の記述
GUIで作成された先述のスクリプトの後にQVD生成処理を追記しました。
下図のように「QVD生成」というタブを追加して記載しました。（タブは「＋」で追加）
（※参考 QVD生成の構文について）
LET vWeek = year(Today())&'/'&week(Today());
Store トレーニング記録 Into [lib://DataFiles/トレーニング記録_kintone_$(vWeek).qvd];
drop Table トレーニング記録;これで、QVDファイルが生成されますので、あとは過去分として出力したCSVファイルと生成されるQVDをマージして読み込めば完了（のはず）です！
過去分CSVファイルと自動生成QVDファイルのマージ読み取り
ここまでのところで、kintoneアプリから500件を超える分のデータをQlik Senseにロードする準備が整いました。あとはkintoneアプリからエクスポートした既に登録済みの12,000件のデータをCSVファイルと、週ごとに出力されるQVDファイルをまとめてQlik Senseのトレーニング実績アプリ にロードする設定を行います。
ポイントは2つあります。
① 12,000件のCSVファイルと週ごとのQVDファイルの構成が同じであること
② 週ごとに増えていくQVDファイルはワイルドカード「*」で指定すること
下図のサンプルで確認すると、「トレーニング記録20210501.csv」と「トレーニング記録_kintone_*.qvd」は同じ構成のデータとなっています。このような場合はQlik Senseがロードした際にマージしてひとつのテーブルにしてくれます。
また、読み取り先のQVDファイル名が「トレーニング記録_kintone_*.qvd」とされているため、週ごとに「*」の部分が変わって生成され増えていくQVDファイルをまとめて全てロードすることができます。
おわり
Comments