Sqoop官网下载地址：http://archive.apache.org/dist/sqoop/

Sqoop是建立Hadoop、Hive环境安装之上的，所以需要Hadoop和Hive的环境搭建，Hive即需要依赖于HDFS又需要依赖YARN。安装好Hadoop后需要进行启动HDFS和YARN。

一、下载安装包

[root@stg-airflow001 ~]$ cd /data/software/
[root@stg-airflow001 /data/software]$ wget http://archive.apache.org/dist/sqoop/1.99.7/sqoop-1.99.7-bin-hadoop200.tar.gz
[root@stg-airflow001 /data/software]$ ll
总用量 1398724
-rw-r--r-- 1 root root 102436055 7月   6 2020 sqoop-1.99.7-bin-hadoop200.tar.gz

二、解压安装包

[root@stg-airflow001 /data/software]$ tar xf sqoop-1.99.7-bin-hadoop200.tar.gz -C /opt/
[root@stg-airflow001 /data/software]$ cd /opt/
[root@stg-airflow001 /opt]$ ll
总用量 0
drwxr-xr-x  8 root   root   157 8月   6 12:58 sqoop-1.99.7-bin-hadoop200

三、创建软连接

[root@stg-airflow001 /opt]$ ln -s sqoop-1.99.7-bin-hadoop200 sqoop

[root@stg-airflow001 /opt]$ ll
总用量 0
lrwxrwxrwx  1 root   root    26 8月   6 14:03 sqoop -> sqoop-1.99.7-bin-hadoop200
drwxr-xr-x  8 root   root   157 8月   6 12:58 sqoop-1.99.7-bin-hadoop200

四、配置环境变量

[root@stg-airflow001 /opt]$ vim /etc/profile.d/sqoop.sh
export PATH=/opt/sqoop/bin:$PATH

五、刷新环境变量

[root@stg-airflow001 /opt]$ source  /etc/profile.d/sqoop.sh

六、备份配置文件

[root@stg-airflow001 /opt]$ cd /opt/sqoop/conf/

[root@stg-airflow001 /opt/sqoop/conf]$ ll
总用量 28
-rw-r--r-- 1 root root 1584 7月  20 2016 sqoop_bootstrap.properties
-rw-r--r-- 1 root root 9797 8月   6 14:42 sqoop.properties

[root@stg-airflow001 /opt/sqoop/conf]$ cp sqoop.properties sqoop.properties.bak 

[root@stg-airflow001 /opt/sqoop/conf]$ ll
总用量 28
-rw-r--r-- 1 root root 1584 7月  20 2016 sqoop_bootstrap.properties
-rw-r--r-- 1 root root 9797 8月   6 14:42 sqoop.properties
-rw-r--r-- 1 root root 9714 7月  20 2016 sqoop.properties.bak

七、修改配置文件

# 1.方式一：默认连接
[root@stg-airflow001 /opt/sqoop/conf]$ vim sqoop.properties
... ...
# Hadoop configuration directory
org.apache.sqoop.submission.engine.mapreduce.configuration.directory=/opt/hadoop/etc/hadoop/

# 进行全局替换
# 设置日志文件的目录
0,%s/@LOGDIR@/\/data\/hadoop\/sqoop\/logs/g
# 设置数据的存放目录
0,%s/@BASEDIR@/\/data\/hadoop\/sqoop\/bin\/BASEDIR/g

# 2.方式二:连接mysql
[root@stg-airflow001 /opt/sqoop/conf]$ vim sqoop.properties
...  ...
# Hadoop configuration directory
org.apache.sqoop.submission.engine.mapreduce.configuration.directory=/opt/hadoop/etc/hadoop/

# JDBC repository provider configuration
org.apache.sqoop.repository.jdbc.handler=org.apache.sqoop.repository.mysql.MySqlRepositoryHandler
org.apache.sqoop.repository.jdbc.transaction.isolation=READ_COMMITTED
org.apache.sqoop.repository.jdbc.maximum.connections=10
org.apache.sqoop.repository.jdbc.url=jdbmysql://161.89.107.66:6301/sqoop?createDatabaseIfNotExist=true
org.apache.sqoop.repository.jdbc.driver=com.mysql.jdbc.Driver
org.apache.sqoop.repository.jdbc.user=hive
org.apache.sqoop.repository.jdbc.password=hive

