sqoop+mysql+hive收集数据

场景一. 业务场景中存在需要将业务数据存放到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:worksofthadoop-3.3.0

#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=D:worksofthadoop-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:worksoftapache-hive-3.1.2-bin

#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.步骤二连接数据库测试时报错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