sqoop--mysql与hdfs数据互导

最新推荐文章于 2023-05-11 21:29:06 发布
最新推荐文章于 2023-05-11 21:29:06 发布
阅读量1.5k 收藏
点赞数
分类专栏： hadoop 文章标签： sqoop mysql hdfs
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ww1982_0_0_0/article/details/9170285
版权

此例中hadoop版本为1.2.0，sqoop版本为1.4.3，mysql版本为5.1.61。

一、从mysql到hdfs

1.下载mysql的jdbc驱动

http://dev.mysql.com/downloads/connector/j/

2.配置驱动

解压安装包

[hadoop@node1 ~]$ tar -zxvf mysql-connector-java-5.1.25.tar.gz

拷贝jdbc驱动到lib目录

[hadoop@node1 mysql-connector-java-5.1.25]$ ls
build.xml  CHANGES  COPYING  docs  mysql-connector-java-5.1.25-bin.jar  README  README.txt  src
[hadoop@node1 mysql-connector-java-5.1.25]$ cp mysql-connector-java-5.1.25-bin.jar /home/hadoop/sqoop-1.4.3/lib/

3.编辑导出脚本

[hadoop@node1 bin]$ vi mysql2hdfs.sh
#连接字符串
CONNECTURL=jdbc:mysql://10.190.105.10/employees
#mysql用户名
MYSQLNAME=employee
#mysql用户密码
MYSQLPASSWORD=employee
#要导出的表名
mysqlTableName=dept_emp
#要保存的位置
hdfsPath=/user/hadoop/test/$oralceTableName
./sqoop import --append --connect $CONNECTURL --username $MYSQLNAME --password $MYSQLPASSWORD --target-dir $hdfsPath  --num-mappers 1 --table $mysqlTableName --fields-terminated-by '|'

4.修改mysql中employee用户权限

mysql> grant all on employee.* to 'employee'@'%' identified by 'employee';
Query OK, 0 rows affected (0.00 sec)

mysql>  select user,host from user;
+----------+-------------------+
| user     | host              |
+----------+-------------------+
| employee | %                 |
| employee | 10.190.105.51     |
| root     | 127.0.0.1         |
|          | localhost         |
| employee | localhost         |
| root     | localhost         |
|          | node1.localdomain |
| root     | node1.localdomain |
+----------+-------------------+
8 rows in set (0.00 sec)

5.执行迁移脚本

[hadoop@node1 bin]$ sh mysql2hdfs.sh
Warning: $HADOOP_HOME is deprecated.

