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

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