从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)
#####################