Sqoop的安装与数据的导入导出

Sqoop介绍
Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql…)间进行数据的传递，可以将一个关系型数据库（例如 ： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。其机制是将导入或导出命令翻译成mapreduce程序来实现
在翻译出的mapreduce中主要是对inputformat和outputformat进行定制

image.png

Sqoop的安装
1、将Sqoop包上传到hadoop集群，我这里用的是sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz。解压后改下名字sqoop

[root@mini1 ~]#tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz
[root@mini1 ~]# mv sqoop-1.4.6xxxx（解压后的包名）sqoop

2、修改配置文件
进入conf目录修改sqoop-env-template.sh名字为sqoop-env.sh
并修改该文件内容，三个地方，一个hadoop命令所在位置，一个mapreduce所在位置，一个hive命令所在位置（怎么查看命令位置可以使用which,比如which hive,但是这里可以指定一个父目录）。

[root@mini1 ~]# cd sqoop
[root@mini1 sqoop]#cd conf/
[root@mini1 conf]# ll
总用量 28
-rw-rw-r--. 1 root root 3895 4月  27 2015 oraoop-site-template.xml
-rw-rw-r--. 1 root root 1404 4月  27 2015 sqoop-env-template.cmd
-rwxr-xr-x. 1 root root 1345 4月  27 2015 sqoop-env-template.sh
-rw-rw-r--. 1 root root 5531 4月  27 2015 sqoop-site-template.xml
-rw-rw-r--. 1 root root 5531 4月  27 2015 sqoop-site.xml
[root@mini1 conf]# mv sqoop-env-template.sh sqoop-env.sh
[root@mini1 conf]#vi sqoop-env.sh
...
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/root/apps/hadoop-2.6.4/

#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/root/apps/hadoop-2.6.4/

#Set the path to where bin/hive is available
export HIVE_HOME=/root/apps/hive/
...

3、加入mysql的驱动包
由于装hive的时候就将mysql驱动包传到了hive的lib目录下，这里直接拷贝过来即可

[root@mini1 conf]#cd ..
[root@mini1 sqoop]# cp /root/apps/hive/lib/mysql-connector-java-5.1.28.jar ./lib/

到这就安装完成了。

可能的问题

mysql-connector-java-5.1.28.jar
这个jar包的版本必须在28之上，否则可能会有问题。

数据导入
1、导入数据库表数据导入到hdfs
mysql创建表，插入数据，为了使用方便复制了如下

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>CREATE TABLE `emp` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `deg` varchar(255) NOT NULL,
  `salary` int(11) NOT NULL,
  `dept` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| emp            |
| t_user         |
+----------------+
2 rows in set (0.01 sec)

