はじめに

このエントリでは前編に引き続き、Qlik SenseとAmazon Redshiftを使ったデータ・ディスカバリのデモ環境構築方法についてご説明します。前編ではAmazon Redshiftクラスタの作成とSQLクライアントツール導入によるRedshiftの管理作業環境の構築までを完了しました。以下では、実際にデモデータをAmazon Redshiftにロードし、Qlik Sense・QlikViewからデータ分析を行える環境の構築方法をご説明したいと思います。

手順としては、まずデモデータのCSVファイルをAmazon S3上にアップロードし、Amazon Redshift上にテーブルを作成してS3上のファイルからこのテーブルにコピーする流れとなります。その後、ローカルPCにODBCドライバを導入してAmazon Redshiftへの接続を設定し、Qlik SenseからRedshift上のデータを参照する設定を行ってサンプルチャートの作成を行います。

Amazon Redshift へのデモ用データのロード

事前の準備

  • 以下のリンクからデモ用データをダウンロードし、ダウンロードしたZIPを解凍してCategories.csv、Invoices.csv、SKU.csvの3つのファイルを任意のフォルダに保存します。

>デモ用データファイルをダウンロード

  • AWSのアクセスキー ID と秘密アクセスキーが必要となります。取得方法は以下のURLをご参照ください。

>アクセスキー ID と秘密アクセスキーの取得

デモデータファイル(CSV)のAmazon S3へのアップロード

1. トップのメニューから「Services」をクリックし、「S3」を選択します。

 01_S3_thumb[2]

2. 「Create Bucket」をクリックします。

 02_create_bucket_thumb[1]

3. バケット名を入力し、Amazon Redshiftクラスタを作成したリージョンを選択して「Create」をクリックします。 

 03_create_bucket_thumb[1]

※ バケット名は全Amazon S3システムでユニークである必要があります。

4. 一覧から作成されたバケットをクリックして選択します。 

 04_bucket_list_thumb[1]

5. 作成したバケット内に新規フォルダを作成します。「Actions」をクリックし、「Create Folder」を選択します。

 05_create_folder_thumb[1]

6. 「soucedata」フォルダ名として指定し、フォルダをクリックして選択します。

 05_folder_list_thumb[1]

7. 新たに作成した「sourcedata」フォルダにCSVファイルをアップロードします。「Actions」をクリックし、「Upload」を選択します。

 06_upload_thumb[1]

8.「Add Items」をクリックし、ダウンロードして解凍した3つのファイル(Categories.csv、Invoices.csv 、SKU.csv)を指定します。そして「Start Upload」をクリックします。

 08_upload_thumb[1]

300MB以上のデータをS3に初めてアップロードする場合、以下のメッセージが表示されますので「Enable Enhanced Uploader (BETA)」をクリックします。

 07_enable_enhanced_uploader_thumb[3]

9. アップロードした3つのファイルのチェックボックスをONにし、「Actions」をクリックして「Make Public」を選択します。確認ウインドウでは「OK」をクリックします。

 10_make_public_thumb[2]

Amazon Redshift上でのテーブルの作成

1. SQL Workbench/Jを開き、Categories、Invoices、SKUの3つのテーブルを作成するためのDDL分を実行します。

 11_ddl_thumb[1]

以下のSQL文をコピーして実行します。


CREATE TABLE Categories (
SALESCLASS varchar(2)
,PRODUCTCAT integer not null distkey sortkey
,"ProdCat Desc" varchar(20)
,"SalesCls Desc" varchar(20)
);

CREATE TABLE Invoices (
INVOICENBR integer
,EmpStoreKey varchar(10)
,MODELNBR varchar(30) not null distkey sortkey
,TRANSTYPE varchar(1)
,CUSTOMERID integer
,SalesDate integer
,Quarter varchar(2)
,ITEMTYPE  varchar(3)
,QUANTITY integer
,SALESAMT decimal(8,2)
,LineAmt decimal(8,2)
,CostAmt decimal(8,2)
,MarginAmt decimal(8,2)
,DISTRICT integer
,CUSTZIP varchar(10)
,Channel varchar(20)
,InvCounter integer
,Month  varchar(3)
,Year varchar(4)
,Day varchar(2)
,WeekDay varchar(3)
);

