ページの先頭です。
サイト内の現在位置を表示しています。
ここから本文です。

Step2:新しいデータ(非構造化データ、 Webアクセスログ)の取り込みと可視化のハンズオン

 (目安:30分程度)

1.概要

Step1ではHiveというコンポーネントを利用することで、従来 RDBで行っていた構造化データに対するクエリ処理をHadoop上でも同じように実施できるということを体験しました。

これに対し、 Step2でも同様にHiveを利用することで、ログや画像などの非構造化データに対してもクエリが実施できることと、構造化/非構造化データを組み合わせてクエリが実施できることを体験します。

具体的には、非構造化データとしてWebアクセスログを新たに取り込み、既存の顧客データと組み合わせてクエリ処理を行った結果の見える化を実施します。

Step2は以下の手順で実施し、通常30分程度で完了できます。

a. 非構造化データの分析

      • 非構造化データの取り込み
      • テーブルの作成
      • Hiveクエリの実行
      • 可視化

b. 構造化データを非構造化データと組み合わせ、関連付けて分析

      • データの取り込み(取り込み済みのため省略)
      • テーブルのJOIN
      • Hiveクエリの実行
      • 可視化

Step2の「a.非構造化データの分析」におけるシステムの概要図

Step2の「b.構造化データを非構造化データと組み合わせ、関連付けて分析」におけるシステムの概要図

2.事前準備

チュートリアルStep1 を完了してください。
Step2 では Step1 で取り込んだ構造化データを利用します。

以下のリンクから、Step2で利用するサンプルのWebアクセスログをダウンロードしてください。
ダウンロード済みの方は次の「3.操作手順」へ進んでください。


ダウンロードリンク:access.zip

サンプルデータ(Webアクセスログ)について
Step2では、非構造化データとして以下のようなテキスト形式のWebアクセスログが格納されたサンプルファイル(access.log)を利用します。

222.99.22.236 - - [2017-02-13 12:35:20 +0900] "GET https://www.a-shop.com/news/news-0087.html HTTP/1.1" 200 517 "-" "Mozilla/5.0 (Linux; Android 4.4.4; 401SO Build/23.0.H.0.302) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/34.0.0.0 Mobile Safari/537.36" "1590"
222.99.22.236 - - [2017-02-13 12:35:29 +0900] "GET https://www.a-shop.com/news/news-0001.html HTTP/1.1" 200 487 "https://www.a-shop.com/news/news-0087.html" "Mozilla/5.0 (Linux; Android 4.4.4; 401SO Build/23.0.H.0.302) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/34.0.0.0 Mobile Safari/537.36" "1590"
222.99.22.236 - - [2017-02-13 12:35:33 +0900] "GET https://www.a-shop.com/news/news-0114.html HTTP/1.1" 200 507 "https://www.a-shop.com/news/news-0001.html" "Mozilla/5.0 (Linux; Android 4.4.4; 401SO Build/23.0.H.0.302) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/34.0.0.0 Mobile Safari/537.36" "1590"
222.99.22.236 - - [2017-02-13 12:35:37 +0900] "GET https://www.a-shop.com/news/news-0084.html HTTP/1.1" 200 485 "https://www.a-shop.com/news/news-0114.html" "Mozilla/5.0 (Linux; Android 4.4.4; 401SO Build/23.0.H.0.302) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/34.0.0.0 Mobile Safari/537.36" "1590"

3.操作手順

a. 非構造化データの分析

i. 非構造化データの取り込み

Step1 と同様に、非構造化データに対して Hive テーブルを作成し、クエリ処理を実行できるようにします。

Webアクセスログの取り込み
Ambari にアクセスし、「File View」を使用してWebアクセスログをアップロードします。

Webアクセスログの格納先として新規ディレクトリ「/user/hive/webaccess」を作成し、ダウンロードした access.log をアップロードします。
※「File View」を使用したファイルのアップロード方法は「Step1:既存の販売記録データ(構造化データ: CSV)に対するクエリ処理のハンズオン」を参照してください。


これでWebアクセスログ(非構造化データ)をHDFS上に取り込むことができます。

ii. テーブルの作成

次にクエリを実行できるようにするためにテーブルを作成します。

Step2以降ではクエリ処理や見える化を行うため、Zeppelin を利用します。

Zeppelin はWebブラウザ上でプログラムをインタラクティブに記述したり管理することができ、ノートブックと呼ばれるファイル単位でプログラムや説明文、実行結果などをまとめて管理します。

ブラウザ上でPySpark や SQL (HiveQL) などを記述し実行することで、Spark や Hive などのコンポーネントを利用したデータの解析や可視化を行うことができます。

ここではテーブル作成の前段階としてノートブックであるZeppelin Noteを作成しておきます。

Zeppelin Noteの作成

最初に「Zeppelin UI」へのアクセスとNoteの作成を行います。
WebブラウザでSandboxインストールサーバのIPアドレスにアクセスし、Zeppelinにログインします。

IPアドレス http://<SandboxインストールサーバのIPアドレス(SSH)>:9995

「Zeppelin UI」へ接続すると、以下のような Zeppelin のトップページが表示されます。


Zeppelinでクエリを実行し、見える化を行うためのNoteを作成します。
トップページから「Create new note」をクリックします。


「Create new note」を選択すると、以下のようにNote作成画面が表示されるので、 以下のようにパラメータを指定した後「Create Note」を選択しNoteを作成します。

Note Name

tutorial_step2

Default Interpreter jdbc

これで「tutorial_step2」という名前のZeppelin Noteが作成できます。

次にこのNoteにテーブルを作成していきます。
「Create Note」を選択すると、以下のように作成したNoteの画面が表示されます。


一つの Note には複数の Paragraph を作成することができます。
Paragraph にクエリやプログラムを記述することで、Web UI から直接クエリやプログラムを実行することができます。
Paragraph は Zeppelin Noteに表示されている以下の赤枠の箇所となります。


Zeppelin でテーブル作成のクエリを実行し、取り込んだWebアクセスログに対応するHiveテーブルを作成します。
今回はWebアクセスログを分析するために、以下のテーブルを作成します。

Webアクセスログのテーブル定義:

項番
項目名
項目ID
属性
​1 ホスト名 host STRING
2 ログ名 ident STRING
3 ユーザ名 usr STRING
4 リクエスト受信時刻 time STRING
5 リクエストの内容 request STRING
6 ステータスコード status STRING
7 レスポンスバイト数 size STRING
8 リファラ referer STRING
9 ユーザーエージェント agent STRING
10 顧客ID cid INT

Hive テーブル "webaccess_log" を作成するため、以下のクエリをコピーし Zeppelin の Paragraphに貼り付けてください。
コード番号:2-1

%jdbc(hive)
 
CREATE EXTERNAL TABLE webaccess_log (
  host STRING,
  ident STRING,
  usr STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING,
  cid INT
  ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))? \"([0-9]*)\"'
  )
  LOCATION '/user/hive/webaccess'
;

1行目はParagraphに記載するプログラムを指定するインタプリタの定義となります。
Zeppelin インタプリタとは特定のプログラミング言語(R,Pythonなど)や、データ処理のバックエンド(HadoopやRDBなど)を使用するためのプラグインとなります。

Zeppelinでは複数のインタプリタが用意されており、インタプリタの指定を変えることで、ユーザはParagraph毎に様々な言語やデータ処理のバックエンドを使用することができます。

Step2以降ではHive クエリを実行するため、「%jdbc(hive)」と記載します。

%jdbc(hive)

Webアクセスログのような非構造のテキストデータを取り込む場合、hiveのシリアライザー/デシリアライザー(serde2.RegexSerDe)を使用することで、正規表現によりテーブルを定義することができます。
今回は以下の正規表現 (input.regex) で、Webアクセスログのテキストを指定したカラムに分割しています。

) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))? \"([0-9]*)\"'
  )

記述したクエリを実行するには、Paragraph右上に表示されているを選択します。
処理が完了すると、Paragraph右上に「FINISHED」の表示がされます。

これで非構造化データに対してテーブル「webaccess_log」 を作成することができます。

iii. Hiveクエリの実行

作成したテーブル 「webaccess_log」 に対してクエリを実行します。
2018年6月21日のWebアクセスログの一覧を取得するため、以下のクエリを実行します。
コード番号:2-2

%jdbc(hive)
 
SELECT
  cid,
  request,
  time
FROM
  webaccess_log
WHERE
  time LIKE '%2018-06-21%'
;

上記クエリをParagraph に貼り付け、実行すると画面下部に実行結果が表示されます。
表示されているパネルの一覧からを選択することで、検索クエリの実行結果を確認することができます。


このように、Webアクセスログのような非構造データに対しても、従来と同様に SQL クエリを利用して簡単に検索や分析を行うことができます。

iv. 可視化

Zeppelinでは実行したクエリの結果をグラフとして視覚的に表示することができます。
ある月 (2018/6月) の1日毎のアクセス数を集計するため、以下のクエリを実行します。
コード番号:2-3

%jdbc(hive)
 
SELECT
  day,
  count(day) as access_count
  FROM(
    SELECT
      substr(time,2,10) as day
    FROM
      webaccess_log
  ) AS r
  WHERE
    day LIKE '2018-06%'
  GROUP BY day
;

このクエリを実行すると、以下の結果を得ることができます。


その後、以下の赤枠のパネルを選択することで、クエリの結果をグラフ形式で表示することができます。
右から2番目の折れ線グラフのボタンを押すと、折れ線の形式でグラフが表示されます。
これにより、一日ごとのWebアクセス数の推移を、折れ線グラフ (Line chart) 形式で表示されます。

b. 非構造化データを構造化データと組み合わせ、関連付けて分析

i. データの取り込み

構造化データも非構造化データもこれまでのハンズオンで既に取り込みました。次に進みましょう。

ii. テーブルのJOIN

テーブルのJOINとビューの作成

本Stepにて作成したテーブル「webaccess_log」とStep1で作成したテーブル「customer_table」を組み合わせて新たなビューの作成を行います。

Hive では、通常の RDB に対する SQL 同様にASやJOINなどのSQL文を使用することができます。
2018年7月17日を起点にして、顧客IDごとの最終Webアクセス日を抽出したカラム(webrecency)と、最終購買日を抽出したカラム(recency)を持つビューを作成するため、以下のようにクエリを実行します。
コード番号:2-4

%jdbc(hive)
 
CREATE VIEW customer_table_joined AS
  SELECT
    customerid,
    frequency,
    (unix_timestamp("2018-07-17", 'yyyy-MM-dd') - unix_timestamp(visitedday, 'yyyy-MM-dd')) / (24 * 60 * 60) as recency,
    webrecency
  FROM
    customer_table ct
  JOIN
    (
    SELECT
      cid,
      MIN(unix_timestamp("2018-07-17 +0900", 'yyyy-MM-dd Z') - unix_timestamp(time, '[yyyy-MM-dd HH:mm:ss Z]')) / (24 * 60 * 60) AS webrecency
    FROM
      webaccess_log
    GROUP BY
      cid
    ORDER BY
      cid
    ) AS wl
  ON
    wl.cid = ct.customerid
;

これで「webaccess_log」と「customer_table」を組み合わせたビュー「customer_table_joined」を作成できます。

iii. Hiveクエリの実行

作成したビュー「customer_table_joined」を利用し、顧客データとWebアクセスログを組み合わせた分析を行います 。
「最終購買日」と「最終Webアクセス日」がそれぞれ 100 日以内の顧客を集計するため、以下のクエリを実行します。
コード番号:2-5

%jdbc(hive)
 
SELECT
  *
FROM
  customer_table_joined
WHERE
  recency <= 100 AND webrecency <= 100
;

iv. 可視化

上記クエリの実行結果に対して、一番右の「Scatter chart」を選択すると散布図で表示することができます。

settings をクリックすると、グラフの詳細設定ツールが表示されます。
All fields にはデータのカラムがリストされており、 xAxis と yAxis は、それぞれグラフの x 軸と y 軸を表しています。 All fields に表示されているカラムをマウスで選び、ドラッグ&ドロップでxAxisとyAxisにセットすることができます。
これを使うと、グラフの縦軸と横軸に任意のカラムを設定し、様々な分析軸でデータを可視化できます。

ここでは、xAxisに「customer_table_joined.recency」(=最終購買日) 、yAxis に「customer_table_joined.webrecency」(=最終Webアクセス日)をセットしてみましょう。すると、以下のような顧客の分布が得られます。(見やすさのためグラフの形を整形しています。)

赤枠で囲んだ密集部分として、最近商品を購入し、かつ最近Webも閲覧している、現在の売上を支える優良顧客のセグメントが表れていることが読み取れます。

Step2は以上となります。お疲れ様でした。

  • 本資料中に記載される商品名、OSS名、会社名、ロゴ、トレードマークはそれぞれ各社、各団体の商標または登録商標です。

ページの先頭へ戻る