hive 分析apache日志

1.安装mysql&hive

yum install mysql-server

service mysqld start

mysql -u root -p

create database hive;

mysql> grant all on hive.* to hive@'%' identified by 'hive';

CREATE USER 'hive'@'hive-mysql' IDENTIFIED BY 'hivepasswd';

GRANT ALL PRIVILEGES ON *.* TO 'hive'@'hive-mysql' WITH GRANT OPTION;

tar zcvf hive-0.12.0.tar.gz hive-0.12.0

cp hive-default.xml.template hive-site.xml
cp hive-env.sh.template hive-env.sh
cp hive-log4j.properties.template hive-log4j.properties
cp hive-exec-log4j.properties.template hive-exec-log4j.properties

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>


<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>


 <property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>

 <property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>welcome123</value>
<description>password to use against metastore database</description>
</property>


 <property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
<description>
Enforce metastore schema version consistency.
True: Verify that version information stored in metastore matches with one from Hive jars. Also disable automatic
schema migration attempt. Users are required to manully migrate schema after Hive upgrade which ensures
proper metastore schema migration. (Default)
False: Warn if the version information stored in metastore doesn't match with one from in Hive jars.
</description>
</property>

hive-env.sh


 # Set HADOOP_HOME to point to a specific hadoop install directory
HADOOP_HOME=/home/hadoop/hadoop-2.2.0
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/home/hadoop/hive-0.12.0/conf

 

cp mysql-connector-java-5.1.26-bin.jar to hive/lib

2.导入apache日志

gunzip x.benguo.cn-access_log.4.gz
./hive
CREATE TABLE serde_regex(
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH "/opt/hadoop/hive/data/x.benguo.cn-access_log.4" INTO TABLE serde_regex;

查找某一天的请求总数:

hive> select * from serde_regex limit 1;
hive> select * from serde_regex where time like '%2013\:11\:16%' limit 1;
hive> select count(1) from serde_regex where time like '%2013\:11\:16%';