CREATE TABLE SKU (
"Vendor Name" varchar(10)
,MODELDESC  varchar(30)
,MODELAWC decimal(6,2)
,PRODUCTCAT integer
,PRODUCTGRP integer
,MODELNBR  varchar(30) not null distkey sortkey
,VENDORID integer
,OBSOLETEDT varchar(10)
,Obsolete varchar(3)
);

※以下のキーが上記のDDL文に利用されています:

  • sortkey: ソートキーに設定された列でデータがソートされてディスク上に保存されます。
  • distkey: 複数クラスタノード構成の場合、ここで設定された分散キーに基づいてデータがノード間に分散されて配置されます。

これらのキーはAmazon Redshiftのパフォーマンスに影響する項目となっており、本番環境では以下のドキュメント等を参考に設計・チューニングを行う必要があります。

※下記のSQL 文により作成されたテーブルの一覧の表示が可能です:

SELECT distinct(tablename) FROM pg_table_def WHERE schemaname = 'public'; 

※下記のSQL文によりテーブル構造を確認することが可能です:

DESC <<table name>> 

2. SQL Workbench/Jで、AmazonAmazon S3からRedshift上のテーブルへのコピーを行うcopyコマンドを実行します。

 12_copy_thumb[1]

以下のコマンドをコピーして実行します。(注意: 以下のバケット名、AWSのアクセスキーID 、秘密アクセスキーはご自身で設定された内容に置き換えてください。)


COPY Categories FROM 's3://<バケット名>/sourcedata/Categories.csv' CREDENTIALS 'aws_access_key_id=<アクセスキーID>;aws_secret_access_key=<秘密アクセスキー>' DELIMITER ',' IGNOREHEADER 1;
COPY Invoices FROM 's3://<バケット名>/sourcedata/Invoices.csv' CREDENTIALS 'aws_access_key_id=<アクセスキーID>;aws_secret_access_key=<秘密アクセスキー>' DELIMITER ',' IGNOREHEADER 1;
COPY SKU FROM 's3://<バケット名>/sourcedata/SKU.csv' CREDENTIALS 'aws_access_key_id=<アクセスキーID>;aws_secret_access_key=<秘密アクセスキー>' DELIMITER ',' IGNOREHEADER 1 CSV QUOTE '"';

コピー実行の際に以下のエラーが発生した場合には、アップロードしたファイルに対して「Make Public」が実行されているか、またバケットに対してユーザーに十分な権限が付与されているか(LIST権限がバケットへのアクセスには必要となります)を確認してください。

ERROR: S3ServiceException:Access Denied,Status 403,Error AccessDenied

 13_permissions_thumb[1]

Direct Discoveryを利用したQlik Senseアプリの作成

事前の準備

Amazon EC2インスタンス上へのQlik Sense Serverの導入が必要となります。(Amazon Redshiftと同一リージョンに配置されたインスタンスへの導入が推奨されます。)インストールの手順については以下の過去のエントリにてご紹介させて頂いています。

>Qlik Sense Serverインストール手順

ODBC接続の設定

1. 以下のPostgreSQL ODBC Driver (64bit)をダウンロードしてローカルPC上で解凍します。

http://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0210-x64.zip

2. 解凍したpsqlodbc_x64.msi を実行し、インストーラーの指示に従ってODBC Driverをインストールします。

 01_ODBC_thumb[1]

PostgreSQL ODBC Driverのインストール時に以下のエラーが発生する場合があります。 

 image2014-9-28 18-43-33_thumb[2]

その場合には、以下のサイトから 「Microsoft Visual C++ 2010 Redistributable Package (x64)」をダウンロードしてインストールし、再度PostgreSQL ODBC Driverのインストーラーの実行を行って下さい。

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

3.「管理ツール」から「ODBC データ ソース (64 ビット)」を開き、「システムDSN」タブを開いて「追加」をクリックします。

image

4. 「PostgreSQL Unicode(x64) 」を選択し、「完了」をクリックします。

