Synchroniser automatiquement vos tables OLTP et OLAP dans Actian X (Ingres 11)

L'une des principales caractéristiques de la dernière mise à jour de notre base de données hybride Actian X (Ingres 11) est l'inclusion du moteur analytique x100 et la support des tables x100. L'ajout d'un moteur analytique de classe mondiale à la capacité OLTP(OnLine Transaction Processing) d'Actian X permet de créer une nouvelle catégorie d'applications qui peuvent mélanger des requêtes OLTP et analytiques - chaque requête étant dirigée vers le moteur d'exécution approprié.

Les ingénieurs d'Actian viennent de livrer une amélioration d'Actian X qui ajoute la support des références aux tables X100 dans les procédures de base de données. Cela permet d'utiliser des procédures et des règles de base de données pour déclencher des mises à jour, des insertions et des suppressions dans les tables X100 lorsque des insertions, des mises à jour et des suppressions sont appliquées aux tables Ingres. Cela signifie que vous pouvez synchroniser automatiquement vos tables OLTP (Ingres) et OLAP (X100), en ajoutant de nouvelles données transactionnelles à vos données analytiques.

L'exemple suivant vous guidera dans la création de deux tables (airport et airport_X100) et dans la création des procédures et règles de base de données qui reflèteront les insertions, les mises à jour et les suppressions de la table Ingres vers la table X100. Jetez-y un coup d'œil et dites-nous ce que vous en pensez !

Cette amélioration est fournie par le biais d'un correctif qui peut être téléchargé à partir de esd.actian.com ici.

Créez une table OLTP (aéroport) et une cible OLAP (traitement analytique en ligne) (aéroport_x100) :

CREATE TABLE airport ( ap_id INT, ap_iatacode NCHAR(3) NOT NULL, ap_place NVARCHAR(30), ap_name NVARCHAR(50), ap_ccode NCHAR(2))g
commit ; g
CREATE TABLE airport_x100 ( ap_id INT, ap_iatacode NCHAR(3) NOT NULL, ap_place NVARCHAR(30), ap_name NVARCHAR(50), ap_ccode NCHAR(2)) with structure=x100g
commitg

Tout d'abord, nous allons créer une procédure qui peut être utilisée pour mettre en miroir les insertions de l'aéroport dans airport_x100 :

CREATE PROCEDURE proc_ins_airport_x100 (apid INT NOT NULL NOT DEFAULT, apiatacode NCHAR(3) NOT NULL NOT DEFAULT, applace NVARCHAR(30) NOT NULL NOT DEFAULT, 
     apname NVARCHAR(50) NOT NULL NOT DEFAULT, apccode NCHAR(2) NOT NULL NOT DEFAULT)
     AS BEGIN
          INSERT INTO airport_x100 ( ap_id, ap_iatacode, ap_place, ap_name, ap_ccode)
          VALUES ( :apid, :apiatacode, :applace, :apname, :apccode) ;
     ENDg

Nous créons maintenant une règle qui se déclenchera automatiquement lors d'une insertion dans l'aéroport et lancera la procédure proc_ins_airport_x100 avec chaque paramètre contenant les valeurs des colonnes qui faisaient partie de l'insertion dans l'aéroport :

CREATE RULE trg_ins_airport_x100 AFTER INSERT INTO airport
     EXECUTE PROCEDURE proc_ins_airport_x100 (apid = new.ap_id, apiatacode = new.ap_iatacode, applace = new.ap_place, apname = new.ap_name, apccode = new.ap_ccode)g

Nous insérons 4 lignes dans la table OLTP airport :

INSERT INTO airport VALUES (50000, 'AAA', 'Vector', 'Aomori', 'AU')g
INSERT INTO airport VALUES (50001, 'AA1', 'Vector', 'Tegel', 'AU')g
INSERT INTO airport VALUES (50002, 'AA2', 'Vector', 'Tegel', 'NL')g
INSERT INTO airport VALUES (50003, 'AA3', 'Vector', 'Tegel', 'NL')g

Nous voyons maintenant si notre table OLAP cible (airport_x100) a été mise à jour :

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 50000|AAA |Vecteur |Aomori |AU |Vecteur |Aomori |AU |Vecteur |Aomori |AU
| 50001|AA1 |Vecteur |Tegel |AU
| 50002|AA2 |Vecteur |Tegel |NL
| 50003|AA3 |Vecteur |Tegel |NL | | 50002|AA2 |Vecteur |Tegel |NL
+-------------+--------+--------------------+--------------------+--------+
(4 lignes)

Les insertions ont donc été automatiquement propagées. Examinons maintenant les mises à jour. Les règles multiples déclenchées par une condition unique n'ont pas d'ordre d'exécution défini. Nous veillons donc à ce qu'une seule règle s'exécute ou à ce que l'ordre n'affecte pas le résultat final.

Tout d'abord, nous créons une procédure qui mettra à jour la colonne ap_id de l'aéroport_x100 avec la valeur du paramètre ap_id si la colonne ap_iatacode de l'aéroport_x100 correspond à la valeur du paramètre apiatacodeold :

CREATE PROCEDURE proc_upd_airport_x100_01 ( apid INT NOT NULL NOT DEFAULT, apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS BEGIN
          UPDATE airport_x100 SET ap_id = :apid WHERE ap_iatacode = :apiatacodeold ;
     ENDg

Nous créons maintenant la règle qui déclenche la procédure ci-dessus. Elle ne se déclenche que si la colonne ap_id de la table airport est mise à jour. Il est possible de transmettre la nouvelle et/ou l'ancienne valeur d'une mise à jour à la procédure appelée :

CREATE RULE trg_upd_airport_x100_01 AFTER UPDATE(ap_id) of airport
     EXECUTE PROCEDURE proc_upd_airport_x100_01 ( apid = new.ap_id, apiatacodeold = old.ap_iatacode)g

Nous créons maintenant une deuxième procédure de mise à jour qui, de la même manière, mettra à jour de manière conditionnelle la colonne ap_iatacode de airport_x100 avec la valeur fournie. Outre la mise à jour de airport_x100, cette table génère des messages contextuels lorsqu'elle est exécutée. Nous ne sommes pas limités à des instructions d'insertion, de suppression ou de mise à jour :

CREATE PROCEDURE proc_upd_airport_x100_02 (apiatacode NCHAR(3) NOT NULL NOT DEFAULT, apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS
     DECLARE
          str VARCHAR(30) not null ;
     BEGIN
          str = 'apiatacode = ' + :apiatacode ;
          MESSAGE :str ;
          str = 'apiatacodeold = ' + :apiatacodeold ;
          MESSAGE :str ;
          UPDATE airport_x100 SET ap_iatacode = :apiatacode WHERE ap_iatacode = :apiatacodeold ;
     ENDg

Nous créons maintenant une règle qui déclenchera la procédure ci-dessus si le code ap_iat de la table airport est mis à jour :

CREATE RULE trg_upd_airport_x100_02 AFTER UPDATE(ap_iatacode) of airport
     EXECUTE PROCEDURE proc_upd_airport_x100_02 ( apiatacode = new.ap_iatacode, apiatacodeold = old.ap_iatacode)g

Nous continuons à créer des procédures et des règles qui appliqueront des mises à jour à d'autres colonnes de airport_x100. Il ne s'agit pas d'une obligation. Plusieurs colonnes peuvent être mises à jour dans une seule procédure si nécessaire :

CREATE PROCEDURE proc_upd_airport_x100_03 (applace NVARCHAR(30) NOT NULL NOT DEFAULT, apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS BEGIN
          UPDATE airport_x100 SET ap_place = :applace WHERE ap_iatacode = :apiatacodeold ;
     ENDg

CREATE RULE trg_upd_airport_x100_03 AFTER UPDATE(ap_place) of airport
     EXECUTE PROCEDURE proc_upd_airport_x100_03 ( applace = new.ap_place, apiatacodeold = old.ap_iatacode)g

CREATE PROCEDURE proc_upd_airport_x100_04 (apname NVARCHAR(30) NOT NULL NOT DEFAULT, apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS BEGIN
          UPDATE airport_x100 SET ap_name = :apname WHERE ap_iatacode = :apiatacodeold ;
     ENDg

CREATE RULE trg_upd_airport_x100_04 AFTER UPDATE(ap_name) of airport
     EXECUTE PROCEDURE proc_upd_airport_x100_04 ( apname = new.ap_name, apiatacodeold = old.ap_iatacode)g

CREATE PROCEDURE proc_upd_airport_x100_05 (apccode NVARCHAR(30) NOT NULL NOT DEFAULT, apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS BEGIN
          UPDATE airport_x100 SET ap_ccode = :apccode WHERE ap_iatacode = :apiatacodeold ;
     ENDg

CREATE RULE trg_upd_airport_x100_05 AFTER UPDATE(ap_ccode) of airport
     EXECUTE PROCEDURE proc_upd_airport_x100_05 ( apccode = new.ap_ccode, apiatacodeold = old.ap_iatacode)g

Nous allons maintenant effectuer des mises à jour de nos données transactionnelles dans la table des aéroports pour montrer comment les procédures et les règles ci-dessus appliquent automatiquement les changements à notre table analytique airport_x100 :

update airport set ap_id = 99999 where ap_id = 50000g

La sélection montre que trg_upd_airport_x100_01 a été déclenché et a exécuté la procédure proc_upd_airport_x100_01. La mise à jour d'une seule colonne a été répercutée dans la table airport_x100 :

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 99999|AAA |Vecteur |Aomori |AU
| 50001|AA1 |Vecteur |Tegel |AU
| 50002|AA2 |Vecteur |Tegel |NL
| 50003|AA3 |Vecteur |Tegel |NL | | 50002|AA2 |Vecteur |Tegel |NL
+-------------+--------+--------------------+--------------------+--------+
(4 lignes)

La prochaine mise à jour déclenchera la procédure proc_upd_airport_x100_02 qui affiche également un message lorsqu'elle est déclenchée. Nous allons appliquer un certain nombre de mises à jour à la table des aéroports, puis sélectionner la table analytique airport_x100 pour montrer comment les bases de données et les règles ci-dessus appliquent automatiquement des modifications à nos données analytiques en fonction des transactions qui affectent nos données transactionnelles :

update airport set ap_iatacode = 'AA9' where ap_iatacode = 'AA1'g

     MESSAGE 0 : apiatacode = AA9
     MESSAGE 0 : apiatacodeold = AA1
     (1 ligne)

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 99999|AAA |Vecteur |Aomori |AU
| 50001|AA9 |Vecteur |Tegel |AU
| 50002|AA2 |Vecteur |Tegel |NL
| 50003|AA3 |Vecteur |Tegel |NL | | 50002|AA2 |Vecteur |Tegel |NL
+-------------+--------+--------------------+--------------------+--------+
(4 lignes)

update airport set ap_place = 'VectorUPD' where ap_place = 'Vector'g

(4 lignes)

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 99999|AAA |VectorUPD |Aomori |AU
| 50001|AA9 |VectorUPD |Tegel |AU
| 50002|AA2 |VectorUPD |Tegel |NL | 50003|AA3 |VectorUPD |VectorUPD |Tegel |NL
| 50003|AA3 |VectorUPD |Tegel |NL | | 50002|AA2 |VectorUPD |Tegel |NL
+-------------+--------+--------------------+--------------------+--------+
(4 lignes)

update airport set ap_name = 'TegelUPD' where ap_name = 'Tegel'g

(3 lignes)

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 99999|AAA |VectorUPD |Aomori |AU
| 50001|AA9 |VecteurUPD |TegelUPD |AU |
| 50002|AA2 |VectorUPD |TegelUPD |NL |
| 50003|AA3 |VecteurUPD |TegelUPD |NL |
+-------------+--------+--------------------+--------------------+--------+
(4 lignes)

Nous allons maintenant créer une procédure et une règle pour refléter les suppressions. Dans de nombreux cas, nous souhaitons conserver des données historiques dans nos tables analytiques, mais nous pouvons être amenés à supprimer des lignes pour des raisons de conformité. La procédure et la règle suivantes que nous créons supprimeront automatiquement les données de airport_x100 lorsque des lignes seront supprimées de la table airport :

CREATE PROCEDURE proc_del_airport_x100_01 (apiatacodeold NCHAR(3) NOT NULL NOT DEFAULT)
     AS BEGIN
          DELETE from airport_x100 WHERE ap_iatacode = :apiatacodeold ;
     ENDg
CREATE RULE trg_del_airport_x100_01 AFTER DELETE FROM airport
     EXECUTE PROCEDURE proc_del_airport_x100_01 (apiatacodeold = old.ap_iatacode)g

delete from airport where ap_iatacode = 'AA9'g

(1 ligne)

Cette sélection montre que la ligne supprimée de l'aéroport a été automatiquement supprimée de airport_x100 :

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as -
     ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
 |ap_id |iatacode|ap_place |ap_name |ap_ccode|
 +-------------+--------+--------------------+--------------------+--------+
 | 99999|AAA |VectorUPD |Aomori |AU
 | 50002|AA2 |VecteurUPD |TegelUPD |NL |
 | 50003|AA3 |VectorUPD |TegelUPD |NL |
 +-------------+--------+--------------------+--------------------+--------+
 (3 lignes)

delete from airport where ap_name = 'TegelUPD'g

(2 lignes)

Select montre que 2 lignes supprimées de l'aéroport sont automatiquement supprimées de airport_x100 :

select ap_id, ap_iatacode as iatacode, cast(ap_place as varchar(20)) as ap_place, cast(ap_name as varchar(20)) as ap_name, ap_ccode from airport_x100;g

+-------------+--------+--------------------+--------------------+--------+
|ap_id |iatacode|ap_place |ap_name |ap_ccode|
+-------------+--------+--------------------+--------------------+--------+
| 99999|AAA |VectorUPD |Aomori |AU |
+-------------+--------+--------------------+--------------------+--------+
(1 ligne)

Ces exemples montrent une mise en miroir de base des insertions, des mises à jour et des suppressions des tables transactionnelles vers les tables analytiques pour vous aider à démarrer. Des procédures de base de données beaucoup plus sophistiquées peuvent être élaborées et les modifications appliquées à vos données analytiques n'ont pas besoin d'être le reflet des modifications apportées à vos données transactionnelles. Vous souhaiterez peut-être déclencher une insertion dans votre table analytique après la suppression d'une ligne de vos données transactionnelles afin d'établir une piste d'audit.

Nous aimerions savoir comment vous générez automatiquement et en toute transparence vos données analytiques à partir de vos tables transactionnelles existantes. Veuillez poster une réponse dans nos forums communautaires pour nous faire part des choses intéressantes que vous faites avec la base de données hybride Actian X.

À propos d'Alex Hanshaw

Alex Hanshaw est directeur de l'ingénierie chez Actian. Il s'appuie sur plus de 20 ans d'expérience pratique dans l'élaboration de solutions pour relever les défis complexes des clients. Alex a rejoint l'équipe Ingres Sustaining Engineering en 1997 et a toujours accordé la priorité à la résolution des problèmes des clients dans Actian X, Ingres et Vector. Il a supervisé de nombreuses améliorations de produits qui réduisent les temps d'arrêt et simplifient la maintenance. Alex collabore fréquemment avec des pairs de l'industrie lors de conférences et de groupes d'utilisateur (par exemple, les réunions de la communauté Ingres) afin de partager les meilleures pratiques. Connectez-vous avec Alex sur LinkedIn pour découvrir ses conférences techniques et ses contributions à la communauté. Sur le blogue d'Actian, Alex traite de sujets tels que les stratégies de migration de bases de données, l'optimisation des performances et le dépannage. Consultez ses récents articles pour obtenir des conseils pratiques sur le bon fonctionnement de vos systèmes de données.