Sqoop入门学习

1.sqoop
sqoop是一个用于在Hadoop和关系型数据库服务器之间传输数据的工具。
它用于从关系型数据库(Mysql\Oracle等)导出数据到Hadoop HDFS，并从Hadoop文件系统导出到关系型数据库。
它由Apache软件基金会提供。

为什么使用sqoop?
快速实现Hadoop(HDFS/hive/hbase)与传统数据库(mysql\Oracle)之间的数据传递
Sqoop提供许多数据传输方式。(支持分布式并行)

2.工作流程
RDBMS->import(Sqoop Tool)->Hadoop File
Hadoop File->import(Sqoop Tool)->RDBMS
Sqoop导入：从RDBMS向HDFS导入单独的表，表中的每一行被视为HDFS的记录，所有记录以文本的形式存储在文本文件中
或作为Avro和Sequence文件中的二进制数据存储。
Sqoop导出：从HDFS导出回RDBMS。给Sqoop输入的文件包括记录，这些记录在表中被称为行。这些被读取并解析成一组
记录并用用户指定的分隔符分隔。
Sqoop作业：保存执行命令中的参数，方便被重复执行。比如定时同步。

Sqoop运行原理：
Sqoop接收用户命令后，会生成Map/Reduce代码，并打包将jar包发给Hadoop去执行
Map任务会读取源系统数据，导入到目标数据库中。

3.sqoop安装
首先安装java、hadoop
cd /youfan
#上传并解压
rz sqoop-1.4.5.bin__hadoop-0.20.tar.gz
tar -zxvf sqoop-1.4.5.bin__hadoop-0.20.tar.gz
#配置文件
cd sqoop
cd conf
cp sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh
export HADOOP_COMMON_HOME=/usr/local/hadoop
export HADOOP_MAPRED_HOME=/usr/local/hadoop
#配置环境变量
vim /etc/profile
export sqoop_home=/usr/local/sqoop
export PATH=:$sqoop_home/bin
source /etc/profile
#目录移动
mv sqoop-1.4.5.bin__hadoop-0.20 /usr/local/sqoop
#驱动文件移动
cp mysql-connector-java-5.1.40-bin.jar /usr/local/sqoop/lib/
#查看sqoop版本
sqoop-version
sqoop-help

mysql数据模拟
1.新建数据库
数据库名：datawarehouse
字符集：utf8 -- UTF-8 Unicode
排序规则：utf8_bin

2.新建表

商品类别表productType
id int 20 not null primary key,
productTypeName varchar 20
productTypeleave varchar 2
parentId int int 20

产品表 product

id int 20 primary key ,auto incremental
productName varchar 20,
productTypeid int 20,
originalPrice double(20,2),
huodongPrice double(20,2),
shopid int 20

商品详情 productdetail
id int 20
productid int 20,
place varchar2(50)
brand varchar(50)

商家表：mechart
id int 20 primary key,auto incremental,
mechartName varchar(50),
mechartArea varchar(16),
shopnum int 20

用户表userinfo
id int 20 primary key, auto incrmental,
name varchar 50,
area varchar 16,
age int 6,
email varchar 16,
birthday varchar 16,
account varchar 16,
telphone varchar 16,

商铺表
shopInfo
id int 20 primary key, auto incremental,
shopname varchar(50),
merchartId int 20,
shopdesrc varchar(1000)

