コミュニティ 学ぶ ツールライブラリ レジャー
検索
日本語
ホームページ > データベース > mysql チュートリアル > 本文

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

WBOY
リリース： 2022-06-20 12:01:37
転載
2669 人が閲覧しました

この記事では、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 9999-12-31
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 データ)
##createtime 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 日まで有効期限が切れません。

    #12 月 21 日の製品ジッパー テーブル データ
  • ##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 0012019-12-18
2019-12-21 # 002 販売中 2019-12-19 2019-12-202019-12-20
003 発売中 2019-12-20 2019-12-20 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-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数仓拉链表快照实现

操作流程：

  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 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 サイトの他の関連記事を参照してください。

関連ラベル：
mysql
ソース：csdn.net
前の記事：mysql ストアド プロシージャのループ ステートメントとは何ですか? 次の記事：mysqlが内部コマンドではないという問題を解決するにはどうすればよいですか?
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
著者別の最新記事
最新の問題
結果セットから最小値のみを表示する方法 (MYSQL) 次のステートメントがあります: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewhere...
から 2024-04-06 21:44:19
0
1
603
MySQL SQLクエリを使用して別のテーブルのフィールドの合計を計算する 次のようなスキーマがあります。 属性「user_id」と「username」を持つユーザーテーブルと、属性「customer_id」（user_idのFK）と「finalPrice...
から 2024-04-06 19:39:29
0
1
441
MySQL: 「<ここの変数またはサブクエリ> として...からテキストを選択します」 次のデータを含む次のテーブルがあります: id text language 1 german text german 2 english text english 私が望むのは、次...
から 2024-04-06 19:25:54
0
2
529
MySQL でグループ化してカウントするにはどうすればよいですか? 友人に送信され、削除されていないメッセージの合計数を抽出するクエリを作成しようとしています。これはテーブル構造のスクリーンショットです。 達成したい出力は次のとおりです。 idme...
から 2024-04-06 18:30:17
0
1
353
MySQL は複数のテーブルからデータを取得します 次の列を含む eg_design テーブル、および次の列を含む eg_domains テーブル、および次の列を含む eg_fonts テーブルがあります。 $domain_id に...
から 2024-04-06 18:42:44
0
2
479
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート