共計(jì) 2340 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。
這篇文章主要講解了“Sqoop+Hive+MySQL 怎么配置用戶某時(shí)間范圍”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“Sqoop+Hive+MySQL 怎么配置用戶某時(shí)間范圍”吧！
一、創(chuàng)建配置文件
【1、創(chuàng)建 table_env 文件，增加如下內(nèi)容】
執(zhí)行命令：vi table_env
內(nèi)容：
此處)折疊或打開(kāi)
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、創(chuàng)建從 Hive 導(dǎo)出腳本：創(chuàng)建 hive_to_mysql.sh 文件，增加如下內(nèi)容】
執(zhí)行命令：vi hive_to_mysql.sh
內(nèi)容：
此處)折疊或打開(kāi)
create database works DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
1.2、創(chuàng)建表
點(diǎn)擊 (此處) 折疊或打開(kāi)
– 城市表
create table city_info(
city_id int,
city_name varchar(255),
area varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– 產(chǎn)品表
create table product_info(
product_id int,
product_name varchar(50),
extend_info varchar(100)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
— 用戶點(diǎn)擊表
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;
– 統(tǒng)考結(jié)果表
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 信息】
創(chuàng)建 HIve 表
點(diǎn)擊 (此處) 折疊或打開(kāi)
– 用戶點(diǎn)擊表
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
– 用戶點(diǎn)擊臨時(shí)表
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
– 產(chǎn)品表
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 怎么配置用戶某時(shí)間范圍”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì) Sqoop+Hive+MySQL 怎么配置用戶某時(shí)間范圍這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