image

5. 以下の値を入力し、「データソース」ボタンをクリックします。

パラメタ名
Data Source qlikcluster
Database dev
Server

<Redshiftクラスタのエンドポイント名を入力。 (確認方法は下記注釈を参照のこと)>

例) qlikcluster.xxxxx.ap-northeast-1.Redshift.amazonaws.com

Port 5439
User Name masteruser
Password <masteruserのパスワードを入力>

image

エンドポイント名はRedshiftクラスタのプロパティから「Configuration」 タブを参照することで確認することが出来ます。

 15_endpoint_thumb[1]

6. 「高度な設定」ページで以下の設定を行い、「設定2」をクリックします。

  • 「KSQO(クエリーキーセット最適化オプション)”」チェックボックスをOFF
  • 「ユニークインデックスを使う」チェックボックスをOFF

image

※このデモではシングルのAmazon Redshiftノード構成となっているため「キャッシュサイズ」がデフォルト設定 の100になっていますが、複数ノード構成の場合には最適化が推奨されます。

7. 「更新可能カーソル」チェックボックスがONとなっていることを確認し、「OK」をクリックします。

image

8. テストを実行してODBC Driverの設定を保存します。システムDSNに「qlikcluster」が追加されたことを確認します。

image

Qlik Senseのロードスクリプトの作成

1.アプリ作成権限を持つユーザーでQlik Sense Serverのハブにログインし、「マイワーク」上で「アプリの新規作成」をクリックして任意の名称でアプリを作成します。

image

2. データロードエディタを開きます。

image

3. 「接続の新規作成」をクリックして「ODBC」を選択します。

image

4. 「システムDSN」タブから64bitが選択されている状態で「qlikcluster」を選択し 「保存」をクリックします。 (ユーザー名とパスワードは空白のままにします。)

image

5. 前ステップで追加した「qlikcluster(xxxx)」のデータ接続定義の「接続文字列の関数を挿入」ボタンをクリックし、「LIB CONNECT~」のステートメントを追加します。

image

6. 次に、DIRECT QUERYのステートメントを追加します。

image

以下のDIRECT QUERY文をコピーして前ステップで追加した「LIB CONNECT~」の下に貼り付け、「データのロード」ボタンをクリックしてロードスクリプトを実行します。


SET DirectTableBoxListThreshold  = 4000000; 

DIRECT QUERY 
DIMENSION 
  //--Invoices Table-- 
  transtype AS "Trans Type", 
  itemtype AS "Item Type", 
  channel AS Channel, 
  year AS Year, 
  quarter AS Quarter, 
  month AS Month,
  day AS Day, 
  weekday AS WeekDay,   
  //--SKU Table-- 
  "vendor name" AS "Vendor Name", 
  modeldesc AS "Model Desc",
  dev.public.sku.modelnbr AS "Model Number", 
  obsolete AS Obsolete, 
  //--Categories Table-- 
  "prodcat desc" AS "Product Cat Desc", 
  "salescls desc" AS "Sales Class Desc" 
MEASURE 
  //--Invoices Table-- 
  quantity AS Quantity, 
  salesamt AS "Sales Amount", 
  costamt AS "Cost Amount", 
  marginamt AS "Margin Amount" 
DETAIL 
  //--Invoices Table-- 
  invoicenbr AS "Invoice Number", 
  customerid AS "Customer ID" 
FROM dev."public".invoices, dev."public".sku, dev."public".categories 
WHERE dev."public".invoices.modelnbr = dev."public".sku.modelnbr 
AND dev."public".sku.productcat = dev."public".categories.productcat ; 

  6. ロードスクリプトがエラーなく実行されたことを確認します。 (実際には上記ロードスクリプトで「Dimension」に設定された項目のみがQlik Senseのメモリ内にロードされ、「Measure」や「Detail」に設定された明細データはQlik Senseにはロードされず、Amazon Redshiftに格納されたデータを抽出します。)

image

Qlik Senseでのサンプルチャート作成

1. 左上のメニューから「アプリ概要」を開きます。

image

2. 「シートの新規作成」をクリックして新しいシートを開きます。

