Hive实战

实战案例1——数据ETL

1.1 需求

对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     |          |
+------------------+------------+----------+--+

1.2 数据示例

| 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.3 实现步骤

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;

实战案例2——访问时长统计

2.1 需求

    从web日志中统计每日访客平均停留时间

2.2 实现步骤

1、  由于要从大量请求中分辨出用户的各次访问,逻辑相对复杂,通过hive直接实现有困难,因此编写一个mr程序来求出访客访问信息(详见代码)

启动mr程序获取结果:

[hadoop@hdp-node-01 ~]$ hadoop jar weblog.jar cn.itcast.bigdata.hive.mr.UserStayTime /weblog/input /weblog/stayout

2、  将mr的处理结果导入hive表

drop table t_display_access_info_tmp;
create table t_display_access_info_tmp(remote_addr string,firt_req_time string,last_req_time string,stay_long bigint)
row format delimited fields terminated by '\t';
 
load data inpath '/weblog/stayout4' into table t_display_access_info_tmp;

3、得出访客访问信息表"t_display_access_info"

由于有一些访问记录是单条记录,mr程序处理处的结果给的时长是0,所以考虑给单次请求的停留时间一个默认市场30秒

drop table t_display_access_info;
create table t_display_access_info as
select remote_addr,firt_req_time,last_req_time,
case stay_long
when 0 then 30000
else stay_long
end as stay_long
from t_display_access_info_tmp;

4、统计所有用户停留时间平均值

select avg(stay_long) fromt_display_access_info;

实战案例3——级联求和---常见的累加报表

3.1 需求

有如下访客访问次数统计表 t_access_times

访客

月份

访问次数

A

2015-01

5

A

2015-01

15

B

2015-01

5

A

2015-01

8

B

2015-01

25

A

2015-01

5

A

2015-02

4

A

2015-02

6

B

2015-02

10

B

2015-02

5

……

……

……

需要输出报表:t_access_times_accumulate

访客

月份

月访问总计

累计访问总计

A

2015-01

33

33

A

2015-02

10

43

…….

…….

…….

…….

B

2015-01

30

30

B

2015-02

15

45

…….

…….

…….

…….

3.2 实现步骤 

可以用一个hql语句即可实现:
思路:月访问:group by 月份再累加访问次数 
         累计访问:两个表自己连自己做笛卡尔积 然后过滤只有用户A-用户A的数据 用户B连用户B的数据,形成数据类似
    left       right
A 1 10   A 1 10
A 1 10   A 2 5
A 1 10   A 3 10
A 2 5   A 1 10
A 2 5    A 2 5
A 2 5    A 3 10
将如上数据按月分组 同时根据数据 右边的月份<=左边的数据 进行累加
sum (right.money) where right.month <= left.month

group by left.month

建表:

create table t_access_times(username string,month string,salary int)
row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/t_access_times.dat' into table t_access_times;

检查数据和思路的SQL语句

select username,month,sum(salary) as salary from t_access_times group by username,month

检查数据和思路的SQL语句

select A.* B.* From
(select username,month,sum(salary) as salary from t_access_times group by username,month) A
inner join
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on

A.username=B.username

select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from
(select username,month,sum(salary) as salary from t_access_times group by username,month) A
inner join
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;

总结:

create table t_access_times(username string,month string,salary int)
row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/t_access_times.dat' into table t_access_times;
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
1、第一步,先求个用户的月总金额
select username,month,sum(salary) as salary from t_access_times group by username,month
+-----------+----------+---------+--+
| username  |  month   | salary  |
+-----------+----------+---------+--+
| A         | 2015-01  | 33      |
| A         | 2015-02  | 10      |
| B         | 2015-01  | 30      |
| B         | 2015-02  | 15      |
+-----------+----------+---------+--+

2、第二步,将月总金额表 自己连接 自己连接
+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username  | a.month  | a.salary  | b.username  | b.month  | b.salary  |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A           | 2015-01  | 33        | A           | 2015-01  | 33        |
| A           | 2015-01  | 33        | A           | 2015-02  | 10        |
| A           | 2015-02  | 10        | A           | 2015-01  | 33        |
| A           | 2015-02  | 10        | A           | 2015-02  | 10        |
| B           | 2015-01  | 30        | B           | 2015-01  | 30        |
| B           | 2015-01  | 30        | B           | 2015-02  | 15        |
| B           | 2015-02  | 15        | B           | 2015-01  | 30        |
| B           | 2015-02  | 15        | B           | 2015-02  | 15        |
+-------------+----------+-----------+-------------+----------+-----------+--+
max(a.salary不在分组字段,需要使用聚合函数 sum、max) group by (a.username, a.month 两个分组字段) 
3、第三步,从上一步的结果中
进行分组查询,分组的字段是a.username a.month
求月累计值:  将b.month <= a.month的所有b.salary求和即可
select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from 
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month//比如右边的必须<=左边的进行求和
group by A.username,A.month
order by A.username,A.month;

