Sqoop简介
1. 背景
1.1 概览
- 在大数据处理业务框架中,需要处理的数据一般是2个来源(行为日志和业务数据),有些公司如果有python部门,就需要加上python数据这一个。
- python数据因为各个公司以及各个页面和接口中数据差异较大,一般需要针对公司和特定业务编写程序进行解析,然后存入HDFS或者其他分布式文件系统中。一般都是编写Spark程序(可以分布式进行处理,效率对比java程序要更高。当然,也可以使用java编写mapreduce程序)。
- 行为日志数据,此前已经说过,一般都是使用flume等分布式日志采集框架进行采集,因为这些框架比较成熟,可以直接进行一些预处理,然后存入HDFS或者其他地方。
- 业务数据一般都是存放在关系型数据库如mysql或者oracle,或者windows server中。没错,后2个都是需要花钱的,特别是oracle,一般公司用不起。这时候将数据从关系型数据库导出到大数据文件存放系统中,或者反过来,将数据从大数据文件存放系统中导出到关系型数据库中,目前主要还是使用sqoop,当然也可以是用datax以及其他相同作用框架。
- 不管是流量域数据(行为日志数据),还是业务域数据(存储在关系型数据库中业务数据),都是按照经典数仓分层进行处理,存储,以便于计算和分析,最后做展示。
- 经典数仓分层,一般是DIM维度层,再之上就是ODS贴源层,然后是DW层(一般划分为DWD数据明细层,DWS数据服务层),然后是ADS应用层。而不管是流量域还是业务域的数据,都是需要按照这个分层进行数据预处理,提取,处理,存储到这些层级的。
1.2 官网
- https://sqoop.apache.org/
- 注意,这里区分正常sqoop和sqoop2,但是后sqoop2还不稳定,不稳定,所以不要用于生产,而且还不和之前版本兼容。
2. 安装
- sqoop本质是运行一个mapreduce程序,所以要运行sqoop,先启动hadoop(hdfs,yarn)。因为一般数据都是存放到hdfs中,mapreduce程序一般都是运行在yarn集群中。
- 下载官方压缩包1.4.7版本,然后解压缩。
# x是解压缩
tar -zxvf ...
# z是压缩
tar -zcvf
- 注意,linux操作系统中,一般第三方软件都是安装在opt或者usr目录下,这里是选择在opt目录下新建一个apps目录,用来专门安装第三方程序的。
- 命令和linux的jar命令类似
- 打开sqoop安装目录下的conf目录,编辑sqoop-env.sh
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/opt/apps/hadoop-3.1.1
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/opt/apps/hadoop-3.1.1/
#set the path to where bin/hbase is available
#export HBASE_HOME=
#Set the path to where bin/hive is available
export HIVE_HOME=/opt/apps/hive-3.1.2/
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
#Set the path for where zookeper config dir is
#export ZOOCFGDIR=
这里需要按照自己目的,进行参数设置
HADOOP_COMMON_HOME和HADOOP_MAPRED_HOME都是设置的hadoop的home,也就是安装路径
如果需要使用到hbase,需要设置一下habse的安装路径
使用hive,需要设置参数较多,除了hive的安装路径,还有hive的conf目录路径,以及hadoop拼接hive的class path路径,这样sqoop可以去hadoop以及hive的lib目录下查找第三方依赖jar包。
注意,sqoop本身也会在自己的lib目录下查找第三方依赖jar包。这种根据配置的安装目录以及lib目录路径查找依赖第三方jar包是linux下框架常见的查找机制
如果使用到了zookeeper,还可以设置zookeeper相关配置文件。
- 防止一个mysql的jdbc驱动包到sqoop的lib目录下。根据上述jar包查找机制,这个mysql驱动jar包放在hive或者hadoop的lib目录下,也是可以的。
如果遇到错误,提示找不到hive.HiveConf…
- 直接将hive安装目录中的lib中的hive-common-2.3.5.jar 拷贝到sqoop的lib中,然后测试,如果通过,则表明修复好了。
- 上述步骤如果失败,则继续处理
<1. 按正常流程先安装sqoop
解压,修改sqoop-env.sh
export HADOOP_COMMON_HOME=/opt/apps/hadoop2
export HADOOP_MAPRED_HOME=/opt/apps/hadoop2
export HIVE_HOME=/opt/apps/hive2
<2. 要在/root/.bash_profile中添加一句话:
export HIVE_HOME=/opt/apps/hive2
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
< 3. 要在jdk的权限安全配置中添加如下配置:
vi /opt/apps/jdk/jre/lib/security/java.policy
在最后添加:
grant{
permission javax.management.MBeanTrustPermission “register”;
};
<4. 替换版本冲突的jar包
把sqoop的lib中的所有jackson-.jar重命名 jackson-.jar.bak
然后将hive中的lib中的所有jackson-*.jar 拷贝到sqoop的lib中
<5. 把hive的hive-site.xml拷贝到sqoop的conf目录中
- 测试,cd到sqoop安装目录下
- 查看数据库信息
bin/sqoop list-databases \
--connect jdbc:mysql://doit01:3306 \
--username root \
--password ABC123abc.123
- 查看数据库中表信息
bin/sqoop list-tables \
--connect jdbc:mysql://doit01:3306/realtimedw \
--username root \
--password ABC123abc.123
3. 使用
- 使用说明
# Table 3. Import control arguments:
# Argument Description
# --append Append data to an existing dataset in HDFS
# --as-avrodatafile Imports data to Avro Data Files
# --as-sequencefile Imports data to SequenceFiles
# --as-textfile Imports data as plain text (default)
# --as-parquetfile Imports data to Parquet Files
# --boundary-query <statement> Boundary query to use for creating splits
# --columns <col,col,col…> Columns to import from table
# --delete-target-dir Delete the import target directory if it exists
# --direct Use direct connector if exists for the database
# --fetch-size <n> Number of entries to read from database at once.
# --inline-lob-limit <n> Set the maximum size for an inline LOB
# -m,--num-mappers <n> Use n map tasks to import in parallel
# -e,--query <statement> Import the results of statement.
# --split-by <column-name> Column of the table used to split work units. Cannot be used with --autoreset-to-one-mapper option.
# --split-limit <n> Upper Limit for each split size. This only applies to Integer and Date columns. For date or timestamp fields it is calculated in seconds.
# --autoreset-to-one-mapper Import should use one mapper if a table has no primary key and no split-by column is provided. Cannot be used with --split-by <col> option.
# --table <table-name> Table to read
# --target-dir <dir> HDFS destination dir
# --temporary-rootdir <dir> HDFS directory for temporary files created during import (overrides default "_sqoop")
# --warehouse-dir <dir> HDFS parent for table destination
# --where <where clause> WHERE clause to use during import
# -z,--compress Enable compression
# --compression-codec <c> Use Hadoop codec (default gzip)
# --null-string <null-string> The string to be written for a null value for string columns
# --null-non-string <null-string> The string to be written for a null value for non-string columns
3.1 全量导入
3.1.1 从mysql中导出数据到hdfs
- 说明
并行度的问题补充:
一个maptask从mysql中获取数据的速度约为4-5m/s,而mysql服务器的吞吐量40-50M/s
那么,在mysql中的数据量很大的场景下,可以考虑增加maptask的并行度来提高数据迁移速度
-m就是用来指定maptask的并行度
maptask一旦有多个,这时候指定一个split key一般是表的id,然后根据id进行划分。划分前一般会执行sql查询一下id最大值,最小值,方便做数据分割。
- 准备
确保sqoop把目标路径视作hdfs中的路径,需如下参数配置正确
core-site.xml
<property>
<name>fs.defaultFS</name>
<value>hdfs://doit01:8020/</value>
</property>
需要将mr程序运行在yarn上,则需要确保mapred-site.xml上有如下配置
<property>
<name>mapreduce.framework.name</name>
<value>yarn</value>
</property>
- 导出sqoop命令演示
bin/sqoop import \
--connect jdbc:mysql://doit01:3306/realtimedw \
--username root \
--password ABC123abc.123 \
--table dim_pginfo \
--target-dir /sqoopdata/dim_pginfo2 \
--fields-terminated-by '\001' \
--delete-target-dir \
--compress \
--compression-codec gzip \
--split-by id \
-m 2
参数说明
import 说明是一个数据导入到hdfs的过程
\ 斜杠是多行shell命令换行符号
–connect jdbc:mysql://doit01:3306/realtimedw 这是mysql连接协议及其地址,注意包含了数据库名字
–username root
–password ABC123abc.123
这个分别是mysql的账号和密码
–table dim_pginfo 这是数据库的表名
–target-dir /sqoopdata/dim_pginfo2 这是hdfs上对应存放数据目录路径‘
–fields-terminated-by ‘\001’ 这是文件以什么分割符号切割,这里一般都是以不可见不可打印字符分割,因为可以打印可以显式的符号很容易出现在mysql字段的字符串中,引发后续结构化文件读取处理时那一有效正确切割的问题。
–delete-target-dir 这个是大家写mr程序时经常会遇到的输出文件已存在,就会报异常。这个参数是,如果目标目录已存在,则删除掉旧的目录。注意,一定一定检查,是否有必要删除旧的目录
–compress
–compression-codec gzip
这是指定是否压缩,以及压缩文件格式。可以使用多种压缩文件格式
–split-by id 这是指定,mysql文件中,使用哪个字段进行文件划分,因为mapreduce是会并行执行,这时候需要指定每个并行任务根据哪个字段对文件做切割划分
-m 2 这是maptask的数量设置,这里设置是2.
可以指定生产的文件类型
–as-avrodatafile
–as-parquetfile
–as-sequencefile
–as-textfile
空值处理
import处理,也就是数据从mysql中导出。mysql中的null值,写入hdfs文件时,用什么符号来代替(默认是用的"null")
–null-non-string ‘\N’
–null-string ‘\N’
export处理,hdfs中的文件,什么样的符号应该以null值形式写入mysql
–input-null-non-string ‘\N’
–input-null-string ‘\N’
非id键对数据做切割,可以使用如下参数
-Dorg.apache.sqoop.splitter.allow_text_splitter=true
–split-by name
sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password root \
--table noid \
--target-dir /sqooptest3 \
--fields-terminated-by ',' \
--split-by name \
-m 2
3.1.2 从mysql导入到hive中
注意,对于sqoop来说,import还是export,sqoop是站在大数据处理框架一侧,所以从mysql导出数据,就是import
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password 211123 \
--table doit_jw_stu_base \
--hive-import \
--hive-table doit_jw_stu.base \
--delete-target-dir \
--as-textfile \
--fields-terminated-by ',' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--split-by stu_id \
-m 2
先将数据从mysql导入hdfs,然后利用hive的元数据操作的jar包,去hive的元数据库中生成相应的元数据,并将数据文件导入hive表目录。
所以实际上就是2步,先将数据导出为文件,然后使用hive的功能,将这个文件load data方式导入到hive表中。
参数说明
- –connect jdbc:mysql://h3:3306/ry
–username root
–password 211123 \ 还是一样,mysql的链接地址,数据库名字,账号和密码- –table doit_jw_stu_base \ 这是mysql中表的名字
- –hive-import 表名导入到hive表中
- –hive-table doit_jw_stu.base \ 这是表明导入到hive的doit_jw_stu数据库的base表中
- –delete-target-dir \ 还是一样的,如果hive存放数据的hdfs文件夹存在,就删除对应文件。(hive的元数据存在数据库中,数据存在hdfs中)
- –as-textfile \ 这是存放的文件类型
- –fields-terminated-by ‘,’ \ 字段切割方式
- –compress
–compression-codec gzip \ 这是压缩以及压缩方式- –null-string ‘\N’
–null-non-string ‘\N’ \ 这是指定,如果遇到了mysql中的null,在hdfs文件中以什么符号标识,否则hive解析hdfs中文件时,会无法识别是否是mysql中的null- –hive-overwrite \ 这是覆盖的方式
- –split-by stu_id \ 以什么字段对mysql文件做切分,方便mapreduce程序并行读取处理
- -m 2 maptask的数量
–hive-database xdb 也可以单独指定hive的数据库名字
3.1.3 条件导入
- where条件导入
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password 211123 \
--table doit_jw_stu_base \
--hive-import \
--hive-table yiee_dw.doit_jw_stu_base2 \
--delete-target-dir \
--as-textfile \
--fields-terminated-by ',' \
--compress \
--compression-codec gzip \
--split-by stu_id \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--where "stu_age>25" \
-m 2
–where “stu_age>25” 这就是编写一个sql的where限定条件一样
- –columns条件导入
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password haitao.211123 \
--table doit_jw_stu_base \
--hive-import \
--hive-table yiee_dw.doit_jw_stu_base3 \
--delete-target-dir \
--as-textfile \
--fields-terminated-by ',' \
--compress \
--compression-codec gzip \
--split-by stu_id \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--where "stu_age>25" \
--columns "stu_id,stu_name,stu_phone" \
-m 2
这就是限定数据表中哪几个字段需要导入一样
- –query查询语句导入
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password haitao.211123 \
--hive-import \
--hive-table yiee_dw.doit_jw_stu_base4 \
--as-textfile \
--fields-terminated-by ',' \
--compress \
--compression-codec gzip \
--split-by stu_id \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--query 'select stu_id,stu_name,stu_age,stu_term from doit_jw_stu_base where stu_createtime>"2019-09-24 23:59:59" and stu_