1:业务系统
业务域的数据来自业务系统的数据库
通过sqoop(或datax)抽取到数仓的ods层
在ods层对有需要的表进行增量合并,字段选择,反范式话,形成dwd明细层表
在明细层基础上,进行各类主题的数据统计、分析
课程中,主要分析的主题有:
- 交易域分析
- 营销域分析
- 会员域分析
2:sqoop
1:sqoop是apache旗下的工具主要用于服务器之间传送数据的工具
主要核心功能为
- 导入
- 导出
导入:从mysql数据库中导入到hdfs
导出:从 Hadoop 的文件系统中导出数据到关系数据库如hive
底层工作机制
将导入或者导出命令使用MapReduce程序实现,在翻译出的 MapReduce 中主要是对InputFormat 和 OutputFormat 进行定制
3:问题
在业务系统数据库中的订单表
每条都会显示订单如
但是这样聚合金额就会出错,因为未支付的金额和已支付的金额是同一个用户和商品.
全量快照表:全量表和增量表的合并,保留用户的历史状态
但是这样会导致数据的冗余,每天都会快照.每天的增量数据很大,又不能删除用户状态.创建拉链表,就可以删除7天啊30天啊的用户信息,拉链表里面有数据
增量:每天从业务系统收集用户的状态(如:支付,加入购物车,)到数仓
为什么每条要增量,而不是将业务表的数据全部拉倒全量表.
因为业务系统的表很大,可能几百几十个G产生的io太大.而且每天都拉去,什么公司,产磁盘的吗?,那么使用每条的增量就可以拉去每条的业务表更新的数据,数据小
增量一个状态存一条,数据重复,不利于数仓分析,无法保证某条数据在某天改变
所有这样也不好
拉链表
:
####################################
#
# @Part_0 : sqoop安装
#
####################################
vi 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=
####################################
#
# @Part_1 : 测试命令
#
####################################
## 测试命令:列出mysql中所有的库、表
bin/sqoop list-databases \
--connect jdbc:mysql://doitedu01:3306 \
--username root \
--password ABC123abc.123
bin/sqoop list-tables \
--connect jdbc:mysql://doitedu01:3306/realtimedw \
--username root \
--password ABC123abc.123
######## 附录: 数据导入参数大全 #######################
# 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
############################################################
####################################
#
# @Part_2 : 全量导入
#
####################################
## 1.从mysql将导入数据到HDFS
## 并行度的问题补充:一个maptask从mysql中获取数据的速度约为4-5m/s,而mysql服务器的吞吐量40-50M/s
## 那么,在mysql中的数据量很大的场景下,可以考虑增加maptask的并行度来提高数据迁移速度
## -m就是用来指定maptask的并行度
## 思考:maptask一旦有多个,那么它是怎么划分处理任务?
## 确保sqoop把目标路径视作hdfs中的路径,需如下参数配置正确:
# core-site.xml
# <property>
# <name>fs.defaultFS</name>
# <value>hdfs://doitedu01:8020/</value>
# </property>
## 确保sqoop把mr任务提交到yarn上运行,需如下参数配置正确:
# mapred-site.xml
# <property>
# <name>mapreduce.framework.name</name>
# <value>yarn</value>
# </property>
bin/sqoop import \
--connect jdbc:mysql://doitedu01:3306/realtimedw \
--username root \
--password ABC123abc.123 \
--table dim_pginfo \
--target-dir /sqoopdata/dim_pginfo \
--fields-terminated-by '\001' \
--delete-target-dir \
--compress \
--compression-codec gzip \
--split-by id \
-m 2
# 可以指定要生成的文件的类型
--as-avrodatafile
--as-parquetfile
--as-sequencefile
--as-textfile
## 如果需要压缩
--compress
--compression-codec gzip
## 空值处理
# import方向:mysql中的null值,写入hdfs文件时,用什么符号来代替(默认是用的"null")
--null-non-string '\\N'
--null-string '\\N'
# export方向:hdfs中的文件,什么样的符号应该以null值形式写入mysql
--input-null-non-string <null-str>
--input-null-string <null-str>
## 如果没有数字主键,也可以使用文本列来作为切分task的参照,但是需要增加一个-D参数,如下
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
## 导入mysql数据到hive
## 它的实质: 是先将数据从mysql导入hdfs,然后利用hive的元数据操作jar包,去hive的元数据库中生成相应的元数据,并将数据文件导入hive表目录
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_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
# --hive-database xdb
####################################
#
# @Part_3 : 条件导入
#
####################################
## 条件导入: --where
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_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
## 条件导入: --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
# 有了--query,就不要有--table了,也不要有--where了,也不要有--columns了
## query自由查询导入时,sql语句中必须带 $CONDITIONS条件 : where $CONDITIONS ,要么 where id>20 and $CONDITIONS
## 为什么呢?因为sqoop要将你的sql语句交给多个不同的maptask执行,每个maptask执行sql时肯定要按任务规划加范围条件,
## 所以就提供了这个$CONDITIONS作为将来拼接条件的占位符
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_sex="1" and $CONDITIONS' \
--target-dir '/user/root/tmp' \
-m 2
## --query可以支持复杂查询(包含join、子查询、分组查询)但是,一定要去深入思考你的sql的预期运算逻辑和maptask并行分任务的事实!
# --query "select id,member_id,order_sn,receiver_province from doit_mall.oms_order where id>20 and \$CONDITIONS"
# --query 'select id,member_id,order_sn,receiver_province from doit_mall.oms_order where id>20 and $CONDITIONS'
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password haitao.211123 \
--hive-import \
--hive-table yiee_dw.doit_jw_stu_base6 \
--as-textfile \
--fields-terminated-by ',' \
--compress \
--compression-codec gzip \
--split-by id \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--query 'select b.id,a.stu_id,a.stu_name,a.stu_phone,a.stu_sex,b.stu_zsroom from doit_jw_stu_base a join doit_jw_stu_zsgl b on a.stu_id=b.stu_id where $CONDITIONS' \
--target-dir '/user/root/tmp' \
-m 2
#################我的mysql导入hive
sqoop import \
--connect jdbc:mysql://doitedu01:3306/realtimedw \
--username root \
--password ABC123abc.123 \
--hive-import \
--hive-table test.user_info2 \
--as-textfile \
--fields-terminated-by ',' \
--split-by id \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-overwrite \
--query 'select id,account,gender,province,city from user_info where age is not null and $CONDITIONS' \
--target-dir '/sqoopdata2' \ #随便写一个目录
-m 2
####################################
#
# @Part_4 : 增量导入
#
####################################
## --增量导入 1 --根据一个递增字段来界定增量数据(前提:业务表中的数据一旦生成,就不会再修改)
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password haitao.211123 \
--table doit_jw_stu_zsgl \
--hive-import \
--hive-table yiee_dw.doit_jw_stu_zsgl \
--split-by id \
--incremental append \
--check-column id \
--last-value 40 \
-m 2
## --增量导入 2 --根据修改时间来界定增量数据, (前提:业务表中必须有一个时间字段,且该字段会跟随数据的修改而修改)
## lastmodified 模式下的增量导入,不支持hive导入
sqoop import \
--connect jdbc:mysql://h3:3306/ry \
--username root \
--password haitao.211123 \
--table doit_jw_stu_zsgl \
--target-dir '/sqoopdata/doit_jw_stu_zsgl' \
--incremental lastmodified \
--check-column update_time \
--last-value '2020-03-18 23:59:59' \
--fields-terminated-by ',' \
-m 1 \
# 导入后的数据如果需要跟存量进行合并,则可以附加此参数
--merge-key id \ #导入的增量数据不会简单地追加到目标存储,还会将新旧数据进行合并
## 合并新老数据
bin/sqoop codegen \
--connect jdbc:mysql://impala01:3306/sqooptest \
--username root \
--password ABC123abc.123 \
--table stu \
--bindir /opt/apps/code/stu \
--class-name Stu \
--fields-terminated-by ","
bin/sqoop merge \
--new-data /sqoopdata/stu1 \
--onto /sqoopdata/stu0 \
--target-dir /sqoopdata/stu_all \
--jar-file /opt/apps/code/stu/Stu.jar \
--class-name Stu \
--merge-key id
####################################
#
# @Part_5 : 导出数据
#
####################################
## sqoop导出数据
sqoop export \
--connect jdbc:mysql://h3:3306/dicts \
--username root \
--password haitao.211123 \
--table dau_t \
--input-fields-terminated-by ',' \
--export-dir '/user/hive/warehouse/dau_t' \
--batch # 以batch模式去执行sql
## 控制新旧数据导到mysql时,选择更新模式
sqoop export \
--connect jdbc:mysql://h3:3306/doit_mall \
--username root \
--password root \
--table person \
--export-dir '/export3/' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--update-mode allowinsert \
--update-key id \
--batch
# --update-mode 如果选择updateonly,只会对mysql中已存在的id数据进行更新,不存在的id数据不会插入了
# --update-mode 如果选择allowinsert,既会更新已存在id数据,也会插入新的id数据
## 附录:export控制参数列表
Table 29. Export control arguments:
Argument Description
--columns <col,col,col…> Columns to export to table
--direct Use direct export fast path
--export-dir <dir> HDFS source path for the export
-m,--num-mappers <n> Use n map tasks to export in parallel
--table <table-name> Table to populate
--call <stored-proc-name> Stored Procedure to call
--update-key <col-name> Anchor column to use for updates. Use a comma separated list of columns if there are more than one column.
--update-mode <mode> Specify how updates are performed when new rows are found with non-matching keys in database.
Legal values for mode include updateonly (default) and allowinsert.
--input-null-string <null-string> The string to be interpreted as null for string columns
--input-null-non-string <null-string> The string to be interpreted as null for non-string columns
--staging-table <staging-table-name> The table in which data will be staged before being inserted into the destination table.
--clear-staging-table Indicates that any data present in the staging table can be deleted.
--batch Use batch mode for underlying statement execution.
## 附录:
-- mysql修改库、表编码
修改库的编码:
mysql> alter database db_name character set utf8;
修改表的编码:
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;