찾다

집  >  기사  >  데이터 베이스  >  데이터베이스 지퍼 테이블에 대해 이야기해 보겠습니다.

데이터베이스 지퍼 테이블에 대해 이야기해 보겠습니다.

WBOY
WBOY앞으로
2022-06-20 12:01:112673검색

이 기사에서는 주로 데이터베이스 지퍼 테이블과 관련된 문제를 소개하는 mysql에 대한 관련 지식을 제공합니다. 지퍼 테이블은 이름에서 알 수 있듯이 주로 테이블이 데이터 웨어하우스 정의에 데이터를 저장하는 방식에 대한 데이터 모델입니다. -지퍼라는 것은 역사를 기록하는 것이기에 모두에게 도움이 되었으면 좋겠습니다.

데이터베이스 지퍼 테이블에 대해 이야기해 보겠습니다.

추천 학습: mysql 비디오 튜토리얼

지퍼 테이블 생성의 배경

데이터 웨어하우스의 데이터 모델 설계 과정에서 우리는 종종 다음과 같은 요구 사항에 직면합니다.

1.

2. 사용자 주소, 제품 설명 정보, 주문 상태 등과 같은 일부 필드가 업데이트됩니다.

3. 특정 시점의 과거 스냅샷 정보를 확인해야 합니다. 기간, 예를 들어 과거 특정 기간에 사용자가 몇 번이나 업데이트했는지 확인하는 등 특정 시점의 주문 상태를 확인합니다.

4. 변경 빈도는 그다지 크지 않습니다. 예를 들어 전체 회원 수는 1,000만 명이며 매일 약 100,000명이 추가되고 변경됩니다.

5. 이 테이블의 전체 복사본이 매일 유지된다면 변경되지 않은 부분이 많습니다. 정보는 매번 전체 볼륨에 저장되므로 저장이 매우 어렵습니다.

이런 종류의 테이블에는 여러 가지 옵션이 있습니다.

  • 옵션 1: 매일 최신 복사본만 보관하세요. 예를 들어, 우리는 Sqoop을 사용하여 매일 최신 전체 데이터를 Hive로 추출합니다.
  • 옵션 2: 매일 전체 데이터 조각을 보관하세요.
  • 옵션 3: 지퍼 테이블을 사용하세요.

위 계획의 비교

계획 1

말할 것도 없이 이 계획은 매일 실행하기 매우 간단합니다. 전날의 데이터를 삭제하고 최신 데이터를 다시 추출합니다.

장점은 분명하고, 공간을 절약하며, 일부 일반적인 용도에도 매우 편리합니다. 테이블을 선택할 때 시간 분할을 추가할 필요가 없습니다.

단점도 뚜렷합니다. 이전 계정을 먼저 확인할 수 있는 유일한 방법은 흐름도에서 그리는 등의 다른 방법을 이용하는 것입니다.

플랜 2

매일 슬라이스를 가득 제공하는 것은 비교적 안전한 계획이며, 과거 데이터가 있습니다.

단점은 저장 공간을 너무 많이 차지한다는 점입니다. 이 테이블의 전체 복사본을 매일 보관하면 각 전체 복사본에 변경되지 않은 정보가 많이 저장되므로 저장 공간이 엄청날 것 같습니다. 아직도 그 느낌이 너무 깊네요...

물론 지난 달의 데이터만 유지하는 등의 절충안도 만들 수 있겠죠? 그러나 수요는 뻔뻔스럽고, 데이터의 수명주기는 우리가 완전히 통제할 수 있는 것이 아닙니다.

지퍼 시계

지퍼 시계는 기본적으로 사용상의 요구 사항을 고려합니다.

첫 번째 옵션만큼 작은 공간을 차지하지는 않지만, 일일 증가량은 두 번째 옵션의 1000분의 1, 심지어 1만분의 1에 불과할 수도 있습니다.

사실, 옵션 2가 충족할 수 있는 요구 사항을 충족할 수 있습니다. 최신 데이터를 얻을 수 있을 뿐만 아니라 필터 조건을 추가하고 과거 데이터도 얻을 수 있습니다.

그래서 우리에게는 여전히 지퍼 테이블을 사용하는 것이 필요합니다.

