Sqoop import -之MySQL到HDFS(LZO)的脚本编写与简单实践

sqoop-之MySQL到HDFS(LZO)的脚本编写与简单实践

1 连接数据库

#sqoop help
#sqoop list-databases --help
sqoop list-databases --connect jdbc:mysql://shufang102:3306/ --username root --password 888888

2 sqoop import

sqoop import指令支持将RDBMS中的数据分别以以下形式导入到HADOOP

• 导入到HDFS路径（比较常用的方式，让数据更容易被管理）
• 导入到Hive的表中
• 导入到Hbase的表中

#\是换行符，前面必须有空格，后面不能有空格，这是linux的解析规则

#方式1
sqoop import \
--connect jdbc:mysql://shufang102:3306/hello  \
--username root  \
--password 888888  \
--table emp_info  \
--columns id,name  \
--where "id >= 10 and id <= 3000"  \
--target-dir /emp_info  \
--delete-target-dir \
--num-mappers 2 \
--fields-terminated-by '\t' \
--split-by id
#是否开启压缩
--compress \
#压缩编码格式为lzop（我们平常说的lzo）
--compression-codec lzop \
#将MySQL中的不同类型的null与hive中的null作兼容匹配，hive中存储的是\N
--null-string '\\N'  \
--null-non-string '\\N' 

# 方式2
sqoop import \
--connect jdbc:mysql://shufang102:3306/hello  \
--username root  \
--password 888888  \
--query "select id,name from emp_info where id >= 10 and id <= 3000 and \$CONDITIONS " \
--target-dir /emp_info  \
--delete-target-dir \
--num-mappers 2 \
--fields-terminated-by '\t' \
--split-by id
#是否开启压缩
--compress \
#压缩编码格式为lzop（我们平常说的lzo）
--compression-codec lzop \
#将MySQL中的不同类型的null与hive中的null作兼容匹配，hive中存储的是\N
--null-string '\\N'  \
--null-non-string '\\N' 

• –delete-target-dir 如果输出路径存在删掉路径，防止报错
• –num-mappers ,指定mapper的并行度,最终产生2个文件
• –fields-terminated-by 指定文件中数据存储的结构
• –split-by 按照不同的键进行数据切分
• –query 必须包含$CONDITIONS

2.1 sqoop数据同步策略

1. 全量同步

   • 每天将MySQL中该表的全量数据导入到Hive中，按照分区进行存储，每个分区中都是当天的时间切片（该表必须不能太大）。

2. 增量同步

   • 增量同步只适合MySQL中只存在新增（如支付流水表、订单状态流水表），不存在删除与修改的表，在Hive中以分区的形式进行同步和存储，这样的表在hive中叫增量表。

3. 新增及变化同步（与全量同步场景唯一的区别是数据量很大，为了减少冗余）

   • 不能使用简单的分区
   • 一般在业务那边维护2个时间字段，一个是订单创建时间create_time，一个是订单修改时间operator_time。
   • 实现要么通过在业务表中增加create_time、operator_time,要么使用工具如canal进行binlog的解析。

4. 特殊情况

   • 一般用于码表、地区表等基本不变化（没变化的客观的维度）的表，只需同步一次。
   • 缓慢渐变维可以使用拉链表。
   • 时间维度表等(描述时间的客观表，如该日期是一年中的第几天，一个星期的第几天，一个月的第几天…,类似于给时间打上标签)，一次导入一年或者若干年的数据。

2.2 具体的import脚本编写案例

#!/bin/bash 
#shell脚本最终需要传入2个参数：1  具体业务名称，2 时间（）
#1、声明可用变量
bussiness=shufang
sqoop=/opt/module/sqoop-1.4.7/bin/sqoop
lzojar=/opt/module/hadoop-2.7.7/share/hadoop/common/hadoop-lzo-0.4.21-SNAPSHOT.jar
main_class=com.hadoop.compression.lzo.DistributedLzoIndexer


#非空校验日期，就是看你外面有没有传入值
if [ -n "$2" ] ;then 
    do_date=`date -d $2 +'%F'`
else 
    do_date=`date -d '-1 day' +'%F'`
fi 

table_name=$1
target_dir=/origin_data/db/$bussiness/$table_name/$do_date

###############################以上都是参数准备###############################
echo "do_date = $do_date"
echo "target_dir = $target_dir"

#声明公共导数据函数，这个函数需要传一个参数，这个参数是一个SQL语句
import_data(){
"$sqoop" import \
--connect jdbc:mysql://shufang102:3306/$bussiness  \
--username root  \
--password 888888  \
--query "$1 AND \$CONDITIONS " \
--target-dir $target_dir \
--delete-target-dir \
--fields-terminated-by '\t' \
--num-mappers 1 \
--split-by id \
#是否开启压缩
--compress \
#压缩编码格式为lzop（我们平常说的lzo）
--compression-codec lzop \
#将MySQL中的不同类型的null与hive中的null作兼容匹配，hive中存储的是\N
--null-string '\\N'  \
--null-non-string '\\N'  

# 给lzop文件创建索引
#/user/hive/warehouse/shufang.db
hadoop jar $lzojar  $main_class  $target_dir
}


#开始根据不同的表对公共函数进行封装,函数中$1和$2只是函数内部的临时参数
import_students(){
    import_data "
        select 
        id,
        name,
        create_time
        from $table_name
        where create_time = date_format('$do_date','%Y-%m-%d')
    "
}

case $1 in 
    "students")
    import_students
    ;;
esac 

NOTE:由于shell是从上往下执行的，如果表之间没有依赖关系，那么我们可以将上面的脚本拆分成多个shell脚本交给调度系统去执行，提高并行度，如果我们需要将并行度提高为10，那么我们就拆分成10个文件，以此类推。

2.3 具体简单操作流程

• 在MySQL中创建对应的数据库和表

CREATE DATABASE shufang;

USE shufang;

CREATE TABLE `students` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `create_time` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

INSERT INTO students ...
+------+----------+-------------+
| id   | name     | create_time |
+------+----------+-------------+
| 1001 | zhangsan | 2021-01-18  |
| 1002 | lisi     | 2021-01-18  |
| 1003 | wangwu   | 2021-01-19  |
| 1004 | zhaoliu  | 2021-01-19  |
| 1005 | tianqi   | 2021-01-20  |
| 1006 | wangba   | 2021-01-20  |
+------+----------+-------------+

• vim import_students.sh,脚本内容如2.2
• 执行脚本并传入参数

bash import_students.sh  students 20210118

• 检验是否导入成功并且存储为LZO格式，创建切片索引，如下图