sqoop+mysql+hive收集数据&例子

目录

业务场景

解决方案

具体步骤

一. 下载安装sqoop

二.sqoop连接测试

​

三. 使用sqoop将mysql数据落地到hive表

小结

sqoop 导入import和导出export命令参数

例子1 sqoop import

import时需要注意几点

 例子2 sqoop export

export时需要注意几点

遇到问题

---

业务场景

场景一. 业务场景中存在需要将业务数据存放到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 从表中导出指定的一组列的数据
–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 --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 指定导入Hive表的分区字段的值。
--map-column-hive

例子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.(SqlManager.java:89)
at com.cloudera.sqoop.manager.SqlManager.(SqlManager.java:33)
at org.apache.sqoop.manager.GenericJdbcManager.(GenericJdbcManager.java:51)
at com.cloudera.sqoop.manager.GenericJdbcManager.(GenericJdbcManager.java:30)
at org.apache.sqoop.manager.CatalogQueryManager.(CatalogQueryManager.java:46)
at com.cloudera.sqoop.manager.CatalogQueryManager.(CatalogQueryManager.java:31)
at org.apache.sqoop.manager.InformationSchemaManager.(InformationSchemaManager.java:38)
at com.cloudera.sqoop.manager.InformationSchemaManager.(InformationSchemaManager.java:31)
at org.apache.sqoop.manager.MySQLManager.(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

：指定生成的java文件、编译成的class文件及将生成文件打包为JAR的JAR包文件输出路径

执行后结果如下：