sqoop mysql 安装_sqoop安装及使用

傷城~ 2023-01-12 12:00 192阅读 0赞

简介：

sqoop是一款用于hadoop和关系型数据库之间数据导入导出的工具。你可以通过sqoop把数据从数据库(比如mysql,oracle)导入到hdfs中；也可以把数据从hdfs中导出到关系型数据库中。通过将sqoop的操作命令转化为Hadoop的MapReduce作业进行导入导出，(通常只涉及到Map任务)即sqoop生成的Job主要是并发运行MapTask实现数据并行传输以提升数据传送速度和效率，如果使用Shell脚本来实现多线程数据传送则存在很大的难度Sqoop2(sqoop1.99.7)需要在Hadoop安装目录下的配置文件中设置代理，属于重量级嵌入安装，文中我们使用qoop1(Sqoop1.4.6)。

前提：(若不知道如何安装请看我前面写的hadoop分类的文章)

CloudDeskTop上安装了： hadoop-2.7.3 jdk1.7.0\_79 mysql-5.5.32 sqoop-1.4.6 hive-1.2.2master01和master02安装了： hadoop-2.7.3 jdk1.7.0\_79

slave01、slave02、slave03安装了： hadoop-2.7.3 jdk1.7.0\_79 zookeeper-3.4.10

一、安装：

2、解压：

\[hadoop@CloudDeskTop install\]$ tar -zxvf sqoop-1.4.6.bin\_\_hadoop-2.0.4-alpha.tar.gz -C /software/

3、配置环境：

\[hadoop@CloudDeskTop software\]$ su -lc "vi /etc/profile"

JAVA\_HOME=/software/jdk1.7.0\_79

HADOOP\_HOME=/software/hadoop-2.7.3SQOOP\_HOME=/software/sqoop-1.4.6PATH=$PATH:$JAVA\_HOME/bin:$JAVA\_HOME/lib:$HADOOP\_HOME/bin:$HADOOP\_HOME/sbin:$SQOOP\_HOME/bin

export PATH JAVA\_HOME HADOOP\_HOME SQOOP\_HOME

4、配置完环境后，执行如下语句，立即生效配置文件：

\[hadoop@CloudDeskTop software\]$ source /etc/profile

5、进入/software/sqoop-1.4.6/lib/目录，上传mysql-connector-java-5.1.43-bin.jar包

这个地方的数据库驱动包必须选择该版本(5.1.43)，因为Sqoop需要对接MySql数据库，如果选择的数据库驱动包不是这个版本，很容易出错。

6、配置sqoop

\[hadoop@CloudDeskTop software\]$ cd /software/sqoop-1.4.6/bin/

\[hadoop@CloudDeskTop bin\]$ vi configure-sqoop

注释掉如下代码：用这个符号“:<

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

127 :<

131 echo 'Please set $HBASE\_HOME to the root of your HBase installation.'

132fi133

134\#\# Moved to be a runtime check in sqoop.135 if \[ ! -d "$\{HCAT\_HOME\}"\]; then136 echo "Warning: $HCAT\_HOME does not exist! HCatalog jobs will fail."

137 echo 'Please set $HCAT\_HOME to the root of your HCatalog installation.'

138fi139

140 if \[ ! -d "$\{ACCUMULO\_HOME\}"\]; then141 echo "Warning: $ACCUMULO\_HOME does not exist! Accumulo imports will fail."

142 echo 'Please set $ACCUMULO\_HOME to the root of your Accumulo installation.'

143fi144 if \[ ! -d "$\{ZOOKEEPER\_HOME\}"\]; then145 echo "Warning: $ZOOKEEPER\_HOME does not exist! Accumulo imports will fail."

146 echo 'Please set $ZOOKEEPER\_HOME to the root of your Zookeeper installation.'

147fi148 COMMENT

二、启动(没说明的都默认是在hadoop用户下操作)

【0、在CloudDeskTop的root用户下启动mysql】

\[root@CloudDeskTop ~\]\# cd /software/mysql-5.5.32/sbin/ && ./mysqld start && lsof -i:3306 && cd -

【1、在slave节点启动zookeeper集群(小弟中选个leader和follower)】

cd /software/zookeeper-3.4.10/bin/ && ./zkServer.sh start && cd - && jps

