Sqoop+Hive+MySQL怎么配置用户某时间范围

共计 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 小编将为大家推送更多相关知识点的文章，欢迎关注！