使用Sqoop实现HDFS与Mysql互转
目录[-]
?简介
?环境
?目的
?安装
?MYSQL转HDFS-示例
?HDFS转MYSQ-示例
?技巧
?导出导入的数据与字段的顺序进行匹配
?从HDFS转换回mysql 的时候，自动增长的主键字段处，可以留空
?示例参数说明
?示例参数说明
简介
Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：MySQL ,Oracle ,Postgres等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导入到关系型数据库中。
https://www.doczj.com/doc/d42386347.html,/
环境
当调试过程出现IncompatibleClassChangeError一般都是版本兼容问题。
为了保证hadoop和sqoop版本的兼容性，使用Cloudera，
Cloudera简介：
Cloudera为了让Hadoop的配置标准化，可以帮助企业安装，配置，运行hadoop 以达到大规模企业数据的处理和分析。
https://www.doczj.com/doc/d42386347.html,/content/cloudera-content/cloudera-docs/CDHTarballs/3.25.2013 /CDH4-Downloadable-Tarballs/CDH4-Downloadable-Tarballs.html
下载安装hadoop-0.20.2-cdh3u6，sqoop-1.3.0-cdh3u6。
目的
将mysql数据转换为hadoop文件，从而可以使用map/reduce进行大数据的分析，然后再将分析结果转换回mysql，供业务查询调用。
安装
安装比较简单，直接解压即可
唯一需要做的就是将mysql的jdbc适配包mysql-connector-java-5.0.7-bin.jar copy
到$SQOOP_HOME/lib下。
配置好环境变量：/etc/profile
export SQOOP_HOME=/home/hadoop/sqoop-1.3.0-cdh3u6/
export PATH=$SQOOP_HOME/bin:$PATH
MYSQL转HDFS-示例
./sqoop import --connect jdbc:mysql://10.8.210.166:3306/recsys --username root --password root --table shop -m 1
输出数据：
./hadoop fs -cat /user/recsys/input/shop/2013-05-07/*
生成的hdfs数据
287,516809,0,0,6,25,45.78692,126.65384
288,523944,0,0,29,6,120.26087,30.17264
-------------------------------------------------------
HDFS转MYSQ-示例
./sqoop export --connect jdbc:mysql://10.8.210.166:3306/recsys --username root --password root --table shopassoc /user/recsys/output/shop/$today
输入数据：
./hadoop fs -cat /user/recsys/output/shop/2013-05-07/*
Hdfs原始数据
null,857207,729974,947.0818,29,2013-05-08 10:22:29
null,857207,524022,1154.2603,29,2013-05-08 10:22:29
--------------------------------------------------------------------------
技巧
导出导入的数据与字段的顺序进行匹配
从HDFS转换回mysql 的时候，自动增长的主键字段处，可以留空
示例参数说明
参数类型参数名解释
公共connect Jdbc-url
公共username---
公共password---
公共table表名
Import target-dir制定输出hdfs目录，默认输出到/user/$loginName/
export fields-terminated-by Hdfs文件中的字段分割符，默认是“\t”
export export-dir hdfs文件的路径
导出大数据的拆分：
m使用多少个并行导入，默认是1，未开启，数据量大的话会自动根据主键ID进行拆分split-by使用于没有主键的表的拆分，指定拆分的字段，拆分的原则是分布要广泛（自动拆分）Where同时可以手动执行多次，通过where条件进行手动拆分
参数解释
Job定时作业, 个人觉得没啥意义，通常我更相信crontab
eval执行sql，远程进行一些操作，但是经过测试发现不支持 delete
create-hive-table复制某一个数据源的数据存储到hive
其他命令请参考：https://www.doczj.com/doc/d42386347.html,/cdh/3/sqoop/SqoopUserGuide.html https://www.doczj.com/doc/d42386347.html,/heguangdong/blog/129604
MYSQL用户管理
一、用户登录
格式：mysql -h主机地址-u用户名-p用户密码
mysql -h110.110.110.110 -uroot -p123
本地可以直接mysql –uroot -p
二、用户退出
exit、quit
三、添加用户
https://www.doczj.com/doc/d42386347.html,er表保存的是用户的登录信息
1.直接添加无权限
insert into https://www.doczj.com/doc/d42386347.html,er (host,user,password) values('%','jifei',PASSWORD('jifei'));
1.添加并赋权
grant select on 数据库.* to '用户名'@'登录主机' identified by '密码'
四、用户权限
1.添加权限
grant 权限on 数据库.表to '用户名'@'登录主机';
权限：select ,update,delete,insert(表数据)、create,alert,drop(表结构)、references(外键)、create temporary tables(创建临时表)、index(操作索引)、create view,show view(视图)、create routine,alert routine,execute(存储过程)、all,all privileges(所有权限)
数据库：数据库名或者*(所有数据库)
表：表名或者*(某数据库下所有表)
主机:主机名或者%(任何其他主机)
例：grant selec,insert,update,delete on *.* to 'jifei'@'%';
1.撤销权限
revoke 权限on 数据库.表from '用户名'@'登录主机';//将to改为from
例：revoke all on *.* from ‘jifei’@’%’;
1.查看权限
show grants;//自己
show grants for dba@localhost;//指定用户指定host
五、删除用户
delete from https://www.doczj.com/doc/d42386347.html,er where user='' and host='';
六、修改密码
update https://www.doczj.com/doc/d42386347.html,er set password=PASSWORD('111111') where user='root';
七、找回密码
1.关闭mysql服务
killall -TERM mysqld
1.修改配置文件
vi /etc/https://www.doczj.com/doc/d42386347.html,f
在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
1.重启mysqld
service mysqld restart
1.登录
mysql -uroot -p
1.修改密码
update https://www.doczj.com/doc/d42386347.html,er set password=PASSWORD('111111') where user='root'; flush privileges;//刷新权限
1.修改配置文件
vi /etc/https://www.doczj.com/doc/d42386347.html,f
去掉之前的改动
1.重启服务
2.设置远程用户
八、远程用户
①限制在指定ip登录host为ip详情请看添加权限
②在任意远程ip登录host为%详情请看添加权限
1.远程访问
mysql -h110.110.110.110 -uroot -p123;//指定h为ip详情请看用户登录
一些标准实例：
1. https://www.doczj.com/doc/d42386347.html,er表实例：一般来说，Host字段都使用ip来限制，而不是机器名（机器名可变，不是特别靠谱）
select Host, User from user;
| 172.17.% | dev | | 172.17.0.% | export | | 172.17.0.20 | demo | | 172.28.0.% | dev | | 192.168.% | dev | | 110.111.126.% | demo | | 110.111.126.103 | helper | | 110.111.127.% | webnav | | localhost | backup | | localhost | backupdata | | localhost | root | +-----------------+-----------------+
2. 授权实例：show grants for 'helper'@'110.111.127.%'
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'helper'@'110.111.127.%' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxx' WITH MAX_USER_CONNECTIONS 200 | | GRANT ALL PRIVILEGES ON `helper_online`.* TO 'helper'@'110.111.127.%' | +--------------------------------------------------------------------------------------------------------------------------------------------------+
设置MYSQL允许外部连接
mysql默认情况下，只允许localhost连接，如果需要外部IP连接到mysql，需要向mysql 数据库里的“user”表里添加相关授权。
1。改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"
mysql -u root -pvmware
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
2. 授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
这是我在网上找到的资料，但是都试过了没有效果，只能退而求其次，在user表里面加一条记录host指定为空间的IP。测试通过。只是还是不能任意外部连接。
sqoop list-databases --connect jdbc:mysql://localhost:3306 --username root --password 271220676
1. sqoop导入mysql数据出错
用sqoop导入mysql数据出现以下错误：
14/12/03 16:37:58 ERROR manager.SqlManager: Error reading f rom database: java.sql.SQLException: Streaming result set c
om.mysql.jdbc.RowDataDynamic@54b0a583 is still active. No st atements may be issued when any streaming result sets ar e open and in use on a given connection. Ensure that y ou have called .close() on any active streaming result s ets before attempting more queries.
java.sql.SQLException: Streaming result set com.mysql.jdbc.Ro wDataDynamic@54b0a583 is still active. No statements may b e issued when any streaming result sets are open and in
use on a given connection. Ensure that you have called .close() on any active streaming result sets before att empting more queries.
at com.mysql.jdbc.SQLError.createSQLException(SQ LError.java:930)
at com.mysql.jdbc.MysqlIO.checkForOutstandingStr eamingData(MysqlIO.java:2694)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.ja va:1868)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO .java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(Connect ionImpl.java:2642)
at com.mysql.jdbc.ConnectionImpl.execSQL(Connect ionImpl.java:2571)
at com.mysql.jdbc.StatementImpl.executeQuery(Sta tementImpl.java:1464)
at com.mysql.jdbc.ConnectionImpl.getMaxBytesPerC har(ConnectionImpl.java:3030)
at com.mysql.jdbc.Field.getMaxBytesPerCharacter( Field.java:592)
at com.mysql.jdbc.ResultSetMetaData.getPrecision (ResultSetMetaData.java:444)
at org.apache.sqoop.manager.SqlManager.getColumn InfoForRawQuery(SqlManager.java:285)
at org.apache.sqoop.manager.SqlManager.getColumn TypesForRawQuery(SqlManager.java:240)
at org.apache.sqoop.manager.SqlManager.getColumn Types(SqlManager.java:226)
at org.apache.sqoop.manager.ConnManager.getColum nTypes(ConnManager.java:295)
at org.apache.sqoop.orm.ClassWriter.getColumnTyp es(ClassWriter.java:1773)
at org.apache.sqoop.orm.ClassWriter.generate(Cla ssWriter.java:1578)
at org.apache.sqoop.tool.CodeGenTool.generateORM (CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable( ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTo ol.java:601)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRun ner.java:65)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:17 9)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218 )
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227 )
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
14/12/03 16:37:58 ERROR tool.ImportTool: Encountered IOExcep tion running import job: java.io.IOException: No columns t o generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(Cla ssWriter.java:1584)
at org.apache.sqoop.tool.CodeGenTool.generateORM (CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable( ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTo ol.java:601)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRun ner.java:65)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:17 9)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218 )
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227 )
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
这个是由于mysql-connector-java的bug造成的，出错时我用的是
mysql-connector-java-5.1.10-bin.jar，更新成
mysql-connector-java-5.1.32-bin.jar就可以了。
mysql-connector-java-5.1.32-bin.jar的下载地址为
https://www.doczj.com/doc/d42386347.html,/get/Downloads/Connector-J/mysql-connector-java-5.1.32. tar.gz。下载完后解压，在解压的目录下可以找到
mysql-connector-java-5.1.32-bin.jar。
mytable -m 1