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

Step1:既存の販売記録データに対するクエリ処理のハンズオン

 (目安:30分程度)

1.概要

Step1では、従来 RDBで行っていた構造化データに対するクエリ処理を同様にHadoopでも実施できるということを体験していただきます。

具体的には、構造化データとして購買記録(CSVファイル)の取り込みとHiveを利用した簡単なSQLクエリによる集計処理を実施します。

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

  1. 構造化データ(CSVファイル)の取り込み
  2. テーブルの作成
  3. Hiveによるクエリの実施

Step1のシステム概要図

2.事前準備

ご利用の仮想化環境における事前の環境準備を完了させている場合は次の3. 操作手順にスキップ可能です。

Sandboxのインストール
ご利用の仮想化環境における事前の環境準備を参照しHortonworks Sandbox (2.6.5) をインストールしてください。

以下のリンクから、本チュートリアルで利用するサンプルのCSVファイルをPCにダウンロードしてください


ダウンロードリンク:customer.csv

サンプルデータについて Step1では、以下のようなサンプルの顧客データが格納されたファイル "customer.csv" を利用します。
データの内訳:

項番
項目名
項目ID
​1 顧客ID customerid
2 氏名 name
3 フリガナ namekana
4 郵便番号 zipcode
5 住所 address
6 電話番号 phonenumber
7 メールアドレス emailaddress
8 性別 sex
9 年齢 age
10 購買回数 frequency
11 最終購買日 visitedday

データの内容:

1000,植松智恵理,ウエマツチエリ,301-0005,茨城県龍ケ崎市,0299824632,chieri82737@cbtc.mwc,女,47,15,2018-07-15
1001,日下部英雄,クサカベヒデオ,370-1602,群馬県多野郡神流町,0273306738,Hideo_Kusakabe@rhmbznnsew.hrcfx.tf,男,27,21,2018-06-12
1002,露木正則,ツユキマサノリ,323-0157,栃木県小山市,0285258849,itsuyuki@omuzawvld.vv,男,34,5,2018-04-29
1003,木内明日香,キウチアスカ,350-1115,埼玉県川越市,0487681989,bppkw=duasuka526@mmxlxsv.ub,女,42,3,2018-05-14
1004,篠原桃歌,シノハラモモカ,162-0855,東京都新宿区,0304499689,momoka546@cnitzonekz.qp,女,44,12,2018-02-23
1005,宮崎忠雄,ミヤザキタダオ,350-0128,埼玉県比企郡川島町,0481410308,tadao286@kppky.atluv.ho,男,35,17,2018-06-30

3.操作手順

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

Hiveを利用してクエリ処理を実施するため、顧客データ(CSVファイル) を Hadoop (HDFS) に取り込みます。
CSVファイルをHDFS上にアップロードするため、Ambari の機能「Files View」を使用します。

i. Ambariのログイン

ブラウザでSandboxインストールサーバのIPアドレスにアクセスし、Ambari Web UI にログインします。

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

Ambari Web UIへアクセスすると以下のような画面が表示されますので「Username」、「Password」を指定した後、 「Sign in」を選択してログインしてください。

注意: Sandbox起動後、最初にWebブラウザから上記アドレスにアクセスした際に、ログイン画面ではなく “502 Bad Gateway” というエラーが表示される場合は、Ambari のWebサーバの起動がまだ完了していない可能性があります。少し待ってから、再度同じURLにアクセスしてみてください。暫く待ってもログイン画面が表示される状態にならない場合は、Sandboxの起動に失敗している可能性がありますので、VMwareの機能によって「ゲストを再起動」し、再度お試しください。


Ambari WebUIへログインすると、以下のような「Dashboard」画面が表示されます。
初回はサービスの起動に数十分程度かかりますので、すべてのサービスが起動するまでしばらくお待ち下さい。
(下記赤枠のサービスステータスが、すべて緑のチェックマークになるまでお待ち下さい。)


すべてのサービス起動後、「Dashboard」画面の右上のオフキャンバスメニューを選択します。


オフキャンバスメニューを選択するとメニュー一覧が表示されますので「Files View」を選択します。


「Files View」を選択すると、以下の画像のようにHDFS上のファイルとフォルダ一覧が表示されます。

ii. 格納するディレクトリの作成

顧客データを格納するディレクトリを作成します。
Step1では、「/user/hive/customer」というディレクトリを作成します。
「Files View」に表示されているディレクトリから「user」→「hive」と選択し「hive」ディレクトリへ移動した後、画面右上に表示されている「New Folder」を選択します。


以下のように「Add new folder」の画面が表示されますので「Enter folder name...」の欄にディレクトリ名 "customer" を入力し「Add」を選択すると customer ディレクトリが作成されます。

以下のように customer ディレクトリが作成されたことを確認してください。