13/06/25 21:09:32 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/06/25 21:09:32 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/06/25 21:09:32 INFO tool.CodeGenTool: Beginning code generation
13/06/25 21:09:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `dept_emp` AS t LIMIT 1
13/06/25 21:09:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `dept_emp` AS t LIMIT 1
13/06/25 21:09:33 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hadoop-1.2.0
Note: /tmp/sqoop-hadoop/compile/a3daec0ae4148c40fd3f20a023efd37f/dept_emp.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/06/25 21:09:34 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/a3daec0ae4148c40fd3f20a023efd37f/dept_emp.jar
13/06/25 21:09:34 WARN manager.MySQLManager: It looks like you are importing from mysql.
13/06/25 21:09:34 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
13/06/25 21:09:34 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
13/06/25 21:09:34 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
13/06/25 21:09:34 WARN manager.CatalogQueryManager: The table dept_emp contains a multi-column primary key. Sqoop will default to the column emp_no only for this job.
13/06/25 21:09:34 WARN manager.CatalogQueryManager: The table dept_emp contains a multi-column primary key. Sqoop will default to the column emp_no only for this job.
13/06/25 21:09:34 INFO mapreduce.ImportJobBase: Beginning import of dept_emp
13/06/25 21:09:36 INFO mapred.JobClient: Running job: job_201306251627_0003
13/06/25 21:09:37 INFO mapred.JobClient:  map 0% reduce 0%
13/06/25 21:09:52 INFO mapred.JobClient:  map 100% reduce 0%
13/06/25 21:09:54 INFO mapred.JobClient: Job complete: job_201306251627_0003
13/06/25 21:09:54 INFO mapred.JobClient: Counters: 18
13/06/25 21:09:54 INFO mapred.JobClient:   Job Counters
13/06/25 21:09:54 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=13834
13/06/25 21:09:54 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/06/25 21:09:54 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/06/25 21:09:54 INFO mapred.JobClient:     Launched map tasks=1
13/06/25 21:09:54 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/06/25 21:09:54 INFO mapred.JobClient:   File Output Format Counters
13/06/25 21:09:54 INFO mapred.JobClient:     Bytes Written=11175033
13/06/25 21:09:54 INFO mapred.JobClient:   FileSystemCounters
13/06/25 21:09:54 INFO mapred.JobClient:     HDFS_BYTES_READ=87
13/06/25 21:09:54 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=58770
13/06/25 21:09:54 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=11175033
13/06/25 21:09:54 INFO mapred.JobClient:   File Input Format Counters
13/06/25 21:09:54 INFO mapred.JobClient:     Bytes Read=0
13/06/25 21:09:54 INFO mapred.JobClient:   Map-Reduce Framework
13/06/25 21:09:54 INFO mapred.JobClient:     Map input records=331603
13/06/25 21:09:54 INFO mapred.JobClient:     Physical memory (bytes) snapshot=79998976
13/06/25 21:09:54 INFO mapred.JobClient:     Spilled Records=0
13/06/25 21:09:54 INFO mapred.JobClient:     CPU time spent (ms)=7560
13/06/25 21:09:54 INFO mapred.JobClient:     Total committed heap usage (bytes)=15925248
13/06/25 21:09:54 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=631529472
13/06/25 21:09:54 INFO mapred.JobClient:     Map output records=331603
13/06/25 21:09:54 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
13/06/25 21:09:54 INFO mapreduce.ImportJobBase: Transferred 10.6573 MB in 18.9752 seconds (575.1249 KB/sec)
13/06/25 21:09:54 INFO mapreduce.ImportJobBase: Retrieved 331603 records.
13/06/25 21:09:54 INFO util.AppendUtils: Creating missing output directory - dept_emp

导入完毕。总共导入331603条记录。

6.查看导入数据

[hadoop@node1 bin]$ hadoop fs -ls /user/hadoop/test/dept_emp
Warning: $HADOOP_HOME is deprecated.

Found 2 items
drwxr-xr-x   - hadoop supergroup          0 2013-06-25 21:09 /user/hadoop/test/dept_emp/_logs
-rw-r--r--   3 hadoop supergroup   11175033 2013-06-25 21:09 /user/hadoop/test/dept_emp/part-m-00000

[hadoop@node1 bin]$ hadoop fs -cat /user/hadoop/test/dept_emp/part-m-00000 | more
Warning: $HADOOP_HOME is deprecated.

10001|d005|1986-06-26|9999-01-01
10002|d007|1996-08-03|9999-01-01
10003|d004|1995-12-03|9999-01-01
。。。。。。。。。。。。。。。。。

7.查看mysql中记录数量

mysql> use employees;
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> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.13 sec)


 

 二、从hdfs到mysql

 1.编辑脚本

#jdbc连接字符串
CONNECTURL=jdbc:mysql://10.190.105.10/employees
#mysql中的用户名
MYSQLNAME=employee
#mysql中用户的密码
MYSQLPASSWORD=employee
#要导入的表名，表要事先建立好
mysqlTableName=countries
#hdfs上文件的保存路径
hdfsPath=/user/hadoop/test/COUNTRIES/part-m-00000
#导出命令行
./sqoop export --connect $CONNECTURL --username $MYSQLNAME --password $MYSQLPASSWORD --export-dir $hdfsPath  --num-mappers 1 --table $mysqlTableName --fields-terminated-by '\001'

2.在mysql中创建需要的表

mysql> create table countries(country_id char(2),country_name varchar(40),region_id int);
Query OK, 0 rows affected (0.01 sec)

3.执行脚本

[hadoop@node1 bin]$ sh hdfs2mysql.sh
Warning: $HADOOP_HOME is deprecated.

13/06/25 22:49:19 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/06/25 22:49:19 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/06/25 22:49:19 INFO tool.CodeGenTool: Beginning code generation
13/06/25 22:49:20 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `COUNTRIES` AS t LIMIT 1
13/06/25 22:49:20 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `COUNTRIES` AS t LIMIT 1
13/06/25 22:49:20 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hadoop-1.2.0
Note: /tmp/sqoop-hadoop/compile/e40111465da685560ca38925242c7520/COUNTRIES.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/06/25 22:49:22 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/e40111465da685560ca38925242c7520/COUNTRIES.jar
13/06/25 22:49:22 INFO mapreduce.ExportJobBase: Beginning export of COUNTRIES
13/06/25 22:49:23 INFO input.FileInputFormat: Total input paths to process : 1
13/06/25 22:49:23 INFO input.FileInputFormat: Total input paths to process : 1
13/06/25 22:49:23 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/06/25 22:49:23 WARN snappy.LoadSnappy: Snappy native library not loaded
13/06/25 22:49:24 INFO mapred.JobClient: Running job: job_201306251627_0010
13/06/25 22:49:25 INFO mapred.JobClient:  map 0% reduce 0%
13/06/25 22:49:32 INFO mapred.JobClient:  map 100% reduce 0%
13/06/25 22:49:34 INFO mapred.JobClient: Job complete: job_201306251627_0010
13/06/25 22:49:34 INFO mapred.JobClient: Counters: 18
13/06/25 22:49:34 INFO mapred.JobClient:   Job Counters
13/06/25 22:49:34 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=7127
13/06/25 22:49:34 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/06/25 22:49:34 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/06/25 22:49:34 INFO mapred.JobClient:     Rack-local map tasks=1
13/06/25 22:49:34 INFO mapred.JobClient:     Launched map tasks=1
13/06/25 22:49:34 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/06/25 22:49:34 INFO mapred.JobClient:   File Output Format Counters
13/06/25 22:49:34 INFO mapred.JobClient:     Bytes Written=0
13/06/25 22:49:34 INFO mapred.JobClient:   FileSystemCounters
13/06/25 22:49:34 INFO mapred.JobClient:     HDFS_BYTES_READ=472
13/06/25 22:49:34 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=58409
13/06/25 22:49:34 INFO mapred.JobClient:   File Input Format Counters
13/06/25 22:49:34 INFO mapred.JobClient:     Bytes Read=0
13/06/25 22:49:34 INFO mapred.JobClient:   Map-Reduce Framework
13/06/25 22:49:34 INFO mapred.JobClient:     Map input records=25
13/06/25 22:49:34 INFO mapred.JobClient:     Physical memory (bytes) snapshot=84516864
13/06/25 22:49:34 INFO mapred.JobClient:     Spilled Records=0
13/06/25 22:49:34 INFO mapred.JobClient:     CPU time spent (ms)=960
13/06/25 22:49:34 INFO mapred.JobClient:     Total committed heap usage (bytes)=15925248
13/06/25 22:49:34 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=570187776
13/06/25 22:49:34 INFO mapred.JobClient:     Map output records=25
13/06/25 22:49:34 INFO mapred.JobClient:     SPLIT_RAW_BYTES=121
13/06/25 22:49:34 INFO mapreduce.ExportJobBase: Transferred 472 bytes in 11.312 seconds (41.7255 bytes/sec)
13/06/25 22:49:34 INFO mapreduce.ExportJobBase: Exported 25 records.

4.mysql中核实数据