mysql> desc emp;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(32)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255) | NO   |     | NULL    |                |
| deg    | varchar(255) | NO   |     | NULL    |                |
| salary | int(11)      | NO   |     | NULL    |                |
| dept   | varchar(32)  | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> INSERT INTO `test`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) VALUES ('1', 'zhangsan', 'manager', '30000', 'AA');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `test`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) VALUES ('2', 'lisi', 'programmer', '20000', 'AA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) VALUES ('2', 'wangwu', 'programmer', '15000', 'BB');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> INSERT INTO `test`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) VALUES ('3', 'wangwu', 'programmer', '15000', 'BB');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) VALUES ('4', 'hund', 'programmer', '5000', 'CC');
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+----+----------+------------+--------+------+
| id | name     | deg        | salary | dept |
+----+----------+------------+--------+------+
|  1 | zhangsan | manager    |  30000 | AA   |
|  2 | lisi     | programmer |  20000 | AA   |
|  3 | wangwu   | programmer |  15000 | BB   |
|  4 | hund     | programmer |   5000 | CC   |
+----+----------+------------+--------+------+

使用下面的命令将test数据库中的emp表导入到hdfs（有默认目录）

bin/sqoop import   \
--connect jdbc:mysql://192.168.25.127:3306/test   \
--username root  \
--password 123456   \
--table emp   \
--m 1

数据库ip，使用的数据库
mysql用户名
mysql密码
要导入的表
注:m 1 表示使用一个mapreduce
程序在执行的时候能看到是跑了mapreduce程序的。
执行完毕后页面进行查看（/user/root是默认默认目录，我用的是root用户）

image.png

查看文件内容（数据间逗号隔开的）

[root@mini1 sqoop]# hadoop fs -ls /user/root/emp
Found 2 items
-rw-r--r--   2 root supergroup          0 2017-10-26 09:49 /user/root/emp/_SUCCESS
-rw-r--r--   2 root supergroup        110 2017-10-26 09:49 /user/root/emp/part-m-00000
[root@mini1 sqoop]# hadoop fs -cat  /user/root/emp/part-m-00000
1,zhangsan,manager,30000,AA
2,lisi,programmer,20000,AA
3,wangwu,programmer,15000,BB
4,hund,programmer,5000,CC

注：执行导入的时候很大可能出现下面的异常

java.sql.SQLException: Access denied for user 'root'@'mini1' (using password: YES)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
     ...
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
17/10/26 00:01:46 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter

这基本就是没授权导致的，给mini1授权即可如下

mysql> grant all privileges on *.* to root@mini1 identified by "123456";
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for root@mini1;
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@mini1                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'mini1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'mini1' WITH GRANT OPTION                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------+

2、emp表数据导入到hive表中
其实是先导入到hdfs，再由hdfs导入到hive（属于剪切粘贴）

先将前面生成的目录删了

[root@mini2 ~]# hadoop fs -rm -r  /user/root

执行以下命令导入emp表数据到hive表（表名也是emp）

[root@mini1 sqoop]# bin/sqoop import   \
> --connect jdbc:mysql://192.168.25.127:3306/test   \
> --username root  \
> --password 123456   \
> --table emp   \
> --hive-import \
> --m 1
...
17/10/26 10:04:13 INFO mapreduce.Job: Job job_1508930025306_0022 running in uber mode : false
17/10/26 10:04:13 INFO mapreduce.Job:  map 0% reduce 0%
17/10/26 10:04:17 INFO mapreduce.Job:  map 100% reduce 0%
17/10/26 10:04:18 INFO mapreduce.Job: Job job_1508930025306_0022 completed successfully
17/10/26 10:04:19 INFO mapreduce.Job: Counters: 30
        File System Counters
                FILE: Number of bytes read=0
                FILE: Number of bytes written=124217
                FILE: Number of read operations=0
                FILE: Number of large read operations=0
                FILE: Number of write operations=0
                HDFS: Number of bytes read=87
                HDFS: Number of bytes written=110
                HDFS: Number of read operations=4
                HDFS: Number of large read operations=0
                HDFS: Number of write operations=2
        Job Counters 
                Launched map tasks=1
                Other local map tasks=1
                Total time spent by all maps in occupied slots (ms)=2926
                Total time spent by all reduces in occupied slots (ms)=0
                Total time spent by all map tasks (ms)=2926
                Total vcore-milliseconds taken by all map tasks=2926
                Total megabyte-milliseconds taken by all map tasks=2996224
...
17/10/26 10:04:21 INFO hive.HiveImport: It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.
17/10/26 10:04:27 INFO hive.HiveImport: OK
17/10/26 10:04:27 INFO hive.HiveImport: Time taken: 1.649 seconds
17/10/26 10:04:27 INFO hive.HiveImport: Loading data to table default.emp
17/10/26 10:04:28 INFO hive.HiveImport: Table default.emp stats: [numFiles=1, totalSize=110]
17/10/26 10:04:28 INFO hive.HiveImport: OK
17/10/26 10:04:28 INFO hive.HiveImport: Time taken: 0.503 seconds
17/10/26 10:04:28 INFO hive.HiveImport: Hive import complete.
17/10/26 10:04:28 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.

将重要的输出信息都粘贴了下来，可见是先导入到hdfs的文件中，再移动到hive中的。
去hive中查看是否创建了该表导入了数据

hive> select * from emp;
OK
1       zhangsan        manager 30000   AA
2       lisi    programmer      20000   AA
3       wangwu  programmer      15000   BB
4       hund    programmer      5000    CC
Time taken: 0.641 seconds, Fetched: 4 row(s)

3、导入数据到hdfs指定目录
跟导入数据到hdfs查了句指定目录

[root@mini1 sqoop]# bin/sqoop import   \
> --connect jdbc:mysql://192.168.25.127:3306/test   \
> --username root  \
> --password 123456   \
> --table emp   \
> --target-dir /queryresult \
> --m 1

执行后查看

[root@mini3 ~]# hadoop fs -ls /queryresult 
Found 2 items
-rw-r--r--   2 root supergroup          0 2017-10-26 10:14 /queryresult/_SUCCESS
-rw-r--r--   2 root supergroup        110 2017-10-26 10:14 /queryresult/part-m-00000
[root@mini3 ~]# hadoop fs -cat /queryresult/part-m-00000
1,zhangsan,manager,30000,AA
2,lisi,programmer,20000,AA
3,wangwu,programmer,15000,BB
4,hund,programmer,5000,CC

4、导入表数据子集
有时候不是整张表都要导入，那么可以按照需要来进行导入。
比如只导入id，name，salary三个字段，且要求deg=programmer

如下

bin/sqoop import \
--connect jdbc:mysql://192.168.25.127:3306/test  \
--username root \
--password 123456 \
--target-dir /wherequery2 \
--query 'select id,name,deg from emp WHERE  deg = "programmer" and $CONDITIONS' \
--split-by id \
--fields-terminated-by '\t' \
--m 1

split-by id表示按照id切片，fields-terminated-by ‘\t’表示导入到文件系统中的数据分隔符为”\t”,默认是”,”

[root@mini3 ~]# hadoop fs -ls /wherequery2
Found 2 items
-rw-r--r--   2 root supergroup          0 2017-10-26 10:21 /wherequery2/_SUCCESS
-rw-r--r--   2 root supergroup         56 2017-10-26 10:21 /wherequery2/part-m-00000
[root@mini3 ~]# hadoop fs -cat /wherequery2/part-m-00000
2       lisi    programmer
3       wangwu  programmer
4       hund    programmer

--split-by原理

1)--split-by的原理
设置并行--num-mappers=4，加--split-by的情况会根据主键先查最大值和最小值,即：select min(key_id),max(key_id) from tb_oracle_stock_info_key。

