一、Sqoop简介

官网介绍：https://sqoop.apache.org

Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.Sqoop successfully graduated from the Incubator in March of 2012 and is now a Top-Level Apache project: More informationLatest stable release is 1.4.7 (download, documentation). Latest cut of Sqoop2 is 1.99.7 (download, documentation). Note that 1.99.7 is not compatible with 1.4.7 and not feature complete, it is not intended for production deployment.

Apache Sqoop（TM）是一种工具，旨在在Apache Hadoop和结构化数据存储（如关系数据库）之间高效地传输批量数据 。Sqoop于2012年3月成功毕业于孵化器，现在是Apache的顶级项目。
最新的稳定版本是1.4.7。Sqoop2的最新版本为1.99.7。

请注意，1.99.7与1.4.7不兼容且功能不完整，因此不适用于生产部署。

Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、oracle...)之间进行数据的传递，可以将一个关系型数据库（例如： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。
Sqoop项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速的迭代开发，Sqoop独立成为一个Apache项目。

二、Sqoop原理

将导入或导出命令翻译成MapReduce程序来实现。
在翻译出的MR中没有Reduce阶段只有Map阶段，默认4个Map。

三、Sqoop安装

1、环境准备

必须先配置好Java和Hadoop环境

2、下载和解压安装包

2.1、从官网或镜像下载安装包，本文以Sqoop1.4.6为例

#1.4.6安装包
sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz

2.2、将安装包解压到指定的安装目录

#解压安装包到目录：/opt/module/
tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/

3、配置

3.1、进入Sqoop根目录的conf文件下，重命名配置文件

#重命名配置文件
mv sqoop-env-template.sh sqoop-env.sh 

3.2、修改sqoop-env.sh

#配置Hadoop的安装目录
exportHADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2
exportHADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2

#配置Hive的安装目录 
export HIVE_HOME=/opt/module/hive

#配置Zookeeper的安装目录 
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
export ZOOCFGDIR=/opt/module/zookeeper-3.4.10/conf

#配置HBase的安装目录 
export HBASE_HOME=/opt/module/hbase-1.3.1

3.3、拷贝JDBC驱动（以连接MySQL数据库为例）

#将JDBC驱动拷贝到Sqoop的lib目录下（此处以MySQl为例）
cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/lib/ 

4、测试，验证是否成功

4.1、验证Sqoop是否配置正确

#在Sqoop根目录下
bin/sqoop help

如果出现一些Warning警告，并伴随着帮助命令的输出，则证明配置没有问题

4.2、数据库连接测试

#在Sqoop根目录下
bin/sqoop list-databases 
    --connect jdbc:mysql://bigdata102:3306/ 
    --username root 
    --password 123456

若列出所连接数据库中的数据库名则证明连接成功

四、Sqoop常用命令及参数

1、常用命令

1.1 import

所属类：ImportTool

功能：将数据导入大数据集群

1.2 export

所属类：ExportTool

功能：将数据从大数据集群导出

1.3 import-all-tables

所属类：ImportAllTablesTool

功能：导入数据库下所有的表

1.4 codegen

所属类：CodeGenTool

功能：关系型数据库中的表映射为一个Java类，在该类中有各列对应的各个字段

1.5 create-hive-table

所属类：CreateHiveTableTool

功能：创建Hive表

1.6 eval

所属类：EvalSqlTool

功能：查看SQL执行结果

1.7 job

所属类：JobTool

功能： 用来生成一个sqoop的任务，生成后，该任务并不执行，除非使用命令执行该任务。

1.8 list-databases

所属类： ListDatabasesTool

功能： 列出所有数据库名

1.9 list-tables

所属类： ListTablesTool

功能： 列出某个数据库下所有表

1.10 merge

所属类： MergeTool

功能： 将HDFS中不同目录下面的数据合在一起，并存放在指定的目录中

1.11 metastore

所属类： MetastoreTool

功能： 记录sqoop job的元数据信息，如果不启动metastore实例，则默认的元数据存储目录为：~/.sqoop，如果要更改存储目录，可以在配置文件sqoop-site.xml中进行更改。

1.12 help

所属类： HelpTool

功能：帮助信息

1.13 version

所属类： VersionTool

功能：版本信息

2、参数

2.1 公用参数

公用参数：大多数命令都支持的参数

2.1.1 数据库连接相关

2.1.2 import操作相关

2.1.3 export操作相关

2.1.4 hive相关

2.2 常用命令及其专有参数

2.2.1 import

2.2.2 export

2.2.3 codegen

2.2.4 create-hive-table

2.2.5 eval

2.2.6 import-all-tables

2.2.7 job

2.2.8 merge

2.2.11 metastore

五、使用方法及案例

1、基本概念

1.1 导入数据

从关系数据库管理系统（Relational Database Management System：RDBMS）向大数据集群（HDFS，Hive，HBase）传输数据，称为：导入

1.2 导出数据

从大数据集群向RDBMS传输数据，称为：导出

2、导入数据方法

2.1 RDBMS_to_HDFS

2.1.1 全表导入

#导入命令
bin/sqoop import \
#所要连接的数据库URL
--connectjdbc:mysql://hadoop102:3306/company \
#连接数据库的用户和密码
--username root \
--password 123456 \
#数据库中要导出数据的表，的表名
--table staff \
#指定导入数据在HDFS上存储目录
--target-dir /company \
#如果目标目录存在则删除
--delete-target-dir \
#导入到HDFS上时，表中字段用 \t 作为分隔符
--fields-terminated-by"\t" \
#指定导入任务的Map数量
--num-mappers 2 \
#基于id列，将数据切分成2片（--num-mappers的数量）所选列不能有null值，不然     #null值所在列无法导入。最好选择主键列，数字列
--split-by id #注意：只有在--num-mappers > 1 时才需指定该参数

2.1.2 导入指定列

在 2.1.1全表导入 的基础上使用 --columns 参数

#只导入指定列：id和name
--columns id，name

提示：columns中如果涉及到多列，用逗号分隔，分隔时不要添加空格

2.1.3 导入指定行

在 2.1.1全表导入 的基础上使用 --where 参数

#只导入满足指定条件的行
--where 'id=10'

2.1.4 导入SQL查询结果

在 2.1.1全表导入 的基础上使用 --query "SQL语句" 替换 --table 即可

#将SQL语句查询结果导入
--query "select * from 表 where \$CONDITIONS and id <= 25"

注意!!!!(尖叫提示):
a、must contain'CONDITIONS前必须加转移符，防止shell识别为自己的变量

2.2 RDBMS_to_Hive

bin/sqoop import \
--connectjdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--table staff \
--num-mappers 1 \
--fields-terminated-by "\t" \
#将数据导入到Hive中
--hive-import \
#覆盖掉在hive表中已经存在的数据
--hive-overwrite \
#后面接要创建的hive表,默认使用原表名
--hive-tablestaff_hive

注意！！！该过程分为两步：
第一步将数据导入到HDFS上的临时目录，默认的是/user/HDFS用户名/表名
第二步将导入到HDFS的数据迁移到Hive仓库

3、导出数据方法

3.1 Hive/HDFS_to_RDBMS

#执行Sqoop导出命令
bin/sqoop export \
#导出的数据库URL,用户名及密码
--connectjdbc:mysql://bigdata102:3306/test \
--username root \
--password 123456 \
#导出的数据要进的表
--table staff2 \
#mapper个数
--num-mappers 1 \
#导出数据所在的路径
--export-dir /company \
#指定字段间的分隔符
--input-fields-terminated-by"\t"

提示：Mysql中如果表不存在，不会自动创建

4、打包成脚本（实际使用案例）

4.1 MySQL导入HDFS

#!/bin/bash

#定义Sqoop命令路径
sqoop=/opt/module/sqoop/bin/sqoop

#do_date为传入的日期值，若未指定默认当天
do_date=`date -d '-1 day' +%F`
if [[ -n "$2" ]]; then
    do_date=$2
fi

#定义
import_data(){
$sqoop import \
--connect jdbc:mysql://hadoop102:3306/test \
--username root \
--password 000000 \
--target-dir /origin_data/test/db/$1/$do_date \
--delete-target-dir \
--query "$2 and  \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec lzop \
--null-string '\\N' \
--null-non-string '\\N'

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/test/db/$1/$do_date
}

import_order_info(){
  import_data order_info "select
                            id, 
                            final_total_amount, 
                            order_status, 
                            user_id, 
                            out_trade_no, 
                            create_time, 
                            operate_time,
                            province_id,
                            benefit_reduce_amount,
                            original_total_amount,
                            feight_fee      
                        from order_info
                        where (date_format(create_time,'%Y-%m-%d')='$do_date' 
                        or date_format(operate_time,'%Y-%m-%d')='$do_date')"
}

import_coupon_use(){
  import_data coupon_use "select
                          id,
                          coupon_id,
                          user_id,
                          order_id,
                          coupon_status,
                          get_time,
                          using_time,
                          used_time
                        from coupon_use
                        where (date_format(get_time,'%Y-%m-%d')='$do_date'
                        or date_format(using_time,'%Y-%m-%d')='$do_date'
                        or date_format(used_time,'$Y-%m-%d')='$do_date')"
}

import_order_status_log(){
  import_data order_status_log "select
                                  id,
                                  order_id,
                                  order_status,
                                  operate_time
                                from order_status_log
                                where date_format(operate_time,'%Y-%m-%d')='$do_date'"
}

import_activity_order(){
  import_data activity_order "select
                                id,
                                activity_id,
                                order_id,
                                create_time
                              from activity_order
                              where date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_user_info(){
  import_data "user_info" "select 
                            id,
                            name,
                            birthday,
                            gender,
                            email,
                            user_level, 
                            create_time,
                            operate_time
                          from user_info 
                          where (DATE_FORMAT(create_time,'%Y-%m-%d')='$do_date' 
                          or DATE_FORMAT(operate_time,'%Y-%m-%d')='$do_date')"
}

import_order_detail(){
  import_data order_detail "select 
                              od.id,
                              order_id, 
                              user_id, 
                              sku_id,
                              sku_name,
                              order_price,
                              sku_num, 
                              od.create_time  
                            from order_detail od
                            join order_info oi
                            on od.order_id=oi.id
                            where DATE_FORMAT(od.create_time,'%Y-%m-%d')='$do_date'"
}

import_payment_info(){
  import_data "payment_info"  "select 
                                id,  
                                out_trade_no, 
                                order_id, 
                                user_id, 
                                alipay_trade_no, 
                                total_amount,  
                                subject, 
                                payment_type, 
                                payment_time 
                              from payment_info 
                              where DATE_FORMAT(payment_time,'%Y-%m-%d')='$do_date'"
}

import_comment_info(){
  import_data comment_info "select
                              id,
                              user_id,
                              sku_id,
                              spu_id,
                              order_id,
                              appraise,
                              comment_txt,
                              create_time
                            from comment_info
                            where date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_order_refund_info(){
  import_data order_refund_info "select
                                id,
                                user_id,
                                order_id,
                                sku_id,
                                refund_type,
                                refund_num,
                                refund_amount,
                                refund_reason_type,
                                create_time
                              from order_refund_info
                              where date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_sku_info(){
  import_data sku_info "select 
                          id,
                          spu_id,
                          price,
                          sku_name,
                          sku_desc,
                          weight,
                          tm_id,
                          category3_id,
                          create_time
                        from sku_info where 1=1"
}

import_base_category1(){
  import_data "base_category1" "select 
                                  id,
                                  name 
                                from base_category1 where 1=1"
}

import_base_category2(){
  import_data "base_category2" "select
                                  id,
                                  name,
                                  category1_id 
                                from base_category2 where 1=1"
}

import_base_category3(){
  import_data "base_category3" "select
                                  id,
                                  name,
                                  category2_id
                                from base_category3 where 1=1"
}

import_base_province(){
  import_data base_province "select
                              id,
                              name,
                              region_id,
                              area_code,
                              iso_code
                            from base_province
                            where 1=1"
}

import_base_region(){
  import_data base_region "select
                              id,
                              region_name
                            from base_region
                            where 1=1"
}

import_base_trademark(){
  import_data base_trademark "select
                                tm_id,
                                tm_name
                              from base_trademark
                              where 1=1"
}

import_spu_info(){
  import_data spu_info "select
                            id,
                            spu_name,
                            category3_id,
                            tm_id
                          from spu_info
                          where 1=1"
}

import_favor_info(){
  import_data favor_info "select
                          id,
                          user_id,
                          sku_id,
                          spu_id,
                          is_cancel,
                          create_time,
                          cancel_time
                        from favor_info
                        where 1=1"
}

import_cart_info(){
  import_data cart_info "select
                        id,
                        user_id,
                        sku_id,
                        cart_price,
                        sku_num,
                        sku_name,
                        create_time,
                        operate_time,
                        is_ordered,
                        order_time
                      from cart_info
                      where 1=1"
}

import_coupon_info(){
  import_data coupon_info "select
                          id,
                          coupon_name,
                          coupon_type,
                          condition_amount,
                          condition_num,
                          activity_id,
                          benefit_amount,
                          benefit_discount,
                          create_time,
                          range_type,
                          spu_id,
                          tm_id,
                          category3_id,
                          limit_num,
                          operate_time,
                          expire_time
                        from coupon_info
                        where 1=1"
}

import_activity_info(){
  import_data activity_info "select
                              id,
                              activity_name,
                              activity_type,
                              start_time,
                              end_time,
                              create_time
                            from activity_info
                            where 1=1"
}

import_activity_rule(){
    import_data activity_rule "select
                                    id,
                                    activity_id,
                                    condition_amount,
                                    condition_num,
                                    benefit_amount,
                                    benefit_discount,
                                    benefit_level
                                from activity_rule
                                where 1=1"
}

import_base_dic(){
    import_data base_dic "select
                            dic_code,
                            dic_name,
                            parent_code,
                            create_time,
                            operate_time
                          from base_dic
                          where 1=1" 
}

case $1 in
  "order_info")
     import_order_info
;;
  "base_category1")
     import_base_category1
;;
  "base_category2")
     import_base_category2
;;
  "base_category3")
     import_base_category3
;;
  "order_detail")
     import_order_detail
;;
  "sku_info")
     import_sku_info
;;
  "user_info")
     import_user_info
;;
  "payment_info")
     import_payment_info
;;
  "base_province")
     import_base_province
;;
  "base_region")
     import_base_region
;;
  "base_trademark")
     import_base_trademark
;;
  "activity_info")
      import_activity_info
;;
  "activity_order")
      import_activity_order
;;
  "cart_info")
      import_cart_info
;;
  "comment_info")
      import_comment_info
;;
  "coupon_info")
      import_coupon_info
;;
  "coupon_use")
      import_coupon_use
;;
  "favor_info")
      import_favor_info
;;
  "order_refund_info")
      import_order_refund_info
;;
  "order_status_log")
      import_order_status_log
;;
  "spu_info")
      import_spu_info
;;
  "activity_rule")
      import_activity_rule
;;
  "base_dic")
      import_base_dic
;;

"first")
   import_base_category1
   import_base_category2
   import_base_category3
   import_order_info
   import_order_detail
   import_sku_info
   import_user_info
   import_payment_info
   import_base_province
   import_base_region
   import_base_trademark
   import_activity_info
   import_activity_order
   import_cart_info
   import_comment_info
   import_coupon_use
   import_coupon_info
   import_favor_info
   import_order_refund_info
   import_order_status_log
   import_spu_info
   import_activity_rule
   import_base_dic
;;
"all")
   import_base_category1
   import_base_category2
   import_base_category3
   import_order_info
   import_order_detail
   import_sku_info
   import_user_info
   import_payment_info
   import_base_trademark
   import_activity_info
   import_activity_order
   import_cart_info
   import_comment_info
   import_coupon_use
   import_coupon_info
   import_favor_info
   import_order_refund_info
   import_order_status_log
   import_spu_info
   import_activity_rule
   import_base_dic
;;
esac

4.2 Hive导出MySQL

#!/bin/bash

hive_db_name=test_hive
mysql_db_name=hive2mysql

export_data() {
/opt/module/sqoop/bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/${mysql_db_name}?useUnicode=true&characterEncoding=utf-8"  \
--username root \
--password 000000 \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$hive_db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-mode allowinsert \
--update-key $2 \
--input-null-string '\\N'    \
--input-null-non-string '\\N'
}

case $1 in
  "ads_uv_count")
     export_data "ads_uv_count" "dt"
;;
  "ads_user_action_convert_day") 
     export_data "ads_user_action_convert_day" "dt"
;;
  "ads_gmv_sum_day")
     export_data "ads_gmv_sum_day" "dt"
;;
  "ads_user_topic")
     export_data "ads_user_topic" "dt"
;;
   "all")
     export_data "ads_uv_count" "dt"
     export_data "ads_user_action_convert_day" "dt"
     export_data "ads_gmv_sum_day" "dt"
     export_data "ads_user_topic" "dt"
;;
esac

关于导出update还是insert的问题：
--update-mode：
updateonly 只更新，无法插入新数据
allowinsert 允许新增

--update-key：
允许更新的情况下，指定哪些字段匹配视为同一条数据，进行更新而不增加。多个字段用逗号分隔。

​

六、常见问题及解决方案

1、Sqoop导入导出Null存储一致性问题（遇到）

Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性：

a 在导出数据时采用--input-null-string和--input-null-non-string两个参数

b 导入数据时采用--null-string和--null-non-string

2、Sqoop数据导出一致性问题（遇到）

场景：如Sqoop在导出到Mysql时，使用4个Map任务，过程中有2个任务失败，那此时MySQL中存储了另外两个Map任务导入的数据，此时老板正好看到了这个报表数据。而开发工程师发现任务失败后，会调试问题并最终将全部数据正确的导入MySQL，那后面老板再次看报表数据，发现本次看到的数据与之前的不一致

解决：
可以使用--staging-table –clear-staging
任务执行成功首先在tmp临时表中，然后将tmp表中的数据复制到目标表中（这个时候可以使用事务，保证事务的一致性）

3、 Sqoop在导入数据的时候数据倾斜

https://blog.csdn.net/lizhiguo18/article/details/103969906

sqoop 抽数的并行化主要涉及到两个参数：num-mappers：启动N个map来并行导入数据，默认4个；split-by：按照某一列来切分表的工作单元。

通过ROWNUM() 生成一个严格均匀分布的字段，然后指定为分割字段

4、Sqoop数据导出Parquet（遇到）

ADS层数据用Sqoop往MySql中导入数据的时候，如果用了orc（Parquet）不能导入，需转化成text格式

（1）创建临时表，把Parquet中表数据导入到临时表，把临时表导出到目标表用于可视化

（2）Sqoop里面有参数，可以直接把Parquet转换为text

（3）ads层建表的时候就不要建Parquet表