-
Python+大数据-知行教育(三)-访问咨询主题看板_增量流程
Python+大数据-知行教育(三)-访问咨询主题看板_增量流程
4. 访问咨询主题看板_增量流程
4.1 业务库中模拟增量数据(实际生产中不存在)
- 模拟上一天数据: 在mysql中执行
-- 模拟访问咨询主表数据 CREATE TABLE web_chat_ems_2021_09 AS SELECT * FROM web_chat_ems_2019_07 WHERE create_time BETWEEN '2019-07-01 00:00:00' AND '2019-07-01 23:59:59'; -- 修改表中的时间字段 UPDATE web_chat_ems_2021_09 SET create_time = CONCAT('2021-09-25 ',SUBSTR(create_time,12)); -- 模拟访问咨询附属表数据 CREATE TABLE web_chat_text_ems_2021_09 AS SELECT temp2.* FROM (SELECT * FROM web_chat_ems_2019_07 WHERE create_time BETWEEN '2019-07-01 00:00:00' AND '2019-07-01 23:59:59') temp1 JOIN web_chat_text_ems_2019_07 temp2 ON temp1.id = temp2.id ; -- 注意: 在生产环境中, 每个表中应该都是有若干天的数据, 而不是仅仅只有一天- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
4.2 增量数据采集操作
只需要采集新增的这一天的数据即可
- 思考1: 如何从表中获取新增的这一天的数据呢?
-- 访问咨询的主表数据: SELECT id,create_date_time,session_id,sid,create_time,seo_source,seo_keywords,ip, AREA,country,province,city,origin_channel,USER AS user_match,manual_time,begin_time,end_time, last_customer_msg_time_stamp,last_agent_msg_time_stamp,reply_msg_count, msg_count,browser_name,os_info, '2021-09-26' AS starts_time FROM web_chat_ems_2021_09 WHERE create_time BETWEEN '2021-09-25 00:00:00' AND '2021-09-25 23:59:59' -- 访问咨询的附属表的数据 SELECT temp2.*, '2021-09-26' AS start_time FROM (SELECT id FROM web_chat_ems_2021_09 WHERE create_time BETWEEN '2021-09-25 00:00:00' AND '2021-09-25 23:59:59') temp1 JOIN web_chat_text_ems_2021_09 temp2 ON temp1.id = temp2.id- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 思考2: 将增量的SQL集成到增量的sqoop脚本中
-- 访问咨询主表 sqoop import \ --connect jdbc:mysql://192.168.52.150:3306/nev \ --username root \ --password 123456 \ --query "SELECT id,create_date_time,session_id,sid,create_time,seo_source,seo_keywords,ip, AREA,country,province,city,origin_channel,USER AS user_match,manual_time,begin_time,end_time, last_customer_msg_time_stamp,last_agent_msg_time_stamp,reply_msg_count, msg_count,browser_name,os_info, '2021-09-26' AS starts_time FROM web_chat_ems_2021_09 WHERE create_time BETWEEN '2021-09-25 00:00:00' AND '2021-09-25 23:59:59' and \$CONDITIONS" \ --hcatalog-database itcast_ods \ --hcatalog-table web_chat_ems \ -m 1 -- 访问咨询附属表 sqoop import \ --connect jdbc:mysql://192.168.52.150:3306/nev \ --username root \ --password 123456 \ --query "SELECT temp2.*, '2021-09-26' AS start_time FROM (SELECT id FROM web_chat_ems_2021_09 WHERE create_time BETWEEN '2021-09-25 00:00:00' AND '2021-09-25 23:59:59') temp1 JOIN web_chat_text_ems_2021_09 temp2 ON temp1.id = temp2.id where 1=1 and \$CONDITIONS" \ --hcatalog-database itcast_ods \ --hcatalog-table web_chat_text_ems \ -m 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 思考3: 此脚本为增量化的脚本操作, 随着时间的推移, 每一天都需要执行一次, 而且每次执行只需要修改日期数据即可, 请问有什么简单方案呢?
解决方案: 通过shell脚本的方式来执行, 通过脚本自动获取上一天的日期, 最后将shell脚本放置在ooize中, 进行自动化调度操作- 1
- 2
-
思考4: 如何编写shell脚本呢?
要求: 此脚本能够实现自动获取上一天的日期数据, 并且还支持采集指定日期下数据
难度1: 如何通过shell脚本获取上一天的日期呢? date -d '-2 day' : 获取前二天的日期 date -d '-1 year': 获取上一年日期 date -d '-1 week': 获取上一周的日期 date -d '-1 hour': 获取上一个小时日期 注意上述命令获取后的日期格式为: 2021年 09月 26日 星期日 08:48:12 CST 但是sqoop基本需要的是 2021-09-25 如何解决呢? date -d '-1 day' +'%Y-%m-%d %H:%M:%S' 输出为: 2021-09-25 09:51:42 难度2: 如何让shell脚本接收外部的参数呢? $#: 获取外部传递了几个参数 $N: 获取第几个参数 难度3: 如何在shell中实现判断操作 需求: 如果传递了参数, 设置为参数的值即可, 如果没有传递, 设置为上一天 if [ $# == 1 ] then # 当条件成立的时候, 执行 dateStr=$1 else # 当条件不成立的执行 dateStr=`date -d '-1 day' +'%Y-%m-%d'` fi echo ${dateStr} 注意: [] 两边都需要留有空格 = 左右两边不能有空格 如果需要将命令执行结果赋值给一个变量, 必须给这个命令使用飘号引起来 (`) esc下面那个键 如果获取某一个变量的值: ${变量名}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 编写增量数据采集的shell脚本
hadoop01: 家目录 cd /root vim edu_mode_1_collect.sh 内容如下: #!/bin/bash export SQOOP_HOME=/usr/bin/sqoop if [ $# == 1 ] then dateStr=$1 else dateStr=`date -d '-1 day' +'%Y-%m-%d'` fi dateNowStr=`date +'%Y-%m-%d'` yearStr=`date -d ${dateStr} +'%Y'` monthStr=`date -d ${dateStr} +'%m'` jdbcUrl='jdbc:mysql://192.168.52.150:3306/nev' username='root' password='123456' m='1' ${SQOOP_HOME} import \ --connect ${jdbcUrl} \ --username ${username} \ --password ${password} \ --query "SELECT id,create_date_time,session_id,sid,create_time,seo_source,seo_keywords,ip, AREA,country,province,city,origin_channel,USER AS user_match,manual_time,begin_time,end_time, last_customer_msg_time_stamp,last_agent_msg_time_stamp,reply_msg_count, msg_count,browser_name,os_info, '${dateNowStr}' AS starts_time FROM web_chat_ems_${yearStr}_${monthStr} WHERE create_time BETWEEN '${dateStr} 00:00:00' AND '${dateStr} 23:59:59' and \$CONDITIONS" \ --hcatalog-database itcast_ods \ --hcatalog-table web_chat_ems \ -m ${m} ${SQOOP_HOME} import \ --connect ${jdbcUrl} \ --username ${username} \ --password ${password} \ --query "SELECT temp2.*, '${dateNowStr}' AS start_time FROM (SELECT id FROM web_chat_ems_${yearStr}_${monthStr} WHERE create_time BETWEEN '${dateStr} 00:00:00' AND '${dateStr} 23:59:59') temp1 JOIN web_chat_text_ems_${yearStr}_${monthStr} temp2 ON temp1.id = temp2.id where 1=1 and \$CONDIT IONS" \ --hcatalog-database itcast_ods \ --hcatalog-table web_chat_text_ems \ -m ${m}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 测试脚本是否可以正常执行
sh edu_mode_1_collect.sh 可以在hive中查看对应分区下的数据, 如果有, 说明抽取成功了 select * from itcast_ods.web_chat_ems where starts_time='2021-09-26' limit 10;- 1
- 2
- 3
- 4
-
将shell脚本放置到ooize中,完成自动化调度操作
- 第一步骤: 配置工作流
- 第二步: 配置自动化调度
点击运行启动即可
4.3 增量数据清洗转换操作
- 原有的全量的清洗转换SQL
--动态分区配置 set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; --hive压缩 set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true; --写入时压缩生效 set hive.exec.orc.compression.strategy=COMPRESSION; insert into table itcast_dwd.visit_consult_dwd partition(yearinfo,quarterinfo,monthinfo,dayinfo) select wce.session_id, wce.sid, unix_timestamp(wce.create_time) as create_time, wce.seo_source, wce.ip, wce.area, wce.msg_count, wce.origin_channel, wcte.referrer, wcte.from_url, wcte.landing_page_url, wcte.url_title, wcte.platform_description, wcte.other_params, wcte.history, substr(wce.create_time,12,2) as hourinfo, substr(wce.create_time,1,4) as yearinfo, quarter(wce.create_time) as quarterinfo, substr(wce.create_time,6,2) as monthinfo, substr(wce.create_time,9,2) as dayinfo from itcast_ods.web_chat_ems wce join itcast_ods.web_chat_text_ems wcte on wce.id = wcte.id;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 改造后,增量的清洗转换的SQL
--动态分区配置 set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; --hive压缩 set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true; --写入时压缩生效 set hive.exec.orc.compression.strategy=COMPRESSION; insert into table itcast_dwd.visit_consult_dwd partition(yearinfo,quarterinfo,monthinfo,dayinfo) select wce.session_id, wce.sid, unix_timestamp(wce.create_time) as create_time, wce.seo_source, wce.ip, wce.area, wce.msg_count, wce.origin_channel, wcte.referrer, wcte.from_url, wcte.landing_page_url, wcte.url_title, wcte.platform_description, wcte.other_params, wcte.history, substr(wce.create_time,12,2) as hourinfo, substr(wce.create_time,1,4) as yearinfo, quarter(wce.create_time) as quarterinfo, substr(wce.create_time,6,2) as monthinfo, substr(wce.create_time,9,2) as dayinfo from (select * from itcast_ods.web_chat_ems where starts_time='2021-09-26') wce join (select * from itcast_ods.web_chat_text_ems where start_time='2021-09-26') wcte on wce.id = wcte.id;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 思考: 如何在shell下执行hive的SQL
解决方案: ./hive -e|-f 'sql语句|SQL脚本' -S 说明 -S 表示静默执行- 1
- 2
- 3
- 4
- 5
- 编写shell脚本, 实现增量数据清洗转换操作
hadoop01: cd /root vim edu_mode_1_handle.sh 内容如下 #!/bin/bash export HIVE_HOME=/usr/bin/hive if [ $# == 1 ] then dateStr=$1 else dateStr=`date +'%Y-%m-%d'` fi sqlStr=" set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true; set hive.exec.orc.compression.strategy=COMPRESSION; insert into table itcast_dwd.visit_consult_dwd partition(yearinfo,quarterinfo,monthinfo,dayinfo) select wce.session_id, wce.sid, unix_timestamp(wce.create_time) as create_time, wce.seo_source, wce.ip, wce.area, wce.msg_count, wce.origin_channel, wcte.referrer, wcte.from_url, wcte.landing_page_url, wcte.url_title, wcte.platform_description, wcte.other_params, wcte.history, substr(wce.create_time,12,2) as hourinfo, substr(wce.create_time,1,4) as yearinfo, quarter(wce.create_time) as quarterinfo, substr(wce.create_time,6,2) as monthinfo, substr(wce.create_time,9,2) as dayinfo from (select * from itcast_ods.web_chat_ems where starts_time='${dateStr}') wce join (select * from itcast_ods.web_chat_text_ems where start_time='${dateStr}') wcte on wce.id = wcte.id; " ${HIVE_HOME} -e "${sqlStr}" -S- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
-
将shell脚本配置到ooize中, 从而实现自动化调度
- 第一步: 配置工作流(在原有的采集工作流后续最近一个阶段即可)
- 第二步: 配置自动化调度(可以直接省略…)
4.4 增量数据统计分析操作
- 全量的统计SQL:
-- 访问量: 5条 -- 统计每年的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '-1' as from_url, '5' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd group by yearinfo; -- 统计每年每季度的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '-1' as from_url, '5' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd group by yearinfo,quarterinfo; -- 统计每年每季度每月的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '-1' as from_url, '5' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd group by yearinfo,quarterinfo,monthinfo; -- 统计每年每季度每月每天的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '-1' as from_url, '5' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd group by yearinfo,quarterinfo,monthinfo,dayinfo; -- 统计每年每季度每月每天每小时的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '-1' as from_url, '5' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo; -- 咨询量: 5条 -- 统计每年各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '1' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 group by yearinfo,area; -- 统计每年每季度各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '1' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 group by yearinfo,quarterinfo,area; -- 统计每年每季度每月各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '1' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 group by yearinfo,quarterinfo,monthinfo,area; -- 统计每年每季度每月每天各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '1' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 group by yearinfo,quarterinfo,monthinfo,dayinfo,area; -- 统计每年每季度每月每天每小时各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '1' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
增量的SQL统计:
-- 访问量: 5条 -- 统计每年的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '-1' as from_url, '5' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='2021' group by yearinfo; -- 统计每年每季度的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '-1' as from_url, '5' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='2021' and quarterinfo='3' group by yearinfo,quarterinfo; -- 统计每年每季度每月的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '-1' as from_url, '5' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='2021' and quarterinfo='3' and monthinfo='09' group by yearinfo,quarterinfo,monthinfo; -- 统计每年每季度每月每天的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '-1' as from_url, '5' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='2021' and quarterinfo='3' and monthinfo='09' and dayinfo='25' group by yearinfo,quarterinfo,monthinfo,dayinfo; -- 统计每年每季度每月每天每小时的总访问量 insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '-1' as from_url, '5' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='2021' and quarterinfo='3' and monthinfo='09' and dayinfo='25' group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo; -- 咨询量: 5条 -- 统计每年各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '1' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='2021' group by yearinfo,area; -- 统计每年每季度各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '1' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='2021' and quarterinfo='3' group by yearinfo,quarterinfo,area; -- 统计每年每季度每月各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '1' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='2021' and quarterinfo='3' and monthinfo='09' group by yearinfo,quarterinfo,monthinfo,area; -- 统计每年每季度每月每天各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '1' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='2021' and quarterinfo='3' and monthinfo='09' and dayinfo='25' group by yearinfo,quarterinfo,monthinfo,dayinfo,area; -- 统计每年每季度每月每天每小时各个地区的咨询量 insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '1' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='2021' and quarterinfo='3' and monthinfo='09' and dayinfo='25' group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
说明:
在统计年的时候, 只需要统计加上这一天以后这一年的数据即可, 之前年的数据是不需要统计的 在统计季度的时候,只需要统计加上这一天以后这一年对应的这一季度的数据即可, 之前的季度是不需要统计的 在统计月份的时候, 只需要统计加上这一天以后这一年对应的这一月份的数据即可,之前的月份不需要统计 在统计天的时候, 只需要统计新增的这一天即可 在统计小时的时候, 只需要统计新增的这一天的每个小时- 1
- 2
- 3
- 4
- 5
思考: 在统计的过程中, 比如以年统计, 得到一个新的年的统计结果, 那么在DWS层表中是不是还有一个历史的结果呢? 如何解决呢
说明: 在统计的过程中, 会对之前的统计结果产生影响, 主要受影响: 年统计当年结果数据 季度统计当季度结果数据 月统计当月统计结果数据 注意: 天 和 小时是不受历史结果影响 解决方案: 将之前的结果受影响的结果值删除即可 如何删除呢? 注意hive不支持直接对表中某条数据删除 可以通过删除分区的方案进行处理 思考: 当年的统计结构数据在那个分区下存储着呢? yearinfo='2021' and quarterinfo='-1' and monthinfo ='-1' and dayinfo='-1' 此分区了存储了按年统计的各个产品属性维度的结果数据 当年当季度的统计结构数据再那个分区下 yearinfo='2021' and quarterinfo='3' and monthinfo ='-1' and dayinfo='-1' 当年当季度当月的统计结果在那个分区下: yearinfo='2021' and quarterinfo='3' and monthinfo ='09' and dayinfo='-1' 执行删除: alter table xxx drop partition(yearinfo='2021' and quarterinfo='-1' and monthinfo ='-1' and dayinfo='-1'); alter table xxx drop partition(yearinfo='2021' and quarterinfo='3' and monthinfo ='-1' and dayinfo='-1'); alter table xxx drop partition(yearinfo='2021' and quarterinfo='3' and monthinfo ='09' and dayinfo='-1');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
编写shell脚本:
hadoop01: cd /root vim edu_mode_1_analyse.sh 内容如下: #!/bin/bash export HIVE_HOME=/usr/bin/hive if [ $# == 1 ] then dateStr=$1 else dateStr=`date -d '-1 day' +'%Y-%m-%d'` fi yearStr=`date -d ${dateStr} +'%Y'` monthStr=`date -d ${dateStr} +'%m'` month_for_quarter=`date -d ${dateStr} +'%-m'` quarterStr=$((($month_for_quarter-1)/3+1)) dayStr=`date -d ${dateStr} +'%d'` sqlStr=" set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true; set hive.exec.orc.compression.strategy=COMPRESSION; alter table itcast_dws.visit_dws drop partition(yearinfo='${yearStr}',quarterinfo='-1',monthinfo='-1',dayinfo='-1'); alter table itcast_dws.visit_dws drop partition(yearinfo='${yearStr}',quarterinfo='${quarterStr}',monthinfo='-1',dayinfo='-1'); alter table itcast_dws.visit_dws drop partition(yearinfo='${yearStr}',quarterinfo='${quarterStr}',monthinfo='${monthStr}}',dayinfo='-1'); alter table itcast_dws.consult_dws drop partition(yearinfo='${yearStr}',quarterinfo='-1',monthinfo='-1',dayinfo='-1'); alter table itcast_dws.consult_dws drop partition(yearinfo='${yearStr}',quarterinfo='${quarterStr}',monthinfo='-1',dayinfo='-1'); alter table itcast_dws.consult_dws drop partition(yearinfo='${yearStr}',quarterinfo='${quarterStr}',monthinfo='${monthStr}}',dayinfo='-1'); insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '-1' as from_url, '5' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='${yearStr}' group by yearinfo; insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '-1' as from_url, '5' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='${yearStr}' and quarterinfo='${quarterStr}' group by yearinfo,quarterinfo; insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '-1' as from_url, '5' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' group by yearinfo,quarterinfo,monthinfo; insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '-1' as from_url, '5' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' and dayinfo='${dayStr}' group by yearinfo,quarterinfo,monthinfo,dayinfo; insert into table itcast_dws.visit_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, '-1' as area, '-1' as seo_source, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '-1' as from_url, '5' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' and dayinfo='${dayStr}' group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo; insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, yearinfo as time_str, '1' as grouptype, '5' as time_type, yearinfo, '-1' as quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='${yearStr}' group by yearinfo,area; insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'_',quarterinfo) as time_str, '1' as grouptype, '4' as time_type, yearinfo, quarterinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='${yearStr}' and quarterinfo='${quarterStr}' group by yearinfo,quarterinfo,area; insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo) as time_str, '1' as grouptype, '3' as time_type, yearinfo, quarterinfo, monthinfo, '-1' as dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' group by yearinfo,quarterinfo,monthinfo,area; insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, '-1' as hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str, '1' as grouptype, '2' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' and dayinfo='${dayStr}' group by yearinfo,quarterinfo,monthinfo,dayinfo,area; insert into table itcast_dws.consult_dws partition(yearinfo,quarterinfo,monthinfo,dayinfo) select count(distinct sid) as sid_total, count(distinct session_id) as sessionid_total, count(distinct ip) as ip_total, area, '-1' as origin_channel, hourinfo, concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str, '1' as grouptype, '1' as time_type, yearinfo, quarterinfo, monthinfo, dayinfo from itcast_dwd.visit_consult_dwd where msg_count >= 1 and yearinfo='${yearStr}' and quarterinfo='${quarterStr}' and monthinfo='${monthStr}' and dayinfo='${dayStr}' group by yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area; " ${HIVE_HOME} -e "${sqlStr}" -S- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
测试shell脚本
sh edu_mode_1_analyse.sh 测试: select * from itcast_dws.visit_dws where yearinfo='2021';- 1
- 2
- 3
- 4
最后,将shell脚本配置到oozie (省略)
4.5 增量数据导出操作
说明:
在执行导出的时候, 也需要将mysql中之前的统计的当年当季度和当月的结果数据删除, 然后重新导入操作 此时我们处理的方案, 要进行简化一些, 受影响最大范围当年的数据, 可以直接将当年的统计结果数据全部都删除, 然后重新到DWS层当年的所有数据- 1
- 2
- 3
编写shell脚本:
hadoop01: cd /root vim edu_mode_1_export.sh 内容如下: #!/bin/bash export SQOOP_HOME=/usr/bin/sqoop if [ $# == 1 ] then TD_DATE=$1 else TD_DATE=`date -d '-1 day' +'%Y-%m-%d'` fi TD_YEAR=`date -d ${TD_DATE} +%Y` mysql -uroot -p123456 -h192.168.52.150 -P3306 -e "delete from scrm_bi.visit_dws where yearinfo='$TD_YEAR'; delete from scrm_bi.consult_dws where yearinfo='$TD_YEAR';" jdbcUrl='jdbc:mysql://192.168.52.150:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8' username='root' password='123456' ${SQOOP_HOME} export \ --connect ${jdbcUrl} \ --username ${username} \ --password ${password} \ --table visit_dws \ --hcatalog-database itcast_dws \ --hcatalog-table visit_dws \ --hcatalog-partition-keys yearinfo \ --hcatalog-partition-values $TD_YEAR \ -m 1 ${SQOOP_HOME} export \ --connect ${jdbcUrl} \ --username ${username} \ --password ${password} \ --table consult_dws \ --hcatalog-database itcast_dws \ --hcatalog-table consult_dws \ --hcatalog-partition-keys yearinfo \ --hcatalog-partition-values $TD_YEAR \ -m 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
将shell脚本设置到oozie中(省略)
-
相关阅读:
Solidity 小白教程：18. Import
新人一看就懂：Dubbo3 + Nacos的RPC远程调用框架demo
正式版 API 确定 | Android 12L Beta 1 发布
企业数字化转型，为什么需要企业门户？
Qt之多级折叠下拉导航菜单（采用QTreeWidget）
工作流之activiti7学习进阶篇
DBeaver，一款好用的通用数据库管理器
java单例模式
万字解析：十大排序（直接插入排序+希尔排序+选择排序+堆排序+冒泡排序+快速排序+归并排序+计数排序+基数排序+桶排序）
关于f-stack转发框架的几点分析思考
- 原文地址：https://blog.csdn.net/qq_45588318/article/details/127692024