Zipper 테이블 개념

Zipper 테이블은 주로 데이터 웨어하우스 설계에서 테이블이 데이터를 저장하는 방식을 위해 정의된 데이터 모델입니다. 이름에서 알 수 있듯이 소위 지퍼는 기록을 기록하는 것입니다. 사물의 시작부터 현재 상태까지 모든 변경 사항에 대한 정보를 기록합니다. 지퍼 테이블은 매일 모든 기록을 저장함으로써 발생하는 대용량 저장 문제를 피할 수 있으며, 느리게 변화하는 데이터(SCD2)를 처리하는 일반적인 방법이기도 합니다.

바이두 백과사전 설명: 지퍼 테이블은 과거 상태와 최신 상태 데이터를 유지하는 테이블입니다. 지퍼 테이블은 실제로 스냅샷과 동일하지만 최적화되어 변경되지 않은 일부 기록을 제거했습니다. 지퍼 이용시 고객 기록을 지퍼 테이블을 통해 쉽게 복원할 수 있습니다.

Zipper 테이블 알고리즘

1. 오늘의 전체 데이터를 ND(NowDay) 테이블에 수집합니다.

2. 어제의 전체 데이터를 기록 테이블에서 꺼내어 OD(OldDay) 테이블에 저장할 수 있습니다.

3. 두 테이블은 모든 필드에서 비교되며, (ND-OD)는 해당 날짜의 신규 및 변경된 데이터, 즉 W_I로 표시되는 날짜입니다.

4. , (OD-ND)는 상태입니다. 이때 차단해야 할 데이터는 W_U로 표시되는 END_DATE를 수정해야 합니다.

5. W_I 테이블의 모든 내용을 기록 테이블에 삽입합니다. start_date는 현재 날짜이고 end_date는 '9999-12-31'로 설정할 수 있습니다.

6. start_date는 변경되지 않고 end_date는 변경됩니다. 연결 작업인 현재 날짜와 기록 테이블(OD) 및 W_U 테이블을 비교합니다. 단, W_U 테이블을 기반으로 하는 START_DATE, END_DATE는 둘의 교집합이 END_DATE를 현재 날짜로 변경하여 나타냅니다. 그 기록이 무효하다는 것입니다.

지퍼 테이블 예시 1

간단한 예시를 들어보겠습니다. 예를 들어 주문 테이블이 있습니다.

6월 20일에 3개의 기록이 있습니다.

주문 생성 날짜 주문 번호 주문 상태
2012-06-20 001 주문 생성
2012-06- 20 002 주문 생성
2012-06-20 003 결제 완료

6월 21일까지 테이블에 5개의 기록이 있습니다:

주문 생성 날짜 주문 번호 주문 상태
2012-06-20 001 주문 생성
2012-06-20 002 주문 생성
2012-06 -20 003 결제완료
2012-06-21 004 주문 생성
2012-06-21 005 주문 생성

6월 22일까지 테이블에 6개의 항목이 있습니다. :

주문 생성 날짜 주문 번호 주문 상태
2012-06-20 001 주문 생성
2012-06-20 002 주문 생성
2012-06-20 003 결제완료
2012-06-21 004 주문생성
2012-06-21 005 주문 생성
2012-06-22 006 주문 생성

이 테이블을 데이터 웨어하우스에 보관하는 방법:

1. 전체 금액의 복사본 하나만 보관하면 데이터가 원본과 동일해집니다. 6월 22일에 기록합니다. 6월 21일에 주문 001의 상태를 확인해야 한다면 만족할 수 없습니다.

2. 매일 전체 복사본이 보관되면 데이터 웨어하우스의 테이블에는 총 14개의 기록이 있습니다. , 그러나 많은 기록이 반복적으로 저장되며 작업 변경이 없습니다. 예를 들어 주문 002,004에는 데이터 양이 많아 저장 낭비가 많이 발생합니다. 데이터 웨어하우스에는 다음과 같은 테이블이 있습니다.