# System properties for embedded Derby configuration
org.apache.sqoop.repository.sysprop.derby.stream.error.file=@LOGDIR@/mysqlrepo.log

org.apache.sqoop.jetty.port=12000

#配置第三方jar包位置(没有配置)
org.apache.sqoop.connector.external.loadpath=/usr/local/sqoop-1.99.7/lib

八、上传驱动包

[root@stg-airflow001 ~]$ cd /opt/sqoop/server/lib/
[root@stg-airflow001 /opt/sqoop/server/lib]$ rz

[root@stg-airflow001 /opt/sqoop/server/lib]$ ll
总用量 68656
-rw-r--r-- 1 root root  2415211 8月   6 14:44 mysql-connector-java-8.0.23.jar

九、启动Sqoop

1.初始化Sqoop

[root@stg-airflow001 ~]$ sqoop2-tool upgrade
Setting conf dir: /opt/sqoop/bin/../conf
Sqoop home directory: /opt/sqoop
Sqoop tool executor:
	Version: 1.99.7
	Revision: 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb
	Compiled on Tue Jul 19 16:08:27 PDT 2016 by abefine
Running tool: class org.apache.sqoop.tools.tool.UpgradeTool
0    [main] INFO  org.apache.sqoop.core.PropertiesConfigurationProvider  - Starting config file poller thread
Tool class org.apache.sqoop.tools.tool.UpgradeTool has finished correctly.

2.sqoop验证

[root@stg-airflow001 ~]$ sqoop2-tool verify
Setting conf dir: /opt/sqoop/bin/../conf
Sqoop home directory: /opt/sqoop
Sqoop tool executor:
	Version: 1.99.7
	Revision: 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb
	Compiled on Tue Jul 19 16:08:27 PDT 2016 by abefine
Running tool: class org.apache.sqoop.tools.tool.VerifyTool
0    [main] INFO  org.apache.sqoop.core.SqoopServer  - Initializing Sqoop server.
15   [main] INFO  org.apache.sqoop.core.PropertiesConfigurationProvider  - Starting config file poller thread
Verification was successful.
Tool class org.apache.sqoop.tools.tool.VerifyTool has finished correctly.

ps:此步骤后，mysql数据库中会创建数据库名为SQOOP的数据库，用以存储sqoop的metadata
在执行sqoop2-tool verify时报错
2019-05-24T12:15:45,449 ERROR [main] org.apache.sqoop.repository.common.CommonRepositoryHandler - Can't execute query: CREATE DATABASE IF NOT EXISTS"SQOOP"

在mysql中执行
set global sql_mode ='ANSI_QUOTES';

或者在mysql的配置文档my-default.cnf中添加
sql_mode=ANSI_QUOTES

3.启动sqoop2服务

[root@stg-airflow001 ~]$ sqoop2-server start
Setting conf dir: /opt/sqoop/bin/../conf
Sqoop home directory: /opt/sqoop
Starting the Sqoop2 server...
0    [main] INFO  org.apache.sqoop.core.SqoopServer  - Initializing Sqoop server.
28   [main] INFO  org.apache.sqoop.core.PropertiesConfigurationProvider  - Starting config file poller thread
Sqoop2 server started.

十、运行sqoop客户端

1.运行sqoop客户端

[root@stg-airflow001 ~]$ sqoop2-shell 
Setting conf dir: /opt/sqoop/bin/../conf
Sqoop home directory: /opt/sqoop
八月 13, 2021 9:03:52 下午 java.util.prefs.FileSystemPreferences$1 run
信息: Created user preferences directory.
Sqoop Shell: Type 'help' or '\h' for help.
sqoop:000>

2.设置显示错误信息

sqoop:000> set option --name verbose --value true
Verbose option was changed to true

3.连接sqoop服务端

sqoop:000> set server --host localhost
Server is set successfully

4.验证连接

sqoop:000> show version -all
client version:
  Sqoop 1.99.7 source revision 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb 
  Compiled by abefine on Tue Jul 19 16:08:27 PDT 2016
