Wednesday, September 28, 2011

hadoop log retention

Some people ask me for a "issue" in mapreduce-jobhistory (/jobhistory.jsp) - the history tooks a while to load the site on high-traffic clusters. For that I'll explain the mechanism:

The history-files will be available for 30 days (hardcoded in pre-h21). That produce a lot of logs and waste also space on the hadoop-jobtracker. So I have some installations which hold 20GB on logs in history, as a dependecy a audit of long running jobs isn't really useable.

Beginning from h21 the cleanup is configurable:

Key: mapreduce.jobtracker.jobhistory.maxage
Default: 7 * 24 * 60 * 60 * 1000L (one week)

to set the store into a 3-day period use:

3 * 24 * 60 * 60 * 1000L
That means 3 Days, 24 hours, 60 minutes, 60 seconds and a cache size of 1000.

a other way, but more a hack via crond.d:
find /var/log/hadoop-0.20/history/done/ -type f -mtime +1 |xargs rm -f

Friday, September 23, 2011

Analyze your IIS Logs with hive

As you know, it's really easy to collect logs from a apache driven webfarm into a hive-cluster and analyze them. But how it'll work for IIS? 

Okay, lets do a view inside. IIS let us collect logs in W3C format by checking over the administraion console, register "website", "Active log format". Here you can setup the path where the logs will be stored, the fields you'll logging and much more. After a restart you should see the logs in the desired path. A good idea will be a split into hours, so you can run the jobs every hour on a fresh dataset.

A really easy way will be for a small farm to export the path as a windows shared drive, connect your hive server with the samba-utils:
mount -t cifs //Windows-Server/share -o user=name,password=passwd /mountpoint

Copy the file into hdfs:
hadoop dfs -copyFromLocal /mountpoint/filename <hdfs-dir> (we assume iislog)

Now you can proceed with analysis, we use hive here. Lets assume you want to know which IP has the most traffic.

First you have to describe your tables in hive:
hive> create TABLE iislog (sdate STRING, stime STRING, ssitename STRING,scomputername STRING,sip STRING,csmethod STRING,csuristem STRING,csuriquery STRING,sport INT,csusername STRING,cip STRING,csversion STRING,csuseragent STRING,csCookie STRING,csReferer STRING,scstatus INT,scsubstatus INT,scwin32status INT,scbyte INT,csbytes INT,timetaken INT) partitioned by (time STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '32' STORED AS TEXTFILE;

hive> CREATE TABLE iptraffic (sdate STRING, cip STRING, traffic INT,hits INT,appid STRING,scsuseragent STRING) partitioned by (time STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '124' STORED AS TEXTFILE;

hive> describe iptraffic;
sdate string
cip string
traffic int
hits int
appid string
scsuseragent string
time string
Time taken: 0.07 seconds

The first statement create a table, the rows describes our logfiles, splittet in fields. The second one we need to get the traffic per minute.
Remind, we try to partition the data to get the last results much faster. For that test we need the date -2 mins to create the partition with the data we looking for. To do that write a small script like:

TABLE=IPTRAFFICDATEPAR=`date -d '-2 Min' +"%Y%m%d%H%M"`
DATEPATH=`date -d '-2 Min' +"%Y-%m-%d/%H00/%M"`
SDATE=`date -d '-2 Min' +"%Y-%m-%d"`
STIME=`date -d '-2 Min' +"%H:%M"`
if [ $? -ne 0 ] ; 
 then echo "Couldn't create partition" 
 exit 1
 else echo -e "\n ==> PARTITION (time='$DATEPAR') created" 

hive -e "INSERT OVERWRITE TABLE iptraffic partition (time=$DATEPAR) \ select concat('$SDATE ','$STIME:00'), cip, sum(csbytes)/1024 counter, count(1) hits,ssitename,csuseragent \ from iislog where iislog.time=$DATEPAR and NOT(iislog.cip LIKE '192\.%')\ group by cip,concat('$SDATE ','$STIME:00'), csuseragent, ssitename"

if [ $? -ne 0 ] ; 
 then echo -e "\n ==> a error occured in analysis \n" 
 exit 1
 else echo -e "\n ==> Insert analysis sucessful" 

What will that do?
First we define the date and format them into a standard format we can use for. Then we create the partition and use them in our hive-statement (where clause iptraffic.PARTITION), group with concat by our mainkey (cip = SourceIP) with date and time and copy that into our hive-warehouse-dir.

Let us take a look into the table:
hive> select * from iislog limit 10; 
20110928130000 2011-09-28 10:59:06 W3SVC2 IISTEST GET /images/bluebox.gif -80 - HTTP/1.1 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0) GUID=<deleted> 6313985 NULL 200 0 0 551 1689 201109281300

But we analyzed the data in a new partition:
hadoop dfs -cat /user/hive/warehouse/iptraffic/time=201110071059/* |less

2011-10-07 10:59:00||18|2|W3SVC5|Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Win64;+x64;+Trident/5.0;+MALC)
2011-10-07 10:59:00||1|2|W3SVC7|Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/535.1+(KHTML,+like+Gecko)+Chrome/14.0.835.202+Safari/535.1

you're done.

Thursday, September 15, 2011

Speedup Sqoop

Sqoop [1] (sql to hadoop) lets easy connect RDBMS into a hadoop infrastructure. Newest plugin comes from Microsoft and let us connect MS-SQL Server and hadoop each together. As a cool feature you can create a jar-file from your job, its pretty easy, just here a line:

sqoop export --connect jdbc:<RDBMS>:thin:@<HOSTNAME>:<PORT>:<DB-NAME> --table<TABLENAME> --username<USERNAME> --password<PASSWORD> --export-dir <HDFS DIR WHICH CONTAINS DATA> --direct --fields-terminated-by '<TERMINATOR (Java)>' --package-name <JOBNAME>.<IDENTIFIER> --outdir <WHERE THE JAR SHOULD WRITTEN> --bindir <BIN_DIR>

After you fired up you'll find a jar-package in --outdir, unzip it and you find your java-code and the precompiled class,so you can start to tune them.

Now lets start the job again, but use the precompiled class:

sqoop export --connect jdbc:<RDBMS>:thin:@<HOSTNAME>:<PORT>:<DB-NAME> --table<TABLENAME> --username<USERNAME> --password<PASSWORD> --export-dir <HDFS DIR WHICH CONTAINS DATA> --direct --fields-terminated-by '<TERMINATOR (Java)>' --jar-file <PATH/TO/JAR> 

The step above let you increase the export of large datasets dramatically. So I speedup a export of 100k records from hdfs into a oracle-DB from 16sec into 8sec.