mysql> select * from COUNTRIES;
+------------+--------------------------+-----------+
| country_id | country_name             | region_id |
+------------+--------------------------+-----------+
| AR         | Argentina                |         2 |
| AU         | Australia                |         3 |
| BE         | Belgium                  |         1 |
| BR         | Brazil                   |         2 |
| CA         | Canada                   |         2 |
| CH         | Switzerland              |         1 |
| CN         | China                    |         3 |
| DE         | Germany                  |         1 |
| DK         | Denmark                  |         1 |
| EG         | Egypt                    |         4 |
| FR         | France                   |         1 |
| HK         | HongKong                 |         3 |
| IL         | Israel                   |         4 |
| IN         | India                    |         3 |
| IT         | Italy                    |         1 |
| JP         | Japan                    |         3 |
| KW         | Kuwait                   |         4 |
| MX         | Mexico                   |         2 |
| NG         | Nigeria                  |         4 |
| NL         | Netherlands              |         1 |
| SG         | Singapore                |         3 |
| UK         | United Kingdom           |         1 |
| US         | United States of America |         2 |
| ZM         | Zambia                   |         4 |
| ZW         | Zimbabwe                 |         4 |
+------------+--------------------------+-----------+
25 rows in set (0.00 sec)


 

 

 

 

 


 

