sqoop搭建与使用

写在前面：

安装sqoop的前提是已经具备Java和Hadoop、Zookeeper、MySQL的环境，如何往Hive和HBase导入数据，应具备相关Hive、HBase环境。
（1）将sqoop-1.4.6-cdh5.14.2.tar.gz压缩包放到/opt/software/目录下

[root@nodefour ~]# cd /opt/software/
[root@nodefour software]# ll
总用量 1684240
-rw-r--r-- 1 root root 433895552 12月  8 14:59 hadoop-2.6.0-cdh5.14.2.tar.gz
-rw-r--r-- 1 root root 267038262 12月 17 09:10 hbase-1.2.0-cdh5.14.2.tar.gz
-rw-r--r-- 1 root root        91 12月 14 15:08 jd.txt
-rw-r--r-- 1 root root  30742669 12月 21 11:25 sqoop-1.4.6-cdh5.14.2.tar.gz
-rw-r--r-- 1 root root 992975720 12月 12 17:32 zeppelin-0.8.1-bin-all.tgz

（2）解压

[root@nodefour software]# tar -zxf sqoop-1.4.6-cdh5.14.2.tar.gz  -C /opt/install/

（3）添加软连接

[root@nodefour software]# ln -s /opt/install/sqoop-1.4.6-cdh5.14.2/ /opt/install/sqoop

（4）配置环境变量

[root@nodefour software]# vi /etc/profile

配置内容如下

export SQOOP_HOME=/opt/install/sqoop
export PATH=$SQOOP_HOME/bin:$PATH  

使配置生效

[root@nodefour software]# source /etc/profile

（5）在conf目录下修改配置文件

[root@nodefour software]# cd /opt/install/sqoop
[root@nodefour sqoop]# cd conf/

重命名配置文件

[root@nodefour conf]# mv sqoop-env-template.sh sqoop-env.sh
[root@nodefour conf]# ll
总用量 28
-rw-rw-r-- 1 root root 3895 3月  28 2018 oraoop-site-template.xml
-rwxr-xr-x 1 root root 1345 3月  28 2018 sqoop-env.sh
-rw-rw-r-- 1 root root 1404 3月  28 2018 sqoop-env-template.cmd
-rw-rw-r-- 1 root root 6044 3月  28 2018 sqoop-site-template.xml
-rw-rw-r-- 1 root root 6044 3月  28 2018 sqoop-site.xml

配置sqoop-env.sh文件

[root@nodefour conf]# vi sqoop-env.sh 

export HADOOP_COMMON_HOME=/opt/install/hadoop
export HADOOP_MAPRED_HOME=/opt/install/hadoop
export HIVE_HOME=/opt/install/hive
export ZOOKEEPER_HOME=/opt/install/zookeeper
export ZOOCFGDIR=/opt/install/zookeeper
export HBASE_HOME=/opt/install/hbase

（6）拷贝JDBC驱动
拷贝jdbc驱动到sqoop的lib目录下

[hadoop@nodefour software] cp mysql-connector-java-5.1.44-bin.jar /opt/install/sqoop/lib/

（7）通过以下命令可以查看sqoop的安装情况

[root@nodefour conf]# sqoop help
Warning: /opt/intall/hive does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /opt/install/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /opt/install/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/12/21 11:37:14 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.2
usage: sqoop COMMAND [ARGS]

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

二、RDB到HDFS的数据迁移

（1）测试连接
使用下列命令测试连接mysql,记得把端口号和密码修改成自己的

[root@nodefour conf]# sqoop list-databases --connect jdbc:mysql://192.168.202.204:3306/ --username root --password 123QWEasd!
Warning: /opt/intall/hive does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /opt/install/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /opt/install/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/12/21 11:38:04 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.2
20/12/21 11:38:04 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
20/12/21 11:38:04 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
hive
mysql
performance_schema
test

如上，出现数据库信息即为连接成功。
(2)数据库建表
1.在mysql数据库中建立数据库retail_db

mysql -uroot -p123QWEasd!
create database retail_db;

2.使用retail_db.sql文件建表

mysql> use retail_db;
Database changed
mysql> source /opt/datas/retail_db.sql

3.查看建表情况

mysql> show tables;
+---------------------+
| Tables_in_retail_db |
+---------------------+
| categories          |
| customers           |
| departments         |
| order_items         |
| orders              |
| products            |
+---------------------+
6 rows in set (0.00 sec)

(3)使用sqoop将mysql数据库表customers上传到hdfs

sqoop import \
--connect jdbc:mysql://192.168.202.204:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--table customers \
--username root \
--password 123QWEasd! \
--target-dir /data/retail_db/customers \
--delete-target-dir \
-m 3

在hdfs中查看

可能报的错误

Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject
	at org.apache.sqoop.util.SqoopJsonUtil.getJsonStringforMap(SqoopJsonUtil.java:43)
	at org.apache.sqoop.SqoopOptions.writeProperties(SqoopOptions.java:784)
	at org.apache.sqoop.mapreduce.JobBase.putSqoopOptionsToConfiguration(JobBase.java:392)
	at org.apache.sqoop.mapreduce.JobBase.createJob(JobBase.java:378)
	at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:256)
	at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:513)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
	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.json.JSONObject
	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)
	... 14 more

报上述错误的原因是/opt/install/sqoop/lib/目录下缺少java-json.jar包，下载并放置到目录即可