小程序游戏如何改变传统游戏市场的格局与未来发展方向
324
2023-12-05
这篇文章主要讲解了“Sqoop+Hive+MySQL怎么配置用户某时间范围”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“Sqoop+Hive+MySQL怎么配置用户某时间范围”吧!
一、创建配置文件
【1、创建table_env文件,增加如下内容】执行命令:vi table_env
内容:
此处)折叠或打开
insert overwrite directory ${hivevar:hdfs_path} ROW FORMAT DELIMITED FIELDS TERMINATED BY ${hivevar:field_term} select * from (select c.area,p.product_name,
sum(rn) visit_num,
row_number()over(partition by c.area order by sum(rn) desc) rn,
${hivevar:action_time} action_time
from city_info c join (
select product_id,city_id,count(1) rn from user_click where action_time=${hivevar:action_time} group by product_id,city_id
) u join product_info p
on c.city_id = case when u.city_id is null then concat(cityid,rand()) else u.city_id end
and p.product_id = case when u.product_id is null then concat(prodid,rand()) else u.product_id end
group by c.area,p.product_name) a
where a.rn <= 3;
退出保存(:wq)
【3、创建从Hive导出脚本:创建hive_to_mysql.sh文件,增加如下内容】执行命令:vi hive_to_mysql.sh
内容:
此处)折叠或打开
create database works DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
1.2、创建表
点击(此处)折叠或打开
--城市表
create table city_info(
city_id int,
city_name varchar(255),
area varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--产品表
create table product_info(
product_id int,
product_name varchar(50),
extend_info varchar(100)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 用户点击表
create table user_click(
user_id int,
session_id varchar(50),
action_time varchar(30),
city_id int(11),
product_id int(11)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--统考结果表
create table user_click_rs(
area varchar(50),
product_name varchar(50),
visit_num int(11),
rn int(11),
sdate varchar(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
【2、Hive信息】
创建HIve表点击(此处)折叠或打开
--用户点击表
create external table user_click(
user_id string,
session_id string,
city_id string,
product_id string
)
partitioned by (action_time string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
location /works/user_click;
--用户点击临时表
create external table tmp_user_click(
user_id string,
session_id string,
action_time string,
city_id string,
product_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
location /works/tmp_user_click;
--城市表
create external table city_info(
city_id int,
city_name string,
area string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
location /works/city_info;
--产品表
create external table product_info(
product_id int,
product_name string,
extend_info string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
location /works/product_info;
感谢各位的阅读,以上就是“Sqoop+Hive+MySQL怎么配置用户某时间范围”的内容了,经过本文的学习后,相信大家对Sqoop+Hive+MySQL怎么配置用户某时间范围这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是,小编将为大家推送更多相关知识点的文章,欢迎关注!
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~