如tb_oracle_stock_info_key(股票信息表)中 key_id(主键)最小值为300，最大值为400，那么4个并行度的切片情况如下：

并行度实现的sql如下：

select * from tb_oracle_stock_info_key where key_id between 300 and 325;

select * from tb_oracle_stock_info_key where key_id between 325 and 350;

select * from tb_oracle_stock_info_key where key_id between 351 and 375;

select * from tb_oracle_stock_info_key where key_id between 376 and 400;

综上所述，加--split-by参数后，使用大于1个并行时，效果理论上优于没有加--split-by参数作业。

2)数据倾斜

假设oracle的表tb_oracle_stock_info_key(股票信息表)主键为key_id，sqoop根据max（key_id）来平均分配4份。假设min(key_id)=1,max（key_id）=400，那么导数的时候会按400切割生4份，即 :

select * from tb_oracle_stock_info_key where key_id between 1  and 100;

select * from tb_oracle_stock_info_key where key_id between 101 and 200;

select * from tb_oracle_stock_info_key where key_id between 201 and 300;

select * from tb_oracle_stock_info_key where key_id between 301 and 400;

但是由于数据特殊的原因，key_id=[1,100]分区内自由1条数据，key_id=[101,300]内完全没有数据，99%数据都是key_id=[301,400],这样就会产生数据倾斜，也就是4个并行中，有3个不耗费时间，有1个花了大部分时间，这样的并行效果相当的不好：

因此，在使用并行度的时候需要了解主键的分布情况是是否有必要的。

5、增量导入
增量导入这里是仅导入新增加的表中的行，比如emp表有4条记录，但是我们新表中只需要导入id为3和4的记录进去
使用以下命令

bin/sqoop import \
--connect jdbc:mysql://192.168.25.127:3306/test \
--username root \
--password 123456 \
--table emp --m 1 \
--incremental append \
--check-column id \ 
--last-value 2


[root@mini1 sqoop]# bin/sqoop import \
> --connect jdbc:mysql://192.168.25.127:3306/test \
> --username root \
> --password 123456 \
> --table emp --m 1 \
> --incremental append \
> --check-column id \
> --last-value 2
[root@mini1 sqoop]# hadoop fs -ls /user/root/emp
Found 1 items
-rw-r--r--   2 root supergroup         55 2017-10-26 10:28 /user/root/emp/part-m-00000
[root@mini1 sqoop]# hadoop fs -cat /user/root/emp/part-m-00000
3,wangwu,programmer,15000,BB
4,hund,programmer,5000,CC

数据导出
将hdfs上数据导入到mysql数据库表中
注：需要将mysql上数据库和表创建出来才能导出
继续使用上面的emp表，但是将数据清空

mysql> select * from emp;
+----+----------+------------+--------+------+
| id | name     | deg        | salary | dept |
+----+----------+------------+--------+------+
|  1 | zhangsan | manager    |  30000 | AA   |
|  2 | lisi     | programmer |  20000 | AA   |
|  3 | wangwu   | programmer |  15000 | BB   |
|  4 | hund     | programmer |   5000 | CC   |
+----+----------+------------+--------+------+
4 rows in set (0.00 sec)

mysql> truncate emp;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from emp;
Empty set (0.00 sec)

使用以下命令，将数据从hdfs上指定目录数据导出到mysql指定的数据库和表上

bin/sqoop export \
--connect jdbc:mysql://192.168.25.127:3306/test \
--username root \
--password 123456 \
--table emp \
--export-dir /user/root/emp/

执行完之后查看表emp数据

mysql> select * from emp;
+----+--------+------------+--------+------+
| id | name   | deg        | salary | dept |
+----+--------+------------+--------+------+
|  3 | wangwu | programmer |  15000 | BB   |
|  4 | hund   | programmer |   5000 | CC   |
+----+--------+------------+--------+------+
2 rows in set (0.00 sec)

导出完成

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,311评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,339评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,671评论 0赞 342
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,252评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,253评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,031评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,340评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,973评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,466评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,937评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,039评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,701评论 4赞 323
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,254评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,259评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,485评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,497评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,786评论 2赞 345

推荐阅读更多精彩内容