共计 7185 个字符,预计需要花费 18 分钟才能阅读完成。
Apache Sqoop 数据转换安装配置与基本操作命令
系统环境
CentOS 6.5
组件版本
name | version |
---|---|
Sqoop | 1.4.6 |
Sqoop 安装
解压
懒的敲了
- 配置环境变量
# SQOOP | |
SQOOP_HOME=/home/Hadoop/development/src/sqoop-1.4.6-cdh5.6.0 | |
PATH=$PATH:$SQOOP_HOME/bin | |
export PATH |
cd 到 Sqoop 根目录
配置 Sqoop 核心文件
主要配置 ${SQOOP_HOME}/conf/sqoop-env-template.sh 文件
cp ${SQOOP_HOME}/conf/sqoop-env-template.sh ${SQOOP_HOME}/conf/sqoop-env.sh
主要修改参数
# 指定 Hadoop 安装目录 | |
export HADOOP_COMMON_HOME= | |
# 指定 Hadoop MapReduce 技术 jar 存放目录 | |
export HADOOP_MAPRED_HOME= | |
# Hbase 安装目录 | |
export HBASE_HOME= | |
# Zookeeper 安装目录 | |
export ZOOCFGDIR= |
如果没有安装的可以不用填写
例如
#Set path to where bin/hadoop is available | |
export HADOOP_COMMON_HOME=/home/hadoop/development/src/hadoop-2.6.0-cdh5.6.0 | |
#Set path to where hadoop-*-core.jar is available | |
"这里之所以和 Hadoop 安装目录一样,是由于上次我配置成 mapreduce jar 地址时,sqoop 报错" | |
export HADOOP_MAPRED_HOME=/home/hadoop/development/src/hadoop-2.6.0-cdh5.6.0 | |
#set the path to where bin/hbase is available | |
"我没有安装 Hbase 所以这里直接 注释掉就可以" | |
#export HBASE_HOME= | |
#Set the path to where bin/hive is available | |
export HIVE_HOME=/home/hadoop/development/src/hive-1.1.0-cdh5.6.0 | |
#Set the path for where zookeper config dir is | |
export ZOOCFGDIR=/home/hadoop/development/src/zookeeper-3.4.5-cdh5.6.0 |
注释掉没有使用的组件
bin/config-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 | |
# Moved to be a runtime check in sqoop. | |
if [! -d "${HCAT_HOME}" ]; then | |
echo "Warning: $HCAT_HOME does not exist! HCatalog jobs will fail." | |
echo 'Please set $HCAT_HOME to the root of your HCatalog installation.' | |
fi | |
#if [! -d "${ACCUMULO_HOME}" ]; then | |
echo "Warning: $ACCUMULO_HOME does not exist! Accumulo imports will fail." | |
echo 'Please set $ACCUMULO_HOME to the root of your Accumulo installation.' | |
fi | |
if [! -d "${ZOOKEEPER_HOME}" ]; then | |
echo "Warning: $ZOOKEEPER_HOME does not exist! Accumulo imports will fail." | |
echo 'Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.' | |
fi |
测试 Sqoop 是否配置成功
sqoop list-databases --connect jdbc:mysql://192.168.2.251:3306/canbot --username root --password root | |
Warning: /home/hadoop/development/src/sqoop-1.4.6-cdh5.6.0/bin/../../hbase does not exist! HBase imports will fail. | |
Please set $HBASE_HOME to the root of your HBase installation. | |
Warning: /home/hadoop/development/src/sqoop-1.4.6-cdh5.6.0/bin/../../hcatalog does not exist! HCatalog jobs will fail. | |
Please set $HCAT_HOME to the root of your HCatalog installation. | |
Warning: /home/hadoop/development/src/sqoop-1.4.6-cdh5.6.0/bin/../../accumulo does not exist! Accumulo imports will fail. | |
Please set $ACCUMULO_HOME to the root of your Accumulo installation. | |
Warning: /home/hadoop/development/src/sqoop-1.4.6-cdh5.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail. | |
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. | |
16/05/23 10:34:46 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.6.0 | |
16/05/23 10:34:46 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. | |
16/05/23 10:34:47 INFO manager.MySQLManager: Preparing to use a MySQL "streaming resultset. | |
information_schema | |
canbot | |
from_66internet | |
from_fhmysql | |
hive | |
mysql | |
sys_app_user" |
Sqoop 操作笔记
mysql 导出到 HDFS
import 导入
–connect‘jdbc:mysql://192.168.2.251:3306/canbot?characterEncoding=UTF-8’链接数据库语句
–username root –password root 账号密码
–table kylin01 指定 mysql 中 canbot 库中的 kylin01 表
–target-dir‘/yuqi_dir/sqoop/kylin01’指定将表到导入到 HDFS 中的路径
-m 1 运行 map 数量
./bin/sqoop import --connect 'jdbc:mysql://192.168.2.251:3306/canbot?characterEncoding=UTF-8' --username root --password root --table kylin01 --target-dir '/yuqi_dir/sqoop/kylin01' -m 1
查看 kylin01 表数据
mysql> select * from kylin01; | |
+-----------+------------+-------+------------+ | |
| commodity | username | price | dateTime | | |
+-----------+------------+-------+------------+ | |
| iPhone6s | zhangsan | 1400 | 1488547368 | | |
| iPhone5s | lisi | 1400 | 1463145768 | | |
| iPhone4s | wangsu | 1400 | 1451820168 | | |
| iPhone7s | liouqi | 1400 | 1430655768 | | |
| iPhone8s | admin | 1400 | 1454498568 | | |
| iPhone9s | userkxl | 1400 | 1470205368 | | |
| iPhone3s | zhangsan | 1400 | 1454505768 | | |
| XIAOMI | zhangsan | 1400 | 1463998968 | | |
| XIAOMI | liouwangba | 1400 | 1453558968 | | |
| XIAOMI | zhangaiguo | 1400 | 1471792968 | | |
| huawei 7x | lioudadiao | 1400 | 1444926168 | | |
| huawei 7x | zhangdapao | 1400 | 1469697768 | | |
| huawei 7x | zhujing | 1400 | 1462040568 | | |
| huawei 7x | nuannuan | 1400 | 1462512168 | | |
| huawei 7x | juanmao | 1400 | 1466302968 | | |
| iPhone6s | zhangsan | 1400 | 1488547368 | | |
| iPhone5s | lisi | 1400 | 1463145768 | | |
| iPhone4s | wangsu | 1400 | 1451820168 | | |
| iPhone7s | liouqi | 1400 | 1430655768 | | |
| iPhone8s | admin | 1400 | 1454498568 | | |
| iPhone9s | userkxl | 1400 | 1470205368 | | |
| iPhone3s | zhangsan | 1400 | 1454505768 | | |
| XIAOMI | zhangsan | 1400 | 1463998968 | | |
| XIAOMI | liouwangba | 1400 | 1453558968 | | |
| XIAOMI | zhangaiguo | 1400 | 1471792968 | | |
| huawei 7x | lioudadiao | 1400 | 1444926168 | | |
| huawei 7x | zhangdapao | 1400 | 1469697768 | | |
| huawei 7x | zhujing | 1400 | 1462040568 | | |
| huawei 7x | nuannuan | 1400 | 1462512168 | | |
| huawei 7x | juanmao | 1400 | 1466302968 | | |
+-----------+------------+-------+------------+ | |
30 rows in set |
查看 HDFS 导出的 kylin01 表数据
[/* | ]$ hadoop fs -cat /yuqi_dir/sqoop/kylin01|
16/05/25 04:08:35 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable | |
iPhone6s,zhangsan,1400,1488547368 | |
iPhone5s,lisi,1400,1463145768 | |
iPhone4s,wangsu,1400,1451820168 | |
iPhone7s,liouqi,1400,1430655768 | |
iPhone8s,admin,1400,1454498568 | |
iPhone9s,userkxl,1400,1470205368 | |
iPhone3s,zhangsan,1400,1454505768 | |
XIAOMI,zhangsan,1400,1463998968 | |
XIAOMI,liouwangba,1400,1453558968 | |
XIAOMI,zhangaiguo,1400,1471792968 | |
huawei 7x,lioudadiao,1400,1444926168 | |
huawei 7x,zhangdapao,1400,1469697768 | |
huawei 7x,zhujing,1400,1462040568 | |
huawei 7x,nuannuan,1400,1462512168 | |
huawei 7x,juanmao,1400,1466302968 | |
iPhone6s,zhangsan,1400,1488547368 | |
iPhone5s,lisi,1400,1463145768 | |
iPhone4s,wangsu,1400,1451820168 | |
iPhone7s,liouqi,1400,1430655768 | |
iPhone8s,admin,1400,1454498568 | |
iPhone9s,userkxl,1400,1470205368 | |
iPhone3s,zhangsan,1400,1454505768 | |
XIAOMI,zhangsan,1400,1463998968 | |
XIAOMI,liouwangba,1400,1453558968 | |
XIAOMI,zhangaiguo,1400,1471792968 | |
huawei 7x,lioudadiao,1400,1444926168 | |
huawei 7x,zhangdapao,1400,1469697768 | |
huawei 7x,zhujing,1400,1462040568 | |
huawei 7x,nuannuan,1400,1462512168 | |
huawei 7x,juanmao,1400,1466302968 |
hdfs 导出到 mysql
sqoop export #导出 | |
--connect jdbc:mysql://192.168.2.251:3306/canbot?characterEncoding=UTF-8 --username root --password root # 数据库连接语句 | |
--table a # 指定导入到那张表 | |
--export-dir /yuqi_dir/sqoop/kylin01/part-m-00000 # 要导入的文件存在 HDFS 的路径 | |
-m 2 # 运行 map 的个数 |
Mysql 直接导入到 Hive
sqoop import --connect 'jdbc:mysql://192.168.2.251:3306/canbot?characterEncoding=UTF-8' --username root --password root | |
--table a # 标记要导出的 mysql 表 | |
--hive-import --hive-table d 导入到 Hive 并且 hive 表的名称为"d" | |
-m 1 |
hive> select * from d; | |
OK | |
iPhone6s zhangsan 1400 1488547368 | |
iPhone5s lisi 1400 1463145768 | |
iPhone4s wangsu 1400 1451820168 | |
iPhone7s liouqi 1400 1430655768 | |
iPhone8s admin 1400 1454498568 | |
iPhone9s userkxl 1400 1470205368 | |
iPhone3s zhangsan 1400 1454505768 | |
XIAOMI zhangsan 1400 1463998968 | |
XIAOMI liouwangba 1400 1453558968 | |
XIAOMI zhangaiguo 1400 1471792968 | |
huawei 7x lioudadiao 1400 1444926168 | |
huawei 7x zhangdapao 1400 1469697768 | |
huawei 7x zhujing 1400 1462040568 | |
huawei 7x nuannuan 1400 1462512168 | |
huawei 7x juanmao 1400 1466302968 | |
iPhone6s zhangsan 1400 1488547368 | |
iPhone5s lisi 1400 1463145768 | |
iPhone4s wangsu 1400 1451820168 | |
iPhone7s liouqi 1400 1430655768 | |
iPhone8s admin 1400 1454498568 | |
iPhone9s userkxl 1400 1470205368 | |
iPhone3s zhangsan 1400 1454505768 | |
XIAOMI zhangsan 1400 1463998968 | |
XIAOMI liouwangba 1400 1453558968 | |
XIAOMI zhangaiguo 1400 1471792968 | |
huawei 7x lioudadiao 1400 1444926168 | |
huawei 7x zhangdapao 1400 1469697768 | |
huawei 7x zhujing 1400 1462040568 | |
huawei 7x nuannuan 1400 1462512168 | |
huawei 7x juanmao 1400 1466302968 | |
Time taken: 0.949 seconds, Fetched: 30 row(s) |
更多 Hadoop 相关信息见Hadoop 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=13
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-08/134190.htm
正文完
星哥玩云-微信公众号