一、建立配置檔案
【1、建立table_env檔案，增加如下內容】
執行命令：vi table_env
內容：

點選(此處)摺疊或開啟

city_info=default.city_info
product_info=default.product_info
user_click=default.tmp_user_click

退出儲存(:wq)
注：等號前為RDBMS表，等號後為Hive表

二、從MySQL將資料匯入到Hive表中，並進行資料處理
【1、編寫匯入指令碼：建立mysql_to_hive.sh檔案，增加如下內容】
執行命令：vi mysql_to_hive.sh
內容：

點選(此處)摺疊或開啟

#!/usr/bin/env bash
source /app/works/user_env
if [ $# != 2 ] ; then
echo "USAGE: $0 資料庫表 Hive表"
echo " e.g.: $0 'city_info' 'default.city_info'"
echo " e.g.: $0 'product_info' 'default.product_info'"
echo " e.g.: $0 'user_click' 'default.tmp_user_click'"
exit 1;
fi
### 匯入
sqoop import --connect "${DB_VALUES}" \
--username ${DB_USER} \
--password-file /input/sqoop.pwd \
--table $1 \
--delete-target-dir \
--fields-terminated-by "${INPUT_FIELDS_TERMINATED}" \
--hive-import \
--hive-overwrite \
--hive-table $2 \
-m 1

退出儲存(:wq)

執行命令：chmod 775 mysql_to_hive.sh

【2、編寫處理資料指令碼：建立hive_user_click.hql檔案，增加如下內容】
執行命令：vi hive_user_click.hql
內容：

點選(此處)摺疊或開啟

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table user_click partition(action_time)
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
內容：

點選(此處)摺疊或開啟

#!/usr/bin/env bash
source /app/works/user_env
FILENAME=/app/works/table_env
cat $FILENAME | while read LINE
do
db_table_name=`echo $LINE | cut -d \= -f 1`
hive_table_name=`echo $LINE | cut -d \= -f 2`
echo "***************************************************"
echo "匯入${hive_table_name}資訊開始"
echo "***************************************************"
sh /app/works/mysql_to_hive.sh ${db_table_name} ${hive_table_name}
done
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
內容：

點選(此處)摺疊或開啟

export DB_IP_ADD=192.168.137.130
export DB_PROT=3306
export DB_DIRV=jdbc:mysql
export DB_NAME=works
export DB_USER=root
export DB_VALUES="jdbc:mysql://192.168.137.130:3306/works?useUnicode=true&characterEncoding=utf-8"
export INPUT_FIELDS_TERMINATED='\t'
export HDFS_PATH='/works/tmp/'

退出儲存(:wq)

【2、建立HQL指令碼檔案：建立user_click.hql檔案，增加如下內容】
執行命令：vi user_click.hql
內容：
點選(此處)摺疊或開啟

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
內容：

點選(此處)摺疊或開啟

#!/usr/bin/env bash
source /app/works/user_env
if [ $# != 1 ] ; then
echo "USAGE: $0 日期引數"
echo " e.g.: $0 '2018-01-01'"
exit 1;
fi
### 刪除資料庫中特定資料
sqoop eval \
--connect "${DB_VALUES}" \
--username ${DB_USER} \
--password-file /input/sqoop.pwd \
--query "delete from user_click_rs where sdate='$1'"
### 匯出hive表中資料到HDFS
hive -hivevar action_time=$1 -hivevar field_term=${INPUT_FIELDS_TERMINATED} -hivevar hdfs_path=${HDFS_PATH} -f /app/works/user_click.hql
### 匯出HDFS資料到資料庫中
sqoop export --connect "${DB_VALUES}" \
--username ${DB_USER} \
--password-file /input/sqoop.pwd \
--table user_click_rs \
--export-dir ${HDFS_PATH} \
--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資料庫
點選(此處)摺疊或開啟

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';

若澤大資料交流群：671914634

【Sqoop+Hive+MySQL】使用者某時間範圍，區域最受歡迎的Top N的產品

相關文章