Treasure Data Platform で始めるデータ分析入門 〜7. Data Visualization 〜 Treasure Viewer

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「6. Data Visulization」の第1回目,Treasure Viewer の紹介です。これから3つの可視化ツール:「Treasure Viewer」「Metric Insights」「Tableau」について紹介していきます。これらの用途・機能は全く異なるものであることを念頭に入れて置いて下さい。

 

f:id:doryokujin:20140204112215p:plain

Treasure Viewer は TQA ( Treasure Query Accelerator ) を使用しているユーザーが Treasure Management Console 内から使用できる,主にローデータの簡易 Viewer という位置付けです。Treasure Viewer は js 実装によりインタラクティブな操作が可能になっており,テーブル内の2つのカラムを x-軸,y-軸にドラッグすることで,サンプルデータテーブルおよび Column, Line, Area, グラフを参照することができます。

 

 

f:id:doryokujin:20140204112242p:plain

Treasure Viewer は後に紹介する Tableau に近い UI を持っていますが,Treasure Viewer の目的は,

  • 現在格納されているテーブル内にどのような項目があるのか,
  • どのようなレコードが入っているのか,
  • そしてそれを少し可視化してみよう

というところまでしかカバーしておりません。逆に Tableau は実際にそのようなローデータを集計した結果に対して様々なビジュアルを提供する意味では似ているとはいえ目的・用途が違っております。

 

f:id:doryokujin:20140204112258p:plain

それでは Management Console より Viewer にアクセスしてみましょう。左にあるアイコンリストから「Chart」を選択すると上の画面が表示されます。まずは対象とするデータベース・テーブルを選択します。

 

f:id:doryokujin:20140204112453p:plain

データベース名・テーブル名を特定すると,まず始めにその中に入っているカラム一覧とレコードサンプルを表示してくれます。この裏側ではアドホッククエリ(TQA)が投げられて10件のレコードを取ってきています。(Your Query のところにクエリが表示されています。)

次にリスト化されたカラムから2つ選択肢 x-軸,y-軸にドラッグし,見たい Chart を選択します。

 

f:id:doryokujin:20140204112437p:plain

結果として選んだ x-軸, y-軸から構成されるグラフが表示されます。こちらも裏側ではアドホッククエリ(TQA)が投げられていることが Your Query から確認できます。

 

なお,Treasure Viewer は現在も開発途上でありますので,今後様々な機能が追加されるかもしれません。

次回はより踏み込んだ可視化ツール,Metric Insights を紹介します。

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.6

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第6回目です。

2. Making Cross Table 

f:id:doryokujin:20140203133727p:plain

 

クロス集計は知っている人には説明の必要が無いかもしれませんが,ここでは前回まで紹介した各クエリパターン毎にクロス集計ステップを考えて行きます。それに並行して作成したクロステーブルに適した可視化方法のいくつかを紹介します。

パターン1:< m | dim1 >

こちらは,クロス集計に最低限必要な2つのディメンジョンを持っていませんのでその必要はありません。

パターン2:< m | time >

こちらも1ディメンジョンなのですが,timeカラム自身が階層を持つ(その他アイテムカラム: category ⊃ sub_category ⊃ item など)ので,クロス集計の対象になります。

Part.3 で紹介した以下の結果に対してクロス集計を適用してみます:

Result     :

+------+----+----+-----------+

| y    | m  | d  | sum       |

+------+----+----+-----------+

| 2012 | 12 | 01 | 1647475.0 |

| 2012 | 12 | 02 | 1110545.0 |

| 2012 | 12 | 03 | 1195025.0 |

...

x-軸にday , y-軸に month を選び,z-軸(セル)に sum を入れます。クロス「集計」と書いていますが,本ケースではセルに関して再集計されることはありません。

f:id:doryokujin:20140203141032p:plain

 

 

さて,このクロステーブルを可視化してみましょう。今回はバブルチャートを用います。上図のセルの値の大きさがバブルの大きさ+温度色で表現しています。

f:id:doryokujin:20140203141504p:plain

上図よりわかるのは,月の始め(1〜5日)および半ば(15日〜22日)の値が大きい傾向にあり,特に5月の値の大きさが目立っています。このバブルチャートはクロステーブルを直感的に表現していてわかりやすいですね。

パターン3:< m | dim1, dim2 >

Part.4 で紹介した,

基本例:トヨタ車のモデル (dim1),年式 (dim2) ごとの平均車両価格が安い TOP20

は x-軸にモデル(dim1),y-軸に年式(dim2)を設定すれば良いですね。

基本例:人気モデルの年式ごとの平均価格

は既にクロス集計を紹介しましたが,再掲載しバブルチャートと異なる可視化をしてみましょう。

f:id:doryokujin:20140201115547j:plain

f:id:doryokujin:20140203143628p:plain

上図はヒートマップと呼ばれ,バブルチャートがz-軸の値を円の半径と色で表現していたのに対し,こちらは色温度のみで表現しています。

パターン5:< m1 | Seg(m2) >

こちらも与えられたディメンジョンが1つなのでクロス集計の必要はありません。

パターン6:< m1 | Seg(m2), Seg(m3) >

基本例:中古車価格別,平均走行距離別,頻度分布

先ほど紹介したヒートマップも,セグメントが密になると境界もあいまいになってきます。この場合には "Contour Matrix" と呼ばれる等高線のような表示が有効です。

f:id:doryokujin:20140201161236p:plain

パターン7:< m1 | dim1, Seg(m2) クエリパターン

基本例:走行距離,年式別平均中古車価格

こちらも前回でヒートマップで可視化しましたが,"Symmetric Area Graph" で表現してみましょう。

f:id:doryokujin:20140203152714p:plain

今日はここまでです。

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.5

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第5回目です。

1. Making Mini Cubes 

f:id:doryokujin:20140201153106p:plain

 

Car Sensor データを元に上記のテーブル "usedcar_cube_without_catalog" を作成しました。

Mini Cube 作成ルール
  • メジャーカラムは 1 つだけ選択する。
  • ディメンジョンカラムは最大 2 つまで選択する。
  • ディメンジョンカラムの中で階層構造を持つものは,その上位階層を全部含めたものを1つのディメンジョンカラムとみなす。

さて,まず先に分布を定義しておきましょう。

定義

パターン5 における < Count(1) | Seg(m1) > ,およびパターン6 における< Count(1) | Seg(m1), Seg(m2) > をそれぞれ「m1 (, m2) における頻度分布(離散)」とよび,それぞれDis<m1>, Dis<m1,m2> と書く。わかりやすく「m1 (,m2) 別の分布」と表現しても良い。また,順序付け可能な dim1, dim2 を含む< Count(1) | Seg(m1), dim1 >,< Count(1) | dim1,  dim2 > も分布と定義する。

--

順序づけ可能な dim1 とは,例えばレベルやアイテム保有数などのディメンジョンにもなりうりますが,順序づけが可能で平均を求めることに意味のあるものを言います。

分布の概念は重要で特に平均を求める集計では,データ数が少なかったりばらつきが大きいことによって意味のある値として使えない事が少なくありません。そこで,分布をみることによってどの領域に多く集中(頻度が高い)しているのかを可視化によって捉える事ができます。

パターン5:< m1 | Seg(m2) >
< Count(1) | Seg(m1) > クエリパターン

こちらのパターンはメジャーの集計方法を頻度集計に限定したものです。

SELECT ceil( #measure1/width )*width AS x, COUNT(1) AS cnt

FROM

(

  SELECT #measure1, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure1/width )*width

ORDER BY x

 

基本例:中古車の価格別分布

SELECT ceil(used_price/width)*width AS x, COUNT(1) AS cnt

FROM

(

  SELECT used_price, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

GROUP BY ceil(used_price/width)*width

ORDER BY x

f:id:doryokujin:20140201150135p:plain

中古車の価格別分布です。30〜40万台をピークになだらかに減少しています。500万円以上のものは500万円のセグメントに集約しましたが,500万円以上の車も4281件エントリーされています。

< m1 | Seg(m2) > クエリパターン

より一般的なこちらのパターンを見ていきます。

SELECT ceil( #measure2/width )*width AS x,

  SUM( #measure1 ), COUNT(1) AS cnt, AVG( #measure1 ) AS avg

FROM

(

  SELECT #measure1, #measure2, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure2 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure2/width )*width

ORDER BY x 

基本例:中古車価格別,平均走行距離

SELECT ceil( used_price/width )*width AS x,

  SUM( odd_km ), COUNT(1) AS cnt, AVG( odd_km ) AS avg

FROM

(

  SELECT  odd_km,  used_price, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

GROUP BY ceil( used_price/width )*width

ORDER BY x

f:id:doryokujin:20140201154307p:plain今度は走行距離別の「頻度」ではなく「平均走行距離」を計算しました。500万円以上の車両はまとめて計算し直しています(平均の平均は平均ではありませんので)。

価格帯が安いものほど過走行気味ですが,120万円を越える当たりからは平均5万km前後で推移しているようです。

パターン6:< m1 | Seg(m2), Seg(m3) >
< Count(1) | Seg(m1), Seg(m2) > クエリパターン

SELECT ceil( #measure1/t2.width)*t2.width AS x, ceil( #measure2/t3.width)*t3.width AS y, COUNT(1) AS cnt

FROM

(

  SELECT #measure1, #measure2, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure2 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t3

ON t1.one = t3.one

GROUP BY ceil( #measure1/t2.width)*t2.width, ceil(#measure2/t3.width)*t3.width

ORDER BY x, y

基本例:中古車価格別,平均走行距離別,頻度分布

SELECT ceil(used_price/t2.width)*t2.width AS x, ceil(odd_km/t3.width)*t3.width AS y, COUNT(1) AS cnt

FROM

(

  SELECT used_price, odd_km, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

  AND 0 < odd_km

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( odd_km )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < odd_km

) t3

ON t1.one = t3.one

GROUP BY ceil(used_price/t2.width)*t2.width, ceil(odd_km/t3.width)*t3.width

ORDER BY x, y

f:id:doryokujin:20140201161236p:plain

中古車価格300万以上,走行距離3万km以上を無視して描いた2次元分布図。70万〜130万円の価格帯で低走行距離の車が多くあつまっているのがわかります。

< m1 | Seg(m2), Seg(m3) > クエリパターン

自明なので省略します。

パターン7:< m1 | dim1, Seg(m2) >
< m1 | dim1, Seg(m2) > クエリパターン

SELECT ceil( #measure1/width )*width AS x, %dim1,

  SUM( #measure1 ), COUNT(1) AS cnt, AVG( #measure1 ) AS avg

FROM

(

  SELECT #measure1, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure1/width )*width, %dim1

ORDER BY x, %dim1

基本例:走行距離,年式別平均中古車価格

dim1 として年式,Seg(m2) として走行距離のセグメントで m1: 中古車価格の平均を見ていきましょう。

SELECT ceil(odd_km/width)*width AS x, year,

   SUM( used_price ), COUNT(1) AS cnt, AVG( used_price ) AS avg

FROM

(

  SELECT used_price, odd_km, year, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 <= odd_km AND 0 < year

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( odd_km )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 <= odd_km

) t2

ON t1.one = t2.one

WHERE 0 <= used_price

GROUP BY ceil(odd_km/width)*width, year

ORDER BY x, year desc

f:id:doryokujin:20140203115320p:plain

パターン8:< m1 | dim1, dim2 >

こちらも省略します。

 

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.4

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第4回目です。

1. Making Mini Cubes 

 

f:id:doryokujin:20140201153106p:plain

Car Sensor データを元に上記のテーブル "usedcar_cube_without_catalog" を作成しました。

Mini Cube 作成ルール
  • メジャーカラムは 1 つだけ選択する。
  • ディメンジョンカラムは最大 2 つまで選択する。
  • ディメンジョンカラムの中で階層構造を持つものは,その上位階層を全部含めたものを1つのディメンジョンカラムとみなす。
パターン3:< m | dim1, dim2 >
< m | dim1, dim2 > クエリパターン

SELECT SUM( #measure ) AS sum, COUNT( 1 ) AS cnt

FROM table

WHERE condition

GROUP BY %dim1, %dim2

ORDER BY  cnt

パターン1 にディメンジョンが 1 つ増えたケースです。

基本例:トヨタ車のモデル (dim1),年式 (dim2) ごとの平均車両価格が安い TOP20

SELECT name, model, year,

  SUM(CAST(used_price AS INT)) AS sum_price,

  COUNT(1) AS cnt_price,

  AVG(CAST(used_price AS INT) )AS avg_price

FROM usedcar_cube_without_catalog

WHERE name='トヨタ' AND 0 < used_price

GROUP BY name, model, year

ORDER BY avg_price asc

LIMIT 20

モデル "model" カラムはメーカー名を上位階層に持ちます。生産国もさらにその上の上位階層ですが,メーカー名をトヨタに絞り込んでいるので生産国のカラムは必要無いことになります。

Result     :

+------+-----------+------+-----------+-----------+--------------------+

| name | model     | year | sum_price | cnt_price | avg_price          |

+------+-----------+------+-----------+-----------+--------------------+

| トヨタ  | マークIIセダン  | 1997 | 50000     | 1         | 50000.0            |

| トヨタ  | カローラII    | 1997 | 50000     | 1         | 50000.0            |

| トヨタ  | エスティマエミーナ | 1995 | 70000     | 1         | 70000.0            |

| トヨタ  | カリーナED    | 1994 | 79000     | 1         | 79000.0            |

| トヨタ  | カルディナ     | 1995 | 80000     | 1         | 80000.0            |

| トヨタ  | ビスタ       | 1994 | 80000     | 1         | 80000.0            |

| トヨタ  | カローラII    | 1994 | 84000     | 1         | 84000.00 |

上記の結果は,トヨタ車の中で安価なモデルと年式を特定しています。できるだけ年式の高いモデルで安い車を見つける場合にはこのリスト20から探せば良いことになります。

f:id:doryokujin:20140201111859p:plain

ここで1つ注意点,セグメントを2つ設定するということはそれだけ出力のバリエーションが爆発的に増える一方で,ここの集計値は非常に少ない個数での値になってしまいます。上記の例では,該当するほとんどが個数1ですので,平均を求めたつもりが値を取り出しただけになってしまっています。

基本例:人気モデルの年式ごとの平均価格

先ほど,バリエーションが爆発的に増えると書きましたが,上位 n 件を必要とする場合はそれでもかまいませんが,他に有効な手段として条件文によってバリエーションを特定することです。次は

新車乗用車販売台数月別ランキング

の楽しいページから人気モデルをピックアップして,どのモデルのどの年式がお買い得かを特定しています。結果をcsvにはき出してみました。

モデル,年式,sum_price,cnt_price,avg_price

フィット,2001,30666000,162,189296.2962962963

フィット,2002,121997000,555,219814.4144144144

フィット,2003,111691000,414,270438.25665859564

フィット,2004,166223000,489,340620.9016393443

ノート,2005,177072000,466,380800.0

フィット,2005,158280000,406,389852.21674876846

フィット,2006,134833000,307,440630.71895424835

アクセラ,2004,9390000,21,447142.85714285716

アクセラ,2003,450000,1,450000.0

ノート,2006,113910000,251,453824.7011952191

...

 

今回は次々回で説明するクロス集計を行ってみる事にします。

 

f:id:doryokujin:20140201115434j:plain

 

今まで平均を求める際に SUM と COUNT を一緒に取ってきましたが,クロス集計した結果に対して平均の平均は適用できないからです。

f:id:doryokujin:20140201115828p:plain

ディメンジョンを2つ設定したことによって x-軸,y-軸 に各々を配置し,z-軸(セル)の値をバブルの大きさと色で表現することが可能になりました。どうやらアクセラが年式の割にはノートやフィットよりも値段が高いようです。

パターン4:< m | time, dim2 >
< m | time, dim2 > クエリパターン

SELECT %year, %month, %day, %dim2, SUM( #measure ), COUNT( 1 )

FROM table

WHERE condition

GROUP BY %year, %month, %day, %dim2

ORDER BY  %year, %month, %day

上記のパターンは説明をする必要も無いので省略しますね。

次回はこれら4パターン以外の特殊パターンを紹介します。分布とか,そこらへんです。

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.3

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第3回目です。

1. Making Big Cube(振り返り)

f:id:doryokujin:20140129131303p:plain

 

f:id:doryokujin:20140131165457p:plain

 

前回の Big Cube 作成ステップによって,上図のような1つの統合テーブル("usedcar_cubu_without_catalog")を作成しました。次のステップではこのテーブルに対して処理を行っていきます。

2. Making Mini Cubes

f:id:doryokujin:20140131115237p:plain

 

Big Cube から Mini Cubes を作成していきます。作成される Mini Cube 達はデータサイズが Source Tables (元データ)と比べて概ね 1/100 以下になっているため,保存先としては Treasure Cloud Storage 以外にもローカルファイル,MySQL,Memory 等の選択肢がありますが,ここでは Treasure Cloud Storage に引き続き保存しておくものとします。

さて,ここでの目標はこのステップをできるだけパターン化し,半自動的に行えるようにすることです。そのために以下のルールを適用します。

Mini Cube 作成ルール

  • メジャーカラムは 1 つだけ選択する。
  • ディメンジョンカラムは最大 2 つまで選択する。
  • ディメンジョンカラムの中で階層構造を持つものは,その上位階層を全部含めたものを1つのディメンジョンカラムとみなす。

3番目の意味は,例えば時間カラムであれば(年 > 月 > 日 > 時)の4つのカラムは 1 つのTime ディメンジョンカラムとしてひとくくりし,例えば商品カラムであれば(大カテゴリ > 中カテゴリ > 小カテゴリ > 商品名)を商品ディメンジョンカラムとしてひとくくりにして扱うということです。

定義

< m | dim1, dim2 > を「ディメンジョン dim1, dim2 をセグメントとしたメジャー m に関する集計」と呼ぶ。

上記の定義によって,基本的な集計パターンの表記が可能となります。それではここから Car Sensor のデータと共に具体的に見ていくことにします。

パターン1:< m | dim1 >
< m | dim1 > クエリパターン

SELECT SUM( #measure ) AS sum, COUNT( 1 ) AS cnt

FROM table

WHERE condition

GROUP BY %dim1

ORDER BY  cnt

もっとも基本的なパターンは 1 ディメンジョン,1 メジャーパターンです。

基本例:年式ごとの中古車登録数

年式 "year" をディメンジョンに,1 をメジャーにした最もシンプルな集計です。

SELECT year, COUNT(1) AS cnt
FROM usedcar_cube_without_catalog
GROUP BY year
ORDER BY cnt 

Result     :

+------+----------+

| year | cnt_year |

+------+----------+

| 0    | 96       |

| 1926 | 1        |

| 1928 | 2        |

| 1929 | 1        |

| 1930 | 1        |

| 1931 | 1        |

f:id:doryokujin:20140201103824p:plain

基本例:メーカー名ごとの平均中古車価格

唯一の注意点はメーカー名の上階層に生産国名があることです。以下が具体的なクエリになります。

SELECT country_name, name, SUM(used_price) AS sum_price, COUNT(1) AS cnt_price, AVG(used_price) AS avg_price

FROM usedcar_cube_without_catalog

GROUP BY country_name, name

ORDER BY country_name, avg_price desc

f:id:doryokujin:20140131171748p:plain

 

Treasure Manegement Console からの実行キャプチャ↑,およびリザルト↓。

 

f:id:doryokujin:20140131171902p:plain

 

せっかくなのでいくつか可視化してみましょう。

f:id:doryokujin:20140131172623p:plain

平均車両価格が高いメーカー TOP20。上位には高級車として有名なメーカーが勢揃いですね。

f:id:doryokujin:20140131173409p:plain

↑ そもそもメーカー数がいくらになるのかは,上位階層の「生産国」登場頻度を数えればすぐに見れますね。さらに 「生産国」×「メーカー」のピボットテーブルを作って平均車両価格を見れば面白そうです。こちらは次回のクロス集計章で紹介していきます。

パターン2: < m | time >

特に「dim1 を time ディメンジョンにした集計」は最もシンプルかつメジャーな集計です。残念ながら Car Sensor データでは意味のあるtimeカラムを持っていませんので,他データで集計したものを図示しています。

< m | time > クエリパターン

SELECT %year, %month, %day, SUM( #measure ), COUNT( 1 )

FROM table

WHERE condition

GROUP BY %year, %month, %day

ORDER BY  %year, %month, %day

基本例:デイリーの商品売上総額

SELECT

  TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS y

  TD_TIME_FORMAT(time, 'MM', 'PDT') AS m,

  TD_TIME_FORMAT(time, 'dd', 'PDT') AS d,

  SUM( price * count ) AS sum

 )

FROM pay

WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')

GROUP BY

  TD_TIME_FORMAT(time, 'yyyy', 'PDT'),

  TD_TIME_FORMAT(time, 'MM', 'PDT'),

  TD_TIME_FORMAT(time, 'dd', 'PDT')

ORDER BY y desc, m desc, d asc

結果は以下の通りです。

 Result     :

+------+----+----+-----------+

| y    | m  | d  | sum       |

+------+----+----+-----------+

| 2012 | 12 | 01 | 1647475.0 |

| 2012 | 12 | 02 | 1110545.0 |

| 2012 | 12 | 03 | 1195025.0 |

| 2012 | 12 | 04 | 1916040.0 |

...

課金テーブルを pay としています。出力テーブルはディメンジョンと集計値で構成されます。

応用例:ARPPU

SELECT

  TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,

  TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,

  TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,

   COUNT(distinct uid ) AS uu,

   CAST( SUM( price * count ) AS BIGINT) AS sum

FROM pay

WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')

GROUP BY

  TD_TIME_FORMAT(time, 'yyyy', 'PDT'),

  TD_TIME_FORMAT(time, 'MM', 'PDT'),

  TD_TIME_FORMAT(time, 'dd', 'PDT')

ORDER BY year desc, month desc ,day asc

応用例:ARPU

SELECT

  t1.year AS year,

  t1.month AS month,

  t1.day AS day,

  CAST( t2.sum/t1.uu AS BIGINT ) AS arpu,

  CAST( t2.sum/t2.uu AS BIGINT ) AS arppu

FROM

    (

      SELECT

        TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,

        TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,

        TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,

        COUNT( distinct uid ) AS uu

      FROM login

      WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')

      GROUP BY

        TD_TIME_FORMAT(time, 'yyyy', 'PDT'),

        TD_TIME_FORMAT(time, 'MM', 'PDT'),

        TD_TIME_FORMAT(time, 'dd', 'PDT')

    ) t1

    LEFT OUTER JOIN

    (

      SELECT

        TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,

        TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,

        TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,

        COUNT(distinct uid ) AS uu,

        CAST( SUM( price * count ) AS BIGINT) AS sum

      FROM pay

      WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')

      GROUP BY

        TD_TIME_FORMAT(time, 'yyyy', 'PDT'),

        TD_TIME_FORMAT(time, 'MM', 'PDT'),

        TD_TIME_FORMAT(time, 'dd', 'PDT')

    ) t2

    ON

    (t1.year=t2.year AND t1.month=t2.month AND t1.day=t2.day)

    ORDER BY year desc, month desc ,day asc

ユニークログインユーザー数の計算が t1 で,課金総額とユニーク課金ユーザー数の計算が t2 で行われています。

結果は以下の通りです。

    +------+-------+-----+------+-------+

    | year | month | day | arpu | arppu |

    +------+-------+-----+------+-------+

    | 2012 | 12    | 01  | 106  | 2294  |

    | 2012 | 12    | 02  | 71   | 1891  |

    | 2012 | 12    | 03  | 77   | 1890  |

    | 2012 | 12    | 04  | 123  | 2717  |

 

f:id:doryokujin:20140131180751p:plain

上図は11月の ARPU, ARPPU を日単位で表したものです。 

 

次回は < m | d1, d2 >,< m | time, d2 > パターンとその他: < m1 | Seg(m2) > を紹介します。内容は概ね想像が付くと思います。

 

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.2

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第2回目です。

 

f:id:doryokujin:20140128165631p:plain

 

前回紹介した Cube Design の中で,本日は 前処理 & Big Cube 作成のところを紹介します。このフェーズはローログを参照する上に,面倒な前処理が多数必要となる可能性があります。前処理の話はそれだけで非常に長くなってしまいますので,今回はその部分は省略させて頂きます。また別シリーズで書きます。

Making Big Cube

f:id:doryokujin:20140129145651p:plain

 

Big Cube 作成は上図の 5 つのステップを踏んでいきます。(ちなみに前処理は 1. およびその前のステップで行います。)

0. Source Data

カーセンサーnet Webサービス 

実際の処理を具体的に進んでリクルート様が公開してくれているカーセンサーの中古車相場データを使います。(データを提供して頂いたリクルート様,ありがとうございます!)

1. Join ステップ

システム的に最適化されたテーブル構造と分析者に最適化されたテーブル構造は異なります。後者では,複数のテーブルに分かれている場合はデータの同時参照やクエリがややこしくなるということで,ここでは大いに割り切ってそれを全部 Join しちゃえという戦略で行きます。

しかしこのステップでは項目の取捨選択は深く考えません。後で必要となりそうな項目はとにかく残していきます。なぜならこの Big Cube を作成した後は,二度と Source Table にアクセスしないようにしたいためです。

ここまでポイントをまとめます。

  • 全てのテーブルを Join する。
  • 今後使えそうな項目は全て削らず残しておく。
  • Big Cube 作成後はそれ以前の Source Data にアクセスしない。

f:id:doryokujin:20140129133539p:plain

Car Sensor のデータは,メインの "carsensor", "catalog"  テーブルと id と name のマッピングテーブルがいくつかあり,上記のように紐付いています。ただし, carsensor と catalog テーブルは 1対1 の対応ではなく,同じ code, grade, model でも catalog の方では年式(period)などにより複数のレコードが存在してしまいます。そこで今回は catalog テーブルは join の対象外とします。

join.sql 

上記のクエリファイルをローカルに join.sql 名でダウンロードし,そのディレクトリ内で以下のコマンドで実行します。

$ td query -w -d carsensor -q "join.sql" --result 'td://@/carsensor/usedcar_cube_with_catalog?mode=replace'

query コマンドに関してはドキュメント:Writing the Query Results into your TD Table

を参照して下さい。 "usedcar_cube_with_catalog" テーブルが新しく作成(既存なら書き換え)されます。これが Big Cube になります。また,catalog も join した "usedcar_cube_without_catalog" テーブルも以下のクエリで作成できます。

join2.sql

$ td query -w -d carsensor -q "join2.sql" --result 'td://@/carsensor/usedcar_cube_without_catalog?mode=replace'

2. メジャー,ディメンジョン分類ステップ

つぎに,"carsensor_cube_without_catalog" のカラムをメジャー・ディメンジョンに分類します。

f:id:doryokujin:20140201153106p:plain

  • メジャー:集計できる定量化可能なデータ (通常は数値データ) を含んでいる項目で,SQL ではSUM や COUNT などの集計計算の対象となる項目
  • ディメンジョン:集計の切り口・セグメントとなる項目

このメジャーとディメンジョンで分類できさえすれば,後の集計は下記のクエリテンプレートに従って組み合わせを考えるだけで集計を行う事ができます。

SELECT SUM( #measure ), COUNT( 1 ) {, ANOTHER_MATH_UDF( #measure ), COUNT( DISTINCT( %dimension  ) }

FROM table

WHERE condition

GROUP BY %dimension1, %dimension2

次回はこのクエリテンプレートを用いて Mini Cube を作成していきます。

Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.1

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」を数回に分けて紹介していきます。

 

データサイエンティストとは?

さて,とても個人的な意見ですが,データサイエンティストってのは以下の3つの意味で「プロフェッショナル」でないといけないと思っております。

  1. 「前処理」のプロフェッショナル(マエショリスト)
  2. 「集計をデザイン」する事のプロフェッショナル(アーキテクト)
  3. 「分析」のプロフェッショナル(アナリスト)

 1. 3. についてはご存じかと思われますが,とても重要なのはこの 2. になります。本章ではここにフォーカスを置いていきます。

f:id:doryokujin:20140128163446p:plain

2. はもっと言えば「自分が集計しない」ことを徹底的に追究することです。僕の妄想ですが現状日本においては,多くのデータサイエンティストってのはこの「集計する」という事(レポーティング業務)にリソースが割かれすぎて,その前後の前処理と分析がおろそかになってしまっていると感じています。

同じようで,しかしちょっとずつ項目やセグメントが異なるレポートをうまくパターン化できずに,内容自身は単純な作業なのにほぼ人力で行われているケースって結構あると思うのですよね。

本章のゴールはこの「集計」をうまくデザイン(パターン化)し,それこそ学生アルバイトでもこのプロセスを遂行できるようにすることです。そうすることでデータサイエンティストは本来の仕事に専念でき,かつ一定のクオリティの定時レポーティングがアルバイトリソースで量産できることになります。

# ここではまだまだ思慮の余地がありますが,これでうまくいくのでは,と考えている手法を紹介します。数ヶ月後,精錬されたものを改めて紹介するかもしれません。

 

f:id:doryokujin:20140128164444p:plain

 

 

今回紹介するのは「Cubic Data Processing Design」という,

Source Tables → Big Cube → Mini Cubes → Cross Tables → Visualize

という形でデータを情報を凝縮させていく方法です。それぞれのプロセスについて「専門家」or「アルバイト」のどちらが遂行すべきかを明確にし,リクルート様の提供して下さった「Car Sensor」データを使って具体的に紹介していきます。

皆さんにも手を動かしてもらえるように,Car Sensor データのダウンロード,インポート作業は, 「実データで覚える Treasure Client コマンドラインリファンス」シリーズで紹介しております。

 

f:id:doryokujin:20140128165631p:plain

 

 

今回ももちろん Treasure Data プラットフォームを利用していきます。Source Table から Mini Cubes まではそれらを全部 Treasure Cloud Storage 上に作って行きます。

 

次回は専門アナリストによる Big Cube の作成と前処理のプロセスを実データで見ていきます。お楽しみに。

実データで覚える Treasure Client コマンドラインリファンス 〜2.Data Management〜

本シリーズではTreasure Client ツールのコマンドラインリファレンスを以下の5つのレイヤーに分解し,各々について解説していくものとします。

No.レイヤーコマンド
1. Data Import

one-time import

bulk import

2. Data Management

db

table

3. Data Processing

schema

query

job

4. Data Scheduling

sched

result

5. Other

help

status

server

sample

本日は 2. Data Management を紹介します。本シリーズでは実際にデータを使用して,その分析の流れに沿いながらコマンドラインを紹介していきます。実際に手を動かすためには,前回(1. Data Import) を参考にデータのインポートをお願いします。

1. db コマンド

db コマンドは現在存在する database の確認および追加削除ができる一般的なコマンドです。

$ td help db                                                                                                                                                                                          

Additional commands, type "td help COMMAND" for more details:

 

  db:list                                    # Show list of tables in a database

  db:show <db>                               # Describe information of a database

  db:create <db>                             # Create a database

  db:delete <db>                             # Delete a database

前回は db名: "carsensor" を作りましたので,その中にあるテーブルを見てみましょう。

$ td db:list                                                                                                                                                                                           

+------------------------------+------------+

| Name                         | Count      |

+------------------------------+------------+

| book_crossing_dataset        | 1700014    |

| carsensor                    | 395927     |

+------------------------------+------------+                                                                                                                                               

 

$ td db:show carsensor

+-------------------+------+--------+--------------------------------------------------------------------------+

| Table             | Type | Count  | Schema                                                                   |

+-------------------+------+--------+--------------------------------------------------------------------------+

| body_master       | log  | 10     | name:string, code:string                                                 |

| brand_master      | log  | 149    |                                                                          |

| catalog           | log  | 84633  |                                                                          |

| color_master      | log  | 16     | name:string, code:string                                                 |

| country_master    | log  | 20     | name:string, code:string                                                 |

| large_area_master | log  | 9      | name:string, code:string                                                 |

| pref_master       | log  | 47     | name:string, large_area_name:string, code:string, large_area_code:string |

| usedcar           | log  | 311043 |                                                                          |

+-------------------+------+--------+--------------------------------------------------------------------------+

Schema はテーブル内のデータカラムの型を指定しているものです。Schema の話は次回で紹介します。

2. table コマンド

table コマンドは特定の db に存在するテーブルを確認,および操作するものです。

$ td help table                                                                                                                                 td table help

Additional commands, type "td help COMMAND" for more details:

 

  table:list [db]                            # Show list of tables

  table:show <db> <table>                    # Describe information of a table

  table:create <db> <table>                  # Create a table

  table:delete <db> <table>                  # Delete a table

  table:import <db> <table> <files...>       # Parse and import files to a table

  table:export <db> <table>                  # Dump logs in a table to the specified storage

  table:swap <db> <table1> <table2>          # Swap names of two tables

  table:tail <db> <table>                    # Get recently imported logs

  table:partial_delete <db> <table>          # Delete logs from the table within the specified time range

  table:expire <db> <table> <expire_days>    # Expire data in table after specified number of days        

ここでは以下のコマンドは別セクションに譲るものとします:

  • table:import → Data Import
  • table:export → Data Processing

他の代表的なコマンドを見ていきます:

$ td table:list carsensor                                                                                                                

+-----------+-------------------+------+---------+---------+---------------------------+---------------------------+

| Database  | Table             | Type | Count   | Size    | Last import               | Last log timestamp        |

+-----------+-------------------+------+---------+---------+---------------------------+---------------------------+

| carsensor | body_master       | log  | 10      | 0.0 GB  | 2014-01-22 17:41:17 +0900 | 2014-01-22 17:39:11 +0900 |

| carsensor | brand_master      | log  | 149     | 0.0 GB  |                           |                           |

| carsensor | catalog           | log  | 84,633  | 0.01 GB |                           |                           |

| carsensor | color_master      | log  | 16      | 0.0 GB  | 2014-01-22 17:40:35 +0900 | 2014-01-22 17:39:11 +0900 |

| carsensor | country_master    | log  | 20      | 0.0 GB  | 2014-01-22 17:42:57 +0900 | 2014-01-22 17:39:11 +0900 |

| carsensor | large_area_master | log  | 9       | 0.0 GB  | 2014-01-22 17:44:24 +0900 | 2014-01-22 17:39:11 +0900 |

| carsensor | pref_master       | log  | 47      | 0.0 GB  | 2014-01-22 17:47:16 +0900 | 2014-01-22 17:39:11 +0900 |

| carsensor | usedcar           | log  | 311,043 | 0.1 GB  |                           |                           |

+-----------+-------------------+------+---------+---------+---------------------------+---------------------------+

8 rows in set 

 

$ td table:show carsensor pref_master

Name      : carsensor.pref_master

Type      : log

Count     : 47

Schema    : (

    name:string

    large_area_name:string

    code:string

    large_area_code:string

)

table コマンドで頻繁に使用するのはそのテーブルに入っているデータの一部「眺める」tail コマンドです。

$ td table:tail carsensor usedcar  -n 1 -P

{

  "publication_date": 1390356579,

  "time": 1390356579,

  "shop_datum": "world",

  "recycle": "リ済別",

  "engine": "ガソリン",

  "warranty": "保証付",

  "code": "NI",

  "maintenance": "法定整備付",

  "body_code": "D",

  "name": "日産",

  "warranty_length_kikan": "1年",

  "warranty_distance": "無制限",

  "body_name": "ハッチバック",

  "shop_pref_code": 13,

  "year": 2004,

  "warranty_length": "保証期間:1年",

  "inspection": "車検整備付",

  "shop_pref_name": "東京都",

  "odd_km": 27000,

  "odd": "2.7万km",

  "price": 299000,

  "color": "ビーンズ",

  "model": "マーチ",

  "shop_lat": "35.684586",

  "shop_lng": "139.303458",

  "id": "CU2261202498",

  "grade": "1.2 12c Vセレクション",

  "url_mobile": "http://mob.carsensor.net/c/ld.cgi?EK=59&BK=CU2261202498&vos=csr145",

  "url_pc": "http://www.carsensor.net/usedcar/detail.php?STID=CS210840&BKKN=CU2261202498&vos=ncsrapis00001",

  "url_qr": "http://webservice.recruit.co.jp/common/qr?url=http%3A%2F%2Fmob.carsensor.net%2Fc%2Fld.cgi%3FEK%3D59%26BK%3DCU2261202498%26vos%3Dcsr146",

  "main_photo_s": "http://www.carsensor.net/CSphoto/bkkn/220/195/UD0011220195/UD0011220195_001S.JPG",

  "main_photo_l": "http://www.carsensor.net/CSphoto/bkkn/220/195/UD0011220195/UD0011220195_001L.JPG",

  "shop_name": "日産プリンス西東京販売 レッドステーション八王子楢原店",

  "maintenance_comment": "車検整備一式 エンジンオイル&オイルフィルター交換・ワイパーゴム交換",

  "warranty_comment": "ワイド保証 保証期間:1年(走行距離無制限)",

  "main_caption": "◆◇ご覧頂き有難う御座います。お車の『内容・状態・お支払・ご納車までの流れ』のご質問&不明点等、御座いましたら◆◇お問合せ先 042-625-2800◇◆にお気軽にご連絡下さい。◇◆",

  "desc": "人気のコンパクトハッチ かわいいカラーのビーンズです走行距離が魅力なお買い得なお車です"

}

注:-P は pretty print. -n は取得数です。このコマンドで見れるデータは(timeカラムの情報で)直近のデータを tailing してくるので,データのインポートが直近でない場合は何も返ってこない場合が有ります。その場合は --to または --from でスタートおよびエンドのタイムスタンプまたは"yyyy-mm-dd hh:mm:ss"形式を指定して下さい。

一般には直前の時間情報を取れないほうがほとんどですので,

  1.  table:import したテーブルは $td table:list で last log timestamp 項を確認する。
  2. bulk_import の場合は
$td query -w -d carsensor "SELECT time FROM usedcar LIMIT 10"

を実行する。

最後に,tail コマンドはシェルスクリプトを使用してクエリを叩くことなく簡易集計が可能です。例えば先頭1万件の usedcar テーブルの price 平均を取る場合,

$ td table:tail carsensor usedcar -n 100000 -P | grep "price" | cut -d: -f2 | cut -d, -f1 | awk '{x+=$1;n+=1;}END{print x/n}'

を実行します。他にも色々試して下さい。

 

今回はライトな内容でしたね。終わり。

実データで覚える Treasure Client コマンドラインリファンス 〜1.Data Import〜

本シリーズではTreasure Client ツールのコマンドラインリファレンスを以下の5つのレイヤーに分解し,各々について解説していくものとします。

No.レイヤーコマンド
1. Data Import

one-time import

bulk import

2. Data Management

db

table

3. Data Processing

schema

query

job

4. Data Scheduling

sched

result

5. Other

help

status

server

sample

本日は 1. Data Import を紹介します。本シリーズでは実際にデータを使用して,その分析の流れに沿いながらコマンドラインを紹介していきます。

Treasure Platform の登録および Client Tool のインストール

 それでは早速サインアップを始めましょう!こちらをクリックして下図のページより登録お願いします。

f:id:doryokujin:20140123091913p:plain

 

続いてクライアントツールをダウンロードします。

 

f:id:doryokujin:20140123092331p:plain

Source Data

カーセンサーnet Webサービス

 

リクルート様が公開してくれているカーセンサーの中古車相場データを使います。(データを提供して頂いたリクルート様,ありがとうございます!)

f:id:doryokujin:20140123092639p:plain

One-Time Import

-- one-time import --

 
$ td table:import ↵
usage:
$ td table:import <db> <table> <files...>
 
example:
$ td table:import example_db table1 --apache access.log
$ td table:import example_db table1 --json -t time - < test.json
 
options:
--format FORMAT file format (default: apache)
--apache same as --format apache; apache common log format
--syslog same as --format syslog; syslog
--msgpack same as --format msgpack; msgpack stream format
--json same as --format json; LF-separated json format
-t, --time-key COL_NAME time key name for json and msgpack format (e.g. 'created_at')

1. table:import <db> <table> <files...>

$ td table:create carsensor body_master

$ td table:create carsensor color_master

$ td table:create carsensor country_master

$ td table:create carsensor large_area_master

$ td table:create carsensor pref_master

 

$ td table:import carsensor body_master --format json --time-key time body_master.json

$ td table:import carsensor color_master --format json --time-key time color_master.json

$ td table:import carsensor country_master --format json --time-key time country_master.json

$ td table:import carsensor large_area_master --format json --time-key time large_area_master.json

$ td table:import carsensor pref_master --format json --time-key time pref_master.json

table:import コマンドは手軽にファイルを Treasure Cloud Storage へインポートできるコマンドです。状況に応じて bulk_import と使い分けてください。

  •  対応ファイルフォーマット: apache, syslog, json, msgpack, msgpack.gz

※ 最適なフォーマットは msgpack.gz です。ファイルサイズが小さいという意味で。apache, syslog 形式は特殊な使い方になります。なお,csv, tsv には対応していません。

※ csv, tsv フォーマットを手軽に msgpack.gz に変換したい場合は import:prepare コマンドを利用してください。

※ なお,オプションとして -t (--time-key) が必須です。これは任意のレコードに必ず UNIX_TIMESTAMP を持ったカラムが必要である事を意味しています。TD Storage ではこの time カラムの情報よりレコードを日付&&時間ごとにパーティションを行って管理しています。

[追記] オプションを付けると任意の型のTIMEフォーマットに対応しました。

2. まとめ

これで one-time import は手軽ですが,以下の点に注意が必要です。

  • 実行途中でに失敗した場合には途中からのリトライが不可能。再度同じコマンドを利用するとレコードの重複が生じる。
  • 最適な形式は圧縮された msgpack.gz 形式。msgpack ファイルがある場合は gzip コマンドによって事前圧縮しておく。
  • json -> msgpack.gz も変換が用意なのでできるだけ事前変換しておく。
  • msgpack ファイルの内容確認は,Rubyワンライナーで可能:

$ ruby -rmsgpack -e "File.open('input.msgpack') {|f| MessagePack::Unpacker.new(f).each {|r| p r } }"

Bulk Import

始めに Bulk Import の基本概念を紹介します。

f:id:doryokujin:20140122182616p:plain

  • Prepare: 元データをアップロードに最適なファイルサイズに圧縮します。この操作はローカルで行われ,出力形式は gzipped MessagePack format です。
  • Upload: 圧縮されたデータは Treasure Data’s (row-based) bulk upload storage system に並列アップロードされます。このアップロードはセキュアなインターネットコネクションを用いています。
  • Perform: アップロードされたデータは Treasure Data の列指向データベース(Treasure Cloud Storage)に MapReduce を用いて保存されます。
  • Commit: Perform ステップの後,データは Plazma  (Treasure Data’s columnar, distributed storage system)  互換の形式に変換されています。最後に Commit コマンドによって Plazma データベースにコピーされます。
-- bulk import -- 
$ td import                                                                                                                                        
  import:list                                               # List bulk import sessions
  import:show <name>                               # Show list of uploaded parts
  import:create <name> <db> <table>       # Create a new bulk import session to the the table
  import:java_version                                # Show version
  import:prepare <files...>                        # Convert files into part file format
  import:upload <name> <files...>            # Upload or re-upload files into a bulk import session
  import:auto <name> <files...>               # Upload files and automatically perform and commit
  import:perform <name>                         # Start to validate and convert uploaded files
  import:error_records <name>                # Show records which did not pass validations
  import:commit <name>                         # Start to commit a performed bulk import session
  import:delete <name>                           # Delete a bulk import session
  import:freeze <name>                           # Reject succeeding uploadings to a bulk import session
  import:unfreeze <name>                       # Unfreeze a frozen bulk import session

1. import:create <name> <db> <table> 

bulk_import を行うための「セッション」を作成します。$ td table:create とは異なり,「テーブル」を作成するのではありませんので混同しないようにしてください。

なお,bulk_import でインポートするための<db>,<table>は既に作成されている必要があります。テーブルに既にレコードが入っている場合は bulk_import によって append されます。

$ td db:create carsensor

# for catalog_measure table

$ td table:create carsensor catalog

$ td import:create session_catalog carsensor catalog 

# for usedcar_measure table

$ td table:create carsensor usedcar

$ td import:create session_usedcar carsensor usedcar

# for brand_master table

$ td table:create carsensor brand_master

$ td import:create session_brand_master carsensor brand_master

上段の例では catalog_measure.csv ファイルに関して,セッションを "session_catalog" 名で作成し,Import 先となる db名: "carsensor" と table名: "catalog" を指定しています。(以後は catalog_measure.csv ファイルの Bulk Import のみを進めて行きます。中段の usedcar_measure.csv,下段の brand_master ファイルも同様の処理になります。)

2. import:prepare <files...>

このコマンドは bulk_import でデータをアップロードする前の前処理です。このコマンドによってインプットファイルサイズを bulk_import に最適なフォーマット( msgpack.gz )および最適なファイルサイズ( 約17MB )に分割されます。分割された msgpack.gz ファイルは -o オプションに指定されたディレクトリに保存されていきます。

  • 対応インプットフォーマット: csv, tsv, json, msgpack, msgpack.gz

今回は csv ファイルを扱います。

[追記] まずはディレクトリ内に "parts"ディレクトリを作成しておいて下さい。prepare で分割されたファイルはここに入ります。また,元々あった "parts_result" は既に prepare を実行したファイルが入っていますので,このステップを飛ばしたい人は "parts" に名前を変更して次にお進み下さい。

# for catalog_measure table

$ td import:prepare catalog_measure.csv --format csv --column-header --time-column 'publication_date' -o ./parts/

  

Prepare status:

  Source    : catalog_measure.csv

    Status          : SUCCESS

    Read lines      : 84635

    Valid rows      : 84633

    Invalid rows    : 1

    Converted Files : ./parts/catalog_measure_csv_0.msgpack.gz (1505281 bytes

 

Next steps:

  => execute following 'td import:upload' command. if the bulk import session is not created yet, please create it with 'td import:create <session> <database> <table>' command.

     $ td import:upload <session> './parts/catalog_measure_csv_0.msgpack.gz'

 

# for usedcar_measure table => parts_result フォルダに usedcar_measure_n.msgpack.gz のアウトプットを置いてある。

 

# for catalog_measure table

$ td import:prepare brand_master.csv --format csv --column-header --time-column 'time' -o ./parts/

※ --columns には "column1, colmun2,..." とヘッダーカラム名を羅列します。もしcsvの一行目がヘッダーになっている場合は, --columns の代わりに --column-header オプションを指定してやります。

※ --time-column には UNIX TIMESTAMP となっているカラム名を指定します。TD Storage へのインポートの際,全てのレコードには unix timestamp を持ったカラムが必須となります。なぜならこの unix timestamp を日付かる各時間毎にパーティションを行っているからです。今回のケースでは擬似的に「掲載日」:publication_date を追加し,それを指定しています。

3. td import:upload <name> <files...>

次に分割圧縮したパーツファイルを Treasure Cloud Strage へ並列アップロードします。

$ td import:upload session_catalog './parts/catalog_measure_csv_0.msgpack.gz'

上記のコマンド中にコマンドが強制終了,およびネットワークが切断されて中断された場合にも,再度同じコマンドを実行し直すことでデータの重複無くアップロードを再開してくれます。これが table:import には無い大きなメリットとなっています。

なお,一般的にアップロードファイルは複数有ります。これらを並列に upload することも可能になっています。(デフォルトの並列数は「2」となっています)以下の例では 8並列でアップロードを行ってくれます。

す。

$ td import:upload --parallel 8 <name> ./parts/*

また,連続して他の分割ファイルもその後追加アップロードしていくことが可能です。

$ td import:upload <name> ./parts1/*

$ td import:upload <name> ./parts2/*

$ td import:upload <name> ./parts3/*

アップロードされたファイルを確認するには,

$ td import:show <name>

を実行します。

4. td import:perform <name> 

1. $ td import:freeze session_catalog

2. $ td import:perform session_catalog

Job 7216190 is queued.

 

$ td job:show -w 7216190

JobID       : 7216190

Status      : success

Type        : bulk_import_perform

Priority    : NORMAL

Retry limit : 0

Result      :

Database    : carsensor

Query       :

3. $  td import:list

+--------------------+-----------------------+---------------+----------+----------+

| Name               | Table                   | Status        | Frozen | JobID   |

+--------------------+-----------------------+---------------+----------+----------+

| session_catalog | carsensor.catalog | Ready         | Frozen | 7216190 |

+--------------------+-----------------------+---------------+----------+----------+

 

4. $  td import:error_records session_catalog

(No Output)

  1. まず freeze コマンドでセッションに「蓋」をします(追加アップロードできないようにします,ただし unfreeze コマンドで追加可能に戻せます)。
  2. 日付&&時間をキーにレコードを MapReduce します。MapReduce が実行されますのでレコード数に応じてそれなりに時間がかかります。-w は wait オプションで実行完了まで経過を表示しつづけるオプションです。
  3. perform 完了後の list コマンドの表示結果では,perform の実行結果が表示され,Status:Ready となっています。テーブルへのインポート完了まで後一歩です。
  4. なお,ここで ErrorRecords(perform 中に問題のあったレコード)に関する情報を確認しておく必要があります。

5. td import:commit <name> 

$ td import:commit <name>

Bulk import session 'session_catalog' started to commit.

commit コマンドによって実際のテーブルへとインポートが行われ,一連の import 処理が完了になります。なお,このコマンドで指定する<name>は「セッション」名であり,書き込まれる「テーブル」は 1. の import:create <name> <db> <table> で指定したテーブルになることに注意してください。

6. まとめ

$ td import:list

+--------------------+-----------------------+---------------+----------+----------+

| Name               | Table                   | Status        | Frozen | JobID   |

+--------------------+-----------------------+---------------+----------+----------+

| session_catalog | carsensor.catalog | Committed | Frozen | 7216190 |

+--------------------+-----------------------+---------------+----------+----------+

 

$ td table:tail carsensor catalog -P

{

  "publication_date": 1390356579,

  "time": 1390356579,

  "person": 7,

  "body_code": "M",

  "code": "TO",

  "body_name": "ミニバン",

  "width": 1650,

  "name": "トヨタ",

  "height": 1935,

  "length": 4070,

  "price": 2285000,

  "period": "198808-198908",

  "desc": "※このクルマの燃費は発売当時の資料により、10モードの数値を表示しております。燃費=9.2km/l",

  "model": "ライトエース",

  "series": "E-YM40G",

  "grade": "2.0 FXV 4WD"

}...

これで bulk_import は完了です。多少ステップ数はあるものの,以下の点で table:import コマンドより優れています:

  • upload コマンドによって自動的にインポート対象ファイルを元ファイルより小さな msgpack.gz という圧縮形式に変換してくれ,ネットワーク負担を下げてくれる。
  • アップロードが途中で失敗しても重複することなく途中からリトライできる。
  • 並列アップロードが可能。
  • 作成したセッションを残しておけば,同じファイルを別名のテーブルへ再アップロードなしに commit,つまりテーブル再インポートが可能。

ただし,以下の点に注意してください:

  •  prepare コマンドはシングルスレッドなので遅い。[修正] オプションによって並列処理が可能です。(デフォルトは並列数2です。) 複数ファイルを対象にする場合は, $ ls <files*> | parallel td import:prepare {} などで並列実行する。
  • [追加] prepare コマンドと upload コマンドを同時に事項できるコマンドが import:upload コマンドです。これによると新しい分割ファイルが作成されると同時にそのファイルをアップロードしてくれます。
  • インポート対象ファイルに前処理(不要レコード削除・カラム追加・集計カラム追加)を行う場合は自前で前処理コマンド → msgpack.gz への変換スクリプトを作成する必要がある。

Treasure Data Platform で始めるデータ分析入門 〜5. Data Processing〜

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「4. Data Processing」にフォーカスを当てます。

 

f:id:doryokujin:20140115160517p:plain

 "Data Processing"と聞いて,多くの人は具体的な集計クエリの内容に言及するものだと期待されていたかもしれませんが,本章ではその内容は次回に譲り,もっと大枠の Processing から Visualization に繋げるためのアーキテクチャについて考えて行きたいと思います。

 

f:id:doryokujin:20140116121235p:plain

Hadoop ファミリーを初めとした様々なオープンソースまたは MapR などの製品によって構築される Big Data Infrastructure は上図のように非常に複雑であり,相応のスキルおよび人材リソースが必要になります。

 

f:id:doryokujin:20140115160552p:plain

上図は先ほどと少し切り口を変え, Hadoop ファミリーの Batch Aggregator 達(MapReduce, Hive, Pig)または Ad-hoc Aggregator(Impala) を用いた集計プロセスを経て Business Goal(Visualization やレポーティング)に持って行くためのフローとなっています。

Batch Aggregator は大規模で無数のカラムを持った複雑なローデータに対するバッチ集計なので,大がかりで時間がかかるものになってしまいます。

一方で Business Goal フェーズに求められるのは,インタラクティブなデータ操作であり,前述の大規模集計では時間がかかりすぎて(分単位以上)そのニーズを充足させる事はできません。

よってこれらを結びつけるためには Intermediate Data(中間データ)を挟ませないといけません。バッチ処理によってある程度スマートになったデータをデータベースやメモリに保存しておき,Dashboad や BI からはその中間データを参照することでインタラクティブな操作が可能になるのです。

近年登場した Impala は特定の条件の下でこのバッチ処理を数秒単位で終えてしまうことで,中間データをすっとばして直接 Dashboard や BI と結びつくことを可能にしました。つまり巨大で複雑なローデータに対してUI側からインタラクティブな操作を行う事ができるようになったのです。

 

f:id:doryokujin:20140116115724p:plain

しかしながら,ケースによって Hive, Pig と Impala を使い分けるためには,各々の運用・管理が必要であり,これは骨の折れるところでありました。Treasure Data Platform では,処理の実行時にこの Batch 処理と Ad-hoc 処理を簡単に切り替えることができるメリットがあります。

 

f:id:doryokujin:20140115161439p:plain

f:id:doryokujin:20140115161458p:plain

f:id:doryokujin:20140115161532p:plain前回紹介した Management Console から各種クエリの実行が可能です。データベース名を選択し,どの処理(Hive, Pig, Impala)を行うかを選択し,クエリを記述してSubmitボタンを押すだけで処理が走ります。

 

f:id:doryokujin:20140115161050p:plain

Treasure Data の提供するアドホック型のクエリエンジンは最近のアップデートによって実装されました。その性能はバッチ型に比べて10〜50倍の高速化を可能にします。

 

f:id:doryokujin:20140115161258p:plain

各々の Business Goal におけるバッチ型とアドホック型の使い分けは上の表のようになります。

 

f:id:doryokujin:20140115161318p:plain

 

また, 各業界におけるバッチ型とアドホック型の使い分けは上の表のようになります。

 

f:id:doryokujin:20140117120728p:plain

最後に改めて Treasure Data Processing のまとめた図になります。

 

次回は数回にわたって Data Processing Design を紹介します。サンプルデータを用いながら Data Process の基本パターンを理解しましよう。