suchen

Heim >Datenbank >MySQL-Tutorial >Lassen Sie uns über Datenbank-Zipper-Tabellen sprechen

Lassen Sie uns über Datenbank-Zipper-Tabellen sprechen

WBOY
WBOYnach vorne
2022-06-20 12:01:112742Durchsuche

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL, das hauptsächlich Probleme im Zusammenhang mit Datenbank-Zipper-Tabellen vorstellt. Zipper-Tabellen sind ein Datenmodell, hauptsächlich für die Art und Weise, wie Tabellen Daten im Data Warehouse-Design speichern -Zipper genannt, ist es, die Geschichte aufzuzeichnen. Ich hoffe, es wird für alle hilfreich sein.

Lassen Sie uns über Datenbank-Zipper-Tabellen sprechen

Empfohlenes Lernen: MySQL-Video-Tutorial

Hintergrund der Zipper-Tabellengenerierung

Im Datenmodellentwurfsprozess des Data Warehouse stoßen wir häufig auf folgende Anforderungen:

1. Vergleich des Datenvolumens Groß;

2. Einige Felder in der Tabelle werden aktualisiert, z. B. die Adresse des Benutzers, Produktbeschreibungsinformationen, Bestellstatus usw.; Sie müssen historische Schnappschussinformationen zu einem bestimmten Zeitpunkt anzeigen Überprüfen Sie beispielsweise den Status einer Bestellung zu einem bestimmten Zeitpunkt im Verlauf, überprüfen Sie beispielsweise, wie oft ein Benutzer in einem bestimmten Zeitraum in der Vergangenheit aktualisiert hat usw.; Der Anteil und Die Häufigkeit der Änderungen ist nicht sehr groß, zum Beispiel gibt es insgesamt 10 Millionen Mitglieder, und jeden Tag werden etwa 100.000 hinzugefügt und geändert.

5 Wenn eine vollständige Kopie dieser Tabelle beibehalten wird, bleiben viele unverändert Die Informationen werden jedes Mal im vollen Umfang gespeichert, was eine große Verschwendung darstellt.

Für diese Art von Tabelle gibt es mehrere Optionen:

Option 1: Bewahren Sie jeden Tag nur die neueste Kopie auf. Beispielsweise verwenden wir Sqoop, um jeden Tag die neueste vollständige Datenmenge in Hive zu extrahieren.

Option 2: Behalten Sie jeden Tag einen vollständigen Datenausschnitt.
  • Option 3: Verwenden Sie einen Reißverschlusstisch.
  • Vergleich der oben genannten Pläne

Plan 1

Selbstverständlich ist dieser Plan sehr einfach umzusetzen. Löschen Sie jeden Tag die Daten des Vortages und extrahieren Sie die neuesten erneut. Die Vorteile liegen auf der Hand, es spart Platz und ist auch für einige normale Anwendungen sehr praktisch. Bei der Auswahl eines Tisches muss keine Zeitpartition hinzugefügt werden.

Die Mängel liegen auch auf der Hand. Es gibt keine historischen Daten. Die einzige Möglichkeit, die alten Konten zuerst zu überprüfen, besteht darin, aus dem Flussdiagramm zu ziehen.

Plan 2

Eine volle Portion Scheiben jeden Tag ist ein relativ sicherer Plan, und die historischen Daten sind vorhanden. Der Nachteil besteht darin, dass es zu viel Speicherplatz beansprucht. Wenn jeden Tag eine vollständige Kopie dieser Tabelle aufbewahrt wird, werden in jeder vollständigen Kopie viele unveränderte Informationen gespeichert, was meiner Meinung nach eine enorme Speicherverschwendung darstellt ist Das Gefühl ist immer noch sehr tief...

Natürlich können wir auch einige Kompromisse eingehen, z. B. nur die Daten des letzten Monats behalten? Die Nachfrage ist jedoch schamlos und der Lebenszyklus von Daten ist nicht etwas, das wir vollständig kontrollieren können.

