sqoop的安装和使用

云计算 waitig 551℃ 百度已收录 0评论

sqoop是什么?

sqoop是一个hadoop(HDFS,Hbase,hive)和结构数据库(关系型数据库)之间进行数据整合的工具.
sqoop的安装


1.解压并配置环境变量
2.配置配置文件 ./conf/sqoop-env.sh
mv sqoop-env-template.cmd sqoop-env.sh
vi sqoop-env.sh
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/usr/local/hadoop-2.7.1/

里面安装过的,你就把你目录放在上面,记得把前面的注释给去掉
3.将MySQL的驱动包cp到sqoop的安装目录下的lib目录下面
4.测试
sqoop version
sqoop的命令


import:将结构数据库中的数据导入到HDFS中.
export:将hadoop中的数据导出到关系型数据库中
sqoop的语句

–显示所有的数据库


sqoop list-databases –connect jdbc:mysql://hadoop01:3306 \
–username root –password mysql;

–显示一个数据库中的所有表
sqoop list-tables –connect jdbc:mysql://hadoop01:3306/test \
–username root –password mysql;

—-import导入到HDFS:
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table DEPT \
–target-dir /hello/emp/tmp4

sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table EMP -m 1 –fields-terminated-by ‘\t’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–target-dir /hello/emp/tmp5

—-import指定列导入
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table EMP -m 1 \
–columns ‘EMPNO,ENAME,JOB,MGR’ \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–target-dir /hello/emp/tmp6

—-import导入到hive表
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table EMP -m 1 \
–columns ‘EMPNO,ENAME,JOB,MGR’ \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–create-hive-table –hive-import –hive-overwrite \
–hive-table test.sq2 –delete-target-dir

—-import指定where来导入
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table EMP -m 1 \
–columns ‘EMPNO,ENAME,JOB,MGR’ \
–where ‘EMPNO > 7700’ \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–target-dir /hello/emp/tmp7 –delete-target-dir;

—-import指定Query来导入
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql -m 1 \
–query ‘select EMPNO,ENAME,JOB,MGR from EMP where EMPNO > 7700 and $CONDITIONS’ \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–target-dir /hello/emp/tmp8 –delete-target-dir;

注意:query和table不能一起使用不然会出错Cannot specify –Query and –table together

—-import指定split-by来导入
sqoop import –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver
–username root –password mysql -m 2 \
–table EMP –split-by EMPNO \
–fields-terminated-by ‘\t’ –lines-terminated-by ‘\n’ \
–null-string ‘\\N’ –null-non-string ‘\\N’ \
–target-dir /hello/emp/tmp9 –delete-target-dir;

—-export导出到MySQL中
sqoop export –connect jdbc:mysql://hadoop01:3306/test –driver com.mysql.jdbc.Driver \
–username root –password mysql \
–table emp1 -m 1 \
–eport-dir ‘/hello/emp/tmp8’ –update-mode updateonly \
–update-key id \
–input-fields-terminated-by ‘\t’ –input-lines-terminated-by ‘\n’ \
–input-null-string ‘\\N’ –input-null-non-string ‘\\N’ ;
注意:这里的表emp1是存在


本文由【waitig】发表在等英博客
本文固定链接:sqoop的安装和使用
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)