ww1982_0_0_0
关注
专栏目录
使用Sqoop将数据在HDFS与MySQL互导
光于前裕于后的博客
03-29 4761
1.去官网下载sqoop，直接百度即可 2.解压后进入conf目录 guo@drguo1:/opt/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/conf$ cp sqoop-env-template.sh sqoop-env.sh 3.在sqoop-env.sh添加各种home #Set path to where bin/hadoop is avail
sqoop从mysql导入hdfs_sqoop从mysql导入到hdfs
weixin_30511777的博客
01-30 361
1、mysql-- 创建数据库create database logs;-- 使用use logs;-- 创建表create table weblogs(md5varchar(32),urlvarchar(64),request_datedate,request_timetime,ipvarchar(15));-- 从外部文本文件加载数据load data infile '/path/w...
使用sqoop将mysql数据导入hdfs
qq_51903852的博客
11-23 1257
---------------------mysql to hdfs-------------------------------- [root@mihaoyu151 ~]# sqoop import \ > --connect jdbc:mysql://mihaoyu151:3306/mybatisdb \ > --username root \ > --password root \ > --table student \ > --delete-target-dir \ &
Sqoop（3）—— Mysql数据导入HDFS
天青色等烟雨 而我在等你
03-21 477
Sqoop（3）—— Mysql数据导入HDFS官方文档Sqoop导入导出的官方文档：http://sqoop.apache.org/docs/1.4.7/index.htmlhttp://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html执行Sqoop的两种方式http://sqoop.apache.org/docs/1.4.7/SqoopUserGu...
【Hadoop】通过Sqoop实现从MySQL导入数据到HDFS
你不来，再美的天地，都狼藉 ~
05-11 8068
一文带你了解MySQL通过Sqoop导入数据到Hdfs的详细过程。
Sqoop安装及MySQL、HDFS、Hive、HBase 数据迁移实验
最新发布
10-24
文中介绍了通过 Sqoop 在 MySQL 和 HDFS 之间、MySQL 和 Hive 之间以及 MySQL 和 HBase 之间的数据互导过程，包括如何处理常见的错误。同时，也详细记录了 MySQL 用户创建、授权、数据插入和 Sqoop 配置的相关细节。...
Sqoop使用案例---数据导入----RDBMS到HDFS，RDBMS到Hive，RDBMS到Hbase
qq_43618208的博客
05-23 244
RDBMS到HDFS mysql中创建一个表，并插入数据 $ mysql -uroot -p000000 mysql> create database company; mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255)); mysql> insert into company.staff(name, sex)
cdh sqoop 配置_Sqoop-1.4.6安装部署及详细使用介绍
weixin_39624429的博客
12-21 290
之所以选择Sqoop1是因为Sqoop2目前问题太多。无法正常使用，综合比较后选择Sqoop1。Sqoop1安装配置比较简单一、安装部署(1)、下载安装包解压到/home/duanxz/sqooptar -zxvfsqoop-1.4.6-cdh5.5.2.tar.gz(2)、拷贝mysql的jdbc驱动包mysql-connector-java-5.1.31-bin.jar到sqoop/lib目...
使用Sqoop实现HIve分析结果数据入mysql库
lrf2454224026的博客
09-11 1137
1.将hive作业中的结果，使用sqoop 导入mysql数据库。        创建stock表并导入数据                        创建stock_result表来存放stock的查询结果              自定义jar包并创建相应的函数（这一步走可以省略，可以使用hive自带的函数）                     将查询结果导入stoc...
sqoop HDFS 导入 mysql
guohan的博客
02-17 1025
一、hdfs导入mysql 注意：在mysql导入hive时可以自动创建表，而hdfs导入mysql时则不能在mysql中自动创建表，因此导入前准备工作，在mysql中建议需要导入的表！ 1、最简单的导入 准备工作：在mysql中建表 create table emp_import (id int,name varchar(45),age int); hdfs中/emp_db下存...
SQOOP——MySQL 和 HDFS 的桥梁
03-12 1412
将数据从 mysql import 到 HDFS 中我们要使用 mysql 的话，意味着要对其进行连接，自然使用 JDBC（Java Data Base Connectivity）。在之前配置 hive 的 mysql 时，我们已将 mysql-connector-java-5.1.10.jar 拷贝到 hive/lib 目录下，[root@hadoop0 ~]# cp $HIVE_HOME/lib
一、sqoop 操作mysql与hdfs之间数据传输
风子的博客
09-26 1820
/* SQLyog Ultimate v12.08 (64 bit) MySQL - 5.6.34 : Database - spark */ /*!40101 SET NAMES utf8 */; /!40101 SET SQL_MODE=’’/; /!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /; /!4001...
[原创] MysqlsqoopHDFS 数据交换实验
congnangwu6519的博客
12-18 175
感谢@吴飚飚哥的细心指导，向飚哥精益求精的精神学习 SQOOP：Apache基金会下一个开源产品，Hadoop家族的一个产品，关系型数据库与HDFS文件系统之间进行数据交换，数据迁移的一个工具。 ...
Sqoop增量从MySQL中向hive导入数据
大JAVA解决方案
05-03 1769
增量导入增量导入是仅导入新添加的表中的行的技术。它需要添加‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。下面的语法用于Sqoop导入命令增量选项。--incremental <mode>--check-column <column name>--last value <last check column ...
第八章：sqoop数据迁移工具
小爷欣欣
04-06 3462
sqoop是专门用来迁移数据的，它可以把数据库中的数据迁移到HDFS文件系统，当然也可以从HDFS文件系统导回到数据库。       我来说一下Sqoop的使用场景，假如你们公司有个项目运行好长时间了，积累了大量的数据，现在想升级项目并换种数据库进行存储原来的数据，那么我们就需要先把数据都存放到另一个地方，然后再用新数据库的语句把这些数据插入到新的数据库。在没有Sqoop之前，我们要做到这一点是...
hbase sqoop 实验_[hadoop读书笔记] 第十五章 sqoop1.4.6小实验 - 将mysq数据导入HBASE
weixin_42581003的博客
01-12 166
[hadoop@hadoop-allinone-200-123 bin]$sqoop import --connect jdbc:mysql://192.168.200.250:3306/sqoop --table widgets --hbase-table WIDGETS_IN_HBASE --column-family product --hbase-row-key id --h...
Sqoop学习之路 （一）
weixin_34301307的博客
04-15 921
一、概述 sqoop 是 apache 旗下一款“Hadoop 和关系数据库服务器之间传送数据”的工具。 核心的功能有两个： 导入、迁入 导出、迁出 导入数据：MySQL，Oracle 导入数据到 Hadoop 的 HDFS、HIVE、HBASE 等数据存储系统 导出数据：从 Hadoop 的文件系统中导出数据到关系数据库 mysql 等 Sqoop 的本质还是一个命令行工具，和 HD...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值