问题导读
1.如何自定义hive时间函数？
2.怎样利用hive进行用户相关数据的统计？
3.怎样创建hive可视化页面？
上一篇
大数据实战之App管理平台日志分析（三）
http://www.aboutyun.com/forum.php?mod=viewthread&tid=27136
一、自定义hive时间函数
-------------------------------------------------------------
1.创建新模块app-logs-hive，添加maven模块
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.test</groupId>
<artifactId>app-logs-hive</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.1.0</version>
</dependency>
</dependencies>
</project>
2.创建包com.test.applogs.udf
3.新建DateUtil工具类
package com.test.applogs.udf;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
/**
*
*/
public class DateUtil {
/**
* 得到指定date的零时刻.
*/
public static Date getDayBeginTime(Date d) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd 00:00:00");
return sdf.parse(sdf.format(d));
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 得到指定date的偏移量零时刻.
*/
public static Date getDayBeginTime(Date d, int offset) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd 00:00:00");
Date beginDate = sdf.parse(sdf.format(d));
Calendar c = Calendar.getInstance();
c.setTime(beginDate);
c.add(Calendar.DAY_OF_MONTH,offset);
return c.getTime() ;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 得到指定date所在周的起始时刻.
*/
public static Date getWeekBeginTime(Date d) {
try {
//得到d的零时刻
Date beginDate= getDayBeginTime(d);
Calendar c = Calendar.getInstance();
c.setTime(beginDate);
int n = c.get(Calendar.DAY_OF_WEEK);
c.add(Calendar.DAY_OF_MONTH,-(n - 1));
return c.getTime();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 得到指定date所在周的起始时刻.
*/
public static Date getWeekBeginTime(Date d,int offset) {
try {
//得到d的零时刻
Date beginDate= getDayBeginTime(d);
Calendar c = Calendar.getInstance();
c.setTime(beginDate);
int n = c.get(Calendar.DAY_OF_WEEK);
//定位到本周第一天
c.add(Calendar.DAY_OF_MONTH,-(n - 1));
c.add(Calendar.DAY_OF_MONTH,offset * 7);
return c.getTime();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 得到指定date所在月的起始时刻.
*/
public static Date getMonthBeginTime(Date d) {
try {
//得到d的零时刻
Date beginDate= getDayBeginTime(d);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/01 00:00:00");
return sdf.parse(sdf.format(beginDate));
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 得到指定date所在月的起始时刻.
*/
public static Date getMonthBeginTime(Date d,int offset) {
try {
//得到d的零时刻
Date beginDate= getDayBeginTime(d);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/01 00:00:00");
//d所在月的第一天的零时刻
Date firstDay = sdf.parse(sdf.format(beginDate));
Calendar c = Calendar.getInstance();
c.setTime(firstDay);
//对月进行滚动
c.add(Calendar.MONTH,offset);
return c.getTime();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
4.新建计算当日起始时间函数DayBeginUDF
package com.test.applogs.udf;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 计算day起始毫秒数
*/
@Description(name = "udf_getdaybegin",
value = "getdaybegin",
extended = "getdaybegin() ;\r\n"
+ " getdaybegin(2) \r\n"
+ " getdaybegin('2017/06/29 01:02:03') \r\n"
+ " getdaybegin('2017/06/29 01:02:03',2) \r\n"
+ " getdaybegin(date_obj) \r\n"
+ " getdaybegin(date_obj,2)")
public class DayBeginUDF extends UDF {
/**
* 计算现在的起始时刻(毫秒数)
*/
public long evaluate() throws ParseException {
return evaluate(new Date());
}
/**
* 指定天偏移量
*/
public long evaluate(int offset) throws ParseException {
return evaluate(DateUtil.getDayBeginTime(new Date(), offset));
}
/**
* 计算某天的结束时刻(毫秒数)
*/
public long evaluate(Date d) throws ParseException {
return DateUtil.getDayBeginTime(d).getTime();
}
/**
* 计算某天的结束时刻(毫秒数)
*/
public long evaluate(Date d, int offset) throws ParseException {
return DateUtil.getDayBeginTime(d, offset).getTime();
}
/**
* 计算某天的起始时刻(毫秒数)
*/
public long evaluate(String dateStr) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return evaluate(d);
}
/**
* 计算某天的起始时刻(毫秒数)
*/
public long evaluate(String dateStr, int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return DateUtil.getDayBeginTime(d, offset).getTime();
}
/**
* 计算某天的起始时刻(毫秒数)
*/
public long evaluate(String dateStr, String fmt) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getDayBeginTime(d).getTime();
}
/**
* 计算某天的起始时刻(毫秒数)
*/
public long evaluate(String dateStr, String fmt, int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getDayBeginTime(d, offset).getTime();
}
}
5.计算当日结束时间函数DayEndUDF
package com.test.applogs.udf;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
/**
* 计算day结束毫秒数
*/
@Description(name = "udf_getEndDay",
value = "getEndTimeInDay",
extended = "udf() ; udf('2017/06/27 02:03:04') ; udf('2017-06-27 02-03-04','yyyy-MM-dd HH-mm-ss')")
@UDFType(deterministic = true, stateful = false)
public class DayEndUDF extends UDF {
/**
* 计算今天结束时刻(毫秒数),其实是明天的零时.
*/
public long evaluate() throws ParseException {
return evaluate(new Date());
}
/**
* 计算指定日期的结束时刻(毫秒数)
*/
public long evaluate(Date d) throws ParseException {
Date zeroDate = DateUtil.getDayBeginTime(d);
//日历
Calendar c = Calendar.getInstance();
c.setTime(zeroDate);
c.add(Calendar.DAY_OF_MONTH, 1);
return c.getTimeInMillis();
}
/**
* 计算指定日期,使用的格式是yyyy/MM/dd HH:mm:ss的结束时刻(毫秒数)
*/
public long evaluate(String dateStr) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return evaluate(d);
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
/**
* 计算指定日期结束时刻,使用的格式自行指定(毫秒数)
*/
public long evaluate(String dateStr, String fmt) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return evaluate(d);
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
}
6.格式化日期函数
package com.test.applogs.udf;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 将long型的时间片格式化成指定日期格式
*/
@Description(name = "udf_formattime",
value = "formattime",
extended = "formattime() ;\r\n"
+ " formattime(1234567,'yyyy/MM/01') \r\n"
+ " formattime('1234567','yyyy/MM/dd')")
public class FormatTimeUDF extends UDF {
/**
* 格式化时间,long型
*/
public String evaluate(long ms,String fmt) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt) ;
Date d = new Date();
d.setTime(ms);
return sdf.format(d) ;
}
/**
* 格式化时间，string类型
*/
public String evaluate(String ms,String fmt) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt) ;
Date d = new Date();
d.setTime(Long.parseLong(ms));
return sdf.format(d) ;
}
/**
* 格式化时间，string类型
*/
public String evaluate(long ms ,String fmt , int week) throws ParseException {
Date d = new Date();
d.setTime(ms);
//周内第一天
Date firstDay = DateUtil.getWeekBeginTime(d) ;
SimpleDateFormat sdf = new SimpleDateFormat(fmt) ;
return sdf.format(firstDay) ;
}
}
7.MonthBeginUDF
package com.test.applogs.udf;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 计算day所在月起始毫秒数
*/
@Description(name = "udf_getmonthbegin",
value = "getmonthbegin",
extended = "getmonthbegin() ;\r\n" +
" getmonthbegin(2) \r\n" +
" getmonthbegin('2017/06/29 01:02:03') \r\n" +
" getmonthbegin('2017/06/29 01:02:03',2) \r\n" +
" getmonthbegin(date_obj) \r\n" +
" getmonthbegin(date_obj,2)")
@UDFType(deterministic = true, stateful = false)
public class MonthBeginUDF extends UDF {
/**
* 计算现在的起始时刻(毫秒数)
*/
public long evaluate() throws ParseException {
return DateUtil.getMonthBeginTime(new Date()).getTime() ;
}
/**
* 指定周偏移量
*/
public long evaluate(int offset) throws ParseException {
return DateUtil.getMonthBeginTime(new Date(),offset).getTime();
}
/**
*
*/
public long evaluate(Date d) throws ParseException {
return DateUtil.getMonthBeginTime(d).getTime();
}
/**
*/
public long evaluate(Date d,int offset) throws ParseException {
return DateUtil.getMonthBeginTime(d,offset).getTime();
}
/**
*/
public long evaluate(String dateStr) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return DateUtil.getMonthBeginTime(d).getTime();
}
/**
*/
public long evaluate(String dateStr,int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return DateUtil.getMonthBeginTime(d, offset).getTime();
}
/**
*/
public long evaluate(String dateStr, String fmt) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getMonthBeginTime(d).getTime();
}
/**
*/
public long evaluate(String dateStr, String fmt,int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getMonthBeginTime(d, offset).getTime();
}
}
8.WeekBeginUDF
package com.test.applogs.udf;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 计算day所在周起始毫秒数
*/
@Description(name = "udf_getweekbegin",
value = "getweekbegin",
extended = "getweekbegin() ;\r\n"
+ " getweekbegin(2) \r\n"
+ " getweekbegin('2017/06/29 01:02:03') \r\n"
+ " getweekbegin('2017/06/29 01:02:03',2) \r\n"
+ " getweekbegin(date_obj) \r\n"
+ " getweekbegin(date_obj,2)")
public class WeekBeginUDF extends UDF {
/**
* 计算现在的起始时刻(毫秒数)
*/
public long evaluate() throws ParseException {
return DateUtil.getWeekBeginTime(new Date()).getTime() ;
}
/**
* 指定周偏移量
*/
public long evaluate(int offset) throws ParseException {
return DateUtil.getWeekBeginTime(new Date(),offset).getTime();
}
/**
*
*/
public long evaluate(Date d) throws ParseException {
return DateUtil.getWeekBeginTime(d).getTime();
}
/**
*/
public long evaluate(Date d,int offset) throws ParseException {
return DateUtil.getWeekBeginTime(d,offset).getTime();
}
/**
*/
public long evaluate(String dateStr) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return DateUtil.getWeekBeginTime(d).getTime();
}
/**
*/
public long evaluate(String dateStr,int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d = sdf.parse(dateStr);
return DateUtil.getWeekBeginTime(d, offset).getTime();
}
/**
*/
public long evaluate(String dateStr, String fmt) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getWeekBeginTime(d).getTime();
}
/**
*/
public long evaluate(String dateStr, String fmt,int offset) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat(fmt);
Date d = sdf.parse(dateStr);
return DateUtil.getWeekBeginTime(d, offset).getTime();
}
}
9.导出jar包并复制到hive/lib下
10.添加jar到hive的classpath
add jar /soft/hive/lib/app-logs-hive-1.0-SNAPSHOT.jar;
11.注册函数
$hive> use applogsdb;
$hive> create function getdaybegin AS 'com.test.applogs.udf.DayBeginUDF';
$hive> create function getweekbegin AS 'com.test.applogs.udf.WeekBeginUDF';
$hive> create function getmonthbegin AS 'com.test.applogs.udf.MonthBeginUDF';
$hive> create function formattime AS 'com.test.applogs.udf.FormatTimeUDF';
12.测试函数
$hive> select getdaybegin();
二、Hive统计查询
-------------------------------------------------------------
1.新增用户查询
a.日新增用户查询
//今天新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getdaybegin() and mintime < getdaybegin(1)) t ;
//昨天新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getdaybegin(-1) and mintime < getdaybegin()) t ;
//指定日的日新增用户数
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getdaybegin('2017/05/09 00:00:00') and mintime < getdaybegin('2018/05/09 00:00:00',1)) t ;
b.周新增用户
//当前周新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getweekbegin() and mintime < getweekbegin(1)) t ;
//当前周新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getweekbegin(-1) and mintime < getweekbegin()) t ;
//当前周新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getweekbegin('2017/05/09 00:00:00') and mintime < getweekbegin('2018/05/09 00:00:00',1)) t ;
c.月新增用户
//当前月新增
select count(*) from (select min(createdatms) mintime from ext_startup_logs where appid = 'sdk34734' group by deviceid having mintime >= getmonthbegin() and mintime < getmonthbegin(1)) t ;
2.总用户数查询
select count(distinct deviceid) from ext_startup_logs where appid = 'sdk34734' ;
3.活跃用户查询
a.日活[where直接映射到分区表的对应分区，减小查询量，加快查询效率]
select count(distinct deviceid) from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM')
and day = formattime(getdaybegin(),'dd');
b.周活
select count(distinct deviceid) from ext_startup_logs where appid = 'sdk34734' where createdatms >= getweekbegin() and createdatms < getweekbegin(1) ;
c.月活
select count(distinct deviceid) from ext_startup_logs where appid = 'sdk34734' where createdatms >= getmonthbegin() and createdatms < getmonthbegin(1) ;
d.一次查询出一周内每天的日活跃数。
select formattime(createdatms,'yyyy/MM/dd') day ,count(distinct deviceid) from ext_startup_logs where appid = 'sdk34734' and createdatms >= getweekbegin() and createdatms < getweekbegin(1) group by day ;
f.一次查询出一个月内每周的周活跃数。
select formattime(createdatms,'yyyy/MM/dd',0) week ,count(distinct deviceid) from ext_startup_logs where appid = 'sdk34734' and createdatms >= getweekbegin(-6) and createdatms < getweekbegin(-1) group by week ;
g.一次查询出过去的三个月内每周的月活跃数。
select formattime(createdatms,'yyyy/MM',0) month ,count(distinct deviceid) from ext_startup_logs
where appid = 'sdk34734' and createdatms >= getmonthbegin(-4) and createdatms < getmonthbegin(-1)
group by month ;
三、创建可视化web模块,基于SSM
-------------------------------------------------------
1.创建模块app-logs-visualize-web，添加maven支持,添加web支持
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.test</groupId>
<artifactId>app-logs-visualize-web</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.10</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>dubbo</artifactId>
<version>2.5.3</version>
</dependency>
<dependency>
<groupId>com.101tec</groupId>
<artifactId>zkclient</artifactId>
<version>0.9</version>
</dependency>
</dependencies>
</project>
2.配置WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<filter>
<filter-name>characterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter
</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>characterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<servlet>
<servlet-name>dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet
</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
3.配置分发器WEB-INF/dispatcher-servlet.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:dubbo="http://code.alibabatech.com/schema/dubbo"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://code.alibabatech.com/schema/dubbo
file:///d:/dubbo.xsd">
<mvc:annotation-driven/>
<!-- 静态资源 -->
<mvc:resources mapping="/html/**" location="/html/"/>
<mvc:resources mapping="/css/**" location="/css/"/>
<mvc:resources mapping="/js/**" location="/js/"/>
<mvc:resources mapping="/images/**" location="/images/"/>
<!-- 扫描控制器 -->
<context:component-scan
base-package="com.test.applogs.visualize.web.controller"/>
<!-- 配置视图解析器 -->
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/jsps/"/>
<property name="suffix" value=".jsp"/>
</bean>
<dubbo:application name="consumer_app"/>
<dubbo:registry address="zookeeper://s200:2181"/>
<dubbo:consumer timeout="5000"/>
<dubbo:reference id="statService"
interface="com.test.applogs.visualize.service.StatService"/>
</beans>
4.创建web/css web/html web/image web/js web/jsps 目录，并复制echarts.js和myjs.js到web/js下
5.创建用户domain包com.test.applogs.visualize.domain和用户状态类StatBean
package com.test.applogs.visualize.domain;
/**
* 统计分析类
*/
public class StatBean {
//统计日期
private String date ;
//统计数量
private long count ;
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public long getCount() {
return count;
}
public void setCount(long count) {
this.count = count;
}
}
6.创建dao包com.test.applogs.visualize.dao和基本接口BaseDao
package com.test.applogs.visualize.dao;
import com.test.applogs.visualize.domain.StatBean;
/**
* BaseDao接口
*/
public interface BaseDao<T> {
public StatBean findNewUsers();
}
7.创建dao的实现包和实现类[用于查询数据库的类，CRUD]
a.包com.test.applogs.visualize.dao.impl
b.实现类
package com.test.applogs.visualize.dao.impl;
import com.test.applogs.visualize.dao.BaseDao;
import com.test.applogs.visualize.domain.StatBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
/**
* 统计
*/
@Repository("statDao")
public class StatDaoImpl extends SqlSessionDaoSupport implements BaseDao {
/**
* 查询新增用户
*/
public StatBean findNewUsers(){
return getSqlSession().selectOne("stats.newusers");
}
@Resource
public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
super.setSqlSessionFactory(sqlSessionFactory);
}
}
8.创建服务Service包以及接口[用于注册和加载java beans的接口]
a.包com.test.applogs.visualize.service
b.基础服务接口BaseService
package com.test.applogs.visualize.service;
import com.test.applogs.visualize.domain.StatBean;
/**
*/
public interface BaseService<T> {
public StatBean findNewUsers();
}
c.用户状态查询服务接口StatService
package com.test.applogs.visualize.service;
import com.test.applogs.visualize.domain.StatBean;
/**
* Service
*/
public interface StatService extends BaseService<StatBean> {
public StatBean findNewUsers();
public Long todayNewUsers(String appid) ;
}
9.创建服务接口的包和实现类[用于注册和加载dao和damain]
a.com.test.applogs.visualize.service.impl
b.基本实现类
package com.test.applogs.visualize.service.impl;
import com.test.applogs.visualize.dao.BaseDao;
import com.test.applogs.visualize.service.BaseService;
import javax.annotation.Resource;
/**
* BaseService实现类
*/
public abstract class BaseServiceImpl<T> implements BaseService<T> {
private BaseDao<T> dao ;
@Resource
public void setDao(BaseDao<T> dao) {
this.dao = dao;
}
public BaseDao<T> getDao() {
return dao;
}
}
c.状态服务查询实现类
package com.test.applogs.visualize.service.impl;
import com.test.applogs.visualize.dao.BaseDao;
import com.test.applogs.visualize.domain.StatBean;
import com.test.applogs.visualize.service.StatService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* 统计服务
*/
@Service("statService")
public class StatServiceImpl extends BaseServiceImpl<StatBean> implements StatService {
@Resource(name="statDao")
public void setDao(BaseDao<StatBean> dao) {
super.setDao(dao);
}
/**
* 查询新增用户
*/
public StatBean findNewUsers() {
return getDao().findNewUsers();
}
public Long todayNewUsers(String appid) {
return null;
}
}
10.创建控制器包com.test.applogs.visualize.web.controller和类StatController
package com.test.applogs.visualize.web.controller;
import com.test.applogs.visualize.domain.StatBean;
import com.test.applogs.visualize.service.StatService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
/**
* 统计分析类
*/
@Controller
@RequestMapping("/stat")
public class StatController {
@Resource(name="statService")
private StatService ss ;
/**
* appid = "sdk34734"
* 本周每天新增用户数
*/
@RequestMapping("/newusers")
public String findNewUsers(){
StatBean bean = ss.findNewUsers();
System.out.println(bean.getCount());
return "index" ;
}
@RequestMapping("/index")
public String toStatPage(){
return "index" ;
}
}
11.创建resources/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
<properties>
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/big5?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</properties>
-->
<!-- 定义类别名 -->
<typeAliases>
<typeAlias type="com.test.applogs.visualize.domain.StatBean" alias="_StatBean"/>
</typeAliases>
<!--
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
-->
<mappers>
<mapper resource="StatBeanMapper.xml"/>
</mappers>
</configuration>
12.创建resources/beans.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/s ... ing-context-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
<!-- 扫描dao和service包 -->
<context:component-scan base-package="com.test.applogs.visualize.service.impl,com.test.applogs.visualize.dao.impl" />
<!-- 事务通知-->
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="*" propagation="REQUIRED" isolation="DEFAULT"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:advisor advice-ref="txAdvice" pointcut="execution(* *..*Service.*(..))" />
</aop:config>
<!-- 数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="org.apache.hive.jdbc.HiveDriver"/>
<property name="jdbcUrl" value="jdbc:hive2://s100:10000/applogsdb"/>
<property name="user" value=""/>
<property name="password" value=""/>
<property name="maxPoolSize" value="10"/>
<property name="minPoolSize" value="2"/>
<property name="initialPoolSize" value="3"/>
<property name="acquireIncrement" value="2"/>
</bean>
<!-- 配置sessionfactory -->
<bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:mybatis-config.xml" />
</bean>
<!-- 事务管理器 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
13.启动hiveserver2服务器
$> hiveserver2 &
14.是否hiveserver2启动成功
$> netstat -anop | grep 10000
15.测试数据源是否连通.
@Test
public void testConn() throws SQLException {
ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
DataSource ds = (DataSource)ac.getBean("dataSource");
Connection conn = ds.getConnection();
Statement st = conn.createStatement();
ResultSet set = st.executeQuery("select getdaybegin()");
set.next();
long time = set.getLong(1);
System.out.println(time);
}
16.编写mybatis--mapper映射文件[StatBeanMapper.xml]
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="stats">
<!-- 查询新增用户 -->
<select id="newusers" resultMap="rm_StatBean">
select count(*) stcount
from (
select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
) t
</select>
<!-- 指定app今天新增用户数 -->
<select id="selectAppIdTodayNewusers">
select count(*) stcount
from
(
select appid, deviceid , min(createdatms) mintime
from ext_startup_logs
where appid = #{appid}
group by deviceid
having mintime >= getdaybegin() and mintime < getdaybegin(1)
) t
</select>
<resultMap id="rm_StatBean" type="_StatBean">
<result column="stcount" property="count" />
</resultMap>
</mapper>
17.测试StatBeanMapper.xml
@Test
public void testService() throws SQLException {
ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
StatService ss = (StatService)ac.getBean("statService");
StatBean users = ss.findNewUsers();
System.out.println(users.getCount());
System.out.println(users.getDate());
}
18.StatBeanMapper.xml 配置文件添加新的查询方法<!-- 指定app今天新增用户数 -->
<!-- 指定app今天新增用户数 -->
<select id="selectAppIdTodayNewusers" resultType="long">
select count(*) stcount
from
(
select appid, deviceid , min(createdatms) mintime
from ext_startup_logs
where appid = #{appid}
group by deviceid
having mintime >= getdaybegin() and mintime < getdaybegin(1)
) t
</select>
19.BaseDao接口中添加对应的映射函数
package com.test.applogs.visualize.dao;
import com.test.applogs.visualize.domain.StatBean;
/**
* BaseDao接口
*/
public interface BaseDao<T> {
public StatBean findNewUsers();
//指定app今日新增用户数
public Long todayNewUser(String appid);
}
20.StatDaoImpl实现新增方法
class StatDaoImpl{
...
//指定app今日新增用户数
public Long todayNewUser(String appid) {
return getSqlSession().selectOne("stats.selectAppIdTodayNewusers",appid);
}
...
}
21.StatService接口实现新增方法
package com.test.applogs.visualize.service;
import com.test.applogs.visualize.domain.StatBean;
/**
* Service
*/
public interface StatService extends BaseService<StatBean> {
public StatBean findNewUsers();
public Long todayNewUsers(String appid) ;
}
22.StatServiceImpl实现新增的方法
public class StatServiceImpl extends BaseServiceImpl<StatBean> implements StatService {
...
//指定app今日新增用户数
public Long todayNewUsers(String appid) {
return getDao().todayNewUser(appid);
}
...
}
23.测试新方法
@Test
public void testService1() throws SQLException {
ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
StatService ss = (StatService)ac.getBean("statService");
System.out.println(ss.todayNewUsers("sdk34734"));
}
四、注意事项
-------------------------------------------------
1.关闭beans.xml中的service事务
<!-- 事务通知-->
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<!-- 关闭事务 -->
<tx:method name="*" propagation="SUPPORTS" isolation="DEFAULT"/>
</tx:attributes>
</tx:advice>
2.mybatis的映射文件中sql语句中有大于小于号'< >' 需要使用'> <' 进行转义
五、嵌套子查询：查询一周内某个appid，每天新增的用户数
-----------------------------------------------
1.hsql语句
//1.查询本周新增的所有用户
select deviceid, min(createdatms) as mintime from ext_startup_logs group by deviceid having mintime >= getweekbegin() and mintime < getweekbegin(1);
//2.查询本周内每天的新增用户 --- 对1.的查询结果，添加别名，然后再次分组查询。
select formattime(t.mintime, 'yyyy/MM/dd') , count(*) from ( 1. ) as t group by formattime(t.mintime, 'yyyy/MM/dd');
select formattime(t.mintime, 'yyyy/MM/dd') , count(*) from (select deviceid, min(createdatms) as mintime from ext_startup_logs group by deviceid having mintime >= getweekbegin() and mintime < getweekbegin(1)) as t group by formattime(t.mintime, 'yyyy/MM/dd');
2.解决自定义UDF函数formattime，加载不到类的错误
a.将jar包put到hdfs上
$> hdfs dfs -put app-logs-hive-1.0-SNAPSHOT.jar /data/jars/
b.删除原来注册的函数
hive> drop function getdaybegin;
hive> drop function getweekbegin;
hive> drop function getmonthbegin;
hive> drop function formattime;
...
c.创建新的UDF函数，使用using jar命令
hive> create function getdaybegin as 'com.test.applogs.udf.DayBeginUDF' using jar 'hdfs://s100/data/jars/app-logs-hive-1.0-SNAPSHOT.jar';
hive> create function getweekbegin as 'com.test.applogs.udf.WeekBeginUDF' using jar 'hdfs://s100/data/jars/app-logs-hive-1.0-SNAPSHOT.jar';
hive> create function getmonthbegin as 'com.test.applogs.udf.MonthBeginUDF' using jar 'hdfs://s100/data/jars/app-logs-hive-1.0-SNAPSHOT.jar';
hive> create function formattime as 'com.test.applogs.udf.FormatTimeUDF' using jar 'hdfs://s100/data/jars/app-logs-hive-1.0-SNAPSHOT.jar';
最新经典文章，欢迎关注公众号
---------------------
作者：葛红富
来源：CSDN
原文：https://blog.csdn.net/xcvbxv01/article/details/84310720
|
/2 
京ICP备2020039040号
简书网举报电话：021-34700000
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡
