Hive
and Hadoop for Data Analytics on Apache Log
In
this Apache Hive tutorial, we explain how to use Hive in a Hadoop distributed
processing environment to enable Web analytics on large datasets. The Web
analytics process will involve analyzing weblog details such as URLs accessed,
cookies, access dates with times, and IP addresses. This information will be
used to analyze visitors' website usage as well as their browsing patterns and
behavior. Armed with this information, site owners can predict what a
particular user likes on the site and personalize it accordingly. For their
part, developers can add extra tracking values in the weblog for additional
analytics.
Apache Hadoop and
Hive for Data Processing
Apache
Hadoop, the open source distributed computing framework for handling large
datasets, uses the HDFS file system for storing files and Map/Reduce model for
processing large datasets. Apache Hive, a sub-project of Hadoop, is a data
warehouse infrastructure used to query and analyze large datasets stored in
Hadoop files. Although Hadoop Hive is a non-SQL database, it will support some
SQL as well. With its Hive-SQL option, Hive users can query the Hive tables.
Hive works on top of Hadoop and ZooKeeper, a centralized Hadoop service for
maintaining configuration information, naming, providing distributed
synchronization, and providing group services.
Weblogs
can be processed by the Hadoop Map Reduce program and stored in HDFS.
Meanwhile, Hive supports fast reading of the data in the HDFS location, basic
SQL, joins, and batch data load to the Hive database.
Weblog Formats and
Processing
Both
Apache Web Server and Microsoft IIS record website requests into log files, but
the formats of those logs differ. Apache's preferred weblog format is the combined
log format, which logs all the details of Web usages. Here is an example of
the combined weblog format.
LogFormat "%h %v %u
%t \"%r\" %>s %b \"%{Referer}i\"
\"%{User-Agent}i\" \"%{Cookie}i\""
Here
is the IIS preferred format, Microsoft IIS W3C Extended Log File Format:
c-ip cs-username date
time sc-bytes sc-status cs-uri-stem cs[Referer] cs[User-Agent] cs[Cookie]
Large
retail applications are accessed by many users around the world, so their
weblog file sizes might be between 10 and 15 gigabytes. For example,
Amazon.com's weblog is more than 15 gigabytes. This weblog information is used
to predict customer interest and personalize the site.
Page
tagging is used for tagging the Web page and tracking usage of the page. Web
analytics providers such as WebTrends and Google Analytics use this option for
tracking page views and storing this view information in log files.
Hadoop MapReduce for
Parsing Weblogs
Below
is a sample Apache log where the log fields are terminated by a space. The OpenCSV
framework is used for parsing these logs. It is using field
terminated characters to parse the log and split the fields.
127.0.0.1 - -
[10/Apr/2007:10:39:11 +0300] "GET / HTTP/1.1" 500 606 "-"
"Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.3) Gecko/20061201
Firefox/2.0.0.3 (Ubuntu-feisty)"
Here
are the steps for parsing a log file using Hadoop MapReduce:
1. Load log files into
the HDFS location using this Hadoop command:
2. hadoop fs -put <local
file path of weblogs> <hadoop HDFS
location>
3. The Opencsv2.3.jar
framework is used for parsing log records.
4.
Below
is the Mapper program for parsing the log file from the HDFS location.
public static class ParseMapper extends Mapper<Object, Text,
NullWritable,Text >{
private Text word = new
Text();
public void map(Object
key, Text value, Context context) throws IOException, InterruptedException
{
CSVParser parse = new CSVParser('
','\"');
String sp[]=parse.parseLine(value.toString());
int spSize=sp.length;
StringBuffer rec= new StringBuffer();
for(int i=0;i<spSize;i++){
rec.append(sp[i]);
if(i!=(spSize-1))
rec.append(",");
}
word.set(rec.toString());
context.write(NullWritable.get(), word);
}
}
5.
The
command below is the Hadoop-based log parse execution. The MapReduce program is
attached in this article. You can add extra parsing methods in the class. Be
sure to create a new JAR with any change and move it to the Hadoop distributed
job tracker system.
6. hadoop jar <path of
logparse jar> <hadoop HDFS logfile path> <output path of parsed log file>
7.
The
output file is stored in the HDFS location, and the output file name starts
with "part-".
Hadoop Hive Configuration
The
sections to follow explain how to configure Hive for weblog analytics.
Requirements
· Java 1.6
· Hadoop 0.20.x.
Installing Hadoop Hive from a Stable
Release
First,
download the latest stable release of Hive from one of the Apache download
mirrors.
Next,
unpack the tarball, which will create a subdirectory named hive-x.y.z:
$ tar -xzvf hive-x.y.z.tar.gz
Point
the environment variable HIVE_HOME to the installation
directory:
$ cd hive-x.y.z
$ export
HIVE_HOME={{pwd}}
Finally,
add $HIVE_HOME/bin to your PATH:
$ export
PATH=$HIVE_HOME/bin:$PATH
Running Hadoop Hive
Because
Hive uses Hadoop either:
· you must have Hadoop
in your path, OR
· export HADOOP_HOME=<hadoop-install-dir>
In
addition, you must create /tmp
and /user/hive/warehouse (aka hive.metastore.warehouse.dir)
and set them chmod
g+w
in HDFS before a table can be created in Hive.
Commands
to perform this setup are as follows:
$ $HADOOP_HOME/bin/hadoop fs -mkdir /tmp
$ $HADOOP_HOME/bin/hadoop fs -mkdir /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse
I
also find it useful, but not necessary, to set HIVE_HOME as follows:
$ export
HIVE_HOME=<hive-install-dir>
To
use the Hive command line interface (CLI) from the shell:
$ $HIVE_HOME/bin/hive
Hive Runtime Configuration
Hive queries are executed using MapReduce queries.
Therefore, the behavior of such queries can be controlled by the Hadoop
configuration variables.
- The CLI command SET can be used to
set any Hadoop (or Hive) configuration variable. For example:
- hive> SET
mapred.job.tracker=myhost.mycompany.com:50030;
·
hive>
SET -v;
Hive, MapReduce and Local-Mode
The
Hive compiler generates MapReduce jobs for most queries. These jobs are then
submitted to the Map-Reduce cluster indicated by this variable:
mapred.job.tracker
This
usually points to a MapReduce cluster with multiple nodes, but Hadoop also
provides an option to run MapReduce jobs locally on the user's PC. This can be very useful for running queries over
small data sets because in such cases local mode execution is usually much
faster than submitting jobs to a large cluster. Data is accessed transparently
from HDFS. Conversely, local mode runs with only one reducer and can be very
slow when processing larger data sets.
Starting
with version 0.7, Hive fully supports local mode execution, which you can
activate by enabling the following option:
Hive> SET
mapred.job.tracker=local;
In
addition, mapred.local.dir should point to a path that's valid on the
local machine (for example /tmp/<:username>/mapred/local). (Otherwise, the
user will get an exception allocating local disk space).
Starting
with version 0.7, Hive also supports a mode to run MapReduce jobs in local-mode
automatically. The relevant options are:
Hive> SET hive.exec.mode.local.auto=false;
Hadoop Hive Data Load
Hive
provides tools to enable easy data ETL, a mechanism to put structures on the
data, and defines a simple SQL-like query language, called QL, that enables
users familiar with SQL to query the data. At the same time, Hive QL also
allows programmers familiar with MapReduce to plug in their custom mappers and
reducers to perform more sophisticated analysis that may not be supported by
the built-in capabilities of the language.
Below
is the create table command for Hive.
create table weblogs
(
client_ip string,
full_request_date string,
day string,
month string,
month_num int,
year string,
hour string,
minute string,
second string,
timezone string,
http_verb string,
uri string,
http_status_code string,
bytes_returned string,
referrer string,
user_agent string
)
row format delimited
fields terminated by '\t' stored as textfile
The
below command is used for loading data from an HDFS location to a Hive table.
LOAD DATA INPATH
'<HDFS file path of parsed file>' INTO TABLE <table name>
After
loading the data into the table, the normal user can query Hive using Hive QL.
Below is an example query for getting user counts of each location.
SELECT client_ip , COUNT(client_ip) FROM weblogs GROUP BY
client_ip
Hadoop Hive JDBC Support
Hive
also supports JDBC connections. To connect Hive with JDBC, you need to start
the Hive Thrift Server as follows.
Export HIVE_PORT=9999
Hive –service hiveserver
Here
are the steps to establish a Hive JDBC connection:
1.
Add
hive-jdbc0.7.jar in the classpath; this is a type-4 driver.
2.
Use
the org.apache.hadoop.hive.jdbc.HiveDriver driver for the connection.
3.
Connection
String : jdbc:hive://<hive
IP Address>:<hive port>/<database name>
4.
Use
Hive QL to query the table in Hive, and it will return the result set.
5.
Using
the result set, you can project the output in graphs or charts easily.
Here
is a sample Hive JDBC program:
Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
Connection con =
DriverManager.getConnection("jdbc:hive://10.0.0.1:9999/default","",
"");
Statement stmt =
con.createStatement();
Resultset res =
stmt.executeQuery("SELECT client_ip
, COUNT(client_ip) FROM weblogs GROUP BY
client_ip");
while (res.next())
{
System.out.println(res.getInt(1) +
"\t" + res.getString(2));
}