cd /software/zookeeper-3.4.10/bin/ && ./zkServer.sh status && cd -

【2、master01启动HDFS集群】cd /software/ && start-dfs.sh && jps

【3、master01启动YARN集群】cd /software/ && start-yarn.sh && jps

【YARN集群启动时，不会把另外一个备用主节点的YARN集群拉起来启动，所以在master02执行语句:】

cd /software/ && yarn-daemon.sh start resourcemanager && jps

【4、查看进程】

![97dfd62733ccca4ee5d90510ef5e9dc2.png][]

![9fa5357f1276bd5a796d7a1dda161325.png][]

【6、查询sqoop版本来判断sqoop是否安装成功】

\[hadoop@CloudDeskTop software\]$ sqoop version

![a7277903096593d74dcac6272c47c525.png][]

三、测试

说明：导入与导出操作的方向是以HDFS集群为基准参考点来定义的，如果数据从HDFS集群流出则表示导出，如果数据流入HDFS集群则表示导入Hive表中的数据实际上是存储到HDFS集群中的，因此对Hive表的导入与导出实际上都是在操作HDFS集群中的文件。

首先，在本地创建数据：

![69ae73c76407cb390edc9261bc952454.png][]

![8433a66d80dae04fd2abb77c6dc72c19.png][]

在hive数据库建表后上传到集群中表存放数据的路径下：

![8e2800c2253c3d7bb3facc961204312a.png][]

\[hadoop@CloudDeskTop test\]$ hdfs dfs -put testsqoop.out /user/hive/warehouse/mmzs.db/testsqoop

目标一、将hdfs集群的数据导入到mysql数据库中

1、在hive数据库mmzs中创建表，并导入数据

\[hadoop@CloudDeskTop software\]$ cd /software/hive-1.2.2/bin/\[hadoop@CloudDeskTop bin\]$ ./hive

hive>show databases;

OKdefaultmmzs

mmzsmysql

Time taken:0.373 seconds, Fetched: 3row(s)

hive> create table if not exists mmzs.testsqoop(id int,name string,age int) row format delimited fields terminated by '\\t';

Time taken:0.126seconds

hive> select \*from mmzs.testsqoop;

OK1 ligang 2

2 chenghua 3

3 liqin 1

4 zhanghua 4

5 wanghua 1

6 liulinjin 5

7 wangxiaochuan 6

8 guchuan 2

9 xiaoyong 4

10 huping 6Time taken:0.824 seconds, Fetched: 10 row(s)

2、在mysql数据库中创建相同字段的表

\[root@CloudDeskTop bin\]\# cd ~\[root@CloudDeskTop~\]\# cd /software/mysql-5.5.32/bin/\[root@CloudDeskTop bin\]\# ./mysql -uroot -p123456 -P3306 -h192.168.154.134 -e "create database mmzs character set utf8"\[root@CloudDeskTop bin\]\# ./mysql -uroot -p123456 -h192.168.154.134 -P3306 -Dmmzs

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is12Server version:5.5.32Source distribution

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 tables;

Empty set (0.00sec)

mysql> create table if not exists testsqoop(uid int(11),uname varchar(30),age int)engine=innodb charset=utf8->;

Query OK,0 rows affected (0.06sec)

mysql>desc testsqoop;+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

| uid | int(11) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

\+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql> select \*from testsqoop;

Empty set (0.01 sec)

3、使用Sqoop将Hive表中的数据导出到MySql数据库中(整个HDFS文件导出)

\[hadoop@CloudDeskTop software\]$ sqoop-export --help

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

17/12/30 21:54:38 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6usage: sqoop export \[GENERIC-ARGS\] \[TOOL-ARGS\]

Common arguments:--connect Specify JDBC connect

string--connection-manager Specify connection managerclassname--connection-param-file Specify connection

parameters file--driver Manually specify JDBC

driverclassto use--hadoop-home Override

$HADOOP\_MAPRED\_HOME\_ARG--hadoop-mapred-home

$HADOOP\_MAPRED\_HOME\_ARG--help Print usage instructions-P Read password from console--password Set authentication

password--password-alias Credential provider

password alias--password-file Set authentication

password file path--relaxed-isolation Use read-uncommitted

