気軽に始めてみよう！クラウド時代のデータウェアハウス超入門

始めに

私は8年ほど前に情報処理試験でデータウェアハウスというものがあるということを知りました。当時は4択問題で問題文に 意思決定支援 というキーワードが出てきたら何なのかよく分かんないけど選択してました。それからずっと興味がありながら実物に触ったことはなかったのですが、クラウド型のデータウェアハウスが登場し触る機会を得ることができました。以前に比べデータウェアハウスはかなり身近なものになってきたのではないでしょうか。弊社でもAmazon RedshiftというAWSのサービスを利用した案件が増えてきています。 クラスメソッド、POSデータ数十億件をリアルタイム分析する基盤を「Amazon Redshift」「Tableau」で開発

この記事ではデータウェアハウスの知識から分析ツールを使ってAmazon Redshiftに接続するところまで簡単にまとめています。実際にどんなものか、触ったことがない方はこの記事を見てぜひ体験してみて下さい！

データウェアハウスって何？

情報を分析し意思決定支援を行うためのデータベースのことや、そのようなデータベースを含む意思決定支援システムのことをデータウェアハウスと言います。

普通のデータベースと何が違うの？

業務システムなどで利用される普通のデータベースと何が違うのか整理したいと思います。

ソフトウェアの違い

データウェアハウスではリレーショナルデータベースを使います。データの操作はSQLが使われます。じゃあ業務システムで使われるデータベースと同じなの？と思う方がいるかもしれませんがそうではありません。普通のデータベースと違いデータウェアハウスは分析用のデータベースになります。集計などを行うことが多いのでソフトウェアも集計処理に特化した列指向データベース(カラムナデータベース)と言われるデータベースが使われています。

製品によっては他にも細かい違いがあります。例えばAmazon Redshift はベースはPostgreSQLですが、PostgreSQLと違う点もあるようです。 Amazon Redshift および PostgreSQL

テーブル設計の違い

テーブル設計の違いを簡単に説明します。データウェアハウスでは多次元データベースを実装するためにスタースキーマというスキーマが使われます。スタースキーマはファクトテーブル、ディメンションテーブルという2種類のテーブルで構成されています。

ファクトテーブル

複数のディメンションテーブルの外部キーを複合キーにしているテーブルです。集計したい値を持っています。

ディメンションテーブル

マスター情報のテーブルです。データを分析する際にグルーピングしたい項目が列に含まれます。

以下がサンプルのER図です。緑がディメンションテーブル、黄色がファクトテーブルです。

ディメンションテーブルの中は第3正規形まで正規化せず、第2正規形で留めておくことでテーブルを結合するコストがかからないようにします。ファクトテーブルはディメンションテーブルのキー値と集計したい値のみの構成であれば第3正規形になります。時系列でデータを保持できるように設計し、更新、削除などでファクトテーブルの過去のデータが消えることがないようにします。

データウェアハウスに触れてみよう

大体、どんなものか分かったと思うので実際に構築して試してみたいと思います。

データベースを作る

実際にデータウェアハウスを触ってみましょう。今回はAWSのRedshiftというサービスを利用します。 Redshiftを利用するにはAmazon Web Service のアカウントが必要になります。以下の記事を参考にRedshiftのクラスタを作成して下さい。

Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：前編)

テーブルを作る

データベースができたら次はテーブルを作成しましょう。以下のDDLでデータベースにサンプルのテーブルを作成して下さい。SQLでの操作は通常のRDBと同じなのでPostgreSQLのクライアントツールであれば利用することができます。以下のページにSQL Workbench/Jというクライアントツールの設定方法などが載っています。

SQL Workbench クライアントの設定

DDLは以下になります。

--時間テーブル
create table dim_time(
    time_id smallint not null distkey sortkey,
    caldate date not null,
    day smallint not null,
    week smallint not null,
    month smallint not null,
    year smallint not null,
    holiday boolean default('N') not null
);

--店舗テーブル
create table dim_store(
    store_id integer not null distkey sortkey,
    store_name varchar(30) not null,
    region varchar(30) not null
);

