sqoop简介

sqoop主要用于完成数据迁移工作。

sqoop1使用方式

sqoop1仅支持命令行使用方式，主要为用户提供了import和export两种命令：

• import：将关系数据库（比如MySQL、Oracle等）中的数据导入Hadoop（比如HDFS、HBASE和hive）中，关系型数据库中的每一条记录都将被转化为HDFS文件中的一行，每条记录可表示为文本、二进制文件或sequenceFile等格式。
• export：将Hadoop中的数据倒回到关系型数据库中。HDFS中的文件将按照某个设定的分隔符拆分成一条条记录，插入到数据库表中。

Cloudera manager安装sqoop

sqoop 1 client 安装

1.在需要添加服务的集群上点击下拉菜单，如下图所示，点击"添加服务"，选择sqoop 1 client


2.选择要安装的主机


3.审核配置
4.服务添加到集群

5.安装完成后会在集群中显示

验证sqoop

1. sqoop version 验证安装

[root@g1-bdp-cdhtestin-03 ~]# sqoop version
Warning: /opt/cloudera/parcels/CDH-5.14.4-1.cdh5.14.4.p0.3/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/07/02 15:11:35 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
Sqoop 1.4.6-cdh5.14.4
git commit id
Compiled by jenkins on Tue Jun 12 03:46:55 PDT 2018

2. 测试MySQL连接

sqoop list-databases --connect jdbc:mysql://10.16.208.221  --username root –P12345

3. 检验SQL语句

sqoop eval --connect jdbc:mysql://10.16.208.221 --username root--password 111111/ 
--query "SELECT xi.*, jing.name,wang.latitude,wang.longitude / FROM xi ,jing, wang / 
WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01'"

以上Sqoop语句执行过后，可以确认Sqoop运行正常，Sqoop连接MySQL正常。

sqoop基本使用

1. sqoop 查看帮助

[root@g1-bdp-cdhtestin-03 ~]# sqoop help
Warning: /opt/cloudera/parcels/CDH-5.14.4-1.cdh5.14.4.p0.3/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/07/02 14:44:44 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
usage: sqoop COMMAND [ARGS]Available commands:codegen            Generate code to interact with database recordscreate-hive-table  Import a table definition into Hiveeval               Evaluate a SQL statement and display the resultsexport             Export an HDFS directory to a database tablehelp               List available commandsimport             Import a table from a database to HDFSimport-all-tables  Import tables from a database to HDFSimport-mainframe   Import datasets from a mainframe server to HDFSjob                Work with saved jobslist-databases     List available databases on a serverlist-tables        List available tables in a databasemerge              Merge results of incremental importsmetastore          Run a standalone Sqoop metastoreversion            Display version informationSee 'sqoop help COMMAND' for information on a specific command.

2. sqoop列出databases

（1）查看list-databases如何使用

[root@g1-bdp-cdhtestin-03 ~]# sqoop list-databases --help
Warning: /opt/cloudera/parcels/CDH-5.14.4-1.cdh5.14.4.p0.3/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/07/02 16:30:59 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
usage: sqoop list-databases [GENERIC-ARGS] [TOOL-ARGS]Common arguments:--connect <jdbc-uri>                                       Specify JDBCconnectstring--connection-manager <class-name>                          Specifyconnectionmanagerclass name--connection-param-file <properties-file>                  Specifyconnectionparametersfile--driver <class-name>                                      Manuallyspecify JDBCdriver classto use--hadoop-home <hdir>                                       Override$HADOOP_MAPRED_HOME_ARG--hadoop-mapred-home <dir>                                 Override$HADOOP_MAPRED_HOME_ARG--help                                                     Print usageinstructions--metadata-transaction-isolation-level <isolationlevel>    Defines thetransactionisolationlevel formetadataqueries. Formore detailscheckjava.sql.Connection........

(2) 确定sqoop链接MySQL数据格式

 sqoop list-tables --connect <url> --username root   --password 123456

其中<url>是MySQL jdbc url，格式如下：

jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyVal]

例如：

jdbc:mysql://10.16.208.221

(3) 登录MySQL授予权限

 [wangandi@g1-bdp-test-v01 /home/vip]$ mysql -uroot -p123456 （step1）
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1073
Server version: 5.6.48 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> grant all privileges on *.* to 'root'@'10.16.212.3' identified by '123456';（step 2， 其中10.16.212.3为g1-bdp-cdhtestin-03的IP地址）
Query OK, 0 rows affected (0.43 sec)mysql> FLUSH PRIVILEGES;（step 3）
Query OK, 0 rows affected (0.53 sec)mysql> quit
Bye

原因，未授予权限，执行命令sqoop list-databases --connect jdbc:mysql://10.16.208.221 --username root -P时，报以下错误：

20/07/02 16:12:46 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.sql.SQLException: null,  message from server: "Host 'g1-bdp-cdhtestin-03.dns.guazi.com' is not allowed to connect to this MySQL server"
java.lang.RuntimeException: java.sql.SQLException: null,  message from server: "Host 'g1-bdp-cdhtestin-03.dns.guazi.com' is not allowed to connect to this MySQL server"

原因是host为10.16.208.221的MySQL数据库没有授予g1-bdp-cdhtestin-03的权限，因此在执行sqoop链接数据库命令前需要先进入10.16.208.221的MySQL数据库，为其授予权限。

（4）授权完成后，在g1-bdp-cdhtestin-03的root下执行sqoop链接数据库命令，成功，如下所示：

• sqoop链接MySQL数据库列出databases

[root@g1-bdp-cdhtestin-03 ~]# sqoop list-databases --connect jdbc:mysql://10.16.208.221 --username root -P
Warning: /opt/cloudera/parcels/CDH-5.14.4-1.cdh5.14.4.p0.3/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/07/02 16:25:16 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
Enter password:
20/07/02 16:25:21 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
ambari
mysql
performance_schema

• MySQL直接访问列出的databases

[wangandi@g1-bdp-test-v01 /home/vip]$ mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1076
Server version: 5.6.48 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ambari             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)mysql> quit
Bye

3. sqoop列出表

[root@g1-bdp-cdhtestin-03 ~]# sqoop list-tables --connect jdbc:mysql://10.16.208.221/ambari --username root -P
Warning: /opt/cloudera/parcels/CDH-5.14.4-1.cdh5.14.4.p0.3/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
20/07/02 16:45:49 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.4
Enter password:
20/07/02 16:45:52 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
ClusterHostMapping
QRTZ_BLOB_TRIGGERS
QRTZ_CALENDARS
QRTZ_CRON_TRIGGERS
QRTZ_FIRED_TRIGGERS

效果如下所示，左侧为sqoop查询，右侧为MySQL本地查询，两者结果一致。