这篇文章主要讲解了“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怎么配置用户某时间范围这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！