ホームページ >データベース >mysql チュートリアル >データベースのジッパーテーブルについて話しましょう

データベースのジッパーテーブルについて話しましょう

WBOY転載: 2022-06-20 12:01:112737ブラウズ

この記事では、mysql に関する関連知識を提供します。主にデータベースジッパーテーブルに関する関連問題を紹介します。ジッパーテーブルは、主にデータウェアハウス設計のためのデータモデルです。テーブルは、格納方法によって定義されます。データ. 名前が示すように、いわゆるジッパーは履歴を記録するものです. 一緒に見てみましょう. 皆さんの参考になれば幸いです.

#推奨学習:

mysql ビデオチュートリアル

ジッパーテーブル生成の背景

データウェアハウスのデータモデルを設計するプロセスでは、次のような要件に遭遇することがよくあります:

1. データ量が比較的多い;

2. いくつかのフィールドユーザーの住所、製品説明情報、注文ステータスなど、テーブル内の情報が更新されます;

3. 特定の時点または期間の履歴スナップショット情報を表示する必要があります。たとえば、履歴の特定の時点での注文の履歴スナップショット情報を表示するには、たとえば、ユーザーが過去の特定の期間に何回更新したかを確認するなど、ある時点のステータスを確認します。

#4. 変更の割合と頻度はそれほど大きくありません (たとえば、合計 1,000 万人です) 約 100,000 人の新しいメンバーがいて毎日変更されます;

5. If a full copy ofこのテーブルは毎日保持されるため、毎回多くの変更されていない情報がフルボリュームに保存されることになりますが、これはストレージにとって非常に重要です。大きな無駄です。

この種類にはいくつかのオプションがあります。テーブルの:

オプション 1: 最新のコピーのみを毎日保持します (例: Sqoop を使用して、最新の全量のデータを毎日 Hive に抽出します)。

オプション 2: スライスされた全量のデータを毎日保持します。
オプション 3: ジッパーリストを使用します。

上記のソリューションの比較

オプション 1

このソリューションについて詳しく説明する必要はありません。実装は非常に簡単シンプルで、毎日前日のデータを削除し、最新のデータを再度抽出します。

利点は明白で、スペースを節約できるだけでなく、テーブルを選択するときに時間パーティションを追加する必要がなく、一般的な用途にも非常に便利です。

欠点も明らかです。履歴データが存在しないため、最初に古いアカウントを確認する唯一の方法は、フローシートから引き出すなどの他の方法を使用することです。

オプション 2

毎日スライスを丸ごと食べるのは比較的安全なプランであり、履歴データもそこにあります。

欠点は、ストレージスペースが大きすぎることです。このテーブルのフルコピーを毎日保持すると、多くの変更されていない情報が各フルコピーに保存されることになり、ストレージが大幅に無駄になります。、私はこれについて今でも非常に深く感じています...

もちろん、過去 1 か月のデータのみを保持するなど、いくつかのトレードオフを行うこともできます。しかし、その要求は恥知らずであり、データのライフサイクルは私たちが完全に制御できるものではありません。

ジッパーテーブル

ジッパーテーブルは基本的に使用時のニーズを考慮しています。

まず第一に、スペースの点でトレードオフになります。プラン 1 ほど占有スペースは小さくありませんが、1 日あたりの増加量はプラン 2 の 1,000 分の 1、さらには 1 万分の 1 にすぎない可能性があります。。

実際には、最新のデータを取得するだけでなく、フィルタリング条件を追加して履歴データを取得できるソリューション 2 のニーズを満たすことができます。

したがって、やはりジッパーテーブルを使用する必要があります。

ジッパーテーブルの概念

ジッパーテーブルはデータモデルであり、主にデータウェアハウス設計においてテーブルがデータを格納する方法のために定義されています。記録の歴史。物事の始まりから現在の状態までのすべての変更に関する情報を記録します。ジッパーテーブルは、毎日のすべてのレコードを保存することによって引き起こされる大規模なストレージの問題を回避でき、ゆっくりと変化するデータ (SCD2) を処理する一般的な方法でもあります。

百度百科事典の説明: ジッパーテーブルは、過去のステータスと最新のステータスデータを維持するテーブルです。ジッパーの粒度に応じて、ジッパーテーブルは実際にはスナップショットと同等ですが、最適化されており、スナップショットの一部です。変更されていないレコード、圧縮時の顧客レコードは、ジッパーテーブルを使用して簡単に復元できます。

ジッパーテーブルアルゴリズム

1. その日の完全なデータを ND (NowDay) テーブルに収集します;

2. 昨日の完全なデータを履歴テーブルから取得でき、それを OD ( OldDay (最終日) テーブル;

3. 2 つのテーブルとすべてのフィールドを比較します。 (ND-OD) は、その日の新規データと変更されたデータ、つまりの増分です。 W_I;

4 で表される日。2 つのテーブルの全フィールドを比較します。(OD-ND) は、ステータスが終了したときに閉じる必要があるデータです。END_DATE は変更する必要があります。次のように表されます。 W_U;

5. W_I テーブルの内容を変更しますすべてが履歴テーブルに挿入されます、これらは新しいレコードです、start_date は現在の日、end_date は最大値で、'9999 に設定できます-12-31';

6. 履歴テーブルの W_U 部分を実行します。更新操作では、start_date は変更されませんが、end_date は現在の日付に変更されます。これはリンク操作です。履歴テーブル (OD) ) は、START_DATE と END_DATE を除き、W_U テーブルと比較されます。W_U テーブルが優先されます。これら 2 つの交差部分は、END_DATE を現在の日に変更します。これは、レコードが無効であることを示します。

ジッパーテーブルの例 1

簡単な例として、たとえば注文テーブルがあります:

6 月 20 日には 3 つのレコードがあります:

#注文作成日注文番号注文ステータス2012 -06-20001注文の作成2012-06-20002注文の作成注文2012-06-20003支払い完了

to 6 3 月 21 日、テーブルには 5 つのレコードがあります:

#注文作成日2012-06-20#2012-06-20 2012-06-20#2012-06-21004注文の作成005

注文番号	注文ステータス
001	注文の作成
002	注文の作成
003	支払い完了
		#2012-06-21
注文の作成		## 6 月 22 日までに、テーブルには 6 つのレコードがあります:

#注文作成日

注文番号注文ステータス##2012-06-20注文の作成#2012-06-20002注文の作成#2012-06-202012-06-212012-06-212012 -06-22データウェアハウス内のこのテーブルの保持方法: 1. 全額のコピーが 1 つだけ保持されるため、データは 6 月 22 日の記録と同じです。6 月 21 日の注文 001 のステータスを確認する必要がある場合、それを満たすことはできません; 2. 毎日 1 つのコピーが保持されます。データウェアハウスのテーブルには合計 14 レコードがありますが、注文 002,004 など、多くのレコードはタスクを変更せずに繰り返し保存されます。データ量が大きいため、大量のデータが保存されます。ストレージの無駄; If テーブルがデータウェアハウスに履歴ジッパーテーブルとして保存されるように設計されている場合、次のようなテーブルになります:

		# 001

003	支払い完了
004	注文の作成
005	注文の作成
006	オーダーの作成

注文作成日

注文番号

注文ステータス

dw_bigin_datedw_end_date2012-06-202012-06-20#2012-06-20001支払い完了2012-06-219999-12-312012-06- 20002注文の作成2012-06-209999-12 -31##2012-06-21005注文の作成2012 -06-21 2012-06-212012-06-21005支払い完了2012-06-229999-12-312012-06-22006作成order2012-06 -229999-12-31

		2012-06-20	001	注文の作成


## 2012-06-20	003	支払い完了	2012-06-20	2012-06-21
2012-06-20	003	発送済み	2012-06-22	9999-12-31
2012-06-21	004	注文の作成	2012-06- 21	9999-12- 31



注:	1. dw_begin_date は、レコードのライフサイクルの開始時刻、dw_end_date レコードのライフサイクルの終了時刻を示します;	2. dw_end_date = '9999-12-31' は、レコードが現在有効な状態であることを示します。	3. 現在有効なすべてのレコードをクエリする場合は、 * from order_his where dw_end_date = '9999-12-31';	4. 2012 年 6 月の履歴スナップショットをクエリする場合21 を選択し、* from order_his where dw_begin_date <= '2012-06-21' and end_date >= '2012-06-21' を選択すると、このステートメントは次のレコードをクエリします:

注文作成日

注文番号

注文ステータス

dw_bigin_date

dw_end_date

2012-06- 20# 2012-06-20003支払い完了2012-06-202012 -06-212012-06-21004注文の作成2012-06-219999-12-312012-06-21005注文の作成2012-06- 212012-06-21 は、6 月 21 日のソーステーブルのレコードと完全に一致しています: 注文作成日注文番号注文ステータス

##2012-06-20	001	支払い完了	2012-06-21
002	注文の作成	2012-06-20	9999-12- 31

2012-06-20

001注文の作成注文の作成#

このような履歴ジッパーテーブルは、履歴データの需要を満たすだけでなく、ストレージリソースを大幅に節約できることがわかります。

ジッパーテーブルの例 2:

履歴テーブルには個人の人生の記録がわずかしかない場合があります。これにより、顧客のステータスを毎日記録することによって引き起こされる大量のストレージの問題が回避されます:

	#2012-06-20	002
2012-06-20	003	支払い完了
2012-06-21	004	注文を作成
2012-06-21	005	注文を作成

##クライアント19000101 19070901ハットホームクライアント1907090119130901小学校学校クライアント1913090119160901B中学校 client 1916090119190901C 高校client19190901 19230901D 大学クライアント1923090119601231E 会社client1960123129991231H 自宅で退職

Name個人の	開始日	終了日	ステータス

上記のすべての記録は、たとえば、19070901 までに、クライアントはすでに H ではなく A にいます。したがって、これまでステータスが変化していない最後のレコードを除いて、残りのレコードは実際にはレコードの終了日の状態にあり、レコードの終了日の状態ではなくなります。この現象は、終わりではなく始まりを数えていると理解できます。

ジップテーブルの実装方法

1. 2 つの一時テーブルを定義し、1 つはその日の完全なデータ用、もう 1 つは追加または更新する必要があるデータ用です。

2. その日の全データを取得します

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;

3. 新しいデータまたは変更されたデータを xxxx_NEW 一時テーブルから xxxx_CHG 一時テーブルに抽出します;

INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce;

4. end_date を更新します履歴テーブルの無効なレコードを最大値

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');

5にします。新しいデータまたは変更されたデータをターゲットテーブルに挿入します

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';

製品データを例に挙げます

製品テーブル t_product があります。テーブル構造は次のとおりです:

列名タイプ説明 #goods_idvarchar(50)製品番号##varchar(50)#商品ステータス (レビュー保留中、販売中、販売中、削除済み)製品作成日製品変更日2019年12月20日のデータは以下のとおりです。


		##goods_status
	createtime	varchar(50)
	modifytime	varchar(50)

goods_id

goods_status2019-12-202019-12-202019-12-202019-12-20##004削除されました2019-12-202019-12-20#12 月 20 日 (4 データ)

modifytime		001レビュー予定
002	販売中	2019-12-20
003	販売中	2019-12-20

製品のステータスは時間の経過とともに変化します。記録する必要があります。製品のすべての変更履歴情報が保存されます。	計画 1: 毎日のデータのスナップショットをデータウェアハウスに保存する	この計画では、完全なコピーを毎日保存し、すべてのデータをデータウェアハウスに同期します。多くのレコードが繰り返し保存されます。 . 、変更はありません。

##goods_id

goods_status

作成時間 002##2019-12-20販売中#2019-12 -202019-12-20004削除されました2019-12-15 2019-12-20

#修正時間 ## 001 レビュー予定 2019-12-182019-12-20

販売中 2019-12-19 ##003

12月21日 (10件のデータ)

以下は 12 個のスナップショットです3 月 20 日のデータ##001##2019-12-18##002# 2019-12-192019-12-20003発売中2019-12-20 2019-12-20004削除されました2019-12-152019 -12- 202019-12-18#002販売中2019-12-192019-12-20003発売中2019-12-202019-12-20 004削除005 (新規アイテム)006 (新製品)# # レビュー保留中2019-12-212019-12-21

12月22日（18条数据）

##goods_id	goods_status	createtime	modifytime

Toレビュー対象	#2019-12-20		#販売中


	#以下は 12 月 21 日のスナップショットデータです
	##001	販売中 (保留中から保留中)	2019-12 -21


	#2019-12-15	2019-12-20
予定レビュー済み	2019-12-21	2019-12-21

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: ジッパーテーブルを使用して履歴スナップショットを保存する

ジッパーテーブルは冗長データを保存しません。特定の

行のデータのみを保存する必要があります。毎回完全に同期する場合と比較して、ストレージスペースを節約できます。

履歴スナップショットをクエリできるようになります

2 つの列を追加しました (

dw_start_date、dw_end_date) 、データ行のライフサイクルです。

#12月20日の商品ジッパー一覧データ

##goods_id Goods_statuscreatetimemodifytimedw_start_date001今後レビュー予定##2019-12-18# 2019-12-202019-12-209999-12-31002販売中 2019-12-192019-12-202019-12-209999-12-31003発売中2019-12-202019-12-202019-12-209999-12-31004削除されました2019-12-152019-12 -202019-12-209999-12-31##12 月 20 日のデータは、dw テーブルへの新しいデータインポートですdw_end_date は、特定のデータのライフサイクル終了時刻、つまりデータがこの日に到達する日 (含まれていません) (つまり、有効期限)

				##dw_end_date



					dw_start_date は、特定のデータのライフサイクルの開始時間を示します。つまり、データはその時点から有効です (つまり、発効日)。

dw_end_date は 9999-12-31 です。これは、現在のデータは最新のデータであり、データは 9999 年 12 月 31 日まで有効期限が切れません。

##goods_id

goods_status

##createtime宛先レビューされる2019-12-202019-12-209999- 12-319999-12-31#2019-12-15 2019-12-202019-12-209999-12-31001（変更）2019-12-21 005 (新規)レビュー予定2019-12-21 2019 -12-21

#modifytime

dw_start_date

dw_end_date

001

2019-12-18

2019-12-21

# 002

販売中

2019-12-19

2019-12-202019-12-20

003

発売中

2019-12-20

004

削除されました

#販売中

#2019-12-18

#2019-12-21

9999-12-31

2019-12-21

9999-12- 31

ジッパーテーブルには冗長なデータは格納されていません。つまり、データが変更されない限り、同期する必要はありません。

番号 #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 日の商品ジッパーテーブルデータ

##goods_id##dw_start_datedw_end_date今後レビュー予定2019-12-18##002販売中2019-12-192019-12-202019-12-209999-12-31003発売中2019-12-202019-12-202019-12-222019 -12-20##001販売中2019-12-18##2019-12-21##9999-12-31#005保留中のレビュー2019-12-212019-12-212019-12 -21 9999-12-31006保留中のレビュー2019-12-212019-12-212019-12-219999-12-31##003 (変更)削除されました#2019-12-20##2019-12-22007 (新規)保留中のレビュー2019-12-222019-12-22008 (新規)保留中のレビュー2019-12-222019-12-22

#goods_status

##createtime

##modifytime

001

2019-12-20

2019-12-21

#2019-12- 20

004

削除されました2019-12-15

2019-12-20

9999-12-31

##2019-12-21

2019-12- 22

#9999-12-31

2019 -12-22

9999-12-31

2019-12-22

9999-12-31

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

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数仓拉链表快照实现

操作流程：

在原有dw层表上，添加额外的两列
只同步当天修改的数据到ods层
拉链表算法实现
拉链表的数据为：当天最新的数据 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	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 中国語 Web サイトの他の関連記事を参照してください。

声明：

この記事はcsdn.netで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：mysql ストアドプロシージャのループステートメントとは何ですか?次の記事：mysql ストアドプロシージャのループステートメントとは何ですか?

続きを見る

データベースのジッパーテーブルについて話しましょう

ジッパー テーブル生成の背景

1. その日の完全なデータを ND (NowDay) テーブルに収集します;

簡単な例として、たとえば注文テーブルがあります:

ジッパー テーブルの例 2:

goods_status

12月21日 (10件のデータ)

12月22日（18条数据）

MySQL数仓代码实现

#12月20日の商品ジッパー一覧データ

goods_status

#12 月 22 日の商品ジッパー テーブル データ

MySQL数仓拉链表快照实现

関連記事

ジッパーテーブル生成の背景

ジッパーテーブルの例 2:

#12 月 22 日の商品ジッパーテーブルデータ