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

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

系统环境

CentOS 6.5

组件版本

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)