isolationforimports--skip-dist-cache Skip copying jars to

distributed cache--username Set authentication

username--verbose Print more informationwhileworking

Export control arguments:--batch Indicates

underlying

statements

to be

executed in

batch mode--call Populate the

table usingthisstored

procedure

(one call

per row)--clear-staging-table Indicates

that any

data in

staging

table can be

deleted--columns

Columns to

export to

table--direct Use direct

export fast

path--export-dir

pathforthe

export-m,--num-mappers Use 'n'map

tasks to

export in

parallel--mapreduce-job-name Set name forgenerated

mapreduce

job--staging-table Intermediate

staging

table--table Table to

populate--update-key Update

records by

specified

key column--update-mode Specifies

how updates

are

performed

whennewrows are

found with

non-matching

keys in

database--validate Validate the

copy using

the

configured

validator--validation-failurehandler Fully

qualifiedclassnameforValidationFa

ilureHandler--validation-threshold Fully

qualifiedclassnameforValidationTh

reshold--validator Fully

qualifiedclassnameforthe

Validator

Input parsing arguments:--input-enclosed-by Sets a required field encloser--input-escaped-by Sets the input escape

character--input-fields-terminated-by Sets the input field separator--input-lines-terminated-by Sets the input end-of-linechar

\--input-optionally-enclosed-by Sets a field enclosing

character

Output line formatting arguments:--enclosed-by Sets a required field enclosing

character--escaped-by Sets the escape character--fields-terminated-by Sets the field separator character--lines-terminated-by Sets the end-of-line character--mysql-delimiters Uses MySQL's default delimiter set:

fields: , lines: \\n escaped-by: \\

optionally-enclosed-by: ' --optionally-enclosed-by Sets a field enclosing character

Code generation arguments:--bindir

objects--class-name Sets the generated classname.

This overrides--package-name.

When combined with--jar-file,

sets the inputclass.--input-null-non-string Input null non-string

representation--input-null-string Input nullstring representation--jar-file Disable code generation; use

specified jar--map-column-java Override mapping forspecific

columns to java types--null-non-string Null non-string representation--null-string Null string representation--outdir

code--package-name Put auto-generated classes inthis packageHCatalog arguments:--hcatalog-database HCatalog database name--hcatalog-home Override $HCAT\_HOME--hcatalog-partition-keys Sets the partition

keys to use when

importing to hive--hcatalog-partition-values Sets the partition

values to use when

importing to hive--hcatalog-table HCatalog table name--hive-home

to use when importing

to hive--hive-partition-value Sets the partition

value to use when

importing to hive--map-column-hive Override mapping forspecific column to

hive types.

Generic Hadoop command-line arguments:

(must preceed any tool-specific arguments)

Generic options supported are-conf specify an application configuration file-D use value forgiven property-fs specify a namenode-jt specify a ResourceManager-files specify comma separated files to be copied to the map reduce cluster-libjars specify comma separated jar files to include in the classpath.-archives specify comma separated archives to be unarchived on the compute machines.

The general command line syntax is

bin/hadoop command \[genericOptions\] \[commandOptions\]

At minimum, you must specify--connect, --export-dir, and --table

\#-m是指定map任务的个数

\[hadoop@CloudDeskTop software\]$ sqoop-export --export-dir '/user/hive/warehouse/mmzs.db/testsqoop' --fields-terminated-by '\\t' --lines-terminated-by '\\n' --connect 'jdbc:mysql://192.168.154.134:3306/mmzs' --username 'root' --password '123456' --table 'testsqoop' -m 2

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

17/12/30 22:02:04 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6

