Sqoop导入Hive中mergekey实践

1. 创建hive表merge_test

   beeline -u jdbc:hive2://192.168.1.101:10000 -n hive -e "
create table if not exists default.merge_test (
id int,
name String,
age int,
last_modify_time String
)ROW FORMAT delimited fields terminated by '\t'
STORED AS TEXTFILE;
"

   

2. sqoop导入数据
   merge-key参数是进行了一次完整的mapreduce操作

   sqoop import \
--connect "jdbc:mysql://192.168.1.101:3306/testdb" \
--username "root" \
--password "123456" \
--table "test" \
--columns "id,name,age,last_modify_time" \
--target-dir "/user/hive/warehouse/merge_test" \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-drop-import-delims \
--incremental lastmodified \
--merge-key id \
--check-column last_modify_time \
--last-value "2019-03-19" \
--m 1

   

运行shell脚本

#!/bin/bash

# ********************************************************************************
# 程序名称: merge_test
# 功能描述: 测试数据导出merge
# 输入参数:
#
# 输入资源:
# 输出资源:
#
# 中间资源:
# 创建人员: Charles
# 创建日期: 2019-03-20
# 版本说明: v1.0
# 修改人员:
# 修改日期:
# 修改原因:
# 版本说明:
# ********************************************************************************

#创建hive表
SQL="
create table if not exists default.merge_test (
id int,
name String,
age int,
last_modify_time String
)ROW FORMAT delimited fields terminated by '\t'
STORED AS TEXTFILE;
"

HIVE2_SERVER="192.168.1.101:10000";
HADOOP_NAME="hive";
beeline -u jdbc:hive2://${HIVE2_SERVER} -n ${HADOOP_NAME} -e "${SQL}"

#数据库配置信息
mysql_ip="192.168.1.101:3306"
mysql_database="hft"
mysql_cOnnect="jdbc:mysql://${mysql_ip}/${mysql_database}"
mysql_username="root"
mysql_pwd="hd123456"

#sqoop导入数据
echo "sqoop import start..."

table_name="test"
target_dir="/user/hive/warehouse/merge_test"
target_columns="id,name,age,last_modify_time"

sqoop import \
--connect "$mysql_connect" \
--username "$mysql_username" \
--password "$mysql_pwd" \
--table "$table_name" \
--columns "$target_columns" \
--target-dir "$target_dir" \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-drop-import-delims \
--incremental lastmodified \
--merge-key id \
--check-column last_modify_time \
--last-value "2019-03-19" \
--m 1
echo "sqoop end..."