sqoop详解-导入导出

从mysql 导入到 hadoop 文件:

http://ftp.wayne.edu/apache/sqoop/

-- sqoop下载地址:https://mirrors.tuna.tsinghua.edu.cn/apache/sqoop/1.4.7/

大家下的时候注意自己的版本!!(注意不要下sqoop2,不是给我们这类需求使用的。)

(一定一定要选择 bin__hadoop 版本,否则也可能报错 java.lang.NoClassDefFoundError: org/apache/avro/LogicalType

或者Error: Could not find or load main class org.apache.sqoop.Sqoop 错误 )

-- bin_hadoop 版本为sqoop 1 , 不带bin_hadoop 的为sqoop 2

 

2、sqoop安装

2.1解压sqoop

首先将压缩包放到你想存放的位置,然后输入

tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz(你自己的名字)--

 

进行解压即可,建议加压完成后将名字改成sqoop方便配置,在配置其他工具时也是这样,看着也不那么乱,是一个好习惯。(当然按你个人喜好,而且配置好文件后不要改名字,会导致无法运行,路径错误。)

 

2.2修改配置文件

sqoop的配置文件十分简单,只要配一个java的环境变量就可以了,我们进入解压好的文件夹,输入

mv sqoop-env-template.sh sqoop-env.sh //将匹配文件改名

vi sqoop-env.sh //编辑配置文件

 

打开文件之后找到下面这些变量进行配置,如果有的话就取消注释进行更改,没有的话就自己新建。

export HADOOP_COMMON_HOME=/usr/local/hadoop/

export HADOOP_MAPRED_HOME=/usr/local/hadoop/

export HIVE_HOME=/usr/local/hive/

 

--个人sqoop 配置文件:

[hadoop@big-master1 ~]$ cat /usr/local/sqoop/conf/sqoop-env.sh |grep -Ev '^#|^$'

export HADOOP_COMMON_HOME=/usr/local/hadoop

export HADOOP_MAPRED_HOME=/usr/local/hadoop

export ZOOCFGDIR=/usr/local/zookeeper

2.3放入JAR包

将之前配置mysql的jar包放入sqoop的lib文件夹下。(我传过资源,不知道为什么要收5个币,大家也可以自己搜一下,要注意这个jar包和hive中的jar包要匹配且兼容mysql的版本才可以。8.x的jar包已经兼容了5.5,5.6,5.7和8.0)

mysql-connector-java-8.0.13.jar

 

 

2.4检查配置

进入到sqoop/bin目录下执行./sqoop-version也可以自己配置环境变量直接运行,方法和配置java的一样,自己去etc/profile 下面复制一个java得改改名字,路径也可以。

 

[hadoop@big-master1 bin]$ ./sqoop-version

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 01:52:49 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

Sqoop 1.4.7

git commit id 2328971411f57f0cb683dfb79d19d4d19d185dd8

Compiled by maugli on Thu Dec 21 15:59:58 STD 2017

 

运行完成后出现版本号就可以了,上面的这个错误,找不到主类,我感觉是什么冲突了,但是也没影响我的后续操作,所以我还没处理,大家看到自己也有的话不要紧张,解决了的话也可以留言教教我。

 

2.5检查与mysql的联通

还是在bin目录下输入

./sqoop-list-databases --connect jdbc:mysql://localhost:3306 --username root --password admin

 

 

这里的root是你mysql的用户名admin是密码,输入自己的,3306是配置好的端口,大家记不住可以检查一下自己的hive文件中的关于mysql的配置,一般就是你安装mysql的机器的hostname,端口号应该是默认的大家都是这些。

(展示你mysql的数据库)

 

2.6sqoop的数据导入

我们导入数据首先要有这个数据表,并且里面有我们的数据。

输入mysql -uroot -p进入mysql

输入create table test(id int .......)可以建表,按自己的喜好来,注意mysql建表每句话后面都要有“;”,实在不行用个以前建好的或者自己搜一个复制上。

输入load data local inpath" 文件路径" into table test;导入数据信息

 

在bin目录下输入:(为了不防止大家肤质使用,我把解释放在下面的图里)

./sqoop import --connect jdbc:mysql://h02:3306/test --username root --password admin --target-dir /sqooptest1 --fields-terminated-by ‘,’ --table test_sqoop --split-by id --m 1

 

开始运行:

 

显示successful就表示成功了,下面的failed不用理会,这是我们打开集群

 

就会发现我们指定的文件夹出现了,里面就是我们表里的内容。

 

输入hadoop fs -cat /sqooptest1/part-m-00000查看是否成功导入

 

 

