sqoop 导出mysql,oracle
来源:互联网 发布:机构龙虎榜数据 编辑:程序博客网 时间:2024/05/02 04:51
sqoop mysql 导入,导出
1.
安装(前提hadoop启动)
[hadoop@h91 ~]$ tar -zxvf sqoop-1.3.0-cdh3u5.tar.gz
[hadoop@h91 hadoop-0.20.2-cdh3u5]$ cp hadoop-core-0.20.2-cdh3u5.jar /home/hadoop/sqoop-1.3.0-cdh3u5/lib/
[hadoop@h91 ~]$ cp ojdbc6.jar sqoop-1.3.0-cdh3u5/lib/
[hadoop@h91 ~]$ vi .bash_profile
添加
export SQOOP_HOME=/home/hadoop/sqoop-1.3.0-cdh3u5
[hadoop@h91 ~]$ source .bash_profile
2.
[hadoop@h91 ~]$ cd sqoop-1.3.0-cdh3u5/bin/
[hadoop@h91 bin]$ vi configure-sqoop
注释掉hbase和zookeeper检查
## Moved to be a runtime check in sqoop.
#if [ ! -d "${HBASE_HOME}" ]; then
# echo "Warning: $HBASE_HOME does not exist! HBase imports will fail."
# echo 'Please set $HBASE_HOME to the root of your HBase installation.'
#fi
3.mysql 授权
mysql> insert into mysql.user(Host,User,Password) values("hadoop1","hive",password("mysql"));
mysql> flush privileges;
mysql> grant all privileges on *.* to 'hive'@'%' identified by 'mysql' with grant option;
mysql> flush privileges;
mysql> use test;
mysql> create table sss (id int,name varchar(10));
mysql> insert into sss values(1,'zs');
mysql> insert into sss values(2,'ls');
4.测试sqoop能否连接上mysql
[hadoop@h91 mysql-connector-java-5.0.7]$ cp mysql-connector-java-5.0.7-bin.jar /home/hadoop/sqoop-1.3.0-cdh3u5/lib/
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop list-tables --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql
(显示有sss表)
5.将mysql中的sqoop用户下sss 导入到HDFS中
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop import --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --table sss -m 1
(-m 为并行 默认并行度为4)
[hadoop@h91 hadoop-0.20.2-cdh3u5]$ bin/hadoop fs -ls
多出个sss目录
[hadoop@h91 hadoop-0.20.2-cdh3u5]$ bin/hadoop fs -ls /user/hadoop/sss
[hadoop@h91 hadoop-0.20.2-cdh3u5]$ bin/hadoop fs -cat /user/hadoop/sss/part-m-00000
看到 sss表内容
1,zs
2,ls
6.从HDFS导入到mysql中
mysql> delete from sss;
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop export --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --table sss --export-dir hdfs://hadoop1:9000/user/hadoop/sss/part-m-00000
[root@o222 ~]# mysql -usqoop -p
mysql> use test
mysql> select * from sss;
表中的数据 又 回来了
---------------------------------------------------------------
增量抽取 mysql数据 到HDFS中
类型1(字段增长)
bin/sqoop import --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --table sss -m 1 --target-dir /user/hadoop/a --check-column id --incremental append --last-value 3
类型2(时间增长)
mysql> create table s2 (id int,sj timestamp not null default current_timestamp);
mysql> insert into s2 (id)values(123);
mysql> insert into s2 (id)values(321);
mysql> select * from s2;
+------+---------------------+
| id | sj |
+------+---------------------+
| 123 | 2015-11-20 22:34:09 |
| 321 | 2015-11-20 22:34:23 |
+------+---------------------+
bin/sqoop import --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --table s2 -m 1 --target-dir /user/hadoop/abcd --incremental lastmodified --check-column sj --last-value '2016-02-24 16:40:54'
结果:只有id为321的行更新了
---------------------------------------------------------
配置 sqoop导入的
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop export --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --table qqq --export-dir hdfs://hadoop1:9000/user/hadoop/qqq.txt --input-fields-terminated-by '\t'
****--input-fields-terminated-by '\t'
声明分隔符*******
----------------------------------------------------------
sqoop eval工具:
sqoop下 使用sql语句对 关系型数据库进行操作
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop eval --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --query "select * from sss"
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop eval --connect jdbc:mysql://192.168.159.135:3306/test --username hive --password mysql --query "insert into sss values(3,'ww')"
==============================================================
sqoop hive
mysql 导入到 hive中
mysql> select * from sss;
+------+------+
| id | name |
+------+------+
| 1 | zs |
| 2 | ls |
| 3 | ww |
| 10 | haha |
+------+------+
hive> create table tb1(id int,name string)
row format delimited
fields terminated by ',';
**** sqoop 默认的分隔符为"," ***********
[hadoop@h101 sqoop-1.3.0-cdh3u5]$ bin/sqoop import --connect jdbc:mysql://192.168.8.101/test --username sqoop --password sqoop --table sss --hive-import -m 1 --hive-table tb1 --fields-terminated-by ','
hive> select * from tb1;
============================================================
sqoop oracle
1.
[hadoop@h91 ~]$ cp ojdbc6.jar sqoop-1.3.0-cdh3u5/lib/
测试sqoop 连接oracle
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop list-tables --connect jdbc:oracle:thin:@192.168.8.222:1521:TEST --username scott --password abc
2.
导出到HDFS中
[hadoop@h91 sqoop-1.3.0-cdh3u5]$ bin/sqoop import --connect jdbc:oracle:thin:@192.168.8.222:1521:TEST --username SCOTT --password abc --verbose -m 1 --table S1
(表名字 和 用户名要大写 )
HDFS 中查看
[hadoop@h91 hadoop-0.20.2-cdh3u5]$ bin/hadoop fs -cat /user/hadoop/S1/part-m-00000
101,zhangsan
102,lisi
阅读全文
1 0
- sqoop 导出mysql,oracle
- Sqoop导出MySQL数据
- Sqoop 1.4.6对于Mysql,Oracle的导入导出
- Sqoop HDFS导出到MySQL
- 使用Sqoop将HDFS/Hive/HBase与MySQL/Oracle中的数据相互导入、导出
- 使用Sqoop将HDFS/Hive/HBase与MySQL/Oracle中的数据相互导入、导出
- sqoop从hive中导出oracle数据
- 利用sqoop导出hive数据到 oracle
- sqoop操作之HDFS导出到ORACLE
- [Sqoop]将Hive数据表导出到Mysql
- sqoop 导出 hive分区表 数据到 mysql
- Sqoop导出数据到MYSQL问题
- sqoop导出mysql数据进入hive错误
- sqoop从hbase导出数据到mysql
- sqoop导出hive表到mysql中
- sqoop导出hive表数据到mysql
- SQOOP从HDFS导出数据到MySQL
- Sqoop_详细总结 使用Sqoop将HDFS/Hive/HBase与MySQL/Oracle中的数据相互导入、导出
- Windows 10 安装Scrapy 爬虫框架
- Map<Key,Value>基于Value值排序
- wifi模块简单使用教程,如何将数据发送到电脑(esp8266 arduino库的使用)
- KafkaController介绍
- centos7上ansible初步使用
- sqoop 导出mysql,oracle
- 5.27模拟题 逃避系统警察
- AlphaGo 与柯洁华山论剑,人工智能一手遮天指日可待?
- 关于系统时间处理
- LeetCode 494. Target Sum
- 击中击不中变换的简单应用
- 修改了scikitlearn源文件
- java poi操作Excel
- PyTorch学习系列(二)——数据预处理torchvision.transforms