3.创建HIVE表：
CREATE EXTERNAL TABLE merchart(id String,merchartName String,merchartArea String,shopnum String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' location '/user/hive/warehouse/ods.db/merchart';
CREATE EXTERNAL TABLE product(id String,productName String,productTypeid String,originalPrice String,huodongPrice String,shopid String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  location '/user/hive/warehouse/ods.db/product';
CREATE EXTERNAL TABLE productdetail(id String,productid String,place String,brand String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  location '/user/hive/warehouse/ods.db/productdetail';
CREATE EXTERNAL TABLE producttype(id String,productTypeName String,productTypeleave String,parentId String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  location '/user/hive/warehouse/ods.db/producttype';
CREATE EXTERNAL TABLE shopinfo(id String,shopname String,merchartId String,shopdesrc String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  location '/user/hive/warehouse/ods.db/shopinfo';
CREATE EXTERNAL TABLE userinfo(id String,name String,area String,age String,email String,birthday String,account String,telphone String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  location '/user/hive/warehouse/ods.db/userinfo';

4.sqoop命令：
sqoop export将hdfs导入到关系型数据库
sqoop import将关系型数据库导入到hdfs

#将指定mysql数据库中的merchart，导入到hdfs中，字段之间以逗号分隔。
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table merchart --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/merchart
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table product --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/product
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table productdetail --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/productdetail
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table producttype --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/producttype
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table shopinfo --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/shopinfo
sqoop import --connect jdbc:mysql://192.168.50.104:3306/datawarehouse --username root --password 123456 --table userinfo --fields-terminated-by ',' -m 1 --target-dir /user/hive/warehouse/ods.db/userinfo

sqoop执行报错:
ERROR tool.ImportTool: Encountered IOException running import job: java.io.FileNotFoundExce
解决方案如下：
<configuration>
  <property>
    <name>mapreduce.framework.name</name>
    <value>yarn</value>
 </property>
</configuration>

解决 org.apache.hadoop.yarn.exceptions.InvalidAuxServiceException：

问题原因：
因为Hive底层执行job是hadoop的MP，如果auxService:mapreduce_shuffle不存在，我们就须要配置。
解决方案：
设置yarn-site.xml文件，添加以下内容：
<property>
    <name>yarn.nodemanager.aux-services</name>
    <value>mapreduce_shuffle</value>
</property>
<property>
    <name>yarn.nodemanager.aux-services.mapreduce.shuffle.class</name>
    <value>org.apache.hadoop.mapred.ShuffleHandler</value>
</property>
*注意：在master和slave所有的主从机器上都需要修改这个文件，修改后重启即可，不用格式化（hadoop namenode -format）

5.sqoop高级内容
全量数据导入
# 全量数据导入
sqoop import \
 --connect jdbc:mysql://192.168.xxx.xxx:3316/testdb \
 --username root \
 --password 123456 \
 --query “select * from test_table where \$CONDITIONS” \
 --target-dir /user/root/person_all \ 
 --fields-terminated-by “,” \
 --hive-drop-import-delims \
 --null-string “\\N” \
 --null-non-string “\\N” \
 --split-by id \
 -m 6 \
重要参数说明：
– query    SQL查询语句
– target-dir    HDFS目标目录（确保目录不存在，否则会报错，因为Sqoop在导入数据至HDFS时会自己在HDFS上创建目录）
–hive-drop-import- delims    删除数据中包含的Hive默认分隔符（^A, ^B, \n）
–null-string    string类型空值的替换符（Hive中Null用\n表示）
–null-non-string    非string类型空值的替换符
–split-by    数据切片字段（int类型，m>1时必须指定）
-m    Mapper任务数，默认为4

增量数据导入

增量数据导入分两种，一是基于递增列的增量数据导入（Append方式）。二是基于时间列的增量数据导入（LastModified方式）。
举个栗子，有一个订单表，里面每个订单有一个唯一标识自增列ID，在关系型数据库中以主键形式存在。
之前已经将id在0~5201314之间的编号的订单导入到Hadoop中了（这里为HDFS），
现在一段时间后我们需要将近期产生的新的订单数据导入Hadoop中（这里为HDFS），
以供后续数仓进行分析。此时我们只需要指定–incremental 参数为append，–last-value参数为5201314即可。
表示只从id大于5201314后开始导入

# Append方式的全量数据导入
 sqoop import \
   --connect jdbc:mysql://192.168.xxx.xxx:3316/testdb \
   --username root \
   --password 123456 \
   --query “select order_id, name from order_table where \$CONDITIONS” \
   --target-dir /user/root/orders_all \ 
   --split-by order_id \
   -m 6  \
   --incremental append \
   --check-column order_id \
   --last-value 5201314

参数说明：
–incremental append    基于递增列的增量导入（将递增列值大于阈值的所有数据增量导入Hadoop）
–check-column    递增列（int）
–last-value    阈值（int）

2、lastModify方式
此方式要求原有表中有time字段，它能指定一个时间戳，让Sqoop把该时间戳之后的数据导入至Hadoop（这里为HDFS）。
因为后续订单可能状态会变化，变化后time字段时间戳也会变化，此时Sqoop依然会将相同状态更改后的订单导入HDFS，
当然我们可以指定merge-key参数为orser_id，表示将后续新的记录与原有记录合并。
# 将时间列大于等于阈值的数据增量导入HDFS
 sqoop import \
   --connect jdbc:mysql://192.168.xxx.xxx:3316/testdb \
   --username root \
   --password transwarp \
   --query “select order_id, name from order_table where \$CONDITIONS” \
   --target-dir /user/root/order_all \ 
   --split-by id \
   -m 4  \
   --incremental lastmodified \
   --merge-key order_id \
   --check-column time \
   # remember this date !!!
   --last-value “2014-11-09 21:00:00”  
参数说明：
–incremental lastmodified    基于时间列的增量导入（将时间列大于等于阈值的所有数据增量导入Hadoop）
–check-column    时间列（int）
–last-value    阈值（int）
–merge-key    合并列（主键，合并键值相同的记录）

并发导入参数如何设置？
我们知道通过 -m 参数能够设置导入数据的 map 任务数量，即指定了 -m 即表示导入方式为并发导入，这时我们必须同时指定 - 
-split-by 参数指定根据哪一列来实现哈希分片，从而将不同分片的数据分发到不同 map 任务上去跑，避免数据倾斜。
重要Tip：

生产环境中，为了防止主库被Sqoop抽崩，我们一般从备库中抽取数据。
一般RDBMS的导出速度控制在60~80MB/s，每个 map 任务的处理速度5~10MB/s 估算，即 -m 参数一般设置4~8，表示启动 4~8 个map 
任务并发抽取。