sqoop

一、解压安装

官网:https://sqoop.apache.org/

二、修改配置文件

cd /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/conf
cp sqoop-env-template.sh sqoop-env.sh
vi sqoop-env.sh
#Set path to where bin/hadoop is available
 export HADOOP_COMMON_HOME=/root/apps/hadoop-2.8.1

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

三、workbench连接Linux的MySQL

如果连接不成功,修改mysql权限

update user set host='%' where user=' ';
mysql> grant all privileges  on *.* to [email protected]'%';
Query OK, 0 rows affected (0.00 sec)
flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

解决数据库没有密码,运程默认不连接

mysql> use mysql;
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> update user set password=PASSWORD("grace") where user='root';      
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> Ctrl-C -- exit!
Aborted
[[email protected] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> Ctrl-C -- exit!
Aborted
[[email protected] ~]# systemctl stop mysql.service 
[[email protected] ~]# systemctl start mysql.service
[[email protected] ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[[email protected] ~]# 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.6.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 

 1,选择数据库

use mysql;

2,修改密码

update user set password=PASSWORD("grace") where user='root';

3,重启mysql服务

systemctl stop mysql.service
systemctl start mysql.service

4,登录mysql

mysql -u root -p

接着输入密码即可登录mysql。

一、概述

sqoop 是 apache 旗下一款“Hadoop 和关系数据库服务器之间传送数据”的工具。

核心的功能有两个:

导入、迁入

导出、迁出

导入数据:MySQL,Oracle 导入数据到 Hadoop 的 HDFS、HIVE、HBASE 等数据存储系统

导出数据:从 Hadoop 的文件系统中导出数据到关系数据库 mysql 等 Sqoop 的本质还是一个命令行工具,和 HDFS,Hive 相比,并没有什么高深的理论。

sqoop:工具:本质就是迁移数据, 迁移的方式:就是把sqoop的迁移命令转换成MR程序

hive:工具,本质就是执行计算,依赖于HDFS存储数据,把SQL转换成MR程序

查询databases

 ./sqoop list-databases --connect jdbc:mysql://hdp-3:3306/ --username root -P
[[email protected] bin]# ./sqoop list-databases --connect jdbc:mysql://hdp-3:3306/ --username root -P
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
19/11/29 21:12:27 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password: 
19/11/29 21:12:30 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
beautyGirl
frame
grace
hive
mysql
performance_schema

查询tables

 ./sqoop list-tables --connect jdbc:mysql://hdp-3:3306/frame --username root -P
[[email protected] bin]# ./sqoop list-tables --connect jdbc:mysql://hdp-3:3306/frame --username root -P         
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
19/11/29 21:24:21 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password: 
19/11/29 21:24:27 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
channel
course
res
school
site_pv
stat_channel_pv
stat_hour_pv
stat_month_pv
stat_res_pv
stat_year_pv
student
student_score
sys

语法格式
sqoop import (generic-args) (import-args)
常用参数
–connect jdbc 连接地址
–connection-manager 连接管理者
–driver 驱动类
–hadoop-mapred-home $HADOOP_MAPRED_HOME
–help help 信息
-P 从命令行输入密码
–password 密码
–username 账号
–verbose 打印流程信息
–connection-param-file 可选参数

案例:上传到hdfs表

./sqoop import   --connect jdbc:mysql://hdp-3:3306/frame  --username root  --password grace   --table student   -m 1;
[[email protected] bin]# ./sqoop import   --connect jdbc:mysql://hdp-3:3306/frame  --username root  --password grace   --table student   -m 1;    
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /root/apps/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
19/11/29 21:35:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/11/29 21:35:45 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/11/29 21:35:45 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
19/11/29 21:35:45 INFO tool.CodeGenTool: Beginning code generation
19/11/29 21:35:45 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
19/11/29 21:35:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1
19/11/29 21:35:46 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /root/apps/hadoop-2.8.1
注: /tmp/sqoop-root/compile/8eb7c0d4b2a4f4ce732e81bf3ab1a26e/student.java使用或覆盖了已过时的 API。
注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。
19/11/29 21:36:04 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/8eb7c0d4b2a4f4ce732e81bf3ab1a26e/student.jar
19/11/29 21:36:04 WARN manager.MySQLManager: It looks like you are importing from mysql.
19/11/29 21:36:04 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
19/11/29 21:36:04 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
19/11/29 21:36:04 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
19/11/29 21:36:04 INFO mapreduce.ImportJobBase: Beginning import of student
19/11/29 21:36:04 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address
19/11/29 21:36:05 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/11/29 21:36:06 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/11/29 21:36:07 INFO client.RMProxy: Connecting to ResourceManager at hdp-1/192.168.150.151:8032
19/11/29 21:36:16 INFO db.DBInputFormat: Using read commited transaction isolation
19/11/29 21:36:16 INFO mapreduce.JobSubmitter: number of splits:1
19/11/29 21:36:17 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1575013215007_0001
19/11/29 21:36:19 INFO impl.YarnClientImpl: Submitted application application_1575013215007_0001
19/11/29 21:36:19 INFO mapreduce.Job: The url to track the job: http://hdp-1:8088/proxy/application_1575013215007_0001/
19/11/29 21:36:19 INFO mapreduce.Job: Running job: job_1575013215007_0001
19/11/29 21:37:04 INFO mapreduce.Job: Job job_1575013215007_0001 running in uber mode : false
19/11/29 21:37:05 INFO mapreduce.Job:  map 0% reduce 0%
19/11/29 21:37:25 INFO mapreduce.Job:  map 100% reduce 0%
19/11/29 21:37:28 INFO mapreduce.Job: Job job_1575013215007_0001 completed successfully
19/11/29 21:37:28 INFO mapreduce.Job: Counters: 30
        File System Counters
                FILE: Number of bytes read=0
                FILE: Number of bytes written=154775
                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=53525
                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)=15060
                Total time spent by all reduces in occupied slots (ms)=0
                Total time spent by all map tasks (ms)=15060
                Total vcore-milliseconds taken by all map tasks=15060
                Total megabyte-milliseconds taken by all map tasks=15421440
        Map-Reduce Framework
                Map input records=1000
                Map output records=1000
                Input split bytes=87
                Spilled Records=0
                Failed Shuffles=0
                Merged Map outputs=0
                GC time elapsed (ms)=226
                CPU time spent (ms)=2390
                Physical memory (bytes) snapshot=108527616
                Virtual memory (bytes) snapshot=2086252544
                Total committed heap usage (bytes)=18743296
        File Input Format Counters 
                Bytes Read=0
        File Output Format Counters 
                Bytes Written=53525
19/11/29 21:37:28 INFO mapreduce.ImportJobBase: Transferred 52.2705 KB in 81.7195 seconds (654.9846 bytes/sec)
19/11/29 21:37:28 INFO mapreduce.ImportJobBase: Retrieved 1000 records.

在http://hdp-1:50070查看/user/root路径下

在Linux查看上传成功的表的数据

hadoop fs -cat /user/root/student/part-m-00000;

结果:

462,594,男,13206767129,干平生,[email protected],null
463,716,女,15703992166,人壮,[email protected],null
464,341,女,13006256238,靳若勇,[email protected],null
465,620,男,15701031305,訾宏彬,[email protected],null
466,103,男,15508288624,伊以亮,[email protected],null
467,797,男,15803375108,侯林宏,[email protected],null
468,273,男,15207386441,孙山,[email protected],null
469,774,女,13604552467,江奇,[email protected],null
470,543,女,13404080520,郗晨壮,[email protected],null
471,351,女,13205245018,官行克,[email protected],null
472,440,女,13604370534,人军,[email protected],null
473,431,男,13104961349,空光固,[email protected],null
474,580,男,15008787264,崔利,[email protected],null
475,70,女,15601293669,鄢树,[email protected],null
476,151,男,15701787276,狄成东,[email protected],null
477,539,女,13504937286,黎和,[email protected],null
478,140,男,15505833140,政才士,[email protected],null
479,512,男,13603682420,羊斌,[email protected],null
480,663,女,15002526730,石保民,[email protected],null
481,419,男,13406810255,李邦,[email protected],null
482,145,女,13504949015,嵇良,[email protected],null
483,633,女,13005158953,倪绍彬,[email protected],null
484,410,女,15901324712,宇广,[email protected],null
485,69,女,15301842801,温士家,[email protected],null
486,778,男,13603516281,姚河新,[email protected],null
487,265,男,13705976843,房琛德,[email protected],null
488,315,女,13605191095,瞿建朋,[email protected],null
489,205,男,13306828661,相钧宏,[email protected],null
490,550,女,15808464512,林邦风,[email protected],null
491,674,男,13606058470,干泽友,[email protected],null
492,810,男,13708661623,沙林树,[email protected],null
493,818,女,15505277969,蓝家,[email protected],null
494,273,女,15601717956,后达,[email protected],null
495,333,男,13404417868,正雄茂,[email protected],null
496,283,男,15508450248,仲时民,[email protected],null
497,400,女,13407432660,方时生,[email protected],null
498,234,男,13400754487,叶伯士,[email protected],null
499,589,女,13303461929,应绍翰,[email protected],null
500,630,男,15508641906,陶树,[email protected],null

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

原文链接:加载失败,请重新获取