Apache Sqoop数据转换安装配置与基本操作命令

396次阅读

共计 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@canbot130 ~]$ 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)