大数据开发的步骤:
1.数据采集
2.数据清洗、整理
3.数据分析
4.数据可视化
1、数据采集
业务需求如下:
日志文件:access.log(存放大量访问日志数据)
58.215.204.118 - - [18/Sep/2013:06:51:35 +0000] "GET /wp-includes/js/jquery/jquery.js?ver=1.10.2 HTTP/1.1" 304 0 "http://blog.fens.me/nodejs-socketio-chat/" "Mozilla/5.0 (Windows NT 5.1; rv:23.0) Gecko/20100101 Firefox/23.0"
字段解析:
1) 访客ip地址: 58.215.204.118
2) 访客用户信息: - -
3) 请求时间:[18/Sep/2013:06:51:35 +0000]
4) 请求方式:GET
5) 请求的url:/wp-includes/js/jquery/jquery.js?ver=1.10.2
6) 请求所用协议:HTTP/1.1
7) 响应码:304
8) 返回的数据流量:0
9) 访客的来源url:http://blog.fens.me/nodejs-socketio-chat/
10) 访客所用浏览器:Mozilla/5.0 (Windows NT 5.1; rv:23.0) Gecko/20100101 Firefox/23.0
模拟网站访问,产生动态的日志数据
思路:
1.1.上传access.log文件到/export/softwares/下
1.2.在tomcat下新建一个日志文件
cd /export/servers/tomcat/logs/
touch localhost_access_log.2023-11-27.txt
1.3.编写shell脚本文件
模拟用户访问,动态的写入日志数据到localhost_access_log.2023-11-27.txt
cd /export/servers/
--编写Shell脚本 ,脚本名writeToFile.sh
touch writeToFile.sh
while read line; do
echo $line >> /export/servers/tomcat/logs/localhost_access_log.2023-11-27.txt
echo $line
sleep 0.1
done < /export/softwares/access.log
--注意点:
--创建的脚本文件应该有执行权限,如没有,则使用以下语句增加
Chmod +x writeToFile.sh
--执行脚本
sh writeToFile.sh
--会执行很久……耐心等待
--另辟蹊径(直接把access.log复制过来,然后改个名字也可以,但是这个模拟过程要理清楚)
cp /export/softwares/access.log /export/servers/tomcat/logs/localhost_access_log.2023-11-27.txt
1.4.将tomcat中的日志数据采集到HDFS (使用Flume采集日志)
第一步:启动Hadoop
start-all.sh --启动Hadoop
jps --验证Hadoop是否启动成功
第二步:在flume的conf目录下新建一个tail-hdfs.conf文件
cd /export/servers/flume/conf
touch tail-hdfs.conf
第三步: 根据数据采集需求配置采集方案(vi tail-hdfs.conf),描述在配置文件中
cd /export/servers/flume/conf
vi tail-hdfs.conf
# Name the components on this agent定义这个agent中各组件的名字
a1.sources = r1
a1.sinks = k1
a1.channels = c1
# Describe/configure the source描述和配置source组件:r1
#注意:不能往监控目中重复丢同名文件
a1.sources.r1.type = exec
a1.sources.r1.command = tail -F /export/servers/tomcat/logs/localhost_access_log.2023-11-27.txt
#tail -F 表示只监听新生产的日志文件
a1.sources.r1.channels = c1
# Describe the sink描述和配置sink组件:k1
a1.sinks.k1.type = hdfs
a1.sinks.k1.channel = c1
a1.sinks.k1.hdfs.path = /logs/
a1.sinks.k1.hdfs.filePrefix = events-
a1.sinks.k1.hdfs.round = true
a1.sinks.k1.hdfs.roundValue = 10
a1.sinks.k1.hdfs.roundUnit = minute
a1.sinks.k1.hdfs.rollInterval = 10
a1.sinks.k1.hdfs.rollSize = 1000
a1.sinks.k1.hdfs.rollCount = 20
a1.sinks.k1.hdfs.batchSize = 10
a1.sinks.k1.hdfs.useLocalTimeStamp = true
#生成的文件类型,默认是Sequencefile,可用DataStream,则为普通文本
a1.sinks.k1.hdfs.fileType = DataStream
# Use a channel which buffers events in memory
# 描述和配置channel组件,此处使用是内存缓存的方式
a1.channels.c1.type = memory
a1.channels.c1.capacity = 1000
a1.channels.c1.transactionCapacity = 100
# Bind the source and sink to the channel
# 描述和配置source channel sink之间的连接关系
a1.sources.r1.channels = c1
a1.sinks.k1.channel = c1
注意:如果hdfs中已经存在/logs目录,需要先将其删除,不然会报错(因为新采集的日志数据要存放在该目录下,如果新建时已经存在就会报错)
hdfs dfs -rmr /logs
第四步: 指定采集方案配置文件,在相应的节点上启动flume agent
--启动agent去采集数据
cd ../
--Flume采集日志数据的指令
bin/flume-ng agent -c conf -f conf/netcat-logger.conf -n a1 -Dflume.root.logger=INFO,console
-- -c conf 指定flume自身的配置文件所在目录
-- -f conf/netcat-logger.con 指定我们所描述的采集方案
-- -n a1 指定我们这个agent的名字(代理名称)
由于采集方案配置文件tail-hdfs.conf只监听新产生的日志数据,但是刚刚1.3步骤已经将日志数据全部写入到localhost_access_log.2023-11-27.txt中,自然不会再次新产生数据,所以可以先删除该文件,再新建一个同名文件,把数据一并复制过来即可表示新产生的日志数据,不然会采集不到想要的日志数据
rm -rf /export/servers/tomcat/logs/localhost_access_log.2023-11-27.txt
--先删除该日志文件
cp /export/softwares/access.log /export/servers/tomcat/logs/localhost_access_log.2023-11-27.txt
--再复制一份一模一样的数据文件到logs目录下,表示是新增的日志文件,这样可以监听到,或者修改配置方案也行
采集后的日志文件如下(node01:50070)
2.数据清洗、整理(编写mapreduce代码程序,实现数据的清洗)
原始数据
清洗后的数据
2.1 打开idea(2021版本),新建一个maven项目工程
在setting里面配置maven仓的路径,不然找不到本地仓库会报错
2.2编写代码
第一步:导入pom.xml依赖(然后点右上角的小圆圈,更新)
<?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>org.example</groupId>
<artifactId>ProjectLogs</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-mapreduce-client-core</artifactId>
<version>2.7.5</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.2</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
第二步:新建包、类
项目结构如下:
第三步:写代码
WebLogBean.java
package com.hxci.bean;
import org.apache.hadoop.io.Writable;
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
/**
* 对接外部数据的层,表结构定义最好跟外部数据源保持一致
* 术语: 贴源表
* @author itcast
*
*/
public class WebLogBean implements Writable {
private boolean valid = true;// 判断数据是否合法
private String remote_addr;// 记录客户端的ip地址
private String remote_user;// 记录客户端用户名称,忽略属性"-"
private String time_local;// 记录访问时间与时区
private String request;// 记录请求的url与http协议
private String status;// 记录请求状态;成功是200
private String body_bytes_sent;// 记录发送给客户端文件主体内容大小
private String http_referer;// 用来记录从那个页面链接访问过来的
private String http_user_agent;// 记录客户浏览器的相关信息
//设置属性值
public void set(boolean valid,String remote_addr, String remote_user, String time_local, String request, String status, String body_bytes_sent, String http_referer, String http_user_agent) {
this.valid = valid;
this.remote_addr = remote_addr;
this.remote_user = remote_user;
this.time_local = time_local;
this.request = request;
this.status = status;
this.body_bytes_sent = body_bytes_sent;
this.http_referer = http_referer;
this.http_user_agent = http_user_agent;
}
public String getRemote_addr() {
return remote_addr;
}
public void setRemote_addr(String remote_addr) {
this.remote_addr = remote_addr;
}
public String getRemote_user() {
return remote_user;
}
public void setRemote_user(String remote_user) {
this.remote_user = remote_user;
}
public String getTime_local() {
return this.time_local;
}
public void setTime_local(String time_local) {
this.time_local = time_local;
}
public String getRequest() {
return request;
}
public void setRequest(String request) {
this.request = request;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getBody_bytes_sent() {
return body_bytes_sent;
}
public void setBody_bytes_sent(String body_bytes_sent) {
this.body_bytes_sent = body_bytes_sent;
}
public String getHttp_referer() {
return http_referer;
}
public void setHttp_referer(String http_referer) {
this.http_referer = http_referer;
}
public String getHttp_user_agent() {
return http_user_agent;
}
public void setHttp_user_agent(String http_user_agent) {
this.http_user_agent = http_user_agent;
}
public boolean isValid() {
return valid;
}
public void setValid(boolean valid) {
this.valid = valid;
}
/**
* 重写toString()方法,使用Hive默认分隔符进行分隔,为后期导入Hive表提供便利
* @return
*/
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(this.valid);
sb.append("\001").append(this.getRemote_addr());
sb.append("\001").append(this.getRemote_user());
sb.append("\001").append(this.getTime_local());
sb.append("\001").append(this.getRequest());
sb.append("\001").append(this.getStatus());
sb.append("\001").append(this.getBody_bytes_sent());
sb.append("\001").append(this.getHttp_referer());
sb.append("\001").append(this.getHttp_user_agent());
return sb.toString();
}
/**
* 序列化方法
* @param in
* @throws IOException
*/
@Override
public void readFields(DataInput in) throws IOException {
this.valid = in.readBoolean();
this.remote_addr = in.readUTF();
this.remote_user = in.readUTF();
this.time_local = in.readUTF();
this.request = in.readUTF();
this.status = in.readUTF();
this.body_bytes_sent = in.readUTF();
this.http_referer = in.readUTF();
this.http_user_agent = in.readUTF();
}
/**
* 反序列化方法
* @param out
* @throws IOException
*/
@Override
public void write(DataOutput out) throws IOException {
out.writeBoolean(this.valid);
out.writeUTF(null==remote_addr?"":remote_addr);
out.writeUTF(null==remote_user?"":remote_user);
out.writeUTF(null==time_local?"":time_local);
out.writeUTF(null==request?"":request);
out.writeUTF(null==status?"":status);
out.writeUTF(null==body_bytes_sent?"":body_bytes_sent);
out.writeUTF(null==http_referer?"":http_referer);
out.writeUTF(null==http_user_agent?"":http_user_agent);
}
}
WebLogParser.java
package com.hxci.process;
import com.hxci.bean.WebLogBean;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Set;
public class WebLogParser {
//定义时间格式
public static SimpleDateFormat df1 = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss", Locale.US);
public static SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);
public static WebLogBean parser(String line) {
WebLogBean webLogBean = new WebLogBean();
//把一行数据以空格字符切割并存入数组arr中
//157.55.32.96 - - [18/Sep/2013:19:36:07 +0000] "GET /robots.txt HTTP/1.1" 404 169 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
//false 157.55.32.96 - - 2013-09-18 19:36:07 GET /robots.txt HTTP/1.1 404 169 "-" Mozilla/5. (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)
String[] arr = line.split(" ");
//如果数组长度小于等于11,说明这条数据不完整,因此可以忽略这条数据
if (arr.length > 11) {
//满足条件的数据逐个赋值给webLogBean对象
webLogBean.setRemote_addr(arr[0]);
webLogBean.setRemote_user(arr[1]);
//24/Sep/2013:06:24:51
String time_local = formatDate(arr[3].substring(1));//[18/Sep/2013:19:36:07 -> 18/Sep/2013:19:36:07->2013-09-18 19:36:07
if(null==time_local || "".equals(time_local))
time_local="-invalid_time-";
webLogBean.setTime_local(time_local);
webLogBean.setRequest(arr[6]);
webLogBean.setStatus(arr[8]);
webLogBean.setBody_bytes_sent(arr[9]);
webLogBean.setHttp_referer(arr[10]);
//如果useragent元素较多,拼接useragent
if (arr.length > 12) {
StringBuilder sb = new StringBuilder();
for(int i=11;i<arr.length;i++){
sb.append(arr[i]);
}
webLogBean.setHttp_user_agent(sb.toString());
} else {
webLogBean.setHttp_user_agent(arr[11]);
}
if (Integer.parseInt(webLogBean.getStatus()) >= 400) {// 大于400,HTTP错误
webLogBean.setValid(false);
}
if("-invalid_time-".equals(webLogBean.getTime_local())){
webLogBean.setValid(false);
}
} else {
webLogBean=null;
}
return webLogBean;
}
//添加标识
public static void filtStaticResource(WebLogBean bean, Set<String> pages) {
if (!pages.contains(bean.getRequest())) {
bean.setValid(false);
}
}
//格式化时间方法
public static String formatDate(String time_local) {
try {
return df2.format(df1.parse(time_local));
} catch (ParseException e) {
return null;
}
}
}
WeblogPreProcess.java
package com.hxci.process;
import com.hxci.bean.WebLogBean;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;
/**
* 处理原始日志,过滤出真实请求数据,转换时间格式,对缺失字段填充默认值,对记录标记valid和invalid
*/
public class WeblogPreProcess {
public static void main(String[] args) throws Exception {
System.setProperty("HADOOP_USER_NAME", "root");
Configuration conf = new Configuration();
//conf.set(root);
Job job = Job.getInstance(conf);
job.setJarByClass(WeblogPreProcess.class);
job.setMapperClass(WeblogPreProcessMapper.class);
job.setOutputKeyClass(Text.class);
job.setOutputValueClass(NullWritable.class);
FileInputFormat.setInputPaths(job, new Path("hdfs://node01:8020/logs"));
FileOutputFormat.setOutputPath(job, new Path("hdfs://node01:8020/logs_flume"));
job.setNumReduceTasks(0);
boolean res = job.waitForCompletion(true);
System.exit(res ? 0 : 1);
}
public static class WeblogPreProcessMapper extends Mapper<LongWritable, Text, Text, NullWritable> {
// 用来存储网站url分类数据
Set<String> pages = new HashSet<String>();
Text k = new Text();
NullWritable v = NullWritable.get();
/**
* 设置初始化方法,加载网站需要分析的url分类数据,存储到MapTask的内存中,用来对日志数据进行过滤
* 如果用户请求的资源是以下列形式,就表示用户请求的是合法资源。
*/
@Override
protected void setup(Context context) throws IOException, InterruptedException {
pages.add("/about");
pages.add("/black-ip-list/");
pages.add("/cassandra-clustor/");
pages.add("/finance-rhive-repurchase/");
pages.add("/hadoop-family-roadmap/");
pages.add("/hadoop-hive-intro/");
pages.add("/hadoop-zookeeper-intro/");
pages.add("/hadoop-mahout-roadmap/");
}
@Override
protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
//获取一行数据
String line = value.toString();
//调用解析类WebLogParser解析日志数据,最后封装为WebLogBean对象
WebLogBean webLogBean = WebLogParser.parser(line);
if (webLogBean != null) {
// 过滤js/图片/css等静态资源
WebLogParser.filtStaticResource(webLogBean, pages);
k.set(webLogBean.toString());
context.write(k, v);
}
}
}
}
log4j.properties
log4j.rootLogger=debug, stdout, R
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# Pattern to output the caller's file name and line number.
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=example.log
log4j.appender.R.MaxFileSize=100KB
# Keep one backup file
log4j.appender.R.MaxBackupIndex=5
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n
2.3运行代码
实际上是对数据进行清洗、转换。
大概需要等待几分钟……
2.4查看清洗、整理后的数据
运行完成后可以访问node01:50070/192.168.174.100:50070来查看清洗后的数据
根据步骤2.2的代码可知清洗后的数据存放在/logs_flume中
为了后续分析方便,可以删除/logs_flume/_SUCCESS文件
hdfs dfs -rm /logs_flume/_SUCCESS
随意点击下载某个文件到本地查看,可看到该文件里的数据是已经清洗整理过的数据
或者在hdfs里面直接查看也OK
--查看清洗后的结果文件
hdfs dfs -cat /logs_flume/part-m-00000
3. 数据分析
从以下几个方面分析
1.流量分析(创建每日访问量表dw_pvs_everyday)
2.人均浏览量分析(创建维度表dw_avgpv_user_everyday)
3.不同来源的浏览量分析(创建表dw_pvs_source)
4.不同终端来源浏览量分析(创建表dw_pvs_term)
5.不同错误页面的浏览量的分析(创建表dw_pvs_error)
6.不同页面受欢迎程度分析(创建表dw_pvs_page)
思路:先将hdfs中的数据导入到仓库中,存入原始表ods_weblog_origin,分析数据,然后再对原始数据表ods_weblog_origin进行拆分、细化(一共十张表,一张原始表,一张明细表,两张中间表,六张分析的数据表);
拆分、细化完之后,需要将分析的结果数据导出到mysql,但mysql中只需要创建六张表来接收分析过的数据,因为只有分析过的那六张表才是所需要的数据,在mysql中创建完表后利用sqoop将Hive中的数据导入mysql中(这些步骤是为可视化做准备)
3.1创建数据仓库
先将三台机器的Zookeeper开启,然后在第一台机器上执行命令,进入Hive,然后再进行创建数据库、表和导入数据等等操作
start-all.sh
--开启Hadoop(确保已经开启,如果开过就不用再开一次)
cd /export/servers/zookeeper-3.4.9/bin
--进入Zookeeper的bin目录
zkServer.sh start
--开启Zookeeper(三台机器都需要开启)
cd /export/servers/apache-hive-2.1.1-bin
--先进入Hive目录(node01)
bin/hive
--进入Hive
进入Hive,创建数据仓库
--创建数据仓库
DROP DATABASE IF EXISTS weblog;
CREATE DATABASE weblog;
USE weblog;
3.2在数据仓库中创建表以及向表中导入数据
导入本地数据(/logs_flume),然后再细化表
--创建表
--原始表(存放原始数据)
CREATE TABLE ods_weblog_origin (
valid string, --有效标志
remote_addr string, --来源IP
remote_user string, --用户标志
time_local string, --访问完整时间
request string, --请求的URL
status string, --响应码
body_bytes_sent string, --传输字节数
http_referer string, --来源URL
http_user_agent string --客户终端标志
)
row format delimited fields terminated by '\001';
--向表中导入数据(实质是移动了/logs_flume,做完此操作/logs_flume目录不存在了,但是没有什么影响,已经把需要的数据导入数据仓库中了)
load data inpath '/logs_flume' overwrite into table ods_weblog_origin;
--生成明细表
--1. 创建明细表 ods_weblog_detwail
CREATE TABLE ods_weblog_detwail (
valid string, --有效标志
remote_addr string, --来源IP
remote_user string, --用户标志
time_local string, --访问完整时间
daystr string, --访问日期
timestr string, --访问时间
month string, --访问月
day string, --访问日
hour string, --访问时
request string, --请求的URL
status string, --响应码
body_bytes_sent string, --传输字节数
http_referer string, --来源URL
ref_host string, --来源的host
ref_path string, --来源路径
ref_query string, --来源参数query
ref_query_id string, --来源参数query的值
http_user_agent string --客户终端标志
)
partitioned by (datestr string);
--2. 创建临时中间表 t_ods_tmp_referurl(细化http_referer来源URL)
CREATE TABLE t_ods_tmp_referurl as SELECT a.*, b.*
FROM ods_weblog_origin a LATERAL VIEW
parse_url_tuple(regexp_replace(http_referer, "\"", ""),'HOST', 'PATH', 'QUERY', 'QUERY:id') b
as host, path, query, query_id;
--3. 创建临时中间表 t_ods_tmp_detail(细化time_local访问完整时间)
CREATE TABLE t_ods_tmp_detail as
SELECT b.*, substring(time_local, 0, 10) as daystr,
substring(time_local, 12) as tmstr,
substring(time_local, 6, 2) as month,
substring(time_local, 9, 2) as day,
substring(time_local, 11, 3) as hour
FROM t_ods_tmp_referurl b;
--4. 修改默认动态分区参数
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
--5. 向 ods_weblog_detwail 表中加载数据
insert overwrite table ods_weblog_detwail partition(datestr)
SELECT DISTINCT otd.valid, otd.remote_addr, otd.remote_user,
otd.time_local, otd.daystr, otd.tmstr, otd.month, otd.day, otd.hour,
otr.request, otr.status, otr.body_bytes_sent,
otr.http_referer, otr.host, otr.path,
otr.query, otr.query_id, otr.http_user_agent, otd.daystr
FROM t_ods_tmp_detail as otd, t_ods_tmp_referurl as otr
WHERE otd.remote_addr = otr.remote_addr
AND otd.time_local = otr.time_local
AND otd.body_bytes_sent = otr.body_bytes_sent
AND otd.request = otr.request;
创建表的过程也是对数据分析细化的过程
--数据分析
--流量分析
--创建每日访问量表dw_pvs_everyday
CREATE TABLE IF NOT EXISTS dw_pvs_everyday(pvs bigint, month string, day string);
--从宽表 ods_weblog_detwail 获取每日访问量数据并插入维度表 dw_pvs_everyday
INSERT INTO TABLE dw_pvs_everyday
SELECT COUNT(*) AS pvs, owd.month AS month, owd.day AS day
FROM ods_weblog_detwail owd GROUP BY owd.month, owd.day;
--人均浏览量分析
--创建维度表dw_avgpv_user_everyday
CREATE TABLE IF NOT EXISTS dw_avgpv_user_everyday (day string, avgpv string);
--从宽表 ods_weblog_detwail 获取相关数据并插入维度表 dw_avgpv_user_everyday
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-18', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-18' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-19', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-19' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-20', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-20' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-21', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-21' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-22', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-22' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-23', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-23' GROUP by remote_addr) b;
INSERT INTO TABLE dw_avgpv_user_everyday
SELECT '2013-09-24', SUM(b.pvs)/COUNT(b.remote_addr) FROM
(SELECT remote_addr, COUNT(1) AS pvs FROM ods_weblog_detwail WHERE
datestr = '2013-09-24' GROUP by remote_addr) b;
--不同来源的浏览量分析,创建表dw_pvs_source
CREATE TABLE IF NOT EXISTS dw_pvs_source(pvs bigint, source string);
--从宽表 ods_weblog_detwail 获取相关数据并插入维度表 dw_pvs_source
INSERT INTO TABLE dw_pvs_source
SELECT COUNT(*) AS pvs, owd.ref_host source
FROM ods_weblog_detwail owd GROUP BY owd.ref_host;
-----不同终端来源浏览量分析--------
CREATE TABLE IF NOT EXISTS dw_pvs_term(pvs bigint, term string);
--从宽表 ods_weblog_detwail 获取相关数据并插入维度表 dw_pvs_term
INSERT INTO TABLE dw_pvs_term
SELECT COUNT(*) AS pvs, substring(owd.http_user_agent,2,(instr(owd.http_user_agent,'(') -2))
FROM ods_weblog_detwail owd GROUP BY substring(owd.http_user_agent,2,(instr(owd.http_user_agent,'(') -2));
--不同错误页面的浏览量的分析---
CREATE TABLE IF NOT EXISTS dw_pvs_error(pvs bigint, error string,page string);
--从宽表 ods_weblog_detwail 获取相关数据并插入维度表 dw_pvs_error
INSERT INTO TABLE dw_pvs_error
SELECT COUNT(*) AS pvs, owd.status ,owd.request
FROM ods_weblog_detwail owd GROUP BY owd.status,owd.request;
----不同页面受欢迎程度分析----
CREATE TABLE IF NOT EXISTS dw_pvs_page(pvs bigint, page string);
--从宽表 ods_weblog_detwail 获取相关数据并插入维度表 dw_pvs_page
INSERT INTO TABLE dw_pvs_page
SELECT COUNT(*) AS pvs, owd.request request
FROM ods_weblog_detwail owd GROUP BY owd.request;
完成之后,在hdfs中也可以查看到刚刚所创建的十张表
3.3 打开Navicate工具,新建查询创建数据库和表
具体结构如下图所示 :
注意:100-connect连接的是node01,192.168.174.100
--新建查询,创建 MySql 数据库和表
--创建数据仓库
DROP DATABASE IF EXISTS weblog;
CREATE DATABASE weblog;
USE weblog;
--创建表
--1.每日访问量--
DROP TABLE IF EXISTS `t_pvs_everyday`;
CREATE TABLE `t_pvs_everyday` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`pvs` int NOT NULL,
`month` varchar(10) NOT NULL,
`day` varchar(10) NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--2.人均浏览量--
DROP TABLE IF EXISTS `t_avgpv_user_everyday`;
CREATE TABLE `t_avgpv_user_everyday` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`day` varchar(10) NOT NULL,
`avgpv` double NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--3.不同来源的浏览量分析--
DROP TABLE IF EXISTS `t_pvs_source`;
CREATE TABLE `t_pvs_source` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`pvs` int NOT NULL,
`source` varchar(100) NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--4.不同终端来源浏览量分析--------
DROP TABLE IF EXISTS `t_pvs_term`;
CREATE TABLE `t_pvs_term` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`pvs` int NOT NULL,
`term` varchar(100) NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--5.不同错误页面的浏览量的分析---
DROP TABLE IF EXISTS `t_pvs_error`;
CREATE TABLE `t_pvs_error` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`pvs` int NOT NULL,
`error` varchar(10) NOT NULL,
`page` varchar(500) NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--6.不同页面受欢迎程度分析----
DROP TABLE IF EXISTS `t_pvs_page`;
CREATE TABLE `t_pvs_page` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`pvs` int NOT NULL,
`page` varchar(500) NOT NULL,
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.4使用Sqoop命令将数据导入MySQL
将Hive的数据导入本地的mysql数据库中
cd /export/servers/sqoop
1、使用Sqoop命令将统计结果每日访问量数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 1217110\
--table t_pvs_everyday \
--columns "pvs, month, day" \
--export-dir /user/hive/warehouse/weblog.db/dw_pvs_everyday \
--fields-terminated-by '\001';
2、使用Sqoop命令将统计结果人均浏览量数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 123456 \
--table t_avgpv_user_everyday \
--columns "day,avgpv" \
--export-dir /hive/warehouse/weblog.db/dw_avgpv_user_everyday \
--fields-terminated-by '\001' ;
3、使用Sqoop命令将统计结果不同来源的浏览量数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 1217110 \
--table t_pvs_source \
--columns "pvs,source" \
--export-dir /user/hive/warehouse/weblog.db/dw_pvs_source \
--fields-terminated-by '\001' ;
4、使用Sqoop命令将统计结果不同终端来源浏览量数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 1217110 \
--table t_pvs_term \
--columns "pvs,term" \
--export-dir '/user/hive/warehouse/weblog.db/dw_pvs_term' \
--fields-terminated-by '\001' ;
5、使用Sqoop命令将统计结果不同错误页面数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 1217110 \
--table t_pvs_error \
--columns "pvs,error,page" \
--export-dir '/user/hive/warehouse/weblog.db/dw_pvs_error' \
--fields-terminated-by '\001' ;
6、使用Sqoop命令将统计结果不同页面受欢迎程度数据导入MySQL,具体操作如下所示。
bin/sqoop export \
--connect jdbc:mysql://node01/weblog \
--username root \
--password 1217110 \
--table t_pvs_page \
--columns "pvs,page" \
--export-dir '/user/hive/warehouse/weblog.db/dw_pvs_page' \
--fields-terminated-by '\001' ;
执行完以上命令后,回到navicat查看,可以发现创建的空表中已经有数据了
到这一步就已经将Hive中的数据导入到本地mysql中了
4.数据可视化
1.利用Davinci工具进行数据可视化
第一步:开启达芬奇
cd /export/servers/davinci
bin/start-server.sh --开启Davinci
访问:http://node01:8080/
登录后即可进入
第二步:创建项目
第三步:连接本地mysql数据库
先点击刚刚所创建的项目,然后点左边倒数第二个图标,点击“+”
第四步: 编写SQL语句
编写的SQL语句如下:
--每日访问量数据davinci分析---柱状图
SELECT concat(month,"-",day) days ,pvs
FROM t_pvs_everyday order by day
--人均浏览量数据davinci分析--饼状图
select day,FORMAT(avgpv/tmp.sum_avgpv,3) as percent
from t_avgpv_user_everyday,(select sum(avgpv) as sum_avgpv
from t_avgpv_user_everyday) as tmp;
--不同来源浏览量数据davinci分析-雷达图
SELECT source,pvs FROM t_pvs_source
--不同终端浏览量数据davinci分析-折线图
SELECT term,pvs FROM t_pvs_term
--出错页面浏览量占比davinci分析-饼状图图
--先执行下列语句,新建一个表(执行后在mysql中也会新建)
create table t_pvs_errorcode as
SELECT error,SUM(pvs) AS pvs
FROM t_pvs_error
GROUP BY error;
--执行完上述语句创建表后再执行下面的语句
SELECT error ,FORMAT(pvs/tmp.sum_pvs,3) as PERCENT
FROM t_pvs_errorcode,(SELECT SUM(pvs) AS sum_pvs
FROM t_pvs_errorcode) AS tmp
--不同页面受欢迎程度TOP分析davinci分析-柱状图
SELECT page,pvs
FROM t_pvs_page
ORDER BY pvs DESC
limit 6;
第五步:根据需求可视化
第六步:显示
点击刚刚新建的Portal
利用Davinci工具进行数据可视化完成!
2.编写java代码进行可视化
(没整完……只整了两个图TAvgpvNum每日访问量数据和TPvsSource不同来源浏览量数据……要晓得大概思路……SpringBoot……SSM框架……mybatis…………)