LoginSignup
2
3

More than 5 years have passed since last update.

@AKB428

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

Last updated at Posted at 2015-08-02

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

はてなカウンターとは

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

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

スクリーンショット 2015-08-02 18.42.03.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と同じです

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を投入

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

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)

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

2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3