仿qq网站程序,网站如何做参考文献,wordpress动图打开很慢,html拖拽代码生成器拉链表的设计与实现
数据同步问题
背景
例如#xff1a;MySQL中有一张用户表: tb_user#xff0c;每个用户注册完成以后#xff0c;就会在用户表中新增该用户的信息#xff0c;记录该用户的id、手机号码、用户名、性别、地址等信息。 每天都会有用户注册#xff0c;产生…拉链表的设计与实现
数据同步问题
背景
例如MySQL中有一张用户表: tb_user每个用户注册完成以后就会在用户表中新增该用户的信息记录该用户的id、手机号码、用户名、性别、地址等信息。 每天都会有用户注册产生新的用户信息每天都需要将MySQL中的用户数据同步到Hive数据仓库中需要对用户的信息做统计分析例如统计新增用户的个数、用户性别分布地区分布、运营商分布等指标
如果已经同步的数据发生变化怎么办
2021-01-02MySQL中新增2条用户注册数据并且有1条用户数据发生更新 新增两条用户数据011和012008的addr发生了更新从gz更新为sh 2021-01-03Hive需要对2号的数据进行同步更新处理 问题新增的数据会直接加载到Hive表中但是更新的数据如何存储在Hive表中 解决方案
方案一在Hive中用新的addr覆盖008的老的addr直接更新 优点实现最简单使用起来最方便 缺点没有历史状态008的地址是1月2号在sh但是1月2号之前是在gz的如果要查询008的1月2号之前的addr就无法查询也不能使用sh代替
方案二每次数据改变根据日期构建一份全量的快照表每天一张表 优点记录了所有数据在不同时间的状态 缺点冗余存储了很多没有发生变化的数据导致存储的数据量过大
方案三构建拉链表通过时间标记发生变化的数据的每种状态的时间周期 功能与应用场景
拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题。拉链表的设计是将更新的数据进行状态记录没有发生更新的数据不进行状态存储用于存储所有数据在不同时间上的所有状态通过时间进行标记每个状态的生命周期查询时根据需求可以获取指定时间范围状态的数据默认用9999-12-31等最大值来表示最新状态。
实现过程 SQL实现
1、创建拉链表
zipper.txt
001 186xxKx1234 laoda 0 sh 2021-01-01 9999-12-31
002 186xxxx1235 laoer 1 bj 2021-01-01 9999-12-31
003 186xxxx1236 laosan 0 sz 2021-01-01 9999-12-31
004 186xxxx1237 laosi 1 gZ 2021-01-01 9999-12-31
005 186xxxx1238 laowu 0 sh 2021-01-01 9999-12-31
006 186xxxx1239 laoliu 1 bj 2021-01-01 9999-12-31
007 186xxxx1240 laoqi 0 sz 2021-01-01 9999-12-31
008 186xxxx1241 laoba 1 gz 2021-01-01 9999-12-31
009 186xxxx1242 laojiu 0 sh 2021-01-01 9999-12-31
010 186xxxx1243 laoshi 1 bj 2021-01-01 9999-12-31SQL
-- 1、创建拉链表
create table dwd_zipper(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string
) row format delimited fields terminated by \t;load data local inpath /root/hivedata/zipper.txt into table dwd_zipper;select * from dwd_zipper;2、模拟增量数据采集
zipper_update.txt
008 186xxxx1241 laoba 1 sh 2021-01-02 9999-12-31
011 186xxxx1244 laoshi 1 jx 2021-01-02 9999-12-31
012 186xxxx1245 laoshi 0 zj 2021-01-02 9999-12-31SQL
-- 2、增量数据
create table ods_zipper_update(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string
) row format delimited fields terminated by \t;load data local inpath /root/hivedata/zipper_update.txt into table ods_zipper_update;3、创建临时表
-- 3、创建临时表
create table dwd_zipper_tmp(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string
) row format delimited fields terminated by \t;4、合并历史拉链表与增量表
-- 4、合并历史拉链表与增量表
insert overwrite table dwd_zipper_tmp
select * from ods_zipper_update
union all
-- 查询原来拉链表的所有数据并将这次需要更新的数据的endtime更改为值的starttime
select a.userid,a.phone,a.nick,a.gender,a.addr,a.starttime,-- 如果这条数据没有更新或者这条数据不是要更改的数据就保留原来的值否则就改为新数据的开始时间-1if(b.userid is null or a.endtime 9999-12-31,a.endtime,date_sub(b.starttime,1)) as endtime
from dwd_zipper a left join ods_zipper_update b on a.useridb.userid;
5、覆盖拉链表
-- 5、覆盖拉链表
insert overwrite table dwd_zipper
select * from dwd_zipper_tmp;6、查看拉链表