2.7sqoop的数据导出

和导入一样,十分的简单,我们首先需要在mysql中创建一个和我们要导出的信息一样类型表,比如我在把刚才的sqooptest导出到本地,输入:

create table test1(id int .......); //一定和你导出的数据类型相同

 

不理解类型匹配什么意思的看下面:

 

在做好准备工作之后,我们在bin目录下执行导出命令(就不解释了,和导入差不多的)

./sqoop export --connect jdbc:mysql://h02:3306/test --username root --password admin --table test1 --export-dir /sqooptest1/

 

 

成功之后,输入mysql -uroot -p进入数据库

use test; //你使用的数据库

select * from test1; //你指定导出的表

 

 

成功,应该是只显示第二个35上面的内容的,因为偷懒了,所以和自己测试的时候用的一个表,但是还是试验成功。

 

 

MySQL 操作文档:

#############

[hadoop@big-master1 lib]$ sqoop list-databases --connect jdbc:mysql://192.168.41.17:3306/ --username sqoopuser --password sqoopuser#12345

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

Error: Could not find or load main class org.apache.sqoop.Sqoop

-- 没有 sqoop 1.4.7.jar 包所致,原来的sqoop 下载错误导致 最后一个error 错误。

 

## jar 包下载:

 

sqoop下载地址:https://mirrors.tuna.tsinghua.edu.cn/apache/sqoop/1.4.7/

 

[hadoop@big-master1 bin]$ ./sqoop-version

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 01:52:49 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

Sqoop 1.4.7

git commit id 2328971411f57f0cb683dfb79d19d4d19d185dd8

Compiled by maugli on Thu Dec 21 15:59:58 STD 2017

export SQOOP_HOME="/home/work/soft/sqoop-1.4.7.bin__hadoop-2.6.0"

   export PATH="${PATH}:${SQOOP_HOME}/bin/"

   执行 source ~/.bashrc

 

4.  部署mysql驱动包

 

    https://dev.mysql.com/downloads/connector/j/8.0.html  (下载地址)

   下载回来的文件是 mysql-connector-java-8.0.13.tar.gz ,解压之后。拷贝 mysql-connector-java-8.0.13.jar 到 ${SQOOP_HOME}/lib/ 目录,即

 

[hadoop@big-master1 bin]$ sqoop list-databases --connect jdbc:mysql://192.168.41.17:3306/ --username sqoopuser --password sqoopuser#12345

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 01:58:29 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 01:58:29 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 01:58:29 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

Sun May 24 01:58:30 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

20/05/24 01:58:30 ERROR manager.CatalogQueryManager: Failed to list databases

java.sql.SQLException: Access denied for user 'sqoopuser'@'192.168.41.20' (using password: YES)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:864)

at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1707)

at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1217)

at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)

at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)

at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)

at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)

at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.

警告:不建议在没有服务器身份验证的情况下建立SSL连接。根据MySQL 5.5.45+, 5.6.26+和5.7.6+要求,如果没有设置显式选项,默认情况下必须建立SSL连接。为了符合不使用SSL的现有应用程序,verifyServerCertificate属性设置为“false”。您需要通过设置useSSL=false显式禁用SSL,或者设置useSSL=true并为服务器证书验证提供信任库。

 

###

SSL 有问题: --> 1.Java使用mysql-jdbc连接MySQL出现如下警告:

 

[hadoop@big-master1 bin]$ ./sqoop-list-databases --connect jdbc:mysql://192.168.41.17:3306/?useSSL=false --username sqoopuser --password sqoopuser#12345

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 02:29:46 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 02:29:46 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 02:29:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

20/05/24 02:29:47 ERROR manager.CatalogQueryManager: Failed to list databases

java.sql.SQLException: Access denied for user 'sqoopuser'@'192.168.41.20' (using password: YES)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:864)

at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1707)

at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1217)

--权限问题

 

(root@localhost@(none))>> grant all privileges on *.* to sqoopuser@'192.168.41.20' identified by 'sqoopuser#12345';

Query OK, 0 rows affected, 1 warning (0.12 sec)

-- 每个hadoop 节点都要授权:

(root@localhost@(none))>> flush privileges;

Query OK, 0 rows affected (0.02 sec)

--授权后:

[hadoop@big-master1 bin]$ ./sqoop-list-databases --connect jdbc:mysql://192.168.41.17:3306/?useSSL=false --username sqoopuser --password sqoopuser#12345

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 02:43:55 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 02:43:55 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 02:43:55 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

information_schema

martin

mysql

performance_schema

sys

 

 

##

