目录
业务场景
场景一. 业务场景中存在需要将业务数据存放到hive中做数据BI统计等情形。
场景二. hive数据统计分析后的最终结果需要转存到mysql中以便展示到用户端。
解决方案
我们可以使用sqoop工具，将业务数据库mysql或者oracle中的数据落地到hive表中，以方便后续的大数据统计分析。
具体步骤
说明：mysql数据库或者oracle数据的安装大人们可以自己搜索资料了；
hive和hadoop的安装：
Windows10 安装Hadoop3.3.0_xieedeni的博客-CSDN博客
Windows10安装Hive3.1.2_xieedeni的博客-CSDN博客
一. 下载安装sqoop
1.下载
下载地址：Index of /dist/sqoop
这里注意版本：
sqoop版本为：sqoop1和sqoop2，具体这俩的区别大人们可以自行搜索下资料。
sqoop1为版本1.4.7以下版本，sqoop2为1.99.1以上版本，最新为1.99.7版。
这里我下载使用的是1.4.7版：Index of /dist/sqoop/1.4.7
2.配置环境变量
这里我使用windows环境安装使用
解压后，配置环境变量SQOOP_HOME=sqoop解压后地址，path增加新配置%SQOOP_HOME%/bin
3.修改sqoop配置文件
复制文件%SQOOP_HOME%/conf下的sqoop-env-template.sh 命名为sqoop-env.sh
修改%SQOOP_HOME%/conf/sqoop-env.sh
# Set Hadoop-specific environment variables here.
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=D:\work\soft\hadoop-3.3.0
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=D:\work\soft\hadoop-3.3.0
#set the path to where bin/hbase is available
#export HBASE_HOME=
#Set the path to where bin/hive is available
export HIVE_HOME=D:\work\soft\apache-hive-3.1.2-bin
export HIVE_CONF_DIR=D:/work/soft/apache-hive-3.1.2-bin/conf
#Set the path for where zookeper config dir is
#export ZOOCFGDIR=4.mysql-connector-java-8.0.x.jar
下载和拷贝一个mysql-connector-java-8.0.x.jar到%SQOOP_HOME%/lib目录下：
mysql-connector-java-8.0.21.jar
下载地址：https://dev.mysql.com/downloads/file/?id=496589
二.sqoop连接测试
1.version测试
sqoop version
2.连接数据库测试
sqoop list-databases --connect jdbc:mysql://127.0.0.1:3306/mydb --username root --password 123456
输出了表信息，成功了。
三. 使用sqoop将mysql数据落地到hive表
1.全量导入
sqoop import --connect jdbc:mysql://127.0.0.1:3306/ddbi --username root --password 123456 --table behavior --hive-import --hive-database=dd_database_bigdata --hive-table dwd_base_event_log_his --m 1 --input-null-string '\\N' --input-null-non-string '\\N'这里的input-null-string和input-null-non-string是出来mysql中的空字符串字段
成功了
进行hive查询
select * from tablename where id = 1;
hive需不需要提交建表呢？其实可以不用建，因为再导入时会创建
2.增量导入
sqoop import --connect jdbc:mysql://127.0.0.1:3306/ddbi --username root --password 123456 --table behavior --hive-import --hive-database dd_database_bigdata --hive-table dwd_base_event_log_his --m 1 --incremental append --check-column id --last-value 124870 --input-null-string '\\N' --input-null-non-string '\\N'
更新成功
3.增量导入job
a.创建增量抽取的job
sqoop job --create fdc_equipment_job \
-- import --connect jdbc:oracle:thin:@xx.xx.xx.xx:1521:xx \
--username xxx--password xxx\
--table PROD_FDC.EQUIPMENT \
--target-dir=/user/hive/warehouse/fdc_test.db/equipment \
--hive-import --hive-database fdc_test --hive-table equipment \
--incremental append \
--check-column equipmentid --last-value 1893
说明：增量抽取，需要指定--incremental append，同时指定按照源表中哪个pk字段进行增量--check-column equipmentid，并指定hive表中pk当前最大值--last-value 1893。创建sqoop job的目的是，每次执行job以后，sqoop会自动记录pk的last-value，下次再执行时，就会自动指定last-value，不需要手工去改了。
b.执行sqoop job
sqoop job --exec fdc_equipment_job
c.删除sqoop job
sqoop job --delete fdc_equipment_job
d.查看sqoop job
sqoop job --show sqoop_job_order
sqoop job --create sqoop_job_behavior_his -- import --connect jdbc:mysql://127.0.0.1:3306/ddbi --username root --password 123456--table behavior --hive-import --hive-database dd_database_bigdata --hive-table dwd_base_event_log_his --incremental append --check-column id --last-value 125357 --m 1 --input-null-string '\\N' --input-null-non-string '\\N'
sqoop job --exec sqoop_job_behavior_his
小结
sqoop 导入import和导出export命令参数
通用通用参数
选项 含义说明
–connect 指定JDBC连接字符串
–connection-manager 指定要使用的连接管理器类
–driver 指定要使用的JDBC驱动类
–hadoop-mapred-home 指定$HADOOP_MAPRED_HOME路径
–help 打印用法帮助信息
–password-file 设置用于存放认证的密码信息文件的路径
-P 从控制台读取输入的密码
–password 设置认证密码
–username 设置认证用户名
–verbose 打印详细的运行信息
–connection-param-file 可选，指定存储数据库连接参数的属性文件
import
选项 含义说明
–append 将数据追加到HDFS上一个已存在的数据集上
–as-avrodatafile 将数据导入到Avro数据文件
–as-sequencefile 将数据导入到SequenceFile
–as-textfile 将数据导入到普通文本文件（默认）
–boundary-query 边界查询，用于创建分片（InputSplit）
–columns <col,col,col…> 从表中导出指定的一组列的数据
–delete-target-dir 如果指定目录存在，则先删除掉
–direct 使用直接导入模式（优化导入速度）
–direct-split-size 分割输入stream的字节大小（在直接导入模式下）
–fetch-size 从数据库中批量读取记录数
–inline-lob-limit 设置内联的LOB对象的大小
-m,–num-mappers 使用n个map任务并行导入数据
-e,–query 导入的查询语句
–split-by 指定按照哪个列去分割数据
–table 导入的源表表名
–target-dir 导入HDFS的目标路径
–warehouse-dir HDFS存放表的根路径
–where 指定导出时所使用的查询条件
-z,–compress 启用压缩
–compression-codec 指定Hadoop的codec方式（默认gzip）
–null-string 果指定列为字符串类型，使用指定字符串替换值为null的该类列的值
–null-non-string <null-string 如果指定列为非字符串类型，使用指定字符串替换值为null的该类列的值
--create-hive-table 如果Hive表不存在，则自动创建；如果以及存在，则会报错
--hive-drop-import-delims 导入到Hive时，删除原数据中包含的 \n, \r,\01字符。
--hive-delims-replacement 导入到Hive时，将原数据中的\n, \r, \01 替换成自定义的字符。
--hive-partition-key 指定Hive表的分区字段。
--hive-partition-value <v> 指定导入Hive表的分区字段的值。
--map-column-hive <map> 设置导入Hive时，指定字段的数据类型。如设置ID为S听类型：--map-column-hive ID=String
export
选项 含义说明
–validate 启用数据副本验证功能，仅支持单表拷贝，可以指定验证使用的实现类
–validation-threshold 指定验证门限所使用的类
–direct 使用直接导出模式（优化速度）
–export-dir
导出过程中HDFS源路径
-m,–num-mappers 使用n个map任务并行导出
–table 导出的目的表名称
–call 导出数据调用的指定存储过程名
–update-key 更新参考的列名称，多个列名使用逗号分隔
–update-mode 指定更新策略，包括：updateonly（默认）、allowinsert
–input-null-string 使用指定字符串，替换字符串类型值为null的列
–input-null-non-string 使用指定字符串，替换非字符串类型值为null的列
–staging-table 在数据导出到数据库之前，数据临时存放的表名称
–clear-staging-table 清除工作区中临时存放的数据
–batch 使用批量模式导出例子1 sqoop import
#!/bin/bash
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
#do_date=$(date -d "-1 day" +%F)
if [ -n "$1" ]; then
do_date=$1
else
do_date=$(date -d "-1 day" +%F)
fi
jdbc_url_dduser="jdbc:mysql://xxx:3306/user?serverTimezone=Asia/Shanghai&characterEncoding=utf8&tinyInt1isBit=false"
jdbc_username=root
jdbc_password=123456
echo "===开始从mysql中提取业务数据日期为 $do_date 的数据==="
#sqoop-mysql2hive-appconfig
sqoop import --connect $jdbc_url_dduser --username $jdbc_username --password $jdbc_password --table app_config --hive-overwrite --hive-import --hive-table dd_database_bigdata.ods_app_config --target-dir /warehouse/dd/bigdata/ods/tmp/ods_app_config -m 1 --input-null-string '\\N' --input-null-non-string '\\N'
#sqoop-mysql2hive-content
sqoop import --connect $jdbc_url_ddresource --username $jdbc_username --password $jdbc_password --query "select n_id,u_id,u_app,app_id,global_id,nm_id,n_type,n_title,n_category,n_source,n_publish_time,n_create_time from news where DATE_FORMAT(n_create_time,'%Y-%m-%d')='$do_date' and 1=1 and \$CONDITIONS " -m 1 --hive-partition-key dt --hive-partition-value $do_date --target-dir /warehouse/dd/bigdata/ods/tmp/ods_content --hive-overwrite --hive-import --hive-table dd_database_bigdata.ods_content --input-null-string '\\N' --input-null-non-string '\\N'
echo "===从mysql中提取日期为 $do_date 的数据完成==="import时需要注意几点
1.导入到hive表的存储格式需要是textfile格式，注意指定分隔符
2.注意加 -m 1 ，如果不加时，需要指定 --split-by
3.使用--query 时sql where条件语句中必须含有 $CONDITIONS ，此为sqoop的占位符。如果sql使用了引号包裹时，注意转义：\$CONDITIONS 。
4.使用--query时必须加--target-dir 这是因为--query时数据sqoop操作时先存储到hdfs上，这是指定文件临时存放的位置
例子2 sqoop export
#!/bin/bash
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ]; then
do_date=$1
else
do_date=$(date -d "-1 day" +%F)
fi
jdbc_url="jdbc:mysql://xxx:3306/ddbi?serverTimezone=Asia/Shanghai&characterEncoding=utf8"
jdbc_username=root
jdbc_password=123456
echo "===开始从hive结果表中提取数据到mysql日期为 $do_date 的数据==="
echo "===先删除mysql表中日期为 $do_date 的数据==="
sqoop eval --connect $jdbc_url --username $jdbc_username --password $jdbc_password --query "delete from ads_article_share_info where DATE_FORMAT(date_id,'%Y-%m-%d') = '$do_date'"
echo "===完成删除mysql表中日期为 $do_date 的数据==="
echo "===进行hive导入mysql表中日期为 $do_date 的数据==="
sqoop export --connect $jdbc_url --username $jdbc_username --password $jdbc_password --table ads_article_share_info --export-dir /warehouse/dd/bigdata/ads/ads_article_share_info/dt=$do_date --columns "date_id,measure_id,measure_value,biz_id,biz_code,create_time,update_time" --fields-terminated-by '\t' --input-null-string '\\N' --input-null-non-string '\\N'
echo "===完成hive导入mysql表中日期为 $do_date 的数据==="
echo "===完成从hive结果表中提取数据到mysql日期为 $do_date 的数据==="export时需要注意几点
1.需要从hive表导出到关系型数据库时，hive的表存储格式需要是textfile格式，因为导出其实是以文件形式导出的，如果不是此格式，导出时报错，提示not file。
2.export注意指定--fields-terminated-by分隔符，指的是hive表结构的分隔符
3.sqoop导出hive表分区的数据时，--export-dir指定到分区，比如--export-dir /warehouse/dd/bigdata/ads/ads_article_share_info/dt=2021-11-01
4.sqoop导出到mysql时，需要更新数据有以下几种方式：
a.可使用--update-key，指定mysql中检验更新的主键，此时注意，多个时可以逗号分隔，但需要注意此字段最好是主键，字段是not null的。例如 ：--update-mode allowinsert --update-key stat_date,create_date 。使用时需要加--update-mode （allowinsert，updateonly），指定是检验到仅更新还是可进行新增操作。
b.如果需要导出到mysql中的表需要含有null字段的作为唯一确定一行数据的情形时，可先删除，再进行export操作。如例子中的情形。
遇到问题
1.步骤二连接数据库测试时报错Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/lang/StringUtils
2021-09-30 13:55:56,530 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/lang/StringUtils
at org.apache.sqoop.manager.MySQLManager.initOptionDefaults(MySQLManager.java:73)
at org.apache.sqoop.manager.SqlManager.<init>(SqlManager.java:89)
at com.cloudera.sqoop.manager.SqlManager.<init>(SqlManager.java:33)
at org.apache.sqoop.manager.GenericJdbcManager.<init>(GenericJdbcManager.java:51)
at com.cloudera.sqoop.manager.GenericJdbcManager.<init>(GenericJdbcManager.java:30)
at org.apache.sqoop.manager.CatalogQueryManager.<init>(CatalogQueryManager.java:46)
at com.cloudera.sqoop.manager.CatalogQueryManager.<init>(CatalogQueryManager.java:31)
at org.apache.sqoop.manager.InformationSchemaManager.<init>(InformationSchemaManager.java:38)
at com.cloudera.sqoop.manager.InformationSchemaManager.<init>(InformationSchemaManager.java:31)
at org.apache.sqoop.manager.MySQLManager.<init>(MySQLManager.java:65)
at org.apache.sqoop.manager.DefaultManagerFactory.accept(DefaultManagerFactory.java:67)
at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:184)
at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:272)
at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:44)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.lang.StringUtils
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
... 20 more
缺少commons-lang包，这里我们下载并放到%SQOOP%/lib下
http://mirrors.tuna.tsinghua.edu.cn/apache//commons/lang/binaries/commons-lang-2.6-bin.zip
2.mysql导入到hive报错
2021-10-08 15:40:35,682 ERROR hive.HiveConfig: Could not load org.apache.hadoop.hive.conf.HiveConf. Make sure HIVE_CONF_DIR is set correctly.
2021-10-08 15:40:35,687 ERROR tool.ImportTool: Import failed: java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
将$HIVE_HOME/lib下的hive-exec-**.jar 放到sqoop 的lib 下可以解决以下问题。
3.mysql导入到hive报错 HiveConf of name xxx does not exist
应该是没有引用到hive lib下的资源文件，查询了很多资料，所增加环境变量
export HADOOP_CLASSPATH=${HADOOP_CLASSPATH}:/opt/cdh5.7.6/hive-1.1.0-cdh5.7.6/lib/*
这个是linux写法，具体windows怎么做，很疑惑。我尝试将hive lib下的包均放到sqoop lib下，各种尝试后发现不行。最后重新了下hive，莫名成功了。。。。毕竟自己搭建的环境，兼容性没这么好啊。
cd %HIVE_HOME%/bin
hive --service metastore &4.windows下执行import命令后报错java.lang.ClassNotFoundException: Class tablename not found
执行的命令：
sqoop import --connect "jdbc:mysql://xxx:3306/ddbi?serverTimezone=Asia/Shanghai" --username root --password 123456 --table behavior --hive-import --hive-database dd_database_bigdata --hive-table dwd_base_event_log_his --m 1 --input-null-string '\\N' --input-null-non-string '\\N'
//其中，behavior 是mysql下的一个数据表
报错错误信息：
java.lang.Exception: java.lang.RuntimeException: java.lang.ClassNotFoundException: Class behavior not found
错误原因：
因为在使用sqoop import命令时，生成的java文件会默认产生在当前目录下，而产生的.jar文件和.class文件会默认存放在/tmp/sqoop-/compile下，两者不在同一文件目录下，导致错误。所以，我们需要将java文件，.jar文件和.class文件放在同一目录下。
解决方法：
为了使数据不存放在根目录下，将产生的文件放在xx/tmp下，我们需要切换至//tmp目录下
使用如下命令：
cd D:\\tmp
sqoop import --connect "jdbc:mysql://xxx:3306/ddbi?serverTimezone=Asia/Shanghai" --username root --password 123456 --table behavior --hive-import --hive-database dd_database_bigdata --hive-table dwd_base_event_log_his --m 1 --input-null-string '\\N' --input-null-non-string '\\N' --bindir ./注意增加 --bindir ./
官方说明：
--bindir <dir>：指定生成的java文件、编译成的class文件及将生成文件打包为JAR的JAR包文件输出路径
执行后结果如下：
扫一扫
667
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
