Sqoop 学习笔记

概述

Apache Sqoop™ 是一种工具，旨在有效地在Apache Hadoop和结构化数据存储（例如关系数据库）之间传输批量数据。

工作机制

1.Sqoop任务是通过解析生成mapReduce执行，底层默认是用了四个mapTask执行，没有reduceTask
2.导入（import）流程：
1）读取要导入数据的表结构
2）读取参数，设置好job
3）调用mapreduce执行任务
–a.首先要对数据进行切分
–b.写入范围，以便读取
–c.读取范围参数
–d.创建RecordReader并从数据库中读取数据
–e.创建map
–f.执行map
3.导出（export）导出流程：导入过程的逆向过程

安装及使用

1 下载并解压
1）下载地址：https://mirrors.bfsu.edu.cn/apache/sqoop/

2）上传安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz到hadoop102的/opt/software路径中

3）解压sqoop安装包到指定目录，如：

[linux@hadoop102 software]$ tar -zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/

4）解压sqoop安装包到指定目录，如：

[linux@hadoop102 module]$ mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop

2 修改配置文件

1. 进入到/opt/module/sqoop/conf目录，重命名配置文件

[linux@hadoop102 conf]$ mv sqoop-env-template.sh sqoop-env.sh

2. 修改配置文件

[linux@hadoop102 conf]$ vim sqoop-env.sh 

增加如下内容

export HADOOP_COMMON_HOME=/opt/module/hadoop-3.1.3
export HADOOP_MAPRED_HOME=/opt/module/hadoop-3.1.3
export HIVE_HOME=/opt/module/hive
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.5.7
export ZOOCFGDIR=/opt/module/zookeeper-3.5.7/conf

3 拷贝JDBC驱动
1）将mysql-connector-java-5.1.48.jar 上传到/opt/software路径

2）进入到/opt/software/路径，拷贝jdbc驱动到sqoop的lib目录下。

[linux@hadoop102 software]$ cp mysql-connector-java-5.1.48.jar /opt/module/sqoop/lib/

4 验证Sqoop
我们可以通过某一个command来验证sqoop配置是否正确：

[linux@hadoop102 sqoop]$ bin/sqoop help

出现一些Warning警告（警告信息已省略），并伴随着帮助命令的输出：

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table     Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables     Import tables from a database to HDFS
  import-mainframe    Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases        List available databases on a server
  list-tables           List available tables in a database
  merge              Merge results of incremental imports
  metastore           Run a standalone Sqoop metastore
  version            Display version information

5 测试Sqoop是否能够成功连接数据库

[linux@hadoop102 sqoop]$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password root

出现如下输出：

information_schema
metastore
mysql
oozie
performance_schema

import

import常用参数

常用参数

–connect 指定mysql的url
–password 指定mysql登陆的密码
–username 指定mysql登陆的账号

导入数据到HDFS配置

–append 指定是否将数据追加到指定位置
–as-textfile 指定数据保存到hdfs的时候以text文件格式保存
–as-parquetfile 指定数据保存到hdfs的时候以parquet文件格式保存
–columns 指定导mysql哪些列的数据
–delete-target-dir 指定是否删除目标路径
–fetch-size 指定每个批次从mysql拉取多少数据
–num-mappers[简写-m] 指定maptask的个数
–query[简写-e] 通过sql语句指定从mysql导出哪些数据
–split-by 指定按照哪个字段进行数据分配[数据分配到哪个mapper]
–table 指定导出mysql哪个表的数据
–target-dir 指定数据保存到HDFS哪个路径
–where 指定导出mysql数据的条件 “age>10 and age<20”
–compress[简写-z] 指定数据保存到HDFS的时候是否压缩
–compression-codec 指定压缩格式
–null-string 指定mysql字符串列为null值的时候保存到HDFS以哪种形式保存
–null-non-string 指定mysql非字符串列为null值的时候保存到HDFS以哪种形式保存

增量导入

–check-column 指定通过哪个字段识别数据是增量数据
–incremental [append<只导入新增数据>/lastmodified<既导入新增，又导入修改的数据>]
–last-value 指定check-column上次导入的最大值，后续表中大于该值的数据就是增量数据
–fields-terminated-by 指定数据保存到HDFS的时候字段之间的分隔符是什么
–line-terminated-by 指定数据保存到HDFS的时候行之间的分隔符是什么

数据导入hive表配置

–hive-import 指定将数据导入hive表中
–hive-overwrite 指定数据导入hive表的时候是否以覆盖的形式导入
–create-hive-table 指定数据导入hive表的时候，如果hive不存在自动创建（存在就不要写这句不然会报错）
–hive-table 指定导入的hive的表名
–hive-partition-key 指定数据导入hive分区表的时候，分区字段的名称
–hive-partition-value 指定数据导入hive分区表的时候，分区字段的值

将数据导入到HDFS的示例

#! /bin/bash
APP=gmall
sqoop=/opt/module/sqoop/bin/sqoop

if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d '-1 day' +%F`
fi

import_data(){
$sqoop import \
--connect jdbc:mysql://hadoop102:3306/$APP \
--username root \
--password root \
--target-dir /origin_data/$APP/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-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/$APP/db/$1/$do_date
}

不同的表按照不同的同步策略进行同步

新增及变化策略
import_order_info(){
  import_data order_info "select *     
                        from order_info
                        where (date_format(create_time,'%Y-%m-%d')='$do_date' 
                        or date_format(operate_time,'%Y-%m-%d')='$do_date')"
}
新增策略
import_order_status_log(){
  import_data order_status_log "select *
                                from order_status_log
                                where date_format(operate_time,'%Y-%m-%d')='$do_date'"
}
全量同步策略
import_sku_info(){
  import_data sku_info "select *
                        from sku_info where 1=1"
}
特殊策略
第一次全量同步，后续不需要同步
1）客观世界维度
没变化的客观世界的维度（比如性别，地区，民族，政治成分，鞋子尺码）可以只存一份固定值。
2）日期维度
日期维度可以一次性导入一年或若干年的数据。
import_base_region(){
  import_data base_region "select id,region_name
                            from base_region
                            where 1=1"
}

case $1 in
"base_dic")
      import_base_dic
;;
"activity_rule")
      import_activity_rule
;;
......
"first")
   import_base_category1
   import_order_detail
   import_payment_info
   .........
;;
"all")
   import_base_category1
   import_base_trademark
   import_coupon_info
   .........
;;
esac

将数据导入Hive的示例

#! /bin/bash

source /etc/profile

#数据库配置信息
mysql_tabname="$1"
mysql_ip="127.0.0.1:3306"
mysql_database="rtm_data"
mysql_connect="jdbc:mysql://${mysql_ip}/${mysql_database}"
mysql_username="root"
mysql_pwd="root"
ext_tabname="stg_s16_${mysql_tabname}"

sql="select *,
    "s16" as data_source, 
     current_timestamp  as etl_created_time,
     current_timestamp as etl_modified_time
from ${mysql_tabname} 
where 1=1 and \$CONDITIONS";

sqoop import \
 --connect  "$mysql_connect" \
 --username "$mysql_username" \
 --password "$mysql_pwd" \
 --query "${sql}" \
 --fields-terminated-by '\t' \
 --hive-drop-import-delims \
 --delete-target-dir \
 --num-mappers 1 \
 --hive-import \
 --hive-database stagedb \
 --hive-table "$ext_tabname" \
 --target-dir user/etl/stagedb/"$ext_tabname"

export

export常用参数
–connect 指定mysql的url
–password 指定mysql登陆的密码
–username 指定mysql登陆的账号
–columns 指定数据导入到mysql哪些列中
–num-mappers[简写-m] 指定mapper个数
–table 指定数据导入到mysql哪个表
–update-key 指定HDFS数据与mysql数据通过哪个列判断是同一条数据
–update-mode updateonly[如果HDFS与mysql存在相同数据，那么导入的时候更新mysql的数据]/allowinsert[如果存在相同的数据，则更新，如果不存在，则插入]
–export-dir 指定HDFS数据路径
–input-null-string 指定HDFS字符串列的数据如果是null值，保存到MySQL的时候以一个什么形式存储
–input-null-non-string 指定HDFS非字符串列的数据如果是null值，保存到MySQL的时候以一个什么形式存储
–input-fields-terminated-by 指定HDFS中数据字段之间的分隔符
–input-lines-terminated-by 指定HDFS数据之间行的分隔符

将数据从Hive导出到Mysql的示例

#!/bin/bash

hive_db_name=gmall
mysql_db_name=gmall_report

export_data() {
/opt/module/sqoop/bin/sqoop export \
-Dmapreduce.job.queuename=hive \
--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_user_topic")
     export_data "ads_user_topic" "dt"
;;
  "ads_area_topic")
     export_data "ads_area_topic" "dt,iso_code"
;;
   "all")
     export_data "ads_user_topic" "dt"
     export_data "ads_area_topic" "dt,iso_code"
     #其余表省略未写
;;
esac

关于导出update还是insert的问题

–update-mode：
updateonly 只更新，无法插入新数据
allowinsert 允许新增

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

–input-null-string和–input-null-non-string：分别表示，将字符串列和非字符串列的空串和“null”转义。

Sqoop常见问题

1.Sqoop导入导出null存储一致性问题

Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性。在导出数据时采用–input-null-string和–input-null-non-string两个参数。导入数据时采用–null-string和–null-non-string。

2.Sqoop数据导出一致性问题
如Sqoop在导出到Mysql时，使用4个Map任务，过程中有2个任务失败，那此时MySQL中存储了另外两个Map任务导入的数据，此时老板正好看到了这个报表数据。而开发工程师发现任务失败后，会调试问题并最终将全部数据正确的导入MySQL，那后面老板再次看报表数据，发现本次看到的数据与之前的不一致，这在生产环境是不允许的。
官网：http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html
–staging-table方式

sqoop export 
--connect jdbc:mysql://192.168.137.10:3306/user_behavior 
--username root 
--password 123456 
--table app_cource_study_report 
--columns watch_video_cnt,complete_video_cnt,dt 
--fields-terminated-by "\t" 
--export-dir "/user/hive/warehouse/tmp.db/app_cource_study_analysis_${day}" --staging-table app_cource_study_report_tmp 
--clear-staging-table 
--input-null-string '\N'

3.sqoop底层运行的任务是什么？

只有Map阶段，没有reduce阶段的任务。默认是用的4个Maptask

4.Sqoop在导入数据的时候数据倾斜

1.Num-mapper:启动n个maptask来并行导入数据，默认4个，将maptask数量修改为1
2.Sqoop可以通过导入数据时有一个参数 ：
–query 导入部分数据 （特例：where 1=1 就是全量数据 ） 后面必须加 and $CONDITIONS
其实它的作用是数据分割条件的占位符，也就是说最终数据查询语句中的 $CONDITIONS 会被split-by>=501 and split-by<=1000 这样的分割条件替换。
–table 直接全量数据

按照自增主键来切分表的工作单元，也可以通过rownamber生成一个均匀分布的字段，再根据参数split-by 来切分这个字段

5.sqoop数据导出Parquet

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

1.创建临时表，把Parquet中表数据导入到临时表，把临时表导出到目标表用于可视化
2.ads层建表的时候就不要建Parquet表