Reißverschlussuhr

Die Reißverschlussuhr berücksichtigt grundsätzlich unsere Bedürfnisse im Einsatz. Zuallererst gibt es einen Kompromiss hinsichtlich des Platzbedarfs. Obwohl es nicht so wenig Platz einnimmt wie die erste Option, kann sein täglicher Zuwachs nur ein Tausendstel oder sogar ein Zehntausendstel der zweiten Option betragen.

Tatsächlich kann es die Anforderungen erfüllen, die Option 2 erfüllen kann. Es kann nicht nur die neuesten Daten abrufen, sondern auch Filterbedingungen hinzufügen und historische Daten abrufen.

Daher ist es für uns weiterhin notwendig, Reißverschlusstische zu verwenden.

Zipper-Tabellenkonzept

Zipper-Tabelle ist ein Datenmodell, das hauptsächlich für die Art und Weise definiert ist, wie Tabellen Daten im Data Warehouse-Design speichern. Wie der Name schon sagt, dient der sogenannte Zipper dazu, den Verlauf aufzuzeichnen. Zeichnen Sie Informationen über alle Änderungen an einer Sache vom Anfang bis zum aktuellen Zustand auf. Zipper-Tabellen können das massive Speicherproblem vermeiden, das durch die Speicherung aller Datensätze für jeden Tag entsteht, und sind auch eine gängige Methode für den Umgang mit sich langsam ändernden Daten (SCD2).

Erklärung aus der Baidu-Enzyklopädie: Eine Zipper-Tabelle ist eine Tabelle, die den historischen Status und die neuesten Statusdaten verwaltet. Abhängig von der Zipper-Granularität entspricht die Zipper-Tabelle tatsächlich einem Snapshot, wurde jedoch optimiert und einige unveränderte Datensätze entfernt. Die Kundendaten zum Zeitpunkt des Reißverschlusses können über die Reißverschlusstabelle einfach wiederhergestellt werden.

Zipper-Tabellenalgorithmus

1. Die vollständigen Daten des Tages können aus der Verlaufstabelle entnommen und in der OD-Tabelle (OldDay) gespeichert werden

3. Zwei Tabellen werden in allen Feldern verglichen, (ND-OD) sind die neuen und geänderten Daten des Tages, das heißt, das Inkrement des Tages, dargestellt durch W_I

4 , (OD-ND) ist der Status. Zu diesem Zeitpunkt müssen die Daten geändert werden, die durch W_U dargestellt werden.

5 Fügen Sie den gesamten Inhalt der W_I-Tabelle in die Verlaufstabelle ein Datensätze, start_date ist der aktuelle Tag und end_date ist der maximale Wert. Sie können den W_U-Teil der Verlaufstabelle aktualisieren Zum aktuellen Tag, bei dem es sich um eine Verknüpfungsoperation handelt, werden die Verlaufstabelle (OD) und die W_U-Tabelle verglichen, mit Ausnahme von START_DATE und END_DATE, die auf der W_U-Tabelle basieren dass der Datensatz ungültig ist.

Zipper-Tabelle Beispiel 1

Nehmen Sie ein einfaches Beispiel, zum Beispiel gibt es eine Bestelltabelle:

Am 20. Juni gibt es 3 Datensätze:

20.06.2012 Bestellstatus 001
Erstellungsdatum der Bestellung Bestellnummer Bestellstatus
20.06.2012 001 Bestellung. erstellt
2012-06 -20 002 Bestellung erstellen
20.06.2012
Bestellung erstellen

20.06.2012Zahlung abgeschlossen 21.06.2012 in der Tabelle Datensatz: Erstellungsdatum der BestellungBestellnummerBestellstatus20.06.2012001Bestellung erstellt20.06.2012. 002Bestellung erstellen20.06.2012003Zahlung abgeschlossen21.06.2012004
002 Bestellen -20 003

Bestellung erstellen

21.06.2012005Bestellung erstellen 22.06.2012006Auftrag erstellenDie Methode zum Aufbewahren dieser Tabelle im Data Warehouse: Datum der Auftragserstellung BestellnummerBestellstatusdw_bigin_datedw_end_date2012-06 -20001 Bestellung erstellen2012-06-202012-06-20001Zahlung. abgeschlossen2012-06-219999-12-31
.
1 Behalten Sie nur eine Kopie des gesamten Betrags bei, dann sind die Daten mit denen identisch Datensatz am 22. Juni. Wenn Sie den Status der Bestellung 001 am 21. Juni überprüfen müssen, kann dieser nicht erfüllt werden 2 Wenn täglich eine vollständige Kopie aufbewahrt wird, enthält die Tabelle im Data Warehouse insgesamt 14 Datensätze , aber viele Datensätze werden wiederholt gespeichert und es gibt keine Aufgabenänderung. Beispielsweise enthält die Bestellung 002.004 eine große Datenmenge, was zu viel Speicherverschwendung führt. Wenn die Tabelle als historische Reißverschlusstabelle gespeichert werden soll Im Data Warehouse gibt es eine Tabelle wie die folgende:

20.06.2012

002

Bestellung erstellen

20.06.20129999-12-3120.06.2012003 Zahlung abgeschlossen2012-06 - 20.06.2012 012-06-21004 Bestellung erstellen21.06.20129999-12-31 Bestellung erstellenZahlung abgeschlossen Bestellung erstellen Beschreibung: 1, dw_begin_date bedeutet die Lebenszyklus-Startzeit des Datensatzes, dw_end_date gibt die Lebenszyklus-Endzeit des Datensatzes an; 2. 12-31‘ zeigt an, dass sich der Datensatz derzeit in einem gültigen Zustand befindet Fragen Sie den historischen Schnappschuss vom 21.06.2012 ab und wählen Sie dann * aus order_his aus, wobei dw_begin_date <= '2012-06-21' und end_date >= '2012-06-21' sind. Diese Anweisung fragt die folgenden Datensätze ab: dw_bigin_dateZahlung abgeschlossen Auftrag erstellen Zahlung abgeschlossenBestellung erstellen
21.06.2012 005
21.06.2012 21.06.2012 21.06.2012 005
22.06.2012 9999-12-31 22.06.2012 006
2012-06- 22 9999-12-31
Erstellungsdatum der Bestellung Bestellnummer Bestellstatus
dw_end_date 20.06.2012 001
21.06.2012 9999- 31.12. 20.06.2012 002
20.06.2012 9999-12-31 20.06.2012 003
20.06.2012 21.06.2012 20 21.06.12 004
21.06.2012

9999-12-31

2012- 21.06.

005

Auftrag erstellen21.06.2012 ist genau derselbe wie der Datensatz in der Quelltabelle am 21. Juni: Erstellungsdatum der BestellungBestellnummerBestellung erstellen 2012 -06-20Bestellung erstellen

Es ist ersichtlich, dass eine solche historische Zipper-Tabelle nicht nur den Bedarf an historischen Daten decken kann, sondern auch in hohem Maße Speicherressourcen einspart Verlaufstabelle Nur ein paar Datensätze wie dieser vermeiden das Problem der massiven Speicherung, die durch die tägliche Aufzeichnung des Kundenstatus entsteht:

Name
20 21.06.12
Bestellstatus 20.06.2012 001
20.06.2012 002 Auftrag erstellen
003 Zahlung abgeschlossen 21.06.2012 004
21.06.2012 005 Bestellung erstellen
StartdatumKunde 190001011907090119130901 19160901 1923090119601231E Unternehmen Kunde1960123129991231 H ist zu Hause im RuhestandJeder Datensatz oben befindet sich beispielsweise nicht am Ende von 19070901, der Kunde befindet sich bereits in A, nicht in H. Mit Ausnahme des letzten Datensatzes, dessen Status sich bisher nicht geändert hat, befinden sich die anderen Datensätze daher tatsächlich in dem Status zum Enddatum des Datensatzes und nicht mehr in dem Status zum Enddatum des Datensatzes. Dieses Phänomen kann so verstanden werden, dass es den Anfang, aber nicht das Ende zählt. 
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;
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');
4. Aktualisieren Sie das Enddatum des ungültigen Datensatzes auf den Maximalwert Daten in die Zieltabelle geändert. Beschreibunggoods_idvarchar(50) Produktnummer
Enddatum Status
19070901 H zu Hause Kunde
19130901 Eine Grundschule Kunde
1 9160901 B Junior High School Kunde
Implementierungsmethode der Zipper-Tabelle 1 Definieren Sie zwei temporäre Tabellen, eine enthält die vollständigen Daten des Tages und die andere enthält die Daten, die hinzugefügt oder aktualisiert werden müssen. 2 Tag 3. Extrahieren Sie die neuen oder geänderten Daten aus der temporären Tabelle xxxx_CHG.

goods_status

varchar(50)

Produktstatus (Überprüfung ausstehend, ausstehend, im Verkauf, gelöscht)

Erstellungszeit

varchar (50)

waren_id

waren_status 001Ausstehend. Überprüfung2019-12-2 020.12.2019002Für Ausverkauf20.12.201920.12.2019003Im Ausverkauf20.12.201920.12.2019004 Gelöscht2019- 20.12.20.12.2019
erstellenzeit ändernzeit

Der Status des Produkts ändert sich im Laufe der Zeit und wir müssen die historischen Informationen aller Änderungen am Produkt speichern. 0012019 -12- 18002zu verkaufen19.12.201920.12.2019003zu verkaufen20.12.2019 2019 -12-20004Gelöscht2019-12-152019-12-2021. Dezember (10 Daten)
Plan 1: Snapshot der Daten jedes Tages im Data WarehouseDieser Plan sieht vor, jeden Tag eine vollständige Kopie zu speichern und alle Daten mit dem Data Warehouse zu synchronisieren. Viele Datensätze werden wiederholt ohne Änderungen gespeichert. 20. Dezember (4 Daten) Überprüfung ausstehend20.12.2019
Das Folgende sind die Schnappschussdaten vom 20. Dezember2019-12-202019-12-1920.12.2019 Das Folgende sind die Snapshot-Daten vom 21. Dezember 9-12-212019-12-20. 2019 -12-2020.12.20192019-21 006 ( neuer Artikel)21.12.2019
12月22日（18条数据）
001 Überprüfung ausstehend2019-12-18
002 Zu verkaufen

2019 -12 -20
002 zu verkaufen 2019-12-19
003 zu verkaufen 2019-12-20
004 Gelöscht 15.12.2019
005 (neuer Artikel) Überprüfung ausstehend 21.12.2019

Überprüfung ausstehend		 21.12.2019
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

Aus dem obigen Fall können wir ersehen, dass die Tabelle jeden Tag eine vollständige Menge behält und in jeder vollständigen Menge viele unveränderte Informationen gespeichert werden. Wenn die Datenmenge groß ist, ist dies eine enorme Speicherverschwendung. Die Tabelle kann so gestaltet werden, dass die Reißverschlusstabelle nicht nur den historischen Status der Daten widerspiegelt, sondern auch maximal Speicherplatz spart.

Option 2: Verwenden Sie die Zipper-Tabelle, um historische Schnappschüsse zu speichern. Die Zip-Tabelle speichert keine redundanten Daten. Es müssen nur die Daten einer bestimmten -Zeile gespeichert werden, was im Vergleich zur vollständigen Synchronisierung jedes Mal Geld spart Zeit Speicherplatz

kann historische Snapshots abfragen

行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间

能够查询到历史快照

额外的增加了两列（dw_start_date、dw_end_dateZwei zusätzliche Spalten hinzugefügt (dw_start_date, dw_end_date), die den Lebenszyklus von Datenzeilen darstellen. 🔜

dw_start_date
dw_end_date2019-12-209999-12-31 dw_start_date stellt die Startzeit des Lebenszyklus eines bestimmten Datenelements dar, d. h. die Daten sind ab diesem Zeitpunkt (d. h. dem Datum des Inkrafttretens) gültig dw_end_date stellt die Startzeit eines bestimmten Datenelements dar. Die Endzeit des Lebenszyklus, dh die Daten erreichen diesen Tag (nicht enthalten) (dh das Ablaufdatum)dw_end_date ist 9999-12-31, Dies bedeutet, dass es sich bei den aktuellen Daten um die neuesten Daten handelt und die Daten erst am 31.12.9999 ablaufen. Daten der Produkt-Zip-Liste am 21. Dezember createtimemodifytimedw_start_datedw_end_dateÜberprüfung ausstehend 21.12.2019002
001 2019-12- 20.12.2019 20.12.2019 2019-12 -20 9999-12-31 004deleted 2019-12-152019-12-20
Bei den Daten vom 20. Dezember handelt es sich um brandneue Daten, die in die DW-Tabelle importiert wurden
001
2019-12-18 2019-12-20 2019-12- 20
Zu verkaufen

12. -19.12.2019
  • 20.12.2019
  • 9 999-12-31
003
2019-12-15
20.12.201920.12.20199999-12-31005 (Neu)Überprüfung ausstehend 21.12.201921.12.2019 9999-12-31

In der Reißverschlusstabelle sind keine redundanten Daten gespeichert, d. h. solange sich die Daten nicht ändern, ist keine Synchronisierung erforderlich.

  • Der Status der Produktdaten mit der Nummer 001 hat sich geändert (von „Überprüfung ausstehend“ → „zu verkaufen“) ), und das ursprüngliche dw_end_date muss von 9999 -12-31 Änderungen auf 2019-12-21 geändert werden, was den ausstehenden Überprüfungsstatus angibt, der vom 20.12.2019 (einschließlich) bis zum 21.12.2019 gültig ist (exklusiv);
  • 001 Nummer wird mit neuem Status Datensätze gespeichert, dw_start_date ist 21.12.2019, dw_end_date ist 9999/12/31;
  • neue Daten 005, 006, dw_start_date ist 2019/12/ 21, dw_end_date ist 9999/12/31.
Daten der Produkt-Zip-Liste am 22. Dezember
9999-12 -31 001 (Änderung) Zum Verkauf 1.12.2019 8 2019- 21.12. 21.12.2019
21.12.2019
Zu verkaufen2019-12 - 20.12.201920.12.2019 2019- 12 -20. 9999-12-31001 21.12.2019 21. Dezember 2019 2019- 12- 219999-12-31 9999-12-31 -222019 -12 -22 22.12.0192019 -12-229999-12-31
goods_id goods_status createtime modifytime. dw_start_date dw_end_date
001
003 (Änderung)
Gelöscht 20.12.2019 22.12.2019 2019-12-22

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

  • 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视频教程

Das obige ist der detaillierte Inhalt vonLassen Sie uns über Datenbank-Zipper-Tabellen sprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!

Stellungnahme：
Dieser Artikel ist reproduziert unter:csdn.net. Bei Verstößen wenden Sie sich bitte an admin@php.cn löschen
Vorheriger Artikel：Was sind die Schleifenanweisungen in gespeicherten MySQL-Prozeduren?Nächster Artikel：Was sind die Schleifenanweisungen in gespeicherten MySQL-Prozeduren?

In Verbindung stehende Artikel

Mehr sehen