共计 2340 个字符，预计需要花费 6 分钟才能阅读完成。
这篇文章主要讲解了“Sqoop+Hive+MySQL 怎么配置用户某时间范围”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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
退出保存(: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 怎么配置用户某时间范围这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