[hadoop@big-master1 bin]$ ./sqoop import --connect jdbc:mysql://192.168.41.17:3306/martin --username sqoopuser --password sqoopuser#12345 --target-dir /sqoop-mysql22 --fields-terminated-by ',' --table test_user --split-by id --m 1

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 02:58:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 02:58:40 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 02:58:40 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

20/05/24 02:58:40 INFO tool.CodeGenTool: Beginning code generation

Sun May 24 02:58:41 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

20/05/24 02:58:41 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test_user` AS t LIMIT 1

20/05/24 02:58:41 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test_user` AS t LIMIT 1

20/05/24 02:58:41 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop

Note: /tmp/sqoop-hadoop/compile/ec39f431909cf1e3515845189ad4dcf7/test_user.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

20/05/24 02:58:44 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/ec39f431909cf1e3515845189ad4dcf7/test_user.jar

20/05/24 02:58:44 WARN manager.MySQLManager: It looks like you are importing from mysql.

20/05/24 02:58:44 WARN manager.MySQLManager: This transfer can be faster! Use the --direct

20/05/24 02:58:44 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.

20/05/24 02:58:44 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)

20/05/24 02:58:44 INFO mapreduce.ImportJobBase: Beginning import of test_user

20/05/24 02:58:44 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

20/05/24 02:58:46 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

Sun May 24 02:59:08 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

20/05/24 02:59:08 INFO db.DBInputFormat: Using read commited transaction isolation

20/05/24 02:59:09 INFO mapreduce.JobSubmitter: number of splits:1

20/05/24 02:59:10 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1589524167213_0005

20/05/24 02:59:11 INFO impl.YarnClientImpl: Submitted application application_1589524167213_0005

20/05/24 02:59:11 INFO mapreduce.Job: The url to track the job: http://big-master1:8088/proxy/application_1589524167213_0005/

20/05/24 02:59:11 INFO mapreduce.Job: Running job: job_1589524167213_0005

20/05/24 02:59:23 INFO mapreduce.Job: Job job_1589524167213_0005 running in uber mode : false

20/05/24 02:59:23 INFO mapreduce.Job: map 0% reduce 0%

20/05/24 02:59:35 INFO mapreduce.Job: map 100% reduce 0%

20/05/24 02:59:39 INFO mapreduce.Job: Job job_1589524167213_0005 completed successfully

20/05/24 02:59:39 INFO mapreduce.Job: Counters: 30

File System Counters

FILE: Number of bytes read=0

FILE: Number of bytes written=182081

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=513

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)=9622

Total time spent by all reduces in occupied slots (ms)=0

Total time spent by all map tasks (ms)=9622

Total vcore-milliseconds taken by all map tasks=9622

Total megabyte-milliseconds taken by all map tasks=9852928

Map-Reduce Framework

Map input records=3

Map output records=3

Input split bytes=87

Spilled Records=0

Failed Shuffles=0

Merged Map outputs=0

GC time elapsed (ms)=241

CPU time spent (ms)=2510

Physical memory (bytes) snapshot=185679872

Virtual memory (bytes) snapshot=2127249408

Total committed heap usage (bytes)=117440512

File Input Format Counters

Bytes Read=0

File Output Format Counters

Bytes Written=513

20/05/24 02:59:39 INFO mapreduce.ImportJobBase: Transferred 513 bytes in 53.7068 seconds (9.5519 bytes/sec)

20/05/24 02:59:39 INFO mapreduce.ImportJobBase: Retrieved 3 records.

 

 

[hadoop@big-master1 ~]$ hdfs dfs -ls /sqoop-mysql22

Found 2 items

-rw-r--r-- 3 hadoop supergroup 0 2020-05-24 02:59 /sqoop-mysql22/_SUCCESS

-rw-r--r-- 3 hadoop supergroup 513 2020-05-24 02:59 /sqoop-mysql22/part-m-00000

[hadoop@big-master1 ~]$ hdfs dfs -cat /sqoop-mysql22/part-m-00000

localhost,root,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,mysql_native_password,,N,2020-05-21 23:05:49.0,null,N

localhost,mysql.session,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,mysql_native_password,*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE,N,2020-05-21 23:05:51.0,null,Y

localhost,mysql.sys,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,mysql_native_password,*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE,N,2020-05-21 23:05:51.0,null,Y

 

 

 

 

######################3

[hadoop@big-master1 ~]$ sqoop import --connect jdbc:mysql://192.168.41.17:3306/martin?useSSL=false --username sqoopuser --password sqoopuser#12345 --target-dir /sqoop-mysql11 --fields-terminated-by ',' --table tbl_sqoop --split-by id --m 1

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 03:03:49 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 03:03:49 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 03:03:49 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

20/05/24 03:03:49 INFO tool.CodeGenTool: Beginning code generation

20/05/24 03:03:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tbl_sqoop` AS t LIMIT 1

20/05/24 03:03:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tbl_sqoop` AS t LIMIT 1

20/05/24 03:03:50 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop

Note: /tmp/sqoop-hadoop/compile/aa35fa52d35639b881303fb0af24044b/tbl_sqoop.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

20/05/24 03:03:52 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/aa35fa52d35639b881303fb0af24044b/tbl_sqoop.jar

20/05/24 03:03:52 WARN manager.MySQLManager: It looks like you are importing from mysql.

20/05/24 03:03:52 WARN manager.MySQLManager: This transfer can be faster! Use the --direct

20/05/24 03:03:52 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.

20/05/24 03:03:52 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)

20/05/24 03:03:52 INFO mapreduce.ImportJobBase: Beginning import of tbl_sqoop

20/05/24 03:03:53 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

20/05/24 03:03:54 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

20/05/24 03:04:16 INFO db.DBInputFormat: Using read commited transaction isolation

20/05/24 03:04:17 INFO mapreduce.JobSubmitter: number of splits:1

20/05/24 03:04:18 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1589524167213_0006

20/05/24 03:04:19 INFO impl.YarnClientImpl: Submitted application application_1589524167213_0006

20/05/24 03:04:20 INFO mapreduce.Job: The url to track the job: http://big-master1:8088/proxy/application_1589524167213_0006/

20/05/24 03:04:20 INFO mapreduce.Job: Running job: job_1589524167213_0006

20/05/24 03:04:32 INFO mapreduce.Job: Job job_1589524167213_0006 running in uber mode : false

20/05/24 03:04:32 INFO mapreduce.Job: map 0% reduce 0%

20/05/24 03:04:42 INFO mapreduce.Job: map 100% reduce 0%

20/05/24 03:04:46 INFO mapreduce.Job: Job job_1589524167213_0006 completed successfully

20/05/24 03:04:46 INFO mapreduce.Job: Counters: 30

File System Counters

FILE: Number of bytes read=0

FILE: Number of bytes written=181397

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=180

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)=7740

Total time spent by all reduces in occupied slots (ms)=0

Total time spent by all map tasks (ms)=7740

Total vcore-milliseconds taken by all map tasks=7740

Total megabyte-milliseconds taken by all map tasks=7925760

Map-Reduce Framework

Map input records=10

Map output records=10

Input split bytes=87

Spilled Records=0

Failed Shuffles=0

Merged Map outputs=0

GC time elapsed (ms)=125

CPU time spent (ms)=1960

Physical memory (bytes) snapshot=180146176

Virtual memory (bytes) snapshot=2120441856

Total committed heap usage (bytes)=113770496

File Input Format Counters

Bytes Read=0

File Output Format Counters

Bytes Written=180

20/05/24 03:04:46 INFO mapreduce.ImportJobBase: Transferred 180 bytes in 52.2869 seconds (3.4425 bytes/sec)

20/05/24 03:04:46 INFO mapreduce.ImportJobBase: Retrieved 10 records.

 

 

[hadoop@big-master1 ~]$ hdfs dfs -ls /sqoop-mysql11

Found 2 items

-rw-r--r-- 3 hadoop supergroup 0 2020-05-24 03:04 /sqoop-mysql11/_SUCCESS

-rw-r--r-- 3 hadoop supergroup 180 2020-05-24 03:04 /sqoop-mysql11/part-m-00000

[hadoop@big-master1 ~]$ hdfs dfs -cat /sqoop-mysql11/part-m-00000

0001,jbw,23,man

0002,33,18,girl

0003,jack,23,woman

0004,233,23,woman

0005,zhansan,23,man

0006,lisi,23,man

0007,wangermazi,23,man

0008,jbw2,23,man

0009,jbw3,23,man

0010,jbw4,23,man

 

 

从hadoop 到处数据到MySQL数据库:

(root@localhost@martin)>> CREATE TABLE `tbl_sqoop_01` (

-> `id` varchar(11) DEFAULT NULL,

-> `name` varchar(11) DEFAULT NULL,

-> `age` int(11) DEFAULT NULL,

-> `sex` varchar(11) DEFAULT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.30 sec)

 

(root@localhost@martin)>> show tables;

+------------------+

| Tables_in_martin |

+------------------+

| tbl_sqoop |

| tbl_sqoop_01 |

| test_user |

+------------------+

3 rows in set (0.00 sec)

 

[hadoop@big-master1 ~]$ sqoop export --connect jdbc:mysql://192.168.41.17:3306/martin?useSSL=false --username sqoopuser --password sqoopuser#12345 --table tbl_sqoop_01 --export-dir /sqoop-mysql11/

Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

20/05/24 03:15:03 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7

20/05/24 03:15:03 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

20/05/24 03:15:04 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

20/05/24 03:15:04 INFO tool.CodeGenTool: Beginning code generation

20/05/24 03:15:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tbl_sqoop_01` AS t LIMIT 1

20/05/24 03:15:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tbl_sqoop_01` AS t LIMIT 1

20/05/24 03:15:04 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop

Note: /tmp/sqoop-hadoop/compile/78b1fff3393c79458be15bfd0a23ad44/tbl_sqoop_01.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

20/05/24 03:15:07 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/78b1fff3393c79458be15bfd0a23ad44/tbl_sqoop_01.jar

20/05/24 03:15:07 INFO mapreduce.ExportJobBase: Beginning export of tbl_sqoop_01

20/05/24 03:15:07 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

20/05/24 03:15:09 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative

20/05/24 03:15:09 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative

20/05/24 03:15:09 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

20/05/24 03:15:29 INFO input.FileInputFormat: Total input files to process : 1

20/05/24 03:15:29 INFO input.FileInputFormat: Total input files to process : 1

20/05/24 03:15:30 INFO mapreduce.JobSubmitter: number of splits:4

20/05/24 03:15:30 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative

20/05/24 03:15:31 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1589524167213_0007

20/05/24 03:15:32 INFO impl.YarnClientImpl: Submitted application application_1589524167213_0007

20/05/24 03:15:32 INFO mapreduce.Job: The url to track the job: http://big-master1:8088/proxy/application_1589524167213_0007/

20/05/24 03:15:32 INFO mapreduce.Job: Running job: job_1589524167213_0007

20/05/24 03:15:45 INFO mapreduce.Job: Job job_1589524167213_0007 running in uber mode : false

20/05/24 03:15:45 INFO mapreduce.Job: map 0% reduce 0%

20/05/24 03:15:56 INFO mapreduce.Job: map 100% reduce 0%

20/05/24 03:15:59 INFO mapreduce.Job: Job job_1589524167213_0007 completed successfully

20/05/24 03:15:59 INFO mapreduce.Job: Counters: 30

File System Counters

FILE: Number of bytes read=0

FILE: Number of bytes written=724200

FILE: Number of read operations=0

FILE: Number of large read operations=0

FILE: Number of write operations=0

HDFS: Number of bytes read=954

HDFS: Number of bytes written=0

HDFS: Number of read operations=16

HDFS: Number of large read operations=0

HDFS: Number of write operations=0

Job Counters

Launched map tasks=4

Data-local map tasks=4

Total time spent by all maps in occupied slots (ms)=33650

Total time spent by all reduces in occupied slots (ms)=0

Total time spent by all map tasks (ms)=33650

Total vcore-milliseconds taken by all map tasks=33650

Total megabyte-milliseconds taken by all map tasks=34457600

Map-Reduce Framework

Map input records=10

Map output records=10

Input split bytes=492

Spilled Records=0

Failed Shuffles=0

Merged Map outputs=0

GC time elapsed (ms)=1046

CPU time spent (ms)=5050

Physical memory (bytes) snapshot=707518464

Virtual memory (bytes) snapshot=8466890752

Total committed heap usage (bytes)=463994880

File Input Format Counters

Bytes Read=0

File Output Format Counters

Bytes Written=0

20/05/24 03:15:59 INFO mapreduce.ExportJobBase: Transferred 954 bytes in 50.3426 seconds (18.9501 bytes/sec)

20/05/24 03:15:59 INFO mapreduce.ExportJobBase: Exported 10 records.

 

(root@localhost@martin)>> select * from tbl_sqoop_01;

+------+------------+------+-------+

| id | name | age | sex |

+------+------------+------+-------+

| 0007 | wangermazi | 23 | man |

| 0008 | jbw2 | 23 | man |

| 0001 | jbw | 23 | man |

| 0009 | jbw3 | 23 | man |

| 0002 | 33 | 18 | girl |

| 0003 | jack | 23 | woman |

| 0010 | jbw4 | 23 | man |

| 0004 | 233 | 23 | woman |

| 0005 | zhansan | 23 | man |

| 0006 | lisi | 23 | man |

+------+------------+------+-------+

10 rows in set (0.00 sec)

#####################

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值