17/12/30 22:02:04 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.17/12/30 22:02:04INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.17/12/30 22:02:04INFO tool.CodeGenTool: Beginning code generation17/12/30 22:02:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.\* FROM \`testsqoop\` AS t LIMIT 1

17/12/30 22:02:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.\* FROM \`testsqoop\` AS t LIMIT 1

17/12/30 22:02:05 INFO orm.CompilationManager: HADOOP\_MAPRED\_HOME is /software/hadoop-2.7.3注:/tmp/sqoop-hadoop/compile/e2b7e669ef4d8d43016e44ce1cddb620/testsqoop.java使用或覆盖了已过时的 API。

注: 有关详细信息, 请使用-Xlint:deprecation 重新编译。17/12/30 22:02:11 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/e2b7e669ef4d8d43016e44ce1cddb620/testsqoop.jar17/12/30 22:02:11INFO mapreduce.ExportJobBase: Beginning export of testsqoop

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in \[jar:file:/software/hadoop-2.7.3/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: Found binding in \[jar:file:/software/hbase-1.2.6/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: See http://www.slf4j.org/codes.html\#multiple\_bindings for an explanation.

SLF4J: Actual binding is of type \[org.slf4j.impl.Log4jLoggerFactory\]17/12/30 22:02:11INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar17/12/30 22:02:13INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative17/12/30 22:02:13INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative17/12/30 22:02:13INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps17/12/30 22:02:22 INFO input.FileInputFormat: Total input paths to process : 1

17/12/30 22:02:22 INFO input.FileInputFormat: Total input paths to process : 1

17/12/30 22:02:23 INFO mapreduce.JobSubmitter: number of splits:2

17/12/30 22:02:23INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative17/12/30 22:02:24 INFO mapreduce.JobSubmitter: Submitting tokens forjob: job\_1514638990227\_000117/12/30 22:02:25INFO impl.YarnClientImpl: Submitted application application\_1514638990227\_000117/12/30 22:02:25 INFO mapreduce.Job: The url to track the job: http://master01:8088/proxy/application\_1514638990227\_0001/

17/12/30 22:02:25INFO mapreduce.Job: Running job: job\_1514638990227\_000117/12/30 22:03:13 INFO mapreduce.Job: Job job\_1514638990227\_0001 running in uber mode : false

17/12/30 22:03:13 INFO mapreduce.Job: map 0% reduce 0%

17/12/30 22:03:58 INFO mapreduce.Job: map 100% reduce 0%

17/12/30 22:03:59INFO mapreduce.Job: Job job\_1514638990227\_0001 completed successfully17/12/30 22:03:59 INFO mapreduce.Job: Counters: 30File System Counters

FILE: Number of bytes read=0FILE: Number of bytes written=277282FILE: Number of read operations=0FILE: Number of large read operations=0FILE: Number of write operations=0HDFS: Number of bytes read=484HDFS: Number of bytes written=0HDFS: Number of read operations=8HDFS: Number of large read operations=0HDFS: Number of write operations=0Job Counters

Launched map tasks=2Data-local map tasks=2Total time spent by all maps in occupied slots (ms)=79918Total time spent by all reduces in occupied slots (ms)=0Total time spent by all map tasks (ms)=79918Total vcore-milliseconds taken by all map tasks=79918Total megabyte-milliseconds taken by all map tasks=81836032Map-Reduce Framework

Map input records=10Map output records=10Input split bytes=286Spilled Records=0Failed Shuffles=0Merged Map outputs=0GC time elapsed (ms)=386CPU time spent (ms)=4950Physical memory (bytes) snapshot=216600576Virtual memory (bytes) snapshot=1697566720Total committed heap usage (bytes)=32874496File Input Format Counters

Bytes Read=0File Output Format Counters

Bytes Written=0

17/12/30 22:03:59 INFO mapreduce.ExportJobBase: Transferred 484 bytes in 105.965 seconds (4.5675 bytes/sec)17/12/30 22:03:59 INFO mapreduce.ExportJobBase: Exported 10 records.

运行截图

小结：从运行过程可以看出只有Map任务，没有Reduce任务。

4、在mysql数据库再次查询结果

mysql> select \*from testsqoop;+------+---------------+------+

| uid | uname | age |

\+------+---------------+------+

| 1 | ligang | 2 |

| 2 | chenghua | 3 |

| 3 | liqin | 1 |

| 4 | zhanghua | 4 |

| 5 | wanghua | 1 |

| 6 | liulinjin | 5 |

| 7 | wangxiaochuan | 6 |

| 8 | guchuan | 2 |

| 9 | xiaoyong | 4 |

| 10 | huping | 6 |

\+------+---------------+------+

10 rows in set (0.00 sec)

从结果可以证明数据导出到mysql数据库成功。

目标二、将mysql的数据导入到hdfs集群中

1、删除hive中mmzs数据库的testsqoop表的数据

![42a328957678a4180fd82350d8831cf7.png][]

确认真的删除了：

![b9d1afc526b06ed6140d26c841c89727.png][]

2、将mysql中的数据导入到hdfs群

A、指定部分查询数据导入到集群众

\[hadoop@CloudDeskTop software\]$ sqoop-import --append --connect 'jdbc:mysql://192.168.154.134:3306/mmzs' --username 'root' --password '123456' --query 'select \* from mmzs.testsqoop where uid>3 and $CONDITIONS' -m 1 --target-dir '/user/hive/warehouse/mmzs.db/testsqoop' --fields-terminated-by '\\t' --lines-terminated-by '\\n'

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

17/12/30 22:40:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6

17/12/30 22:40:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.17/12/30 22:40:55INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.17/12/30 22:40:55INFO tool.CodeGenTool: Beginning code generation17/12/30 22:40:55 INFO manager.SqlManager: Executing SQL statement: select \* from mmzs.testsqoop where uid>3 and (1 = 0)17/12/30 22:40:55 INFO manager.SqlManager: Executing SQL statement: select \* from mmzs.testsqoop where uid>3 and (1 = 0)17/12/30 22:40:55 INFO manager.SqlManager: Executing SQL statement: select \* from mmzs.testsqoop where uid>3 and (1 = 0)17/12/30 22:40:55 INFO orm.CompilationManager: HADOOP\_MAPRED\_HOME is /software/hadoop-2.7.3注:/tmp/sqoop-hadoop/compile/cd00e059648175875074eed7f4189e0b/QueryResult.java使用或覆盖了已过时的 API。

注: 有关详细信息, 请使用-Xlint:deprecation 重新编译。17/12/30 22:40:58 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/cd00e059648175875074eed7f4189e0b/QueryResult.jar17/12/30 22:40:58 INFO mapreduce.ImportJobBase: Beginning query import.

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in \[jar:file:/software/hadoop-2.7.3/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: Found binding in \[jar:file:/software/hbase-1.2.6/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: See http://www.slf4j.org/codes.html\#multiple\_bindings for an explanation.

SLF4J: Actual binding is of type \[org.slf4j.impl.Log4jLoggerFactory\]17/12/30 22:40:59INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar17/12/30 22:41:01INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps17/12/30 22:41:08INFO db.DBInputFormat: Using read commited transaction isolation17/12/30 22:41:09 INFO mapreduce.JobSubmitter: number of splits:1

17/12/30 22:41:09 INFO mapreduce.JobSubmitter: Submitting tokens forjob: job\_1514638990227\_000317/12/30 22:41:10INFO impl.YarnClientImpl: Submitted application application\_1514638990227\_000317/12/30 22:41:10 INFO mapreduce.Job: The url to track the job: http://master01:8088/proxy/application\_1514638990227\_0003/

17/12/30 22:41:10INFO mapreduce.Job: Running job: job\_1514638990227\_000317/12/30 22:41:54 INFO mapreduce.Job: Job job\_1514638990227\_0003 running in uber mode : false

17/12/30 22:41:54 INFO mapreduce.Job: map 0% reduce 0%

17/12/30 22:42:29 INFO mapreduce.Job: map 100% reduce 0%

17/12/30 22:42:31INFO mapreduce.Job: Job job\_1514638990227\_0003 completed successfully17/12/30 22:42:32 INFO mapreduce.Job: Counters: 30File System Counters

FILE: Number of bytes read=0FILE: Number of bytes written=138692FILE: Number of read operations=0FILE: Number of large read operations=0FILE: Number of write operations=0HDFS: Number of bytes read=87HDFS: Number of bytes written=94HDFS: Number of read operations=4HDFS: Number of large read operations=0HDFS: Number of write operations=2Job Counters

Launched map tasks=1Other local map tasks=1Total time spent by all maps in occupied slots (ms)=32275Total time spent by all reduces in occupied slots (ms)=0Total time spent by all map tasks (ms)=32275Total vcore-milliseconds taken by all map tasks=32275Total megabyte-milliseconds taken by all map tasks=33049600Map-Reduce Framework

Map input records=7Map output records=7Input split bytes=87Spilled Records=0Failed Shuffles=0Merged Map outputs=0GC time elapsed (ms)=170CPU time spent (ms)=2020Physical memory (bytes) snapshot=109428736Virtual memory (bytes) snapshot=851021824Total committed heap usage (bytes)=19091456File Input Format Counters

Bytes Read=0File Output Format Counters

Bytes Written=94

17/12/30 22:42:32 INFO mapreduce.ImportJobBase: Transferred 94 bytes in 91.0632 seconds (1.0322 bytes/sec)17/12/30 22:42:32 INFO mapreduce.ImportJobBase: Retrieved 7records.17/12/30 22:42:32 INFO util.AppendUtils: Appending to directory testsqoop

在集群中查询是否真的导入了数据：

![25efb84551c12b14c20e352ead9f1ab1.png][]

在hive数据库中中查询是否真的导入了数据：

![6d7e6a519df04c7745dc5dd8139a2e50.png][]

从结果可以证明数据导入到hdfs集群成功。

删除集群数据，方便下次导入操作：

\[hadoop@master01 software\]$ hdfs dfs -rm -r /user/hive/warehouse/mmzs.db/testsqoop/part-m-00000

B、指定一张表，整个表的数据一起导入到集群中

sqoop-import --append --connect 'jdbc:mysql://192.168.154.134:3306/mmzs' --username 'root' --password '123456' --table testsqoop -m 1 --target-dir '/user/hive/warehouse/mmzs.db/testsqoop/' --fields-terminated-by '\\t' --lines-terminated-by '\\n'

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

\[hadoop@CloudDeskTop software\]$ sqoop-import --append --connect 'jdbc:mysql://192.168.154.134:3306/mmzs' --username 'root' --password '123456' --table testsqoop -m 1 --target-dir '/user/hive/warehouse/mmzs.db/testsqoop/' --fields-terminated-by '\\t' --lines-terminated-by '\\n'

17/12/30 22:28:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6

17/12/30 22:28:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.17/12/30 22:28:32INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.17/12/30 22:28:32INFO tool.CodeGenTool: Beginning code generation17/12/30 22:28:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.\* FROM \`testsqoop\` AS t LIMIT 1

17/12/30 22:28:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.\* FROM \`testsqoop\` AS t LIMIT 1

17/12/30 22:28:33 INFO orm.CompilationManager: HADOOP\_MAPRED\_HOME is /software/hadoop-2.7.3注:/tmp/sqoop-hadoop/compile/d427f3a0d1a3328c5dc9ae1bd6cbd988/testsqoop.java使用或覆盖了已过时的 API。

注: 有关详细信息, 请使用-Xlint:deprecation 重新编译。17/12/30 22:28:36 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/d427f3a0d1a3328c5dc9ae1bd6cbd988/testsqoop.jar17/12/30 22:28:36WARN manager.MySQLManager: It looks like you are importing from mysql.17/12/30 22:28:36 WARN manager.MySQLManager: This transfer can be faster! Use the --direct17/12/30 22:28:36 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.17/12/30 22:28:36INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)17/12/30 22:28:36 INFO mapreduce.ImportJobBase: Beginning importof testsqoop

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in \[jar:file:/software/hadoop-2.7.3/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: Found binding in \[jar:file:/software/hbase-1.2.6/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class\]

SLF4J: See http://www.slf4j.org/codes.html\#multiple\_bindings for an explanation.

SLF4J: Actual binding is of type \[org.slf4j.impl.Log4jLoggerFactory\]17/12/30 22:28:36INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar17/12/30 22:28:38INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps17/12/30 22:28:45INFO db.DBInputFormat: Using read commited transaction isolation17/12/30 22:28:45 INFO mapreduce.JobSubmitter: number of splits:1

17/12/30 22:28:46 INFO mapreduce.JobSubmitter: Submitting tokens forjob: job\_1514638990227\_000217/12/30 22:28:46INFO impl.YarnClientImpl: Submitted application application\_1514638990227\_000217/12/30 22:28:47 INFO mapreduce.Job: The url to track the job: http://master01:8088/proxy/application\_1514638990227\_0002/

17/12/30 22:28:47INFO mapreduce.Job: Running job: job\_1514638990227\_000217/12/30 22:29:29 INFO mapreduce.Job: Job job\_1514638990227\_0002 running in uber mode : false

17/12/30 22:29:29 INFO mapreduce.Job: map 0% reduce 0%

17/12/30 22:30:06 INFO mapreduce.Job: map 100% reduce 0%

17/12/30 22:30:07INFO mapreduce.Job: Job job\_1514638990227\_0002 completed successfully17/12/30 22:30:08 INFO mapreduce.Job: Counters: 30File System Counters

FILE: Number of bytes read=0FILE: Number of bytes written=138842FILE: Number of read operations=0FILE: Number of large read operations=0FILE: Number of write operations=0HDFS: Number of bytes read=87HDFS: Number of bytes written=128HDFS: Number of read operations=4HDFS: Number of large read operations=0HDFS: Number of write operations=2Job Counters

Launched map tasks=1Other local map tasks=1Total time spent by all maps in occupied slots (ms)=33630Total time spent by all reduces in occupied slots (ms)=0Total time spent by all map tasks (ms)=33630Total vcore-milliseconds taken by all map tasks=33630Total megabyte-milliseconds taken by all map tasks=34437120Map-Reduce Framework

Map input records=10Map output records=10Input split bytes=87Spilled Records=0Failed Shuffles=0Merged Map outputs=0GC time elapsed (ms)=177CPU time spent (ms)=2490Physical memory (bytes) snapshot=109060096Virtual memory (bytes) snapshot=850882560Total committed heap usage (bytes)=18972672File Input Format Counters

Bytes Read=0File Output Format Counters

Bytes Written=128

17/12/30 22:30:08 INFO mapreduce.ImportJobBase: Transferred 128 bytes in 89.4828 seconds (1.4304 bytes/sec)17/12/30 22:30:08 INFO mapreduce.ImportJobBase: Retrieved 10records.17/12/30 22:30:08 INFO util.AppendUtils: Appending to directory testsqoop

运行结果

在集群中查询是否真的导入了数据：

![c22490072c028de0f81d416c1175dfb4.png][]

在hive数据库中中查询是否真的导入了数据：

![35848031eddce2d6d03f3dde1adfff09.png][]

从结果可以证明数据导入到hdfs集群成功。

[8f900a89c6347c561fdf2122f13be562.png]: /images/20221119/7ee7add8d4914f818ed012e850cd7a7b.png
[961ddebeb323a10fe0623af514929fc1.png]: /images/20221119/07ca23bacb6044f6869e7b812d0273de.png
[97dfd62733ccca4ee5d90510ef5e9dc2.png]: /images/20221119/b7a9cd46826e4df8ab2d9103524d1ce6.png
[9fa5357f1276bd5a796d7a1dda161325.png]: /images/20221119/9afe2857cb9548f0a4d2bea1c88517fa.png
[a7277903096593d74dcac6272c47c525.png]: /images/20221119/9e7917ccd2624e9c91431f602c961131.png
[69ae73c76407cb390edc9261bc952454.png]: /images/20221119/b9776ab99e154d6eac8afd79ba06638a.png
[8433a66d80dae04fd2abb77c6dc72c19.png]: /images/20221119/1965c75ec4d84cc7acbf27fbbdf448ce.png
[8e2800c2253c3d7bb3facc961204312a.png]: /images/20221119/6bd0e7fd77ce4a91bcd00b8e811849cb.png
[42a328957678a4180fd82350d8831cf7.png]: /images/20221119/4a1444e7a1364de995c9dafcec0231c7.png
[b9d1afc526b06ed6140d26c841c89727.png]: /images/20221119/117ee0d38e6a4d9baabde986af02fae5.png
[25efb84551c12b14c20e352ead9f1ab1.png]: /images/20221119/648e9dbad7314917b0e467c858b4b1ae.png
[6d7e6a519df04c7745dc5dd8139a2e50.png]: /images/20221119/a826fa57fbcd4679bfff107ce7973e6c.png
[c22490072c028de0f81d416c1175dfb4.png]: /images/20221119/39b488db3b534b51b89aac4ed6f35cef.png
[35848031eddce2d6d03f3dde1adfff09.png]: /images/20221119/2ab0c4b022834ff9a127204c219234db.png