Sqoop+Hive+MySQL怎么配置用戶某時(shí)間范圍

這篇文章主要講解了“Sqoop+Hive+MySQL怎么配置用戶某時(shí)間范圍”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Sqoop+Hive+MySQL怎么配置用戶某時(shí)間范圍”吧！

一、創(chuàng)建配置文件
【1、創(chuàng)建table_env文件，增加如下內(nèi)容】
執(zhí)行命令：vi table_env
內(nèi)容：

此處)折疊或打開

1. insert overwrite directory '${hivevar:hdfs_path}' ROW FORMAT DELIMITED FIELDS TERMINATED BY '${hivevar:field_term}' select * from (select c.area,p.product_name,
   

2.        sum(rn) visit_num,
   

3.        row_number()over(partition by c.area order by sum(rn) desc) rn,
   

4.         '${hivevar:action_time}' action_time
   

5.   from city_info c join (
   

6.          select product_id,city_id,count(1) rn from user_click where action_time='${hivevar:action_time}' group by product_id,city_id
   

7.        ) u join product_info p
   

8.    on c.city_id = case when u.city_id is null then concat('cityid',rand()) else u.city_id end
   

9.   and p.product_id = case when u.product_id is null then concat('prodid',rand()) else u.product_id end
   

10.  group by c.area,p.product_name) a
    

11. where a.rn <= 3;

退出保存(:wq)

【3、創(chuàng)建從Hive導(dǎo)出腳本：創(chuàng)建hive_to_mysql.sh文件，增加如下內(nèi)容】
執(zhí)行命令：vi hive_to_mysql.sh
內(nèi)容：

此處)折疊或打開

1. create database works DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

1.2、創(chuàng)建表

點(diǎn)擊(此處)折疊或打開

1. --城市表
   

2. create table city_info(
   

3. city_id int,
   

4. city_name varchar(255),
   

5. area varchar(255)
   

6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

7.  
   

8. --產(chǎn)品表
   

9. create table product_info(
   

10. product_id int,
    

11. product_name varchar(50),
    

12. extend_info varchar(100)
    

13. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

14. 
    

15. -- 用戶點(diǎn)擊表
    

16. create table user_click(
    

17. user_id int,
    

18. session_id varchar(50),
    

19. action_time varchar(30),
    

20. city_id int(11),
    

21. product_id int(11)
    

22. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

23. 
    

24. --統(tǒng)考結(jié)果表
    

25. create table user_click_rs(
    

26. area varchar(50),
    

27. product_name varchar(50),
    

28. visit_num int(11),
    

29. rn int(11),
    

30. sdate varchar(10)
    

31. )ENGINE=InnoDB DEFAULT CHARSET=utf8;

【2、Hive信息】
創(chuàng)建HIve表

點(diǎn)擊(此處)折疊或打開

1. --用戶點(diǎn)擊表

2. create external table user_click(

3. user_id string,

4. session_id string,

5. city_id string,

6. product_id string

7. )

8. partitioned by (action_time string)

9. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

10. location '/works/user_click';

11. 
    

12. --用戶點(diǎn)擊臨時(shí)表

13. create external table tmp_user_click(

14. user_id string,

15. session_id string,

16. action_time string,

17. city_id string,

18. product_id string

19. )

20. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

21. location '/works/tmp_user_click';

22. 
    

23. --城市表

24. create external table city_info(

25. city_id int,

26. city_name string,

27. area string

28. )

29. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

30. location '/works/city_info';

31. 
    

32. --產(chǎn)品表

33. create external table product_info(

34. product_id int,

35. product_name string,

36. extend_info string

37. )

38. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

39. location '/works/product_info';

感謝各位的閱讀，以上就是“Sqoop+Hive+MySQL怎么配置用戶某時(shí)間范圍”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)Sqoop+Hive+MySQL怎么配置用戶某時(shí)間范圍這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！