【Sqoop+Hive+MySQL】用户某时间范围，区域最受欢迎的Top N的产品

  • loveheping

    2018-01-20 23:34:21
  • 数据分析
  • 原创
一、创建配置文件
【1、创建table_env文件，增加如下内容】
执行命令：vi table_env
内容：

点击(此处)折叠或打开

  1. city_info=default.city_info
  2. product_info=default.product_info
  3. user_click=default.tmp_user_click
退出保存(:wq)
注：等号前为RDBMS表，等号后为Hive表

二、从MySQL将数据导入到Hive表中，并进行数据处理
【1、编写导入脚本：创建mysql_to_hive.sh文件，增加如下内容】
执行命令：vi mysql_to_hive.sh
内容：

点击(此处)折叠或打开

  1. #!/usr/bin/env bash

  3. source /app/works/user_env

  5. if [ $# != 2 ] ; then
  6. echo "USAGE: $0 数据库表 Hive表"
  7. echo " e.g.: $0 'city_info' 'default.city_info'"
  8. echo " e.g.: $0 'product_info' 'default.product_info'"
  9. echo " e.g.: $0 'user_click' 'default.tmp_user_click'"
  10. exit 1;
  11. fi
  14. ### 导入
  15. sqoop import --connect "${DB_VALUES}" \
  16. --username ${DB_USER} \
  17. --password-file /input/sqoop.pwd \
  18. --table $1 \
  19. --delete-target-dir \
  20. --fields-terminated-by "${INPUT_FIELDS_TERMINATED}" \
  21. --hive-import \
  22. --hive-overwrite \
  23. --hive-table $2 \
  24. -m 1
退出保存(:wq)

执行命令：chmod 775 mysql_to_hive.sh

【2、编写处理数据脚本：创建hive_user_click.hql文件，增加如下内容】
执行命令：vi  hive_user_click.hql
内容：

点击(此处)折叠或打开

  1. set hive.exec.dynamic.partition.mode=nonstrict;
  2. insert overwrite table user_click partition(action_time)
  3. select user_id,session_id,city_id,product_id,substring(action_time,1,10) action_time from tmp_user_click distribute by action_time
退出保存(:wq)

【3、编写循环组装处理数据脚本：创建import_sqoop_while.sh文件，增加如下内容】
执行命令：vi import_sqoop_while.sh
内容：

点击(此处)折叠或打开

  1. #!/usr/bin/env bash
  3. source /app/works/user_env
  4. FILENAME=/app/works/table_env
  6. cat $FILENAME | while read LINE
  7. do
  8. db_table_name=`echo $LINE | cut -d \= -f 1`
  9. hive_table_name=`echo $LINE | cut -d \= -f 2`
  11. echo "***************************************************"
  12. echo "导入${hive_table_name}信息开始"
  13. echo "***************************************************"
  16. sh /app/works/mysql_to_hive.sh ${db_table_name} ${hive_table_name}
  19. done
  22. hive -f hive_user_click.hql
退出保存(:wq)

执行命令：chmod 775 import_sqoop_while.sh

【4、执行脚本】
./import_sqoop_while.sh

三、从Hive将数据导入到MySQL表中，并进行数据处理
【1、创建配置文件：创建user_env文件，增加如下内容】
执行命令：vi user_env
内容：

点击(此处)折叠或打开

  1. export DB_IP_ADD=192.168.137.130
  2. export DB_PROT=3306
  3. export DB_DIRV=jdbc:mysql
  4. export DB_NAME=works
  5. export DB_USER=root
  6. export DB_VALUES="jdbc:mysql://192.168.137.130:3306/works?useUnicode=true&characterEncoding=utf-8"
  7. export INPUT_FIELDS_TERMINATED='\t'
  8. export HDFS_PATH='/works/tmp/'
退出保存(:wq)

【2、创建HQL脚本文件：创建user_click.hql文件，增加如下内容】
执行命令：vi user_click.hql
内容：
点击(此处)折叠或打开
  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、创建从Hive导出脚本：创建hive_to_mysql.sh文件，增加如下内容】
执行命令：vi hive_to_mysql.sh
内容：

点击(此处)折叠或打开

  1. #!/usr/bin/env bash
  3. source /app/works/user_env
  5. if [ $# != 1 ] ; then
  6. echo "USAGE: $0 日期参数"
  7. echo " e.g.: $0 '2018-01-01'"
  8. exit 1;
  9. fi
  11. ### 删除数据库中特定数据
  12. sqoop eval \
  13. --connect "${DB_VALUES}" \
  14. --username ${DB_USER} \
  15. --password-file /input/sqoop.pwd \
  16. --query "delete from user_click_rs where sdate='$1'"
  18. ### 导出hive表中数据到HDFS
  19. hive -hivevar action_time=$1 -hivevar field_term=${INPUT_FIELDS_TERMINATED} -hivevar hdfs_path=${HDFS_PATH} -f /app/works/user_click.hql
  21. ### 导出HDFS数据到数据库中
  22. sqoop export --connect "${DB_VALUES}" \
  23. --username ${DB_USER} \
  24. --password-file /input/sqoop.pwd \
  25. --table user_click_rs \
  26. --export-dir ${HDFS_PATH} \
  27. --input-fields-terminated-by "${INPUT_FIELDS_TERMINATED}"
退出保存(:wq)

执行命令：chmod 775 hive_to_mysql.sh

【4、执行脚本】
./hive_to_mysql.sh '2016-05-05'

四、测试需要信息
【1、MySQL信息】
1.1、创建MySQL数据库
点击(此处)折叠或打开
  1. create database works DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
1.2、创建表

点击(此处)折叠或打开

  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. --产品表
  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;

  15. -- 用户点击表
  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;

  24. --统考结果表
  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信息】
创建HIve表

点击(此处)折叠或打开

  1. --用户点击表
  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';
  12. --用户点击临时表
  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';
  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';
  32. --产品表
  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';



若泽大数据交流群：671914634
请使用浏览器的分享功能分享到微信等