--商品テーブル
create table dim_goods(
    goods_id integer not null distkey sortkey,
    goods_name varchar(30) not null,
    category varchar(30) not null
);

--売上テーブル
create table fact_sales(
    time_id integer not null sortkey,
    store_id integer not null,
    goods_id integer not null distkey,
    sales_quantity smallint not null
);

このサンプルでは売上テーブルがファクトテーブル、 店舗テーブル、時間テーブル、商品テーブルがディメンションテーブルになっています。

列名の後ろにsortkeyやdistkeyという見慣れない属性がありますが、これは Redshift独自のソートキーとディストリビューションキーというものです。詳細を知りたい方は以下の記事をご覧ください。 Amazon Redshift DB開発者ガイド  テーブル設計のベストプラクティス

データのインポート

通常、データウェアハウスでは業務用のデータベースなどからETLツールを利用してデータをインポートします。 ETLとはExtract/Transform/Loadの頭文字をとったものです。データを抽出後に加工し、データウェアハウスにロードするといった工程になります。以下にTalend Open Studioという無料のETLツールの記事へのリンクを張っておきますのでETLツールに興味がある方はご覧ください。 Talend Open Studio for Data Integrationを試してみた

今回はすでにExtract/Transformされたものとし、インポートするデータを以下のリンクからダウンロードしてAmazon S3にアップロードして下さい。

サンプルデータ

アップロードできたらcopyコマンドでテーブルにインポートします。私は以下のようなコマンドでインポートしました。 テーブル作成と同様にPostgreSQLのクライアントツールで実行します。

copy dim_time from 's3://sample-redshift/dim_time_pipe.txt'
CREDENTIALS 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>'
delimiter '|' dateformat 'YYYY-MM-DD';

copy dim_goods from 's3://sample-redshift/dim_goods_pipe.txt'
CREDENTIALS 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>'
delimiter '|'

copy dim_store from 's3://sample-redshift/dim_store_pipe.txt'
CREDENTIALS 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>'
delimiter '|'

copy fact_sales from 's3://sample-redshift/fact_sales_pipe.txt'
CREDENTIALS 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>'
delimiter '|'

データ分析してみる

これだけでは普通のデータベースを構築するのとあまり変わらないので、作成したデータベースにBIツールを使って接続し簡単にデータ分析してみたいと思います。BIツールはTableau Desktopというツールを使います。

Tableau Desktopの体験版の導入は以下の記事をご覧ください。 Tableau Desktopを試しに使ってみる

Tableau から Redshiftへの接続は下の注意事項を見てから以下の記事をご覧ください。 注意事項：以下の記事の最後のテーブルを選択するところは今回は複数テーブルがあるので「複数の表」を選択してください。 Tableau DesktopからRedshiftに接続

接続できたら試しに動かしてみましょう。表を表示するためにはまずディメンションとメジャーを選択する必要があります。 メジャーとは分析したい値、ディメンションとはデータを分析する際の切り口になる項目のことです。 例えば、店舗ごとの売上合計を表示したい場合は、メジャーが売上、ディメンションが店舗IDもしくは店舗名になります。 スタースキーマでいうとメジャーはファクトテーブルの集計項目、ディメンションはディメンションテーブルの項目になります。

以下の図のようにディメンションとメジャーを列と行の領域にドラッグアンドドロップします。

グラフが表示されました。どの地域が売り上げが高いのか一目瞭然です。

複数のディメンションを選択することもできます。以下の例では商品と休日フラグをディメンションにしています。 このグラフで特定の商品が休日に売れることが分かった、なんてことがあるかもしれないですね。 このようにデータを分析し、パターンや法則性を抽出することをデータマイニングと言います。

最後に

データウェアハウス超入門は以上になります。データウェアハウスというと敷居が高いイメージがありましたが、意外に簡単にできました。ビックデータ分析というとHadoopなども選択肢にあると思いますが、触ったことないので今後はHadoopの調査などしたいと思っています。最後に終わったらRedshiftのクラスタを削除するのを絶対に忘れないようにしましょう。