はてなカウンターのアクセスログ(CSV)をHiveテーブルに投入する

Hive勉強していて、お手軽なサンプルデータがないかなーと探していたら自分のサイトのアクセスログが丁度いい感じに集まっていたのでそれをHiveに入れてみるまでのTips。


#はてなカウンターとは

たぶんGoogleアナリティクスがあるのでもうオワコンだとは思いますが、はてなが用意しているアクセスカウンターサービスです、はてなだけじゃなくていろんなサイトに埋め込みできます。

http://counter.hatena.ne.jp/

はてなのブログやダイアリーを使ってる方なら自動的に使っているはず。
過去にはてなのサービスを使っていた方ならまだカウンターが回ってるはずで割りと有効活用できそうなアクセスログです。

![スクリーンショット 2015-08-02 18.42.03.png](https://qiita-image-store.s3.amazonaws.com/0/36931/73d8dc3a-6835-271c-8491-001c5197b0ca.png)


#はてなカウンターのログ構成

はてなカウンターはCSV形式でログをダウンロードできます。
カンマ区切りファイルでカラム構成は以下です。

|項目|サンプル|
|----|-------|
|アクセス日時|2015-08-01 00:01:06|
|アクセス元IP|255.255.255.255|
|リファラ|http://search.smt.docomo.ne.jp/result?search_box=%E3|
|ユーザーエージェント|Mozilla/5.0 (Linux; Android 4.2.2; SH-08E Build/S8210)  AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.93 Safari/537.36|
|ロケーション|ja,en-US;q=0.8,en;q=0.6|
|ブラウザ画面サイズ|600x960|
|たぶんブラウザの画面ビット数|32|
|アクセスURL|http://d.hatena.ne.jp/hogehoge/touch/20140329|


# Hive インストール

今回はOSXのHome brewを使います。

``brew install hive``

# Hive シェル起動

コンソールで

``hive``

を打つとローカルモード（Hadoopクラスタを使わないスタンドアロンモード）でHiveが起動します。

# はてな用データーベースを作成

基本的にはSQLと同じです

```sql
hive>
create databases hatena;
use hatena;
```

# CSVをパースするためcsv-serdeをダウンロード

HiveはそのままではCSVをまともにパースできないのでちゃんとしたCSVパーサーをダウンロードします。
（※カンマ区切りだけならHiveデフォルトで可能だが、値にカンマとかエスケープなどが入るとパースできない）

https://cwiki.apache.org/confluence/display/Hive/CSV+Serde
https://github.com/ogrodnek/csv-serde

githubのリンクから
csv-serde-1.1.2-0.11.0-all.jar
をダウンロードする。

## アドオン追加


```
hive>add jar /Users/nico/Downloads/csv-serde-1.1.2-0.11.0-all.jar
```


## DDL投入

CSVファイル構造に合わせて以下のDDLを投入


```sql
CREATE TABLE hatena_counter_access_log_2015_07 (
  date STRING,
  ip  STRING,
  ref_url STRING,
  ua STRING ,
  loc STRING,
  screen STRING,
  screen_bit STRING,
  url STRING
)
ROW FORMAT SERDE 'com.bizo.hive.serde.csv.CSVSerde'
 with serdeproperties (
   "separatorChar" = ",",
   "quoteChar"     = '"',
   "escapeChar"    = "\\"
  )   
STORED AS TEXTFILE;
```

構文はそんなに難しくないですが**ROW FORMAT SERDE**でファイルフォーマットを定義します。



#CSVファイルのデータをテーブルに投入

```
LOAD DATA INPATH "/Users/nico/Downloads/001-2015-07.csv" into table hatena_counter_access_log_2015_07;
```

log
```
Loading data to table hatena.hatena_counter_access_log_2015_07
Table hatena.hatena_counter_access_log_2015_07 stats: [numFiles=1, numRows=0, totalSize=14516646, rawDataSize=0]
OK
Time taken: 0.588 seconds
```

投入に成功すると該当ファイルは削除されるので注意


#データ確認

## count


```
hive> select count(*) from hatena.hatena_counter_access_log_2015_07;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
15/08/02 19:05:39 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Execution log at: /var/folders/v4/rl_pwfrd0hg8j2_49by71fhw0000gn/T//Siori/Siori_20150802190505_59c93b51-c463-452b-a349-993fdd3ac2dd.log
Job running in-process (local Hadoop)
Hadoop job information for null: number of mappers: 0; number of reducers: 0
2015-08-02 19:05:42,796 null map = 0%,  reduce = 0%
2015-08-02 19:05:43,864 null map = 100%,  reduce = 100%
Ended Job = job_local561875456_0001
Execution completed successfully
MapredLocal task succeeded
OK
52861
Time taken: 9.449 seconds, Fetched: 1 row(s)
```



## select 

```
hive> select * from hatena.hatena_counter_access_log_2015_07 limit 1;
OK
2015-08-01 00:01:06	123.13.112.122	http://search.smt.docomo.ne.jp/result?search_box=%E3%Mozilla/5.0 (Linux; Android 4.2.2; SH-08E Build/S8210) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.93 Safari/537.36	ja,en-US;q=0.8,en;q=0.6	600x960	32	http://d.hatena.ne.jp/cdcdcdcdcd/touch/20141111
Time taken: 0.024 seconds, Fetched: 1 row(s)
```

# TEXTFILEからRCFileに変換する

## DDL

```sql
CREATE TABLE hatena_counter_access_log_2015_07_rc (
  access_date TIMESTAMP,
  ip  STRING,
  ref_url STRING,
  ua STRING ,
  loc STRING,
  screen STRING,
  screen_bit SMALLINT,
  url STRING
)
stored as rcfile;
```

## 変換(Insert override)

```
INSERT OVERWRITE TABLE hatena_counter_access_log_2015_07_rc select date,ip,ref_url,ua,loc,screen,screen_bit,url from hatena_counter_access_log_2015_07;
```


## RCFile count
```
hive> select count(*) from hatena_counter_access_log_2015_07_rc;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
15/08/02 19:31:55 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Execution log at: /var/folders/v4/rl_pwfrd0hg8j2_49by71fhw0000gn/T//Siori/Siori_20150802193131_1a1f9f6e-da5f-454d-a4f2-2e8de498b620.log
Job running in-process (local Hadoop)
Hadoop job information for null: number of mappers: 0; number of reducers: 0
2015-08-02 19:31:58,495 null map = 100%,  reduce = 100%
Ended Job = job_local533189434_0001
Execution completed successfully
MapredLocal task succeeded
OK
52861
Time taken: 8.329 seconds, Fetched: 1 row(s)
```

## RCFile select 

```
hive> select * from hatena_counter_access_log_2015_07_rc limit 1;
OK
2015-07-01 00:01:06	111.111.11.11	http://search.smt.docomo.ne.jp/result?search_box=%E3%	Mozilla/5.0 (Linux; Android 4.2.2; SH-08E Build/S8210) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.93 Safari/537.36	ja,en-US;q=0.8,en;q=0.6	600x960	32	http://d.hatena.ne.jp/hogehoge/touch/20140329
Time taken: 0.027 seconds, Fetched: 1 row(s)
```

## RCFile desc formatted

```
hive> desc formatted hatena_counter_access_log_2015_07_rc;
OK
# col_name            	data_type           comment

access_date         timestamp
ip                  	string
ref_url             string
ua                  	string
loc                 string
screen              	string
screen_bit          	smallint
url                 string

# Detailed Table Information
Database:           hatena
Owner:              	Siori
CreateTime:         Sun Aug 02 19:22:06 JST 2015
LastAccessTime:     UNKNOWN
Protect Mode:       None
Retention:          	0
Location:           file:/user/hive/warehouse/hatena.db/hatena_counter_access_log_2015_07_rc
Table Type:         MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE	true
numFiles            	1
numRows             52861
rawDataSize         13176720
totalSize           13470197
transient_lastDdlTime	1438511461

# Storage Information
SerDe Library:      	org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe
InputFormat:        	org.apache.hadoop.hive.ql.io.RCFileInputFormat
OutputFormat:       org.apache.hadoop.hive.ql.io.RCFileOutputFormat
Compressed:         No
Num Buckets:        	-1
Bucket Columns:     []
Sort Columns:       []
Storage Desc Params:
serialization.format	1
Time taken: 0.058 seconds, Fetched: 38 row(s)
```

## RCFile where timestamp > ?

```
hive> select count(*) from hatena_counter_access_log_2015_07_rc where access_date > '2015-07-25';
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
15/08/02 19:52:11 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Execution log at: /var/folders/v4/rl_pwfrd0hg8j2_49by71fhw0000gn/T//Siori/Siori_20150802195252_68269622-d3d5-4036-9151-f22942f86e48.log
Job running in-process (local Hadoop)
Hadoop job information for null: number of mappers: 0; number of reducers: 0
2015-08-02 19:52:14,592 null map = 0%,  reduce = 0%
2015-08-02 19:52:15,781 null map = 100%,  reduce = 100%
Ended Job = job_local451369236_0001
Execution completed successfully
MapredLocal task succeeded
OK
22872
Time taken: 9.444 seconds, Fetched: 1 row(s)
```

大丈夫そうです。
これではてなカウンターのログからいろいろ解析できるようになりました。