0    [main] WARN  org.apache.hadoop.util.NativeCodeLoader  - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
server version:
  Sqoop 1.99.7 source revision 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb 
  Compiled by abefine on Tue Jul 19 16:08:27 PDT 2016
API versions:
  [v1]

5.创建link对象

# 1.检查sqoop服务已经注册的connectors
sqoop:000> show connector
+------------------------+---------+------------------------------------------------------------+----------------------+
|          Name          | Version |                           Class                            | Supported Directions |
+------------------------+---------+------------------------------------------------------------+----------------------+
| generic-jdbc-connector | 1.99.7  | org.apache.sqoop.connector.jdbc.GenericJdbcConnector       | FROM/TO              |
| kite-connector         | 1.99.7  | org.apache.sqoop.connector.kite.KiteConnector              | FROM/TO              |
| oracle-jdbc-connector  | 1.99.7  | org.apache.sqoop.connector.jdbc.oracle.OracleJdbcConnector | FROM/TO              |
| ftp-connector          | 1.99.7  | org.apache.sqoop.connector.ftp.FtpConnector                | TO                   |
| hdfs-connector         | 1.99.7  | org.apache.sqoop.connector.hdfs.HdfsConnector              | FROM/TO              |
| kafka-connector        | 1.99.7  | org.apache.sqoop.connector.kafka.KafkaConnector            | TO                   |
| sftp-connector         | 1.99.7  | org.apache.sqoop.connector.sftp.SftpConnector              | TO                   |
+------------------------+---------+------------------------------------------------------------+----------------------+

sqoop:000> create link -c generic-jdbc-connector
Creating link for connector with name generic-jdbc-connector
Please fill following values to create new link object
Name: mysql-link

Database connection

Driver class: com.mysql.jdbc.Driver
Connection String: jdbmysql://node1:3306/ecs
Username: root
Password: ****
Fetch Size: 
Connection Properties: 
There are currently 0 values in the map:
entry#

SQL Dialect

Identifier enclose: 备注：定界符（输入的空格）
New link was successfully created with validation status OK and name mysql-link

sqoop:000> show link
+------------+------------------------+---------+
| Name | Connector Name | Enabled |
+------------+------------------------+---------+
| mysql-link | generic-jdbc-connector | true |
+------------+------------------------+---------+

sqoop:000> show link -a
1 link(s) to show: 
link with name mysql-link (Enabled: true, Created by root at 5/24/19 1:22 PM, Updated by root at 5/24/19 1:22 PM)
Using Connector generic-jdbc-connector with name {1}
Database connection
Driver class: com.mysql.jdbc.Driver
Connection String: jdbmysql://node1:3306/ecs
Username: root
Password: 
Fetch Size: 
Connection Properties: 
SQL Dialect
Identifier enclose: 

sqoop:000> create link -c hdfs-connector
Creating link for connector with name hdfs-connector
Please fill following values to create new link object
Name: hdfs-link

HDFS cluster

URI: hdfs://node1:9820
Conf directory: /home/hadoop-3.2.0/etc/hadoop
Additional configs:: 
There are currently 0 values in the map:
entry# 
New link was successfully created with validation status OK and name hdfs-link
sqoop:000>

HDFS 导出到mysql
sqoop:000> create job -f "hdfs-link" -t "mysql-link"
Creating job for links with from name hdfs-link and to name mysql-link
Please fill following values to create new job object
Name: hdfs_mysql_job

Input configuration

Input directory: /user/data
Override null value: 
Null value:

Incremental import

Incremental type: 
0 : NONE
1 : NEW_FILES
Choose: 0
Last imported date:

Database target

Schema name: ecs
Table name: users
Column names: 
There are currently 0 values in the list:
element# 
Staging table: 
Clear stage table:

Throttling resources

Extractors: 
Loaders:

Classpath configuration

Extra mapper jars: 
There are currently 0 values in the list:
element# 
New job was successfully created with validation status OK and name hdfs_mysql_job

sqoop:000> show job
+----+----------------+----------------------------+-------------------------------------+---------+
| Id | Name | From Connector | To Connector | Enabled |
+----+----------------+----------------------------+-------------------------------------+---------+
| 1 | hdfs_mysql_job | hdfs-link (hdfs-connector) | mysql-link (generic-jdbc-connector) | true |
+----+----------------+----------------------------+-------------------------------------+---------+