image

3. 「編集」をクリックして、編集モードをONにします。

image

4. 以下の棒グラフを作成します。 軸に「Channel」、メジャーに「Sum([Sales Amount])」を追加し、グラフが表示されたことを確認します。 

image

  • チャートタイプ: 棒グラフ
  • 軸: Channel
  • メジャー: Sum([Sales Amount])

    Qlik Senseのチャート作成についての補足

    Qlik Senseでインメモリにデータを格納した場合、SET分析を利用することにより非常に柔軟な集計を行うことが可能ですが、Direct DiscoveryではSET分析を利用することが出来ません。例えば、今回のデモ用データで「返品率%」を計算する場合、SET分析では以下で算出が可能です。

     count(DISTINCT {<TRANSTYPE={R}>} INVOICENBR) / count(DISTINCT INVOICENBR)

    同様の計算をDirect DiscoveryでSET分析を利用せずに計算を行うにはAmazon Redshift側のテーブル定義を修正する必要があります。以下、その手順の例をご説明します。

    1. 以下のSQL文をSQL Workbench/Jから実行します。このSQL文により「TRANSTYPE」に格納されている値が「R」(返品)の場合、「rtn_invoicenbr」に「INVOICENBR」の値を格納しています。

    ALTER TABLE Invoices ADD COLUMN rtn_invoicenbr int default NULL;
    UPDATE Invoices SET rtn_invoicenbr = INVOICENBR WHERE TRANSTYPE = 'R';

    2. 以下の行をロードスクリプトの「DETAIL」 に追加して、リロードを再実行します。

    rtn_invoicenbr AS "Return Invoice Number"

    3.シート上で追加した「rtn_invoicenbr」列を利用して、以下の形で返品率%や返品数の計算が可能となります。

    [チャネル毎の返品率%]
    image2014-9-29 16-51-5_thumb[1]

    • チャートタイプ: テーブル
    • 軸: Channel
    • メジャー: Count(DISTINCT [Return Invoice Number])/Count(DISTINCT [Invoice Number])

    [チャネル毎の返品数]
    image2014-9-29 16-52-59_thumb[1]

    • チャートタイプ: 棒チャート
    • 軸: Channel
    • メジャー: Count(DISTINCT [Return Invoice Number])

    QlikViewでの利用方法

    これまでQlik SenseでDirect Discoveryを利用したRedshift上のデータへのアクセス方法についてご説明しましたが、同様にQlikViewからアクセスする方法について以下ご説明させて頂きます。尚、以下の手順はQlik Sense Serverが稼働するAmazon EC2インスタンス上にQlikView Desktopを導入して作業を行っています。

    1. 任意の名称でQlikViewのアプリを作成し、ロードスクリプトの編集画面を開きます。「32Bit互換モード」はOFFとなっていることを確認し、データベースへの「接続」ボタンをクリックします

    image

    2. データソースから作成済みの「qlikcluster」を選択して「OK」をクリックすると、ロードスクリプトに「ODBC CONNECT~」ステートメントが追加されます。

    image

    3. Qlik Senseのロードスクリプトに利用したものと同様のDIRECT QUERY文をコピーし、先ほど追加した「ODBC CONNECT~」の下に貼り付けてロードスクリプトを実行します。

    image

    4. ロードスクリプトがエラーなく実行されたことを確認します。

    image

    5. 以下の例の様な形でチャートの作成が可能となります。

    image

    まとめ

    以上、2回に分けてQlik SenseとAmazon Redshiftを組み合わせてデータ分析を行うデモ環境を構築する手順をご説明しました。Qlik Senseの特長の一つとしてインメモリで高速にデータ分析を行えることが挙げられますが、今回実施したようにDirect Discoveryの機能を利用してデータウェアハウス(DWH)側に直接クエリを発行して分析を行うことも可能で、両方を組み合わせたハイブリッドな手法を取ることも可能です。今回はDirect Discoveryについては細かくご説明は行わずにサンプルスクリプトをそのまま利用しましたが、今後Direct Discoveryにフォーカスしたご説明もアップしていきたいと思います。