Hiveでのデータ集計の流れ（テーブル定義、データロード、集計）に沿って、HQLサンプル　（Azure HDInsight）

今回は、実際のデータ処理の流れに沿って、HiveQLのサンプルをまとめたいと思います。

前提

環境　Azure HDInsight　Windows Hadoopクラスター Version 3.2

対象ログ　accesslogは、テキストファイル形式で、サーバー毎に、毎日１個。
データ項目29個、スペース区切り

処理の流れ
accesslog（テキストログ）
↓
accesslog_sum（サンプル的な集計、日毎のログ件数）

HiveQL

◆テーブル定義

CREATE TABLE accesslog (
 t1 string, t2 string, t3 string, t4 string, t5 string, t6 string,
 t7 string, t8 string, t9 string, t10 string, t11 string, t12 string,
 t13 string, t14 string, t15 string, t16 string, t17 string, t18 string,
 t19 string, t20 string, t21 string, t22 string, t23 string, t24 string,
 t25 string, t26 string, t27 string, t28 string, t29 string)
 PARTITIONED BY(sv STRING, yyyymmdd String, log_num STRING)
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ' '
 LINES TERMINATED BY '\n'
 STORED AS TEXTFILE;

◆テーブル定義の確認

DESCRIBE EXTENDED accesslog;

◆テーブルへのデータロード

LOAD DATA INPATH 'wasb:///mydata/sv01/20150401_access.log0000' OVERWRITE INTO TABLE accesslog PARTITION (sv='sv01',yyyymmdd='20150701',log_num='0');
LOAD DATA INPATH 'wasb:///mydata/sv01/20150401_access.log0001' OVERWRITE INTO TABLE accesslog PARTITION (sv='sv01',yyyymmdd='20150401',log_num='1');
LOAD DATA INPATH 'wasb:///mydata/sv02/20150401_access.log0000' OVERWRITE INTO TABLE accesslog PARTITION (sv='sv02',yyyymmdd='20150701',log_num='0');
LOAD DATA INPATH 'wasb:///mydata/sv02/20150401_access.log0001' OVERWRITE INTO TABLE accesslog PARTITION (sv='sv02',yyyymmdd='20150401',log_num='1');

◆実際のAzureストレージ・コンテナーに格納されたときのパス

/hive/warehouse/accesslog/sv=sv01/yyyymmdd=20150401/log_num=0/20150401_access.log0000
/hive/warehouse/accesslog/sv=sv01/yyyymmdd=20150401/log_num=1/20150401_access.log0000
/hive/warehouse/accesslog/sv=sv02/yyyymmdd=20150401/log_num=0/20150401_access.log0000
/hive/warehouse/accesslog/sv=sv02/yyyymmdd=20150401/log_num=1/20150401_access.log0000

◆レコード件数確認表の作成

CREATE TABLE access_sum (
 sv String,
 yyyymmdd String,
 log_num String,
 count String)
 STORED AS ORC;

◆レコード件数の確認表を更新

INSERT OVERWRITE TABLE acceesslog_sum
 SELECT sv, yyyymmdd, log_num, count(*) as count
 FROM accesslog
 GROUP BY sv, yyyymmdd, log_num;

その他、補助的なHiveQL

◆実行プランの確認

explain select * from <table_name>

◆テーブル定義で、1行目（ヘッダー行）の読み飛ばし

 TBLPROPERTIES("skip.header.line.count"="1");

※DDL定義中で使う　（使い方）

参考資料

TD社 HQLガイド（いつも、ありがとうございます）
http://docs.treasuredata.com/categories/hive

Aman Singh さんのメモ
https://sites.google.com/site/hadoopandhive/system/app/pages/subPages?path=/home

Hiveパーティショニング紹介記事（MS）
https://azure.microsoft.com/ja-jp/documentation/articles/machine-learning-data-science-move-hive-tables/