修改job
sqoop:000> update job -n hdfs_mysql_job

启动job
sqoop:000> start job -n hdfs_mysql_job
Submission details
Job Name: hdfs_mysql_job
Server URL: http://node1:12000/sqoop/
Created by: root
Creation date: 2019-05-24 14:27:45 HKT
Lastly updated by: root
External ID: job_1558650816925_0003
http://node1:8088/proxy/application_1558650816925_0003/
2019-05-24 14:27:45 HKT: BOOTING - Progress is not available

查看job状态
sqoop:000> status job -n hdfs_mysql_job
Submission details
Job Name: hdfs_mysql_job
Server URL: http://node1:12000/sqoop/
Created by: root
Creation date: 2019-05-24 14:27:45 HKT
Lastly updated by: root
External ID: job_1558650816925_0003
http://node1:8088/proxy/application_1558650816925_0003/
2019-05-24 14:30:10 HKT: BOOTING - 0.00 %

启动job，并显示执行状态
sqoop:000> start job -n hdfs_mysql_job -s


停止job
sqoop:000> stop job -n hdfs_mysql_job


mysql导入到hadoop
sqoop:000> create job -f "mysql-link" -t "hdfs-link"
Name: mysql_hdfs_job

Database source

Schema name: ecs
Table name: role
SQL statement: 
Column names: 
There are currently 0 values in the list:
element# 
Partition column: 
Partition column nullable: 
Boundary query:

Incremental read

Check column: 
Last value:

Target configuration

Override null value: 
Null value: 
File format: 
0 : TEXT_FILE
1 : SEQUENCE_FILE
2 : PARQUET_FILE
Choose: 0
Compression code 
0 : NONE
1 : DEFAULT
2 : DEFLATE
3 : GZIP
4 : BZIP2
5 : LZO
6 : LZ4
7 : SNAPPY
8 : CUSTOM
Choose: 0
Custom code 
Error message: Can't be null nor empty 
Output directory: /user/data
Append mode:

Throttling resources

Extractors: 
Loaders:

Classpath configuration

Extra mapper jars: 
There are currently 0 values in the list:
element# 
New job was successfully created with validation status OK and name mysql_hdfs_job
 

出现错误时，开启显示错误详细信息
set option --name verbose --value true


启动job时报错
Exception: org.apache.sqoop.common.SqoopException Message: GENERIC_JDBC_CONNECTOR_0003:Unable to access meta data -
查看错误信息，使用ecs账户时错误信息
Caused by: Exception: java.sql.SQLSyntaxErrorException Message: Unknown table 'role' in information_schema
使用root账户时，错误信息是：
Caused by: Exception: java.sql.SQLSyntaxErrorException Message: Table 'SQOOP.role' doesn't exist
看起来是识别不到设置的数据ecs，默认取的第一个数据库

使用sqoop2从mysql导入到hdfs的测试失败


sqoop2-shell报错
[root@node1 ~]# sqoop2-shell
Setting conf dir: /usr/local/sqoop-1.99.7/bin/../conf
Sqoop home directory: /usr/local/sqoop-1.99.7
Exception in thread "main" java.lang.ExceptionInInitializerError
at org.codehaus.groovy.runtime.InvokerHelper.<clinit>(InvokerHelper.java:66)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.createCallConstructorSite(CallSiteArray.java:87)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallConstructor(CallSiteArray.java:60)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:235)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:247)
at org.codehaus.groovy.tools.shell.Groovysh.<clinit>(Groovysh.groovy:54)
at org.apache.sqoop.shell.SqoopShell.main(SqoopShell.java:75)
Caused by: groovy.lang.GroovyRuntimeException: Conflicting module versions. Module [groovy-all is loaded in version 2.4.11 and you are trying to load version 2.4.0
解决方法：找出groovy.jar删除
[root@node1 sqoop-1.99.7]# find . -name 'groo*'

[root@node1 sqoop-1.99.7]# rm -r ./shell/lib/groovy-all-2.4.0.jar
rm: remove regular file ‘./shell/lib/groovy-all-2.4.0.jar’? y