Sqoop

简介

用于在HDFS和关系型数据库之间数据的流转

原理

将导入导出命令转换为MapReduce的Map任务，对InputFormat和OutputFormat进行定制

配置

sqoop-env.sh

export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2 
export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2 
export HIVE_HOME=/opt/module/hive 
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10 
export ZOOCFGDIR=/opt/module/zookeeper-3.4.10
export HBASE_HOME=/opt/module/hbase

cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin_hadoop-2.0.4-alpha/lib/

RDBMS -> HDFS

// 全部导入
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

// 查询导入
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query 'select name,sex from staff where id <= 1 and $CONDITIONS'

// 导入制定列
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex

// 使用Sqoop关键字筛选查询导入数据
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"
--where "id=1"

RDMS -> Hive

bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--hive-import \
--hive-overwrite \
--hive-table staff_hive

RDMS -> Hbase

bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table company \
--columns "id,name,sex" \
--column-family "info" \
--hbase-create-table \
--hbase-row-key "id" \
--num-mappers 1 \
--split-by id

Hbase1.0.1之后的版本需手动创建Hbase表
hbase> create hbase_company','info'

Hive/HDFS -> RDBMS

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--num-mappers 1 \
--export-dir /user/hive/warehouse/staff_hive \
--input-fields-terminated-by "\t"