使用hadoop和hive来进行应用的日志数据分析

整个架构流程的背景是：

1、各个应用产生日志打印约定格式的syslog，然后在服务器端部署syslog-ng server做日志的统一收集。

2、在syslog-ng server所在的服务器做日志文件的分类然后发送日志消息给storm做实时流数据统计。

3、同时每日凌晨启动rsync服务将前一天的日志文件发送到hadoop和hive服务器做非实时数据分析

使用hadoop和hive来进行应用的日志数据分析的详细流程：

1、安装hadoop

hadoop的安装以及配置在我的文章中有详细的描述：

http://blog.csdn.net/jsjwk/article/details/8923999

2、安装hive

hive的安装非常简单，只需要下载安装包：

wget http://mirrors.cnnic.cn/apache/hive/hive-0.10.0/hive-0.10.0.tar.gz

然后解压后，修改一点点配置文件用于连接hadoop的配置即可。

3、在hive中创建表

    /**     * 根据日期来创建hive的邮件日志表     * @param date     * @return     * @throws SQLException     */    public String createTable(Calendar cal) throws SQLException    {String tableName = getTableName(cal.getTime());StringBuilder sql = new StringBuilder();        sql.append("create table if not exists ");        sql.append(tableName);        sql.append("( ");        sql.append("syslog_month string,  ");        if(cal.get(Calendar.DAY_OF_MONTH)<10){            sql.append("syslog_day_pre string,  ");        }        sql.append("syslog_day string, ");        sql.append("syslog_time string, ");        sql.append("ip string, ");        sql.append("source string, ");        sql.append("message array<string>, ");        sql.append("information1 string, ");        sql.append("information2 string, ");        sql.append("information3 string,  ");        sql.append("information4 string,  ");        sql.append("information5 string)  ");        sql.append("row format delimited fields terminated by ' '  ");        sql.append("collection items terminated by ','  ");        sql.append("map keys terminated by  ':' ");                LOG.info("[创建HIVE表的DLL]"+sql.toString());                HiveUtil.createTable(sql.toString());        return tableName;    }

4、加载日志到hive中

    /**     * 加载本地文件到hive邮件日志表     * @param path     * @param tableName     * @throws SQLException     */    public void loadData(String path,String tableName) throws SQLException    {StringBuilder sql = new StringBuilder();sql.append("load data local inpath ");sql.append("'");sql.append(path);sql.append("'");//sql.append(" overwrite into table ");        sql.append(" into table ");        sql.append(tableName);                LOG.info("[加载数据到HIVE表的DLL]"+sql.toString());                HiveUtil.loadData(sql.toString());    }

5、然后就可以根据自己的需求进行各类简单的查询了：

（1）查询所有数据

    /**     * 查询所有数据     */    public ResultSet queryData(String tableName) throws SQLException    {StringBuilder sql = new StringBuilder();sql.append("select syslog_month,syslog_day,syslog_time,ip,source,message,");sql.append("information1,information2,information3,information4,information5 ");sql.append("from ");sql.append(tableName);LOG.info("[查询所有HIVE数据的DLL]"+sql.toString());ResultSet res = HiveUtil.queryData(sql.toString());return res;    }

（2）查询UserId和CategoryId分类的总延时

    /**     * 查询UserId和CategoryId分类的总延时     */    public List<Map<String,Object>> queryHiveDataForUserIdAndCategoryIdDelay(String tableName) throws SQLException    {StringBuilder sql = new StringBuilder();sql.append("select t2.message[4],t2.message[5],count(*),sum(t2.message[1]-t1.message[2]),sum(t2.message[1]-t1.message[2])/count(*) ");sql.append("from ");sql.append("(select * from "+tableName+" where message[0]='&QUEUE' ) t1 ");sql.append("FULL OUTER JOIN ");sql.append("(select * from "+tableName+" where message[0]='&OUT' OR message[0]='&WORKERERROR' OR message[0]='&ERROR' ) t2 ");sql.append("ON concat(t1.message[1],'0$',substring(t1.message[6],2,length(t1.message[6])-3))==t2.message[3] ");sql.append("where t2.message[1]>=t1.message[2] ");        sql.append("group by t2.message[4],t2.message[5]");        LOG.info("[查询UserId和CategoryId分类的总延时的HIVE数据的DLL]"+sql.toString());                HiveQueryResultSet res = (HiveQueryResultSet) HiveUtil.queryData(sql.toString());List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();while(res.next()){    int userId = res.getInt(1);    int categoryId = res.getInt(2);    int num = res.getInt(3);    double delay = res.getDouble(4);    double avgDelay = res.getDouble(5);        Map<String,Object> map = new HashMap<String,Object>();    map.put("userId", userId);    map.put("categoryId", categoryId);    map.put("num", num);    map.put("delay", delay);    map.put("avgDelay", avgDelay);    list.add(map);}        return list;    }