1.Sqoop 组件安装与配置
1.1. 实验目的
完成本实验，您应该能够：
下载和解压 Sqoop
配置 Sqoop 环境
安装 Sqoop
Sqoop 模板命令
1.2. 实验要求
熟悉 Sqoop 环境
熟悉 Sqoop 模板命令
1.3. 实验环境
本实验所需之主要资源环境如表 1-1 所示。
表 1-1 资源环境
| 服务器集群 |
单节点，机器最低配置：双核 CPU、8GB 内存、100G 硬盘 |
| 运行环境 |
CentOS.7.3 |
| 服务和组件 |
服务和组件根据实验需求安装 |
1.4. 实验过程
1.4.1. 实验任务一：解压 Sqoop
[root@master ~]# ls
anaconda-ks.cfg mysql-community-common-5.7.18-1.el7.x86_64.rpm mysql-connector
apche-hive mysql-community-devel-5.7.18-1.el7.x86_64.rpm zookeeper-3.4.8.tar.gz
hbase-1.2.1-bin.tar.gz mysql-community-libs-5.7.18-1.el7.x86_64.rpm
mysql-community-client-5.7.18-1.el7.x86_64.rpm mysql-community-server-5.7.18-1.el7.x86_64.rpm
[root@master ~]# ls
anaconda-ks.cfg mysql-community-common-5.7.18-1.el7.x86_64.rpm mysql-connector
apche-hive mysql-community-devel-5.7.18-1.el7.x86_64.rpm sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
hbase-1.2.1-bin.tar.gz mysql-community-libs-5.7.18-1.el7.x86_64.rpm zookeeper-3.4.8.tar.gz
mysql-community-client-5.7.18-1.el7.x86_64.rpm mysql-community-server-5.7.18-1.el7.x86_64.rpm
[root@master ~]# tar xf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /usr/local/src
[root@master ~]# cd /usr/local/src
[root@master src]# ls
hadoop hbase hive jdk sqoop-1.4.7.bin__hadoop-2.6.0 zookeeper
将解压后生成的 sqoop-1.4.7.bin__hadoop-2.6.0 文件夹更名为 sqoop。
[root@master src]# mv sqoop-1.4.7.bin__hadoop-2.6.0/ sqoop
[root@master src]# ls
hadoop hbase hive jdk sqoop zookeeper
1.4.2. 实验任务二：配置 Sqoop 环境
1.4.2.1. 步骤一：创建 Sqoop 的配置文件 sqoop-env.sh
复制 sqoop-env-template.sh 模板，并将模板重命名为 sqoop-env.sh
[root@master src]# cd sqoop/conf/
[root@master conf]# ls
oraoop-site-template.xml sqoop-env-template.cmd sqoop-env-template.sh sqoop-site-template.xml sqoop-site.xml
[root@master conf]# cp sqoop-env-template.sh sqoop-env.sh
[root@master conf]# ls
oraoop-site-template.xml sqoop-env.sh sqoop-env-template.cmd sqoop-env-template.sh sqoop-site-template.xml sqoop-site.xml
1.4.2.2. 步骤二：修改 sqoop-env.sh 文件，添加 Hdoop、Hbase、Hive 等组件的安装路径
注意，下面各组件的安装路径需要与实际环境中的安装路径保持一致
[root@master conf]# vi sqoop-env.sh
#添加
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/usr/local/src/hadoop
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/usr/local/src/hadoop
#set the path to where bin/hbase is available
export HBASE_HOME=/usr/local/src/hbase
#Set the path to where bin/hive is available
export HIVE_HOME=/usr/local/src/hive
1.4.2.3. 步骤三：配置 Linux 系统环境变量，添加 Sqoop 组件的路径
[root@master ~]# vi /etc/profile.d/sqoop.sh
#添加
export SQOOP_HOME=/usr/local/src/sqoop
export PATH=$PATH:$SQOOP_HOME/bin
export CLASSPATH=$CLASSPATH:$SQOOP_HOME/lib
1.4.2.4.步骤四：连接数据库
为了使 Sqoop 能够连接 MySQL 数据库，需要将/opt/software/mysql-connector-jav a-5.1.46.jar 文件放入 sqoop 的 lib 目录中。该 jar 文件的版本需要与 MySQL 数据库的 版本相对应，否则 Sqoop 导入数据时会报错。（mysql-connector-java-5.1.46.jar 对应 的是 MySQL 5.7 版本）若该目录没有 jar 包,则使用第 6 章导入 home 目录的 jar 包。
[root@master ~]# cp mysql-connector-java-5.1.46.jar /usr/local/src/sqoop/lib/
1.4.3. 实验任务三：启动 Sqoop
1.4.3.1. 步骤一：执行 Sqoop 前需要先启动 Hadoop 集群
在 master 节点切换到 hadoop 用户执行 start-all.sh 命令启动 Hadoop 集群
[root@master ~]# su - hadoop
Last login: Thu Apr 6 05:27:05 EDT 2023 on pts/0
[hadoop@master ~]$ start-all.sh
This script is Deprecated. Instead use start-dfs.sh and start-yarn.sh
Starting namenodes on [master]
master: starting namenode, logging to /usr/local/src/hadoop/logs/hadoop-hadoop-namenode-master.out
slave1: starting datanode, logging to /usr/local/src/hadoop/logs/hadoop-hadoop-datanode-slave1.out
slave2: starting datanode, logging to /usr/local/src/hadoop/logs/hadoop-hadoop-datanode-slave2.out
192.168.10.20: ssh: connect to host 192.168.10.20 port 22: Connection refused
192.168.10.30: ssh: connect to host 192.168.10.30 port 22: Connection refused
Starting secondary namenodes [0.0.0.0]
0.0.0.0: starting secondarynamenode, logging to /usr/local/src/hadoop/logs/hadoop-hadoop-secondarynamenode-master.out
starting yarn daemons
resourcemanager running as process 1725. Stop it first.
slave1: starting nodemanager, logging to /usr/local/src/hadoop/logs/yarn-hadoop-nodemanager-slave1.out
slave2: starting nodemanager, logging to /usr/local/src/hadoop/logs/yarn-hadoop-nodemanager-slave2.out
192.168.10.20: ssh: connect to host 192.168.10.20 port 22: Connection refused
192.168.10.30: ssh: connect to host 192.168.10.30 port 22: Connection refused
1.4.3.2. 步骤二：检查 Hadoop 集群的运行状态
[hadoop@master ~]$ jps
1696 SecondaryNameNode
1890 Jps
1493 NameNode
1795 ResourceManager
1.4.3.3. 步骤三：测试 Sqoop 是否能够正常连接 MySQL 数据库
# Sqoop 连接 MySQL 数据库 P 大写 密码 Passwd123$
[hadoop@master ~]$ sqoop list-databases --connect jdbc:mysql://127.0.0.1:3306/ --username root -P
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/11 23:27:15 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password: # 此处需要输入 mysql 数据库的密码(我的密码为:Passwd123$)
23/04/11 23:27:32 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Tue Apr 11 23:27:32 EDT 2023 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.
information_schema
hive
mysql
performance_schema
sys
1.4.3.4. 步骤四：连接 hive
为了使 Sqoop 能够连接 Hive，需要将 hive 组件/usr/local/src/hive/lib 目录下的hive-common-2.0.0.jar 也放入 Sqoop 安装路径的 lib 目录中。
[hadoop@master ~]$ cp /usr/local/src/hive/lib/hive-common-2.0.0.jar /usr/local/src/sqoop/lib/
1.4.4. 实验任务四：Sqoop 模板命令
1.4.4.1. 步骤一：创建 MySQL 数据库和数据表。
创建 sample 数据库，在 sample 中创建 student 表，在 student 表中插入了 3 条数 据。
# 登录 MySQL 数据库
[hadoop@master ~]$ mysql -uroot -pPasswd123$
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 登录 MySQL 数据库
mysql> create database sample;
Query OK, 1 row affected (0.00 sec)
# 使用 sample 库
mysql> use sample;
Database changed
# 创建 student 表，该数据表有number 学号和 name 姓名两个字段
mysql> create table student(number char(9) primary key, name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values('01','zhanglin');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values('02','lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('03','yingliu');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('04','shengsan');
Query OK, 1 row affected (0.00 sec)
# 查询 student 表的数据
mysql> select * from student;
+--------+----------+
| number | name |
+--------+----------+
| 01 | zhanglin |
| 02 | lisi |
| 03 | yingliu |
| 04 | shengsan |
+--------+----------+
4 rows in set (0.00 sec)
mysql> quit;
Bye
1.4.4.2. 步骤二：在 Hive 中创建 sample 数据库和 student 数据表
[hadoop@master ~]$ hive
Wed Apr 12 04:49:37 EDT 2023 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.
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> create database sample; # 创建 sample 库
OK
Time taken: 0.689 seconds
hive> show databases; # 查询所有数据库
OK
default
sample
Time taken: 0.154 seconds, Fetched: 2 row(s)
hive> use sample; # 使用 sample 库
OK
Time taken: 0.016 seconds
hive> create table student(number STRING, name STRING) row format delimited fields terminated by "|" stored as textfile; # 创建 student 表
OK
Time taken: 0.174 seconds
hive> exit; # 退出 hive 命令行
1.4.4.3. 步骤三：从 MySQL 导出数据，导入 Hive
需要说明该命令的以下几个参数：
1）--connect：MySQL 数据库连接 URL。
2）--username 和--password：MySQL 数据库的用户名和密码。
3）--table：导出的数据表名。
4）--fields-terminated-by：Hive 中字段分隔符。
5）--delete-target-dir：删除导出目的目录。
6）--num-mappers：Hadoop 执行 Sqoop 导入导出启动的 map 任务数。
7）--hive-import --hive-database：导出到 Hive 的数据库名。
8）--hive-table：导出到 Hive 的表名。
[hadoop@master ~]$ sqoop import --connect jdbc:mysql://master:3306/sample --username root --password Passwd123$ --table student --fields-terminated-by '|' --delete-target-dir --num-mappers 1 --hive-import --hive-database sample --hive-table student
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 04:56:38 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 04:56:38 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 04:56:38 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
23/04/12 04:56:38 INFO tool.CodeGenTool: Beginning code generation
Wed Apr 12 04:56:38 EDT 2023 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.
23/04/12 04:56:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 04:56:39 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 04:56:39 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/src/hadoop
Note: /tmp/sqoop-hadoop/compile/db33a01f018169ace6d8b1c70bfb08d0/student.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
23/04/12 04:56:40 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/db33a01f018169ace6d8b1c70bfb08d0/student.jar
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 04:56:41 INFO tool.ImportTool: Destination directory student is not present, hence not deleting.
23/04/12 04:56:41 WARN manager.MySQLManager: It looks like you are importing from mysql.
23/04/12 04:56:41 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
23/04/12 04:56:41 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
23/04/12 04:56:41 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
23/04/12 04:56:41 INFO mapreduce.ImportJobBase: Beginning import of student
23/04/12 04:56:41 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
23/04/12 04:56:41 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
23/04/12 04:56:41 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Wed Apr 12 04:56:43 EDT 2023 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.
23/04/12 04:56:43 INFO db.DBInputFormat: Using read commited transaction isolation
23/04/12 04:56:44 INFO mapreduce.JobSubmitter: number of splits:1
23/04/12 04:56:44 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1681289315363_0001
23/04/12 04:56:44 INFO impl.YarnClientImpl: Submitted application application_1681289315363_0001
23/04/12 04:56:44 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1681289315363_0001/
23/04/12 04:56:44 INFO mapreduce.Job: Running job: job_1681289315363_0001
23/04/12 04:56:52 INFO mapreduce.Job: Job job_1681289315363_0001 running in uber mode : false
23/04/12 04:56:52 INFO mapreduce.Job: map 0% reduce 0%
23/04/12 04:56:58 INFO mapreduce.Job: map 100% reduce 0%
23/04/12 04:56:59 INFO mapreduce.Job: Job job_1681289315363_0001 completed successfully
23/04/12 04:56:59 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=134465
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=43
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)=3103
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=3103
Total vcore-seconds taken by all map tasks=3103
Total megabyte-seconds taken by all map tasks=3177472
Map-Reduce Framework
Map input records=4
Map output records=4
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=109
CPU time spent (ms)=1250
Physical memory (bytes) snapshot=173342720
Virtual memory (bytes) snapshot=2105765888
Total committed heap usage (bytes)=88604672
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=43
23/04/12 04:56:59 INFO mapreduce.ImportJobBase: Transferred 43 bytes in 17.6902 seconds (2.4307 bytes/sec)
23/04/12 04:56:59 INFO mapreduce.ImportJobBase: Retrieved 4 records.
23/04/12 04:56:59 INFO mapreduce.ImportJobBase: Publishing Hive/Hcat import job data to Listeners for table student
Wed Apr 12 04:56:59 EDT 2023 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.
23/04/12 04:56:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 04:56:59 INFO hive.HiveImport: Loading uploaded data into Hive
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Class path contains multiple SLF4J bindings.
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/hive-jdbc-2.0.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hbase/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
23/04/12 04:57:03 INFO hive.HiveImport: SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
23/04/12 04:57:03 INFO hive.HiveImport:
23/04/12 04:57:03 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/usr/local/src/hive/lib/hive-common-2.0.0.jar!/hive-log4j2.properties
23/04/12 04:57:04 INFO hive.HiveImport: Wed Apr 12 04:57:04 EDT 2023 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.
23/04/12 04:57:04 INFO hive.HiveImport: Wed Apr 12 04:57:04 EDT 2023 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.
23/04/12 04:57:04 INFO hive.HiveImport: Wed Apr 12 04:57:04 EDT 2023 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.
23/04/12 04:57:04 INFO hive.HiveImport: Wed Apr 12 04:57:04 EDT 2023 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.
23/04/12 04:57:06 INFO hive.HiveImport: Wed Apr 12 04:57:06 EDT 2023 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.
23/04/12 04:57:06 INFO hive.HiveImport: Wed Apr 12 04:57:06 EDT 2023 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.
23/04/12 04:57:06 INFO hive.HiveImport: Wed Apr 12 04:57:06 EDT 2023 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.
23/04/12 04:57:06 INFO hive.HiveImport: Wed Apr 12 04:57:06 EDT 2023 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.
23/04/12 04:57:08 INFO hive.HiveImport: OK
23/04/12 04:57:08 INFO hive.HiveImport: Time taken: 0.902 seconds
23/04/12 04:57:09 INFO hive.HiveImport: Loading data to table sample.student
23/04/12 04:57:09 INFO hive.HiveImport: OK
23/04/12 04:57:09 INFO hive.HiveImport: Time taken: 0.515 seconds
23/04/12 04:57:09 INFO hive.HiveImport: Hive import complete.
23/04/12 04:57:09 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.
删除 MySQL 数据， student 表中 number 为主键，添加信息导致主键重复，报错，所以删除表数据
[hadoop@master ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use sample;
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> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> exit;
Bye
1.4.4.4. 步骤四：从 Hive 导出数据，导入到 MySQL。
需要说明该命令的以下几个参数：
1）--connect：MySQL 数据库连接 URL。
2）--username 和--password：MySQL 数据库的用户名和密码。
3）--table：导出的数据表名。
4）--fields-terminated-by：Hive 中字段分隔符。
5）--export-dir：Hive 数据表在 HDFS 中的存储路径。
[hadoop@master ~]$ sqoop export --connect "jdbc:mysql://master:3306/sample?useUnicode=true&characterEncoding=utf-8" --username root --password Passwd123$ --table student --input-fields-terminated-by '|' --export -dir /user/hive/warehouse/sample.db/student/*
23/04/12 17:39:20 INFO mapreduce.Job: Job job_1681287614372_0005 completed successfully
23/04/12 17:39:20 INFO mapreduce.Job: Counters: 31
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=670190
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=969
HDFS: Number of bytes written=0
HDFS: Number of read operations=23
HDFS: Number of large read operations=0
HDFS: Number of write operations=0
Job Counters
Launched map tasks=5
Other local map tasks=1
Data-local map tasks=4
Total time spent by all maps in occupied slots (ms)=38099
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=38099
Total vcore-seconds taken by all map tasks=38099
Total megabyte-seconds taken by all map tasks=39013376
Map-Reduce Framework
Map input records=3
Map output records=3
Input split bytes=871
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=1413
CPU time spent (ms)=5550
Physical memory (bytes) snapshot=771833856
Virtual memory (bytes) snapshot=10563047424
Total committed heap usage (bytes)=443547648
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=0
23/04/12 17:39:20 INFO mapreduce.ExportJobBase: Transferred 969 bytes in 15.8979 seconds (60.9515 bytes/sec)
23/04/12 17:39:20 INFO mapreduce.ExportJobBase: Exported 3 records.
1.4.5. 实验任务五：Sqoop 组件应用
Sqoop 常用设置命令如下：
（1）列出 MySQL 数据库中的所有数据库。
[hadoop@master ~]$ sqoop export --connect "jdbc:mysql://master:3306/sample?useUnicode=true&characterEncoding=utf-8" --username root --password Passwd123$ --table student --input-fieldsterminated-by '|' --export-dir /user/hive/warehouse/sample.db/student/*
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 05:01:37 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 05:01:37 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 05:01:37 ERROR tool.BaseSqoopTool: Error parsing arguments for export:
23/04/12 05:01:37 ERROR tool.BaseSqoopTool: Unrecognized argument: --input-fieldsterminated-by
23/04/12 05:01:37 ERROR tool.BaseSqoopTool: Unrecognized argument: |
23/04/12 05:01:37 ERROR tool.BaseSqoopTool: Unrecognized argument: --export-dir
23/04/12 05:01:37 ERROR tool.BaseSqoopTool: Unrecognized argument: /user/hive/warehouse/sample.db/student/*
Try --help for usage instructions.
[hadoop@master ~]$ sqoop list-databases -connect jdbc:mysql://localhost:3306/ -username root -password Passwd123$
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 05:03:56 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 05:03:56 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 05:03:56 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Wed Apr 12 05:03:57 EDT 2023 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.
information_schema
hive
mysql
performance_schema
sample
sys
（2）连接 MySQL 并列出 sample 数据库中的表。
[hadoop@master ~]$ sqoop list-tables -connect jdbc:mysql://localhost:3306/ -username root -password Passwd123$
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 05:30:02 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 05:30:02 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 05:30:02 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Wed Apr 12 05:30:02 EDT 2023 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.
（3）将关系型数据的表结构复制到 hive 中,只是复制表的结构，表中的内容没有复制 过去。
[hadoop@master ~]$ sqoop create-hive-table -connect jdbc:mysql://localhost:3306/sample -table student -username root -password Passwd123$ -hive-table test
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 05:32:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 05:32:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 05:32:47 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
23/04/12 05:32:47 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
23/04/12 05:32:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Wed Apr 12 05:32:47 EDT 2023 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.
23/04/12 05:32:47 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 05:32:47 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 05:32:48 INFO hive.HiveImport: Loading uploaded data into Hive
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Class path contains multiple SLF4J bindings.
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/hive-jdbc-2.0.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hbase/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
23/04/12 05:32:52 INFO hive.HiveImport: SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
23/04/12 05:32:53 INFO hive.HiveImport:
23/04/12 05:32:53 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/usr/local/src/hive/lib/hive-common-2.0.0.jar!/hive-log4j2.properties
23/04/12 05:32:54 INFO hive.HiveImport: Wed Apr 12 05:32:54 EDT 2023 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.
23/04/12 05:32:54 INFO hive.HiveImport: Wed Apr 12 05:32:54 EDT 2023 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.
23/04/12 05:32:54 INFO hive.HiveImport: Wed Apr 12 05:32:54 EDT 2023 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.
23/04/12 05:32:54 INFO hive.HiveImport: Wed Apr 12 05:32:54 EDT 2023 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.
23/04/12 05:32:56 INFO hive.HiveImport: Wed Apr 12 05:32:56 EDT 2023 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.
23/04/12 05:32:56 INFO hive.HiveImport: Wed Apr 12 05:32:56 EDT 2023 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.
23/04/12 05:32:56 INFO hive.HiveImport: Wed Apr 12 05:32:56 EDT 2023 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.
23/04/12 05:32:56 INFO hive.HiveImport: Wed Apr 12 05:32:56 EDT 2023 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.
23/04/12 05:32:58 INFO hive.HiveImport: OK
23/04/12 05:32:58 INFO hive.HiveImport: Time taken: 1.158 seconds
23/04/12 05:32:59 INFO hive.HiveImport: Hive import complete.
其中–table student 为 MySQL 中的数据库 sample 中的表–hive-table test 为 hive 中新建的表名称。
（4）从关系数据库导入文件到 Hive 中。
[hadoop@master ~]$ $sqoop import --connect jdbc:mysql://master:3306/sample --username root --password Passwd123$ --table student --delete-target-dir --num-mappers 1 --hive-import --hive-database default --hive-table test
-bash: import: command not found
[hadoop@master ~]$ sqoop import --connect jdbc:mysql://master:3306/sample --username root --password Passwd123$ --table student --delete-target-dir --num-mappers 1 --hive-import --hive-database default --hive-table test
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 05:36:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 05:36:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 05:36:54 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
23/04/12 05:36:54 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
23/04/12 05:36:54 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
23/04/12 05:36:54 INFO tool.CodeGenTool: Beginning code generation
Wed Apr 12 05:36:54 EDT 2023 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.
23/04/12 05:36:54 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 05:36:54 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 05:36:54 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/src/hadoop
Note: /tmp/sqoop-hadoop/compile/f3cb5471f7699c0d4daf8537698e14b1/student.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
23/04/12 05:36:55 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/f3cb5471f7699c0d4daf8537698e14b1/student.jar
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 05:36:56 INFO tool.ImportTool: Destination directory student is not present, hence not deleting.
23/04/12 05:36:56 WARN manager.MySQLManager: It looks like you are importing from mysql.
23/04/12 05:36:56 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
23/04/12 05:36:56 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
23/04/12 05:36:56 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
23/04/12 05:36:56 INFO mapreduce.ImportJobBase: Beginning import of student
23/04/12 05:36:56 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
23/04/12 05:36:56 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
23/04/12 05:36:56 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Wed Apr 12 05:36:58 EDT 2023 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.
23/04/12 05:36:58 INFO db.DBInputFormat: Using read commited transaction isolation
23/04/12 05:36:58 INFO mapreduce.JobSubmitter: number of splits:1
23/04/12 05:36:58 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1681291712687_0001
23/04/12 05:36:59 INFO impl.YarnClientImpl: Submitted application application_1681291712687_0001
23/04/12 05:36:59 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1681291712687_0001/
23/04/12 05:36:59 INFO mapreduce.Job: Running job: job_1681291712687_0001
23/04/12 05:37:04 INFO mapreduce.Job: Job job_1681291712687_0001 running in uber mode : false
23/04/12 05:37:04 INFO mapreduce.Job: map 0% reduce 0%
23/04/12 05:37:18 INFO mapreduce.Job: map 100% reduce 0%
23/04/12 05:37:18 INFO mapreduce.Job: Job job_1681291712687_0001 completed successfully
23/04/12 05:37:18 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=134465
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=43
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)=10999
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=10999
Total vcore-seconds taken by all map tasks=10999
Total megabyte-seconds taken by all map tasks=11262976
Map-Reduce Framework
Map input records=4
Map output records=4
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=695
CPU time spent (ms)=2270
Physical memory (bytes) snapshot=170967040
Virtual memory (bytes) snapshot=2106171392
Total committed heap usage (bytes)=83361792
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=43
23/04/12 05:37:18 INFO mapreduce.ImportJobBase: Transferred 43 bytes in 22.3395 seconds (1.9248 bytes/sec)
23/04/12 05:37:18 INFO mapreduce.ImportJobBase: Retrieved 4 records.
23/04/12 05:37:18 INFO mapreduce.ImportJobBase: Publishing Hive/Hcat import job data to Listeners for table student
Wed Apr 12 05:37:18 EDT 2023 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.
23/04/12 05:37:18 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 05:37:18 INFO hive.HiveImport: Loading uploaded data into Hive
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Class path contains multiple SLF4J bindings.
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/hive-jdbc-2.0.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hbase/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
23/04/12 05:37:22 INFO hive.HiveImport: SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
23/04/12 05:37:23 INFO hive.HiveImport:
23/04/12 05:37:23 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/usr/local/src/hive/lib/hive-common-2.0.0.jar!/hive-log4j2.properties
23/04/12 05:37:24 INFO hive.HiveImport: Wed Apr 12 05:37:24 EDT 2023 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.
23/04/12 05:37:24 INFO hive.HiveImport: Wed Apr 12 05:37:24 EDT 2023 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.
23/04/12 05:37:24 INFO hive.HiveImport: Wed Apr 12 05:37:24 EDT 2023 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.
23/04/12 05:37:24 INFO hive.HiveImport: Wed Apr 12 05:37:24 EDT 2023 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.
23/04/12 05:37:26 INFO hive.HiveImport: Wed Apr 12 05:37:26 EDT 2023 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.
23/04/12 05:37:26 INFO hive.HiveImport: Wed Apr 12 05:37:26 EDT 2023 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.
23/04/12 05:37:26 INFO hive.HiveImport: Wed Apr 12 05:37:26 EDT 2023 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.
23/04/12 05:37:26 INFO hive.HiveImport: Wed Apr 12 05:37:26 EDT 2023 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.
23/04/12 05:37:28 INFO hive.HiveImport: OK
23/04/12 05:37:28 INFO hive.HiveImport: Time taken: 0.897 seconds
23/04/12 05:37:28 INFO hive.HiveImport: Loading data to table default.test
23/04/12 05:37:29 INFO hive.HiveImport: OK
23/04/12 05:37:29 INFO hive.HiveImport: Time taken: 0.49 seconds
23/04/12 05:37:29 INFO hive.HiveImport: Hive import complete.
23/04/12 05:37:29 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.
（5）将 Hive 中的表数据导入到 MySQL 中,在进行导入之前，MySQL 中的表 hive_test 表必须已经提前创建好。
#删除 MySQL 数据， student 表中 number 为主键，添加信息导致主键重复，报错，所以删除表数据
[hadoop@master ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use sample;
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> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> exit;
Bye
[hadoop@master ~]$ sqoop export -connect jdbc:mysql://master:3306/sample -username root -password Passwd123$ -table student --input-fields-terminatedby '\001' --export-dir /user/hive/warehouse/test
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 06:27:56 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 06:27:56 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 06:27:56 ERROR tool.BaseSqoopTool: Error parsing arguments for export:
23/04/12 06:27:56 ERROR tool.BaseSqoopTool: Unrecognized argument: --input-fields-terminatedby
23/04/12 06:27:56 ERROR tool.BaseSqoopTool: Unrecognized argument: \001
23/04/12 06:27:56 ERROR tool.BaseSqoopTool: Unrecognized argument: --export-dir
23/04/12 06:27:56 ERROR tool.BaseSqoopTool: Unrecognized argument: /user/hive/warehouse/test
Try --help for usage instructions.
[hadoop@master ~]$ sqoop export --connect jdbc:mysql://master:3306/sample --username root --password Passwd123$ --table student --input-fields-terminated-by '\001' --export-dir /user/hive/warehouse/test
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 06:31:24 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 06:31:24 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 06:31:24 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
23/04/12 06:31:24 INFO tool.CodeGenTool: Beginning code generation
Wed Apr 12 06:31:24 EDT 2023 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.
23/04/12 06:31:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 06:31:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 06:31:24 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/src/hadoop
Note: /tmp/sqoop-hadoop/compile/ca5df4e63a42c7b521e31ba8fc237577/student.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
23/04/12 06:31:25 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/ca5df4e63a42c7b521e31ba8fc237577/student.jar
23/04/12 06:31:25 INFO mapreduce.ExportJobBase: Beginning export of student
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 06:31:26 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
23/04/12 06:31:26 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
23/04/12 06:31:26 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
23/04/12 06:31:26 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
23/04/12 06:31:26 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
23/04/12 06:31:28 INFO input.FileInputFormat: Total input paths to process : 1
23/04/12 06:31:28 INFO input.FileInputFormat: Total input paths to process : 1
23/04/12 06:31:28 INFO mapreduce.JobSubmitter: number of splits:4
23/04/12 06:31:28 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
23/04/12 06:31:28 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1681291712687_0002
23/04/12 06:31:28 INFO impl.YarnClientImpl: Submitted application application_1681291712687_0002
23/04/12 06:31:28 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1681291712687_0002/
23/04/12 06:31:28 INFO mapreduce.Job: Running job: job_1681291712687_0002
23/04/12 06:31:39 INFO mapreduce.Job: Job job_1681291712687_0002 running in uber mode : false
23/04/12 06:31:39 INFO mapreduce.Job: map 0% reduce 0%
23/04/12 06:32:12 INFO mapreduce.Job: map 100% reduce 0%
23/04/12 06:32:12 INFO mapreduce.Job: Job job_1681291712687_0002 completed successfully
23/04/12 06:32:12 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=536616
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=755
HDFS: Number of bytes written=0
HDFS: Number of read operations=19
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)=121339
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=121339
Total vcore-seconds taken by all map tasks=121339
Total megabyte-seconds taken by all map tasks=124251136
Map-Reduce Framework
Map input records=4
Map output records=4
Input split bytes=621
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=8402
CPU time spent (ms)=10840
Physical memory (bytes) snapshot=697860096
Virtual memory (bytes) snapshot=8421277696
Total committed heap usage (bytes)=343932928
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=0
23/04/12 06:32:12 INFO mapreduce.ExportJobBase: Transferred 755 bytes in 46.189 seconds (16.3459 bytes/sec)
23/04/12 06:32:12 INFO mapreduce.ExportJobBase: Exported 4 records.
（6）从数据库导出表的数据到 HDFS 上文件。
[hadoop@master ~]$ sqoop import -connect jdbc:mysql://master:3306/sample -username root -password Passwd123$ -table student --num-mappers 1 -target-dir /user/test
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 08:12:29 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 08:12:29 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 08:12:29 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
23/04/12 08:12:29 INFO tool.CodeGenTool: Beginning code generation
Wed Apr 12 08:12:29 EDT 2023 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.
23/04/12 08:12:30 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 08:12:30 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 08:12:30 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/src/hadoop
Note: /tmp/sqoop-hadoop/compile/399c9a0fd5f91f0f62e925f8cc6c4f52/student.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
23/04/12 08:12:33 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/399c9a0fd5f91f0f62e925f8cc6c4f52/student.jar
23/04/12 08:12:33 WARN manager.MySQLManager: It looks like you are importing from mysql.
23/04/12 08:12:33 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
23/04/12 08:12:33 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
23/04/12 08:12:33 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
23/04/12 08:12:33 INFO mapreduce.ImportJobBase: Beginning import of student
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 08:12:34 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
23/04/12 08:12:36 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
23/04/12 08:12:37 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Wed Apr 12 08:12:39 EDT 2023 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.
23/04/12 08:12:39 INFO db.DBInputFormat: Using read commited transaction isolation
23/04/12 08:12:39 INFO mapreduce.JobSubmitter: number of splits:1
23/04/12 08:12:39 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1681300935029_0001
23/04/12 08:12:40 INFO impl.YarnClientImpl: Submitted application application_1681300935029_0001
23/04/12 08:12:40 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1681300935029_0001/
23/04/12 08:12:40 INFO mapreduce.Job: Running job: job_1681300935029_0001
23/04/12 08:12:46 INFO mapreduce.Job: Job job_1681300935029_0001 running in uber mode : false
23/04/12 08:12:46 INFO mapreduce.Job: map 0% reduce 0%
23/04/12 08:12:51 INFO mapreduce.Job: map 100% reduce 0%
23/04/12 08:12:51 INFO mapreduce.Job: Job job_1681300935029_0001 completed successfully
23/04/12 08:12:51 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=134455
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=39
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)=2939
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=2939
Total vcore-seconds taken by all map tasks=2939
Total megabyte-seconds taken by all map tasks=3009536
Map-Reduce Framework
Map input records=4
Map output records=4
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=54
CPU time spent (ms)=1050
Physical memory (bytes) snapshot=173801472
Virtual memory (bytes) snapshot=2104844288
Total committed heap usage (bytes)=87556096
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=39
23/04/12 08:12:51 INFO mapreduce.ImportJobBase: Transferred 39 bytes in 14.5627 seconds (2.6781 bytes/sec)
23/04/12 08:12:51 INFO mapreduce.ImportJobBase: Retrieved 4 records.
（7）从数据库增量导入表数据到 HDFS 中。
#密码 Passwd123$
[hadoop@master ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use sample;
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> insert into student values('06','ss2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('08','ss8');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values('09','ss9');
Query OK, 1 row affected (0.00 sec)
#非数值型的值不能当做增量
mysql> alter table student modify column number int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> exit;
Bye
[hadoop@master ~]$ sqoop import --connect jdbc:mysql://master:3306/sample --username root --password Passwd123$ --table student --num-mappers 1 --target-dir /user/test --check-column number --incremental append --last-value 3
Warning: /usr/local/src/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/src/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
23/04/12 08:24:42 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
23/04/12 08:24:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
23/04/12 08:24:42 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
23/04/12 08:24:42 INFO tool.CodeGenTool: Beginning code generation
Wed Apr 12 08:24:43 EDT 2023 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.
23/04/12 08:24:43 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 08:24:43 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
23/04/12 08:24:43 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/src/hadoop
Note: /tmp/sqoop-hadoop/compile/f77b8bb6749a8c7c173e68be972783bc/student.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
23/04/12 08:24:44 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/f77b8bb6749a8c7c173e68be972783bc/student.jar
23/04/12 08:24:44 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(`number`) FROM `student`
23/04/12 08:24:44 INFO tool.ImportTool: Incremental import based on column `number`
23/04/12 08:24:44 INFO tool.ImportTool: Lower bound value: 3
23/04/12 08:24:44 INFO tool.ImportTool: Upper bound value: 9
23/04/12 08:24:44 WARN manager.MySQLManager: It looks like you are importing from mysql.
23/04/12 08:24:44 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
23/04/12 08:24:44 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
23/04/12 08:24:44 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
23/04/12 08:24:44 INFO mapreduce.ImportJobBase: Beginning import of student
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/src/hbase/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]
23/04/12 08:24:44 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
23/04/12 08:24:46 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
23/04/12 08:24:46 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Wed Apr 12 08:24:48 EDT 2023 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.
23/04/12 08:24:48 INFO db.DBInputFormat: Using read commited transaction isolation
23/04/12 08:24:48 INFO mapreduce.JobSubmitter: number of splits:1
23/04/12 08:24:48 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1681300935029_0002
23/04/12 08:24:49 INFO impl.YarnClientImpl: Submitted application application_1681300935029_0002
23/04/12 08:24:49 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1681300935029_0002/
23/04/12 08:24:49 INFO mapreduce.Job: Running job: job_1681300935029_0002
23/04/12 08:24:54 INFO mapreduce.Job: Job job_1681300935029_0002 running in uber mode : false
23/04/12 08:24:54 INFO mapreduce.Job: map 0% reduce 0%
23/04/12 08:24:59 INFO mapreduce.Job: map 100% reduce 0%
23/04/12 08:25:00 INFO mapreduce.Job: Job job_1681300935029_0002 completed successfully
23/04/12 08:25:00 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=134678
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=29
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)=3495
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=3495
Total vcore-seconds taken by all map tasks=3495
Total megabyte-seconds taken by all map tasks=3578880
Map-Reduce Framework
Map input records=4
Map output records=4
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=113
CPU time spent (ms)=1110
Physical memory (bytes) snapshot=176205824
Virtual memory (bytes) snapshot=2106580992
Total committed heap usage (bytes)=88080384
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=29
23/04/12 08:25:00 INFO mapreduce.ImportJobBase: Transferred 29 bytes in 14.3954 seconds (2.0145 bytes/sec)
23/04/12 08:25:00 INFO mapreduce.ImportJobBase: Retrieved 4 records.
23/04/12 08:25:00 INFO util.AppendUtils: Appending to directory test
23/04/12 08:25:00 INFO util.AppendUtils: Using found partition 1
23/04/12 08:25:00 INFO tool.ImportTool: Incremental import complete! To run another incremental import of all data following this import, supply the following arguments:
23/04/12 08:25:00 INFO tool.ImportTool: --incremental append
23/04/12 08:25:00 INFO tool.ImportTool: --check-column number
23/04/12 08:25:00 INFO tool.ImportTool: --last-value 9
23/04/12 08:25:00 INFO tool.ImportTool: (Consider saving this with 'sqoop job --create')
查看导入数据
[hadoop@master ~]$ hdfs dfs -cat /user/test/part-m-00000
1,zhanglin
2,lisi
3,yingliu
4,shengsan
[hadoop@master ~]$ hdfs dfs -ls /user/test/
Found 3 items
-rw-r--r-- 2 hadoop supergroup 0 2023-04-12 08:12 /user/test/_SUCCESS
-rw-r--r-- 2 hadoop supergroup 39 2023-04-12 08:12 /user/test/part-m-00000
-rw-r--r-- 2 hadoop supergroup 29 2023-04-12 08:24 /user/test/part-m-00001