<think>好的,用户想寻找关于Hive的实际应用案例或操作指南,特别是实战教程或应用场景。首先,我需要整理Hive常见的应用场景,比如日志分析、数据仓库构建等。然后,结合用户提供的引用内容,看看是否有相关的操作步骤或配置可以引用。 用户提供的引用中,引用[1]提到配置Hive的环境文件,引用[2]涉及查看操作历史,引用[3]是日志配置,引用[4]是推测执行机制。这些可以作为操作指南的一部分,比如在数据清洗或查询优化时调整配置。需要将这些引用自然地融入到回答中,例如在数据清洗步骤中建议配置日志以便追踪,或者在优化查询时启用推测执行。 接下来,思考实际应用案例。比如电商用户行为分析,可以分步骤说明如何创建表、加载数据、进行聚合查询和留存分析。每个步骤需要给出具体的HiveQL示例,并注意使用正确的LaTeX格式,如变量名用$符号包裹。同时,在操作指南部分,可以引用用户提供的配置步骤,如配置hive-env.sh或日志文件,以增强实用性。 还需要生成相关问题,这些问题应该围绕Hive的优化、配置、与其他工具的整合等,帮助用户深入探索。确保问题具体且有针对性,比如性能调优方法或Hive分区表的使用场景。 最后,检查回答是否符合系统级指令:行内公式用$...$,独立公式用$$...$$,正确的中文回答,引用标识正确添加。确保所有技术细节准确,并且引用来源与内容匹配。</think>### Hive 实际应用场景与操作指南 #### 一、Hive 典型应用场景 1. **日志分析** Hive 常用于处理大规模日志数据(如网站点击流、服务器日志)。例如,通过加载日志文件到 Hive 表后,可执行聚合查询统计每日活跃用户数(DAU)或页面访问量(PV)。 ```sql SELECT date, COUNT(DISTINCT user_id) AS dau FROM user_logs GROUP BY date; ``` 2. **数据仓库构建** 在数据仓库中,Hive 支持通过分区表(Partitioned Table)和分桶表(Bucketed Table)优化查询性能。例如按日期分区存储订单数据: ```sql CREATE TABLE orders ( order_id STRING, amount DOUBLE ) PARTITIONED BY (dt STRING); ``` 3. **ETL 数据清洗** 使用 Hive SQL 对原始数据进行清洗,例如过滤无效记录、填充缺失值: ```sql INSERT OVERWRITE TABLE cleaned_data SELECT user_id, COALESCE(ip, '0.0.0.0') FROM raw_data WHERE user_id IS NOT NULL; ``` #### 二、Hive 实战操作指南 1. **环境配置与日志管理** - 配置 `hive-env.sh` 文件以设置内存参数(参考引用[1]): ```bash cd /opt/hive-2.3.3/conf cp hive-env.sh.template hive-env.sh vi hive-env.sh # 修改 HADOOP_HEAPSIZE=2048 ``` - 日志文件配置(参考引用[3]): ```bash cp hive-log4j2.properties.template hive-log4j2.properties cp hive-exec-log4j2.properties.template hive-exec-log4j2.properties ``` 2. **查询优化技巧** - **启用推测执行**:避免因个别慢任务拖累整体作业(参考引用[4]): ```sql SET mapreduce.map.speculative=true; SET mapreduce.reduce.speculative=true; ``` - **使用 Tez 引擎**:替换 MapReduce 以加速查询: ```sql SET hive.execution.engine=tez; ``` 3. **实际案例:电商用户行为分析** **步骤 1:创建用户行为表** ```sql CREATE TABLE user_actions ( user_id STRING, action_time TIMESTAMP, page_url STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; ``` **步骤 2:加载数据** ```sql LOAD DATA INPATH '/user/input/actions.log' INTO TABLE user_actions; ``` **步骤 3:统计热门页面** ```sql SELECT page_url, COUNT(*) AS visit_count FROM user_actions GROUP BY page_url ORDER BY visit_count DESC LIMIT 10; ``` **步骤 4:计算用户留存率** ```sql SELECT first_day, COUNT(DISTINCT a.user_id) AS retained_users, COUNT(DISTINCT a.user_id)/COUNT(DISTINCT b.user_id) AS retention_rate FROM ( SELECT user_id, MIN(action_time) AS first_day FROM user_actions GROUP BY user_id ) a JOIN user_actions b ON a.user_id = b.user_id WHERE DATEDIFF(b.action_time, a.first_day) = 7 GROUP BY first_day; ``` #### 三、操作历史追踪(参考引用[2]) 通过查看 `.hivehistory` 文件可回溯执行过的命令: ```bash cat /home/hadoop/.hivehistory ``` --- §§ 1. Hive 如何通过分区表提升查询性能? 2. 如何通过 Hive 实现与 HBase 的数据集成? 3. Hive 在数据倾斜场景下的优化方法有哪些? 4. Hive on Spark 的配置步骤是什么?
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值