57 Hive案例(数据ETL)
2023-09-11 14:15:40 时间
需求
对web点击流日志基础数据表进行etl(按照仓库模型设计)
按各时间维度统计来源域名top10
已有数据表 “t_orgin_weblog” :
col_name | data_type | comment |
---|---|---|
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 | string |
数据示例
| true|1.162.203.134| - | 18/Sep/2013:13:47:35| /images/my.jpg | 200| 19939 | "http://www.angularjs.cn/A0d9" | "Mozilla/5.0 (Windows |
| true|1.202.186.37 | - | 18/Sep/2013:15:39:11| /wp-content/uploads/2013/08/windjs.png| 200| 34613 | "http://cnodejs.org/topic/521a30d4bee8d3cb1272ac0f" | "Mozilla/5.0 (Macintosh;|
实现步骤
1、对原始数据进行抽取转换
–将来访url分离出host path query query id
drop table if exists t_etl_referurl;
create table t_etl_referurl as
SELECT a.*,b.*
FROM t_orgin_weblog a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id
2、从前述步骤进一步分离出日期时间形成ETL明细表“t_etl_detail” day tm
drop table if exists t_etl_detail;
create table t_etl_detail as
select b.*,substring(time_local,0,11) as daystr,
substring(time_local,13) as tmstr,
substring(time_local,4,3) as month,
substring(time_local,0,2) as day,
substring(time_local,13,2) as hour
from t_etl_referurl b;
3、对etl数据进行分区(包含所有数据的结构化信息)
drop table t_etl_detail_prt;
create table t_etl_detail_prt(
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 string,
host string,
path string,
query string,
query_id string,
daystr string,
tmstr string,
month string,
day string,
hour string)
partitioned by (mm string,dd string);
导入数据
insert into table t_etl_detail_prt partition(mm='Sep',dd='18')
select * from t_etl_detail where daystr='18/Sep/2013';
insert into table t_etl_detail_prt partition(mm='Sep',dd='19')
select * from t_etl_detail where daystr='19/Sep/2013';
分个时间维度统计各referer_host的访问次数并排序
create table t_refer_host_visit_top_tmp as
select referer_host,count(*) as counts,mm,dd,hh from t_display_referer_counts group by hh,dd,mm,referer_host order by hh asc,dd asc,mm asc,counts desc;
4、来源访问次数topn各时间维度URL
取各时间维度的referer_host访问次数topn
select * from (select referer_host,counts,concat(hh,dd),row_number() over (partition by concat(hh,dd) order by concat(hh,dd) asc) as od from t_refer_host_visit_top_tmp) t where od<=3;
相关文章
- 数据分析实际案例之:pandas在餐厅评分数据中的使用
- 微服务架构案例(03):数据库选型简介,业务数据规划设计
- SpringBoot2.0 基础案例(04):定时任务和异步任务的使用方式
- Google Earth Engine——Landsat8谐波模型(NDVI)的时序分析案例
- axios实战学习——调用城市天气api接口数据实现天气查询案例
- 【MATLAB教程案例83】通过matlab实现数据的插值拟合
- 【MATLAB教程案例59】使用matlab实现基于LSTM网络的数据分类预测功能与仿真分析
- 【MATLAB教程案例27】基于matlab的图像配准算法的仿真与分析——sift,surf,kaze等
- JMeter基础脚本编写介绍及案例演示
- 图解css3:核心技术与案例实战. 3.5 CSS3盒子阴影属性
- 大数据与机器学习:实践方法与行业案例.2.2 数据缓冲区
- Python数据可视化2.3 体育案例
- 大数据全真案例带你来解密如何挑选“风水宝地”
- 《深度学习导论及案例分析》一导读
- Sharding-jdbc整合综合案例
- 【快应用】$set数据方法使用案例
- 《Oracle性能优化与诊断案例精选》——第1章 三十八载,Oracle伴我同行 1.1 缘起边陲,恰同学风华正茂
- 《数据科学与大数据分析——数据的发现 分析 可视化与表示》一2.8 案例研究:全球创新网络和分析(GINA)
- 大数据学习——高可用配置案例
- 《数据科学R语言实践:面向计算推理与问题求解的案例研究法》一一导读
- Flink技术灵活使用总结(四)常见案例代码
- Vue学习第21天——插槽slot的理解及案例
- Java多线程基础(一)---Thread API(join深度详解、源码分析和案例分析之代码实现,优雅关闭线程三种方式)
- final修饰和StringBuffer的几个案例(拼接,反转,对称操作)