iii. ファイルのアップロード

続いて、ディレクトリ一覧から作成した「customer」ディレクトリを開きます。
「customer」ディレクトリに移動しますので、画面右上の一覧から「Upload」を選択します。

「Upload」を選択すると、以下のようなファイルアップロード画面が表示されるので、 アップロード画面中央へ取り込む顧客データ「customer.csv」をドラッグ&ドロップします。


ファイルアップロード完了後、以下の画面のようにディレクトリ内にファイルが追加されます。

iv. ファイルへのアクセス

取り込まれた CSV ファイルを画面上で選択すると、画面左上に対象のファイルに対しての操作を行うパネルが表示されます。

例えば、上記のパネルから「Open」を選択すると、Web画面上に CSV ファイル「customer.csv」の内容が表示されます。


これでHDFS上にCSVファイルが取り込まれました。

b. テーブルの作成

次に取り込んだ顧客データに対してクエリ処理を実行するため、Hiveのデータベースとテーブルを作成します。

Ambari の「Hive View」を使用して、HDFS上へ取り込んだデータに対して対応するデータベースやテーブルを作成することができます。
Dashboard」画面右上のオフキャンバスメニュー一覧から「Hive View 2.0」を選択してください。


以下が「Hive View」のクエリ実行画面となります。

i. Hive外部テーブル作成

既存の「default」 データベースの中に、今回利用する顧客データ用の Hive テーブルを作成します。
以下の画面のようにDATABASEの欄に「default」のみ選択されていることを確認してください。

以下のテーブル定義を持つテーブルを作成します。

顧客データのテーブル定義一覧:

項番
項目名
項目ID
属性
​1 顧客ID customerid INT
2 氏名 name STRING
3 フリガナ namekana STRING
4 郵便番号 zipcode STRING
5 住所 address STRING
6 電話番号 phonenumber STRING
7 メールアドレス emailaddress STRING
8 性別 sex STRING
9 年齢 age INT
10 購買回数 frequency INT
11 最終購買日 visitedday STRING

Step1では、Hive の外部テーブルを作成し、取り込んだ顧客データの CSV ファイルに対してクエリを実行します。

外部テーブルには、Hive はテーブル定義の情報のみを管理しており、実際のデータは HDFS 上の CSV ファイルを直接参照します。
クエリ実行時には HDFS 上の CSV ファイルに対して集計などのクエリ処理が実行され、結果が利用者に返却されます。

ii. Customer_Tableの作成

以下のSQLクエリをコピーし、クエリ実行画面に貼り付けてください。
コード番号:1-1

CREATE EXTERNAL TABLE customer_table (
  customerid INT,
  name STRING,
  namekana STRING,
  zipcode STRING,
  address STRING,
  phonenumber STRING,
  emailaddress STRING,
  sex STRING,
  age INT,
  frequency INT,
  visitedday STRING
) ROW FORMAT
  DELIMITED FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  LOCATION '/user/hive/customer'
;

以下のように「Execute」を選択してクエリを実行し、顧客データの外部テーブルを作成します。

iii. Hive外部テーブルの確認

テーブル作成後に「F5」またはブラウザの更新ボタンによりブラウザを一度リロードすると、画面右のテーブル情報一覧に作成したテーブル名が表示されます。


また、「Hive View」画面左上の一覧から「TABLES」タブを選択することで、作成したテーブルのカラム情報などを参照することができます。

これでテーブルの作成が完了し、クエリを実行する準備が整いました。

c. Hiveクエリの実行

作成したテーブル (customer_table) に対してクエリを実行します。
Hiveでは一般的なSQLと同様に、集計クエリやユーザ定義変数を使用することができます。

例えば、「住所が神奈川県で、2018年6月 (2018年6月1日 - 2018年6月30日) に商品を購入した顧客のうち、購買頻度 (frequency) の高い順に上位10名を取得する」という分析を行う場合、以下のクエリを実行します。
コード番号:1-2

SET hivevar:prefecture='神奈川%';
SET hivevar:visitedmonth='2018-06%';
 
SELECT
  c.customerid, c.name, c.address, c.frequency, c.visitedday
FROM
  customer_table c
WHERE
  c.address LIKE ${prefecture} AND c.visitedday LIKE ${visitedmonth}
ORDER BY
  c.frequency DESC
LIMIT
  10
;

以下の画面のようにクエリを記載した後、「Execute」を選択しクエリを実行します。

実行したクエリが完了すると、画面上部に以下のメッセージが出力されます。

クエリ実行完了後、画面下のタブより「RESULTS」を選択すると、クエリの実行結果が表示されます。

これで構造化データに対しクエリを実行することができました。

d. 可視化

(非構造化データの取り扱い時に同じことを実施するのでここでは省略)

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

ページの先頭へ戻る