Parlons des tables de fermeture éclair de la base de données

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les problèmes liés aux tables zippées de base de données. Les tables Zipper sont un modèle de données, principalement pour la façon dont les tables stockent les données dans la définition de l'entrepôt de données, comme son nom l'indique. -appelé fermeture éclair est pour enregistrer l'historique. Jetons-y un coup d'œil, j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

Contexte de la génération de tables de fermeture éclair

Dans le processus de conception de modèles de données d'entrepôt de données, nous rencontrons souvent les besoins suivants :

1. Grand ;

2. Certains champs du tableau seront mis à jour, tels que l'adresse de l'utilisateur, les informations de description du produit, l'état de la commande, etc. ; période, comme , vérifier l'état d'une commande à un moment donné de l'historique, par exemple, vérifier combien de fois un utilisateur a mis à jour au cours d'une certaine période de temps dans le passé, etc.

4. la fréquence des changements n'est pas très importante, par exemple, le total Il y a 10 millions de membres, et environ 100 000 sont ajoutés et modifiés chaque jour

5 Si une copie complète de ce tableau est conservée chaque jour, alors beaucoup de choses restent inchangées ; les informations seront enregistrées dans leur intégralité à chaque fois, ce qui est très difficile à stocker. Un énorme gaspillage

Il existe plusieurs options pour ce type de tableau :

Option 1 : Ne conserver que la dernière copie chaque jour ; Par exemple, nous utilisons Sqoop pour extraire chaque jour la dernière quantité complète de données dans Hive.

• Option 2 : Conservez une tranche complète de données chaque jour.
• Option 3 : Utilisez une table à fermeture éclair.

Comparaison des plans ci-dessus

Plan 1

Inutile de dire que ce plan est très simple à mettre en œuvre. Chaque jour, supprimez les données de la veille et réextraites la dernière.

Les avantages sont évidents, cela permet d'économiser de l'espace, et c'est également très pratique pour certaines utilisations ordinaires. Il n'est pas nécessaire d'ajouter une partition de temps lors de la sélection d'une table.

Les lacunes sont également évidentes. Il n'y a pas de données historiques. La seule façon de vérifier d'abord les anciens comptes est d'utiliser d'autres méthodes, telles que l'extraction du schéma de flux.

Plan 2

Une portion complète de tranches chaque jour est un plan relativement sûr, et les données historiques sont là.

L'inconvénient est que cela prend trop d'espace de stockage. Si une copie complète de ce tableau est conservée chaque jour, de nombreuses informations inchangées seront enregistrées dans chaque copie complète, ce qui représente un énorme gaspillage de stockage, je pense. est Le sentiment est encore très profond...

Bien sûr, nous pouvons aussi faire certains compromis, comme ne conserver que les données du mois dernier ? Cependant, la demande est éhontée et le cycle de vie des données n’est pas quelque chose que nous pouvons totalement contrôler.

Montre à fermeture éclair

La montre à fermeture éclair prend essentiellement en compte nos besoins d'utilisation.

Tout d'abord, il fait un compromis en termes d'espace. Bien qu'il n'occupe pas un espace aussi petit que le plan 1, son incrément quotidien peut n'être qu'un millième voire un dix millième de celui du plan 2.

En fait, il peut répondre aux besoins auxquels l'option 2 peut répondre. Il peut non seulement obtenir les dernières données, mais également ajouter des conditions de filtrage et obtenir des données historiques.

Il nous faut donc toujours utiliser des tables à fermeture éclair.

Concept de table Zipper

La table Zipper est un modèle de données, qui est principalement défini pour la manière dont les tables stockent les données dans la conception d'un entrepôt de données. Comme son nom l'indique, la fermeture éclair consiste à enregistrer l'historique. Enregistrez des informations sur tous les changements survenus dans une chose depuis son début jusqu'à son état actuel. Les tables Zipper peuvent éviter le problème de stockage massif causé par le stockage de tous les enregistrements de chaque jour et constituent également un moyen courant de gérer des données qui évoluent lentement (SCD2).

Explication de l'Encyclopédie Baidu : Une table de fermeture à glissière est une table qui conserve l'état historique et les dernières données d'état. En fonction de la granularité de la fermeture à glissière, la table de fermeture à glissière est en fait équivalente à un instantané, mais elle a été optimisée et a supprimé certains enregistrements inchangés. les enregistrements client au moment de la fermeture éclair peuvent être facilement restaurés via la table de fermeture éclair.

Algorithme de table Zipper

1. Collectez les données complètes de la journée dans la table ND (NowDay)

2 Les données complètes d'hier peuvent être extraites de la table historique et stockées dans la table OD (OldDay) ;

3. Deux tables sont comparées dans tous les champs, (ND-OD) correspondent aux données nouvelles et modifiées du jour, c'est-à-dire l'incrément du jour, représenté par W_I ;

4. , (OD-ND) est le statut À ce stade, les données qui doivent être bloquées doivent être modifiées END_DATE, représentées par W_U ;

5 Insérez tout le contenu de la table W_I dans la table historique. enregistrements, start_date est le jour actuel et end_date est la valeur maximale. Vous pouvez définir sur '9999-12-31' ; Mettre à jour la partie W_U de la table d'historique reste inchangée et la date de fin est modifiée. au jour actuel, qui est une opération de lien, la table d'historique (OD) et la table W_U. Comparez, sauf START_DATE, END_DATE, qui est basée sur la table W_U. L'intersection des deux changera END_DATE en jour actuel, indiquant. que le dossier n'est pas valide.

Exemple de table Zipper 1

Prenons un exemple simple, par exemple, il y a une table de commande :

Il y a 3 enregistrements le 20 juin :

Date de création de la commande	Numéro de commande	Statut de la commande
2012-06-20	001	Commande créée
2012-06- 20	002	Créer une commande
20/06/2012	003	Paiement effectué

Au 21 juin, il y a 5 enregistrements dans le tableau :

Date de création de la commande	Numéro de commande	Statut de la commande
2012-06-20	001	Créer une commande
2012-06-20	002	Créer une commande
2012-06 - 20	003	paiement effectué
2012-06-21	004	Créer une commande
2012-06-21	005	Créer une commande

Au 22 juin, il y a 6 articles dans le tableau Enregistrement :

Date de création de la commande	Numéro de commande	Statut de la commande
20/06/2012	001	Commande créée
20/06/2012	002	Créer une commande
20/06/2012	003	Paiement effectué
21/06/2012	004	Créer une commande
21/06/2012	005	Créer une commande
2012-06-22	006	Créer une commande

La méthode de conservation de ce tableau dans l'entrepôt de données :

1. Ne conservez qu'une seule copie complète, les données seront alors les mêmes que l'enregistrement de juin. 22. Si vous devez vérifier l'état de la commande 001 le 21 juin, elle ne peut pas être satisfaite

2. Si une copie complète est conservée chaque jour, le tableau de l'entrepôt de données contient un total de 14 enregistrements, mais plusieurs ; les enregistrements sont enregistrés à plusieurs reprises et il n'y a aucun changement de tâche. Par exemple, la commande 002 004 contient une grande quantité de données, ce qui entraînera beaucoup de gaspillage de stockage

Si le tableau est conçu pour être enregistré en tant que tableau de fermeture éclair historique dans les données ; entrepôt, il y aura un tableau comme le suivant :

Date de création de la commande	Numéro de commande	Statut de la commande	dw_bigin_date	dw_end_date
20/06/2012	001	Créer commande	2012-06-2 0	2012-06-20
2012-06-20	001	Paiement effectué	2012-06-21	9999-12-31
20/06/2012	002	Créer une commande	20/06/2012	31/12/9999
20/06/2012	003	Paiement effectué	20/06/2012	2012-06-21
2012-06- 20	003	Expédié	2012-06-22	9999-12-31
2012-06- 21	004	Créer commande	2012-06-21	9999-12-31
2012-06-21	005	Créer une commande	2012-06-21	2012-06-2 1
2012 -06-21	005	Paiement effectué	2012-06-22	9999-12-31
2012-06-22	006	Créer une commande	2012-06-22	9999-12-31

Description :

1, dw_begin_date signifie l'heure de début du cycle de vie de l'enregistrement, dw_end_date indique l'heure de fin du cycle de vie de l'enregistrement

2 dw_end_date = '9999-12-31 ; ' indique que l'enregistrement est actuellement dans un état valide ;

3. Si vous interrogez tous les enregistrements valides actuels, sélectionnez * from order_his où dw_end_date = '9999-12-31' ; 2012-06-21, puis sélectionnez * dans order_his où dw_begin_date <= '2012-06- 21' et end_date >= '2012-06-21', cette instruction interrogera les enregistrements suivants :

order date de création2012-06-2020/06/201220/06/2012🜜 Paiement terminé2012-06- 202012-06-2120 12-06-21004 Créer une commande2012-06-219999-12-312012-06-21 005Créer une commande2012-06-2120 12-06-21 est exactement le même que l'enregistrement dans la table source le 21 juin :

numéro de commande	état de la commande	dw_bigin_date	dw_end_date
001	Paiement terminé	2012-06 -21	9999-12-31
002	Créer une commande	20/06/2012- 20	9999-12-31
003

Date de création de la commande Numéro de commandeStatut de la commande20/06/2012001Créer une commande 20/06/2012002Créer une commande 20/06/2012003Paiement effectué2012-06-21 004Créer une commande2012-06-21005Créer une commande

On peut voir qu'une telle table à glissière historique peut non seulement répondre à la demande de données historiques, mais également économiser dans une large mesure les ressources de stockage

Exemple de table à glissière 2 :

Il est possible d'enregistrer la vie d'une personne dans le table d'historique Quelques enregistrements comme celui-ci évitent le problème de stockage massif causé par l'enregistrement quotidien du statut du client :

19070901H à la maisonclient1907090119130901Une école primaireclient1913090 11 9160901École secondaire Bclient19160901 19190901C Lycée client1919090119230901D Université client1923090 119601231E Société client1960123129991231H est retraité à la maison

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;

Prenons les données du produit comme exemple

	Chaque enregistrement ci-dessus n'est pas à la fin. Par exemple, au 19070901, le client est déjà en A, pas en H. Ainsi, à l'exception du dernier enregistrement, dont le statut n'a pas changé jusqu'à présent, le reste des enregistrements est effectivement dans l'état à la date de fin de l'enregistrement et ne est plus dans l'état à la date de fin de l'enregistrement. Ce phénomène peut être compris comme le fait de compter le début mais pas la fin.	Méthode d'implémentation de la table zippée	1 Définissez deux tables temporaires, l'une contient les données complètes de la journée et l'autre les données qui doivent être ajoutées ou mises à jour
3. Extrayez les données nouvelles ou modifiées, de la table temporaire xxxx_NEW vers la table temporaire xxxx_CHG ;	4 Mettez à jour la date de fin de l'enregistrement invalide dans la table historique à la valeur maximale	5. les données modifiées dans la table cible

Il existe une table de produits t_product, la structure de la table est la suivante :

Nom de la colonne

Type

Description99

createtime

varchar(50)

Date de création du produitvarchar(50)001En attente d'examen 2019-12-202019-12-20002À vendre

modifytime	date de modification de l'article	Les données au 20 décembre 2019 sont les suivantes :
goods_id		goods_status
createtime		modifytime

2019-12-20

2019-12-20En vente2019-12-20004 2019-12-202019-12-2020 décembre (4 données)modifytime001en attente d'examen2019 -12- 18

003	2019-12-20		Supprimé
	Le statut du produit changera avec le temps et nous devons enregistrer les informations historiques de toutes les modifications apportées au produit.	Plan 1 : instantané les données de chaque jour dans l'entrepôt de données	Ce plan consiste à enregistrer une copie complète chaque jour et à synchroniser toutes les données avec l'entrepôt de données. De nombreux enregistrements sont enregistrés à plusieurs reprises sans aucune modification.
			goods_id
goods_status		createtime

20/12/2019

002

à vendre19/12/2019

20/12/2019à vendre2019 -12-200042019-12-152019-12-20

21 décembre (10 données)

003	2019-12-20		Supprimé

goods_id	goods_status	createtime	modifytime
Voici les données instantanées du 20 décembre
001	En attente d'examen	2019-12-18	2019-12-20
002	À vendre	2019-12-19	2019 -12 -20
003	En vente	2019-12-20	2019-12-20
004	Supprimé	2019-12-15	2019-12-20
Voici les données instantanées du 21 décembre
001	En attente de vente (d'en attente à en attente)	2019-12-18	2019- 12 -21
002	à vendre	2019-12-19	2019-12-20
003	à vendre	2019-12-20	2019- 12-20
004	Supprimé	2019-12-15	2019-12-20
005 (nouvel article)	en attente d'examen	2019-12-21	2019- 12-21
006 ( nouvel article)	en attente d'examen	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

D'après le cas ci-dessus, nous pouvons voir que la table conserve un montant total chaque jour et que de nombreuses informations inchangées sont enregistrées dans chaque montant total Si la quantité de données est importante, c'est un énorme gaspillage de stockage. Le tableau peut être conçu comme Le tableau à fermeture éclair peut non seulement refléter l'état historique des données, mais également économiser au maximum l'espace de stockage.

Option 2 : Utilisez la table Zipper pour enregistrer des instantanés historiques

La table Zip ne stocke pas les données redondantes. Seules les données d'une certaine ligne doivent être enregistrées, ce qui permettra d'économiser de l'argent par rapport à une synchronisation complète à chaque fois. time L'espace de stockage行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间

能够查询到历史快照

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

peut interroger des instantanés historiques

Ajout de deux colonnes supplémentaires (dw_start_date, dw_end_date), qui représentent le cycle de vie des lignes de données.

Données de la liste zip des produits le 20 décembredw_end_date001En attente d'examen2019-12-182019-12-202019-12-209999-12-31002En attente À vendre 2019-12- 192019-12-202019-12-209999-12-31003en vente2019-12-202019-12-20 2019-12 -209999-12-31004Supprimé2019-12-152019-12-202019-12-209999-12-31

	goods_id	goods_status	createtime	modifytime	dw_start_date

• Les données du 20 décembre sont de toutes nouvelles données importées dans la table dw
• dw_start_date représente l'heure de début du cycle de vie d'une certaine donnée, c'est-à-dire que les données sont valides à partir de cette heure (c'est-à-dire la date d'entrée en vigueur)
dw_end_date représente l'heure de début d'une certaine donnée. L'heure de fin du cycle de vie, c'est-à-dire que les données atteignent ce jour (non inclus) (c'est-à-dire la date d'expiration)

dw_end_date est 9999-12-31, ce qui signifie que les données actuelles sont les dernières données et qu'elles n'expireront pas avant le 9999-12-31

Données de la liste zip des produits le 21 décembredw_end_date001En attente d'examen2019-12-182019-12-202019-12-21002À vendre2019-12 -192019-12-202019-12-2099 99-12-31003en vente20-12-201920-12-2019 20-12-20199999-31-12004Supprimé 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 (change)	À vendre	2019-12-18	2019- 12-21	2019-12-21
	005 (Nouveau)	En attente d'examen	2019-12-21	2019-12-21	2019-12-21

🎜🎜🎜

Il n'y a pas de données redondantes stockées dans la table de fermeture éclair, c'est-à-dire que tant que les données ne changent pas, il n'est pas nécessaire de synchroniser

• Le statut des données du produit numérotées 001 a changé (depuis en attente de révision → pour. vente), et la dw_end_date d'origine doit être modifiée de 9999 -12-31 à 2019-12-21, indiquant le statut de révision en attente, qui est valable du 20/12/2019 (inclus) au 12/2019/ 21 (exclusif);
• le numéro 001 est réenregistré avec un nouveau statut Records, dw_start_date est le 2019/12/21, dw_end_date est le 9999/12/31
• nouvelles données 005, 006, dw_start_date est le 2019/12 ; /21, dw_end_date est le 31/12/9999.

Données de la liste zip des produits le 22 décembre

À vendre2019-12 - 192019-12-202019-12-209999-12-31003en vente2019-12-202019-12-20 2019- 12 -20004Supprimé15/12/201920/12/201920/12/2019 9 999-12-31001À vendre2019-12-182019-12-212019-12-219999-12-31005En attente d'évaluation 2019-12-21 2019-12-21 2019- 12- 219999-12-31003 (change)008 (nouveau)En attente d'examen2019-12-22

goods_id

goods_status

createtime

modifytime

dw_start_date

dw_end_date

001

En attente d'examen

2019-12-18

2019-12-20

2019-12-20

2019-12-21

002

22/12/2019

Supprimé

2019-12-20

2019-12-22

2019-12-22 9999-12-31 2

2019 -12 -22

9999-12-31

20 19-12-22

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!