주문 생성 날짜 2012-06- 20 2012-06-202012-06-202012-06-202012-06- 2020 12-06-212012-06-212012-06-212012-06-22 설명:
주문 번호 주문 상태 dw_bigin_date dw_end_date
001 주문생성 2012-06-2 0 2012-06-20
001 결제완료 2012-06-21 9999-12-31
002 주문 생성 2012-06-20 9999-12-31
003 결제완료 2012-06 -20 2012-06-21
003 발송 2012-06-22 9999-12-31
004 주문 생성 2012-06-21 9999-12-31
005 주문 생성 2012-06-21 2 012-06-21
005 결제 완료 2012-06-22 9999-12-31
006 주문 생성 2012-06- 22 9999-12-31

1, dw_begin_date는 레코드의 수명 주기 시작 시간을 의미하고, dw_end_date는 레코드의 수명 주기 종료 시간을 나타냅니다.

2 dw_end_date = '9999- 12-31'은 레코드가 현재 유효한 상태임을 나타냅니다.

3. 현재 유효한 레코드를 모두 쿼리하는 경우 dw_end_date = '9999-12-31'에서 *를 선택합니다. 2012-06-21의 기록 스냅샷을 쿼리한 다음 dw_begin_date <= '2012-06- 21' 및 end_date >= '2012-06-21'인 order_his에서 *를 선택하면 이 명령문은 다음 레코드를 쿼리합니다.

주문 생성 날짜주문 번호2012-06-20001002003004 005은 6월 21일 소스 테이블의 기록과 정확히 동일합니다.
주문 상태 dw_bigin_date dw_end_date
결제완료 2012-06-21 9999- 12-31 2012-06-20
주문 생성 2012-06-20 9999-12-31 2012-06-20
결제완료 2012-06-20 2012-06-21 20 12-06-21
주문 만들기 2012-06-21 9999-12-31 2012- 06-21
Create order 2012-06-21 20 12-06-21

주문 생성 날짜주문 번호2012-06-20001002003004005
주문 상태
주문 생성 2012-06-20
주문 생성 2012 -06-20
결제완료 2012-06-21
주문생성 2012-06-21
주문 만들기

이러한 과거 지퍼 테이블은 과거 데이터에 대한 수요를 충족할 뿐만 아니라 저장 자원을 크게 절약할 수 있음을 알 수 있습니다.

지퍼 테이블 예 2:

사람의 삶을 기록하는 것이 가능합니다. 히스토리 테이블 이와 같은 몇 가지 레코드만으로 매일 고객 상태를 기록하여 발생하는 대용량 저장 문제를 방지할 수 있습니다. 19000101

19070901H 집에서client1907090119130901A초등학교client191309011 9160901B중학교client 1916090119190901C 고등학교 client1919090119230901D 대학 client1923090119601231E회사 client1960123129991231 H는 집에서 은퇴했습니다
CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;
2. day
INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce;
INSERT INTO VT_xxxx_CHG(xx)SELECT xx FROM VT_xxxx_NEWWHERE (xx) NOT IN (select xx from xxxx_HIS where end_date='max_date');
UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2SET End_Date='current_date'WHERE A1.xx=A2.xx AND A1.End_Date='max_date';
INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG;
제품 데이터를 예로 들어
위의 모든 기록은 끝이 아닙니다. 예를 들어 19070901에 클라이언트는 이미 H가 아닌 A에 있습니다. 따라서 지금까지 상태가 변경되지 않은 마지막 레코드를 제외하고 나머지 레코드는 실제로는 레코드 종료일의 상태이고 더 이상 레코드 종료일의 상태가 아닙니다. 이 현상은 시작은 세고 끝은 세지 않는 것으로 이해될 수 있다. 지퍼 테이블 구현 방법 1. 두 개의 임시 테이블을 정의합니다. 하나는 그날의 전체 데이터이고 다른 하나는 추가하거나 업데이트해야 하는 데이터입니다.
3. xxxx_NEW 임시 테이블에서 xxxx_CHG 임시 테이블로 새 데이터 또는 변경된 데이터를 추출합니다. 4. 기록 테이블에 있는 유효하지 않은 레코드의 end_date를 최대 값으로 업데이트합니다 5. 데이터를 대상 테이블로 변경
제품 테이블 t_product가 있고 테이블 구조는 다음과 같습니다.

Column Name

Type

설명

goods _idvarchar(50) varchar(50) createtimevarchar(50)상품 생성 날짜modifytimevarchar(50)항목 수정 날짜2019년 12월 20일 데이터는 다음과 같습니다
제품 번호 goods_status 제품 상태(검토 대기 중, 보류 중, 판매 중, 삭제됨)
goods_id
goods_status createtime
modifytime

검토 대기 중2019-12-200022019-12-202019-12 -20003판매중2019-12-202019-12-20004 삭제됨2019- 12-202019-12-2012월 20일(데이터 4개)
0012019-12-20 For sale
제품 상태는 시간이 지남에 따라 변경될 수 있으며, 제품의 모든 변경 사항에 대한 기록 정보를 저장해야 합니다. 플랜 1: 매일의 데이터를 데이터 웨어하우스에 스냅샷 이 계획은 매일 전체 복사본을 저장하고 모든 데이터를 데이터 웨어하우스에 동기화하는 것입니다. 많은 기록이 변경 없이 반복적으로 저장됩니다.
goods_id
goods_status

createtime

modifytime
검토 대기 중2019-12-200022019-12-192019-12-20003판매2019-12-20 2019 -12-20004삭제됨2019-12-152019-12-20
12월 21일(10개 데이터)
0012019 -12- 18판매
goods_id goods_status createtime modifytime
다음은 12월 20일 스냅샷 데이터


001 검토 대기 중 2019-12-18 2019-12-20
002 판매용 2019-12-19 2019 -12 -20
003 판매중 2019-12-20 2019-12-20
004 삭제 2019-12-15 2019-12-20
다음은 12월 21일 스냅샷 데이터


001 판매중(보류~보류) 2019-12-18 2019-12 -21
002 판매 2019-12-19 2019-12-20
003 판매 2019-12-20 2019- 12-20
004 삭제됨 2019-12-15 2019-12-20
005(새 항목) 검토 대기 중 2019-12-21 2019- 12-21
006( 새 항목) 검토 대기 중 2019-12-21 2019-12-21
12月22日（18条数据）
goods_id goods_status createtime modifytime
以下为12月20日快照数据


001 待审核 2019-12-18 2019-12-20
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
以下为12月21日快照数据


001 待售（从待审核到待售） 2019-12-18 2019-12-21
002 待售 2019-12-19 2019-12-20
003 在售 2019-12-20 2019-12-20
004 已删除 2019-12-15 2019-12-20
005 待审核 2019-12-21 2019-12-21
006 待审核 2019-12-21 2019-12-21
以下为12月22日快照数据


001 待售 2019-12-18 2019-12-21
002 待售 2019-12-19 2019-12-20
003 已删除（从在售到已删除） 2019-12-20 2019-12-22
004 待审核 2019-12-21 2019-12-21
005 待审核 2019-12-21 2019-12-21
006 已删除（从待审核到已删除） 2019-12-21 2019-12-22
007 待审核 2019-12-22 2019-12-22
008 待审核 2019-12-22 2019-12-22
MySQL数仓代码实现

MySQL初始化

在MySQL中 lalian 库和商品表用于到原始数据层

-- 创建数据库create database if not exists lalian;-- 创建商品表create table if not exists `lalian`.`t_product`(
	goods_id varchar(50), -- 商品编号
    goods_status varchar(50), -- 商品状态
    createtime varchar(50), -- 商品创建时间
    modifytime varchar(50) -- 商品修改时间);

在MySQL中创建ods和dw层来模拟数仓

-- ods创建商品表create table if not exists `lalian`.`ods_t_product`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	cdat varchar(10)   -- 模拟hive分区)default character set = 'utf8';-- dw创建商品表create table if not exists `lalian`.`dw_t_product`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
 	createtime varchar(50), -- 商品创建时间
 	modifytime varchar(50), -- 商品修改时间
 	cdat varchar(10)  -- 模拟hive分区)default character set = 'utf8';

增量导入12月20号数据

原始数据导入12月20号数据（4条）

insert into `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) values('001', '待审核', '2019-12-18', '2019-12-20'),('002', '待售', '2019-12-19', '2019-12-20'),('003', '在售', '2019-12-20', '2019-12-20'),('004', '已删除', '2019-12-15', '2019-12-20');

注意：由于这里使用的MySQL来模拟的数仓所以直接使用insert into的方式导入数据，在企业中可能会使用hive来做数仓使用 kettle 或者 sqoop 或 datax 等来同步数据。

# 从原始数据层导入到ods 层insert into lalian.ods_t_productselect *,'20191220' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191220';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191220';
goods_id goods_status createtime modifytime cdat
1 待审核 2019/12/18 2019/12/20 20191220
2 待售 2019/12/19 2019/12/20 20191220
3 在售 2019/12/20 2019/12/20 20191220
4 已删除 2019/12/15 2019/12/20 20191220

增量导入12月21数据

原始数据层导入12月21日数据（6条数据）

UPDATE `lalian`.`t_product` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层insert into lalian.ods_t_productselect *,'20191221' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191221';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191221';
goods_id goods_status createtime modifytime cdat
1 待售 2019/12/18 2019/12/21 20191221
2 待售 2019/12/19 2019/12/20 20191221
3 在售 2019/12/20 2019/12/20 20191221
4 已删除 2019/12/15 2019/12/20 20191221
5 待审核 2019/12/21 2019/12/21 20191221
6 待审核 2019/12/21 2019/12/21 20191221

增量导入12月22日数据

原始数据层导入12月22日数据（6条数据）

UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '003';UPDATE `lalian`.`t_product` SET goods_status = '已删除', modifytime = '2019-12-22' WHERE goods_id = '006';INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES('007', '待审核', '2019-12-22', '2019-12-22'),('008', '待审核', '2019-12-22', '2019-12-22');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层
insert into lalian.ods_t_product
select *,'20191222' from lalian.t_product ;
# 从ods同步到dw层
insert into lalian.dw_t_productpeizhiwenjian
select * from lalian.ods_t_product where cdat='20191222';

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='20191222';
goods_id goods_status createtime modifytime cdat
1 待售 2019/12/18 2019/12/21 20191222
2 待售 2019/12/19 2019/12/20 20191222
3 已删除 2019/12/20 2019/12/22 20191222
4 已删除 2019/12/15 2019/12/20 20191222
5 待审核 2019/12/21 2019/12/21 20191222
6 已删除 2019/12/21 2019/12/22 20191222
7 待审核 2019/12/22 2019/12/22 20191222
8 待审核 2019/12/22 2019/12/22 20191222

查看dw层的运行结果

select * from lalian.dw_t_product;
goods_id goods_status createtime modifytime cdat
1 待审核 2019/12/18 2019/12/20 20191220
2 待售 2019/12/19 2019/12/20 20191220
3 在售 2019/12/20 2019/12/20 20191220
4 已删除 2019/12/15 2019/12/20 20191220
1 待售 2019/12/18 2019/12/21 20191221
2 待售 2019/12/19 2019/12/20 20191221
3 在售 2019/12/20 2019/12/20 20191221
4 已删除 2019/12/15 2019/12/20 20191221
5 待审核 2019/12/21 2019/12/21 20191221
6 待审核 2019/12/21 2019/12/21 20191221
1 待售 2019/12/18 2019/12/21 20191222
2 待售 2019/12/19 2019/12/20 20191222
3 已删除 2019/12/20 2019/12/22 20191222
4 已删除 2019/12/15 2019/12/20 20191222
5 待审核 2019/12/21 2019/12/21 20191222
6 已删除 2019/12/21 2019/12/22 20191222
7 待审核 2019/12/22 2019/12/22 20191222
8 待审核 2019/12/22 2019/12/22 20191222

위 사례에서 테이블은 매일 전체 볼륨을 유지하고 각 전체 볼륨에는 변경되지 않은 많은 정보가 저장되는 것을 볼 수 있습니다. 데이터 양이 많으면 저장 공간이 엄청나게 낭비됩니다. 테이블은 다음과 같이 디자인할 수 있습니다. 지퍼 테이블은 데이터의 기록 상태를 반영할 뿐만 아니라 저장 공간을 최대한 절약할 수 있습니다.

옵션 2: 지퍼 테이블을 사용하여 기록 스냅샷 저장

zipper 테이블은 중복 데이터를 저장하지 않습니다. 특정 행의 데이터만 저장하면 됩니다. 이는 매번 전체 동기화에 비해 비용을 절약합니다. time 저장 공간行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间

能够查询到历史快照

额外的增加了两列（dw_start_date、dw_end_date

기록 스냅샷을 쿼리할 수 있습니다
데이터 행의 수명 주기인 두 개의 추가 열(dw_start_date, dw_end_date)을 추가했습니다.
12월 20일 제품 zip 목록 데이터dw_end_date001검토 대기 중2019-12-182019-12-202019-12-209999-12-31002판매 대기 중 2019-12- 192019-12-202019-12-209999-12-31003판매중2019-12-202019-12-20 2019-12 -209999-12-31004삭제됨2019-12-152019-12-202019-12-209999-12-31
goods_id goods_status createtime modifytime dw_start_date

  • 12월 20일의 데이터는 dw 테이블로 가져온 새로운 데이터입니다
  • dw_start_date는 특정 데이터 조각의 수명 주기의 시작 시간을 나타냅니다. 즉, 데이터는 해당 시간(즉, 유효 날짜)부터 유효합니다
    • dw_end_date는 특정 데이터의 시작 시간을 나타냅니다. 라이프 사이클의 종료 시간, 즉 데이터가 오늘(포함되지 않음)에 도달합니다(즉, 만료 날짜)
dw_end_date는 9999-12-31입니다. 이는 현재 데이터가 최신 데이터이며 9999-12-31까지 데이터가 만료되지 않는다는 것을 의미합니다.
12월 21일 제품 zip 목록 데이터dw_end_date001검토 대기 중2019-12-182019-12-202019-12-21002판매2019-12 -192019-12-202019-12-2099 99-12-31003판매중2019-12-202019-12-20 2019-12-209999-12-31004삭제됨 2019-12-152019-12-202019-12-209999-12 -319999-12-319999-12-31
goods_id goods_status createtime modifytime dw_start_date
2019-12- 20
001(변경) 판매중 2019-12-18 2019- 12-21 2019-12-21
005(신규) 검토 대기 중 2019-12-21 2019-12-21 2019-12-21
🎜🎜🎜

지퍼 테이블에 중복된 데이터가 저장되어 있지 않습니다. 즉, 데이터가 변경되지 않는 한 동기화할 필요가 없습니다.

  • 001번 상품 데이터의 상태가 변경되었습니다(검토중 → 판매중) ), 원본 dw_end_date를 9999 -12-31에서 2019-12-21으로 변경해야 하며 이는 2019/12/20(포함)부터 2019/12/21까지 유효한 검토 보류 상태를 나타냅니다. (독점);
  • 001 번호는 새로운 상태 레코드로 다시 저장됩니다. dw_start_date는 2019/12/21, dw_end_date는 9999/12/31입니다.
  • 새 데이터 005, 006, dw_start_date는 2019/12/입니다. 21, dw_end_date는 9999/12/31입니다.
12월 22일 제품 zip 목록 데이터

拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

  • 003编号的商品数据的状态发生了变化（从在售→已删除），需要将原有的 dw_end_date从9999-12-31变为2019-12-22，表示在售状态，在2019/12/20（包含） - 2019/12/22（不包含） 有效
  • 003编号新的状态重新保存了一条记录，dw_start_date为2019-12-22，dw_end_date为9999-12-31
  • 新数据007、008、dw_start_date为2019-12-22，dw_end_date为9999-12-31
MySQL数仓拉链表快照实现

操作流程：

  1. 在原有dw层表上，添加额外的两列
  2. 只同步当天修改的数据到ods层
  3. 拉链表算法实现
  4. 拉链表的数据为：当天最新的数据 UNION ALL 历史数据

代码实现

在MySQL中lalian库和商品表用于到原始数据层

-- 创建数据库create database if not exists lalian;-- 创建商品表create table if not exists `lalian`.`t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
    createtime varchar(50), -- 商品创建时间
    modifytime varchar(50) -- 商品修改时间)default character set = 'utf8';

在MySQL中创建ods和dw层 模拟数仓

-- ods创建商品表create table if not exists `lalian`.`ods_t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	cdat varchar(10)   -- 模拟hive分区)default character set = 'utf8';-- dw创建商品表create table if not exists `lalian`.`dw_t_product2`(
	goods_id varchar(50), -- 商品编号
	goods_status varchar(50), -- 商品状态
	createtime varchar(50), -- 商品创建时间
	modifytime varchar(50), -- 商品修改时间
	dw_start_date varchar(12), -- 生效日期
	dw_end_date varchar(12), -- 失效时间
	cdat varchar(10)  -- 模拟hive分区)default character set = 'utf8';

全量导入2019年12月20日数据

原始数据层导入12月20日数据（4条数据）

insert into `lalian`.`t_product_2`(goods_id, goods_status, createtime, modifytime) values('001', '待审核', '2019-12-18', '2019-12-20'),('002', '待售', '2019-12-19', '2019-12-20'),('003', '在售', '2019-12-20', '2019-12-20'),('004', '已删除', '2019-12-15', '2019-12-20');

将数据导入到数仓中的ods层

insert into lalian.ods_t_product2select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';

将数据从ods层导入到dw层

insert into lalian.dw_t_product2select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191220';

增量导入2019年12月21日数据

原始数据层导入12月21日数据（6条数据）

UPDATE `lalian`.`t_product2` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';INSERT INTO `lalian`.`t_product2`(goods_id, goods_status, createtime, modifytime) VALUES('005', '待审核', '2019-12-21', '2019-12-21'),('006', '待审核', '2019-12-21', '2019-12-21');

原始数据层同步到ods层

insert into lalian.ods_t_product2select *,'20191221' from lalian.t_product2 where modifytime >='2019-12-21';

编写ods层到dw层重新计算 dw_end_date

select t1.goods_id, t1.goods_status, t1.createtime, t1.modifytime,
       t1.dw_start_date,
       case when (t2.goods_id is not null and t1.dw_end_date>'2019-12-21') then '2019-12-21'else t1.dw_end_date end as dw_end_date ,
       t1.cdatfrom lalian.dw_t_product2 t1left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_idunionselect goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';

执行结果如下：

goods_id goods_status createtime modifytime dw_start_date dw_end_date
001 검토 대기 중 2019-12-18 2019-12-20 2019-12-20 2019-12-21
002 판매중 2019-12 - 19 2019-12-20 2019-12-20 9999-12-31
003 판매중 2019-12-20 2019-12-20 2019- 12 -20 2019-12-22
004 삭제 2019-12-15 2019-12-20 2019-12-20 9 999-12-31
001 판매중 2019-12-18 2019-12-21 2019-12-21 9999-12-31
005 검토 대기 중 2019-12 -21 2019-12-21 2019-12-21 9999-12-31
006 검토 대기 중 2019-12-21 2019-12-21 2019- 12- 21 9999-12-31
003 (변경) Deleted 2019-12-20 2019-12-22 2019-12-22 9999-12-31
007(신규) 검토 대기 중 2019-12-22 201 22 2019 -12 -22 9999-12-31
008(신규) 검토 대기 중 2019-12-22 20 19-12-22 2019 -12-22 9999-12-31
goods_id goods_status createtime modifytime dw_start_date dw_end_date cdat
1 待审核 2019-12-18 2019-12-20 2019-12-20 2019-12-21 20191220
2 待售 2019-12-19 2019-12-20 2019-12-20 9999-12-31 20191220
3 在售 2019-12-20 2019-12-20 2019-12-20 9999-12-31 20191220
4 已删除 2019-12-15 2019-12-20 2019-12-20 9999-12-31 20191220
1 待售 2019-12-18 2019-12-21 2019-12-21 9999-12-31 20191221
5 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31 20191221
6 待审核 2019-12-21 2019-12-21 2019-12-21 9999-12-31 20191221

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。我们做拉链表的时候要确定拉链表的粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

推荐学习：mysql视频教程

위 내용은 데이터베이스 지퍼 테이블에 대해 이야기해 보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：
이 기사는 csdn.net에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제
이전 기사：mysql 저장 프로시저의 루프 문은 무엇입니까?다음 기사：mysql 저장 프로시저의 루프 문은 무엇입니까?

관련 기사

더보기