Hive Table Using Regex Serde

Connect to hive using either hive or beeline

$ beeline -u jdbc:hive2://localhost:10000/default

0: jdbc:hive2://localhost:10000/default> set hive.server2.logging.operation.level=none; --turn off logging;

You can get sample access logs from this site http://ita.ee.lbl.gov/html/contrib/NASA-HTTP.html.

Load access log file to HDFS location /user/cloudera/weblogs. You can use hue to load the file.

Create an external table using hive

hive> CREATE EXTERNAL TABLE `weblogs`(

`host` string COMMENT 'Host',

`identity` string COMMENT 'User Identity',

`user` string COMMENT 'User identifier',

`time` string COMMENT 'Date time of access',

`request` string COMMENT 'Http request',

`status` string COMMENT 'Http status',

`size` string COMMENT 'Http response size',

`referrer` string COMMENT 'Referrer url',

`useragent` string COMMENT 'Web client agent')

ROW FORMAT SERDE

'org.apache.hadoop.hive.serde2.RegexSerDe'

WITH SERDEPROPERTIES (

'input.regex'='(\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})? (\\S+) (\\S+) (\\[.+?\\]) \"(.*?)\" (\\d{3}) (\\S+) \"(.*?)\" \"(.*?)\"',

'output.format.string'='%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s')

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION

'/user/cloudera/weblogs';

Query the table using HQL:

0: jdbc:hive2://localhost:10000/default> select host, request, size from weblogs limit 10;