Exploiter le Change Data Capture (CDC) avec Kafka et Azure Databricks (1/2)

De nombreux systèmes de bases de données (BDD) proposent une fonctionnalité appelée « Change Data Capture » (CDC). Il peut être utile d’introduire dans vos bases de données de l’analyse des changements effectués sur celles-ci, que ce soit à des fins d’historisation ou pour reproduire en temps réel (ou presque) une base de données on-premises dans un environnement Cloud.

C’est ce dernier use case qui sera utilisé ici pour illustrer l’usage qui peut être fait de la fonctionnalité CDC. Nous allons activer un moteur d’analyse permettant de capter les modifications réalisées sur les tables d’une base de données SQL via la fonctionnalité de Change Data Capture (CDC). Afin de suivre ces modifications, nous utiliserons Kafka avec un connector Debezium qui vous permettra de vous abonner aux modifications de données (données CDC de notre base SQL). Les messages seront ensuite remontés via Kafka et publiés vers un Azure Event Hubs. Enfin, Azure Databricks consommera et traitera les messages de l’Azure Event Hubs et reproduira les tables de notre base dans un Azure Data lake Gen 2.

Nous allons voir comment utiliser cette fonctionnalité CDC dans 2 articles :

• Dans ce premier article (Partie 1), nous verrons l’activation du CDC sur les tables SQL et mise en place de l’environnement Azure :
  • Enregistrement des changements de la BDD en activant la fonctionnalité CDC (SQL Server)
  • Déploiement des ressources de l’environnement Cloud Azure.

• Dans un second article (Partie 2), nous nous intéresserons à l’abonnement aux données CDC via Kafka et Debezium :
  • S’abonner à ces changements via Kafka et Debezium
  • Publier les messages vers Azure Event Hub
  • Consommation, traitement et stockage de ces messages en utilisant Azure Databricks/Data lake.

Activation du Change Data Capture (CDC) sur la base de données SQL

La première étape de notre projet consiste à activer le Change Data Capture sur les bases de données et tables ciblées.

 Qu’est-ce que le Change Data Capture (CDC) ?

Nous allons ici activer un moteur d’analyse permettant d’analyser certains logs de votre base de données : les données de Change Data Capture (CDC).

Le CDC vous permet d’enregistrer en continu les modifications de votre base de données : insertions, mises à jour, suppressions et changements de schémas. Cette fonctionnalité est disponible sur plusieurs systèmes de bases de données (SQLServer, Postgres, etc.).

 Comment activer le CDC sur des tables SQL ?

En plus de la simple activation du CDC sur les bases et tables ciblées, des actions complémentaires sont nécessaires comme la création d’un filegroup dédié ou la gestion des jobs de nettoyage et de capture.

 SQL Server Agent en mode « running ».

L’agent SQL Server est nécessaire au bon fonctionnement du CDC.

Avant tout, vérifions que le SQL Server Agent est bien en mode « running » :

Création d’un compte SQL avec un accès en lecture (read-only access) sur la base de données [MyDB]

Une bonne pratique pour votre projet est de créer un compte SQL dédié que nous utiliserons ensuite pour les connecteurs Kafka-Debezium avec un accès read-only sur la base de données [MyDB].

Création d’un groupe de fichiers (filegroup)

Lorsque vous créez une base de données, par défaut les fichiers créés (principalement des fichiers de données et de logs) sont contenus dans un filegroup principal. Ainsi, ce filegroup principal est le filegroup par défaut, à moins qu’il ne soit modifié à l’aide de la commande  ALTER DATABASE.

Nous allons créer un filegroup spécifique pour regrouper logiquement les fichiers CDC qui vont être créés :

Création du filegroup :

Activation du mode CDC

Avant d’activer le CDC, assurez-vous que le login utilisé :

• a les privilèges sysadmin de SQL Server ;
• est db_owner de la base de données.

Il est possible d’activer le CDC au niveau de la base de données ou au niveau de chaque table.

Activation du mode CDC sur la database [MyDB]

Activation du mode CDC pour chaque table

Lorsque le paramètre @role_name est défini à NULL, seuls les membres des rôles sysadmin et db_owner ont un accès complet aux informations capturées. Lorsqu’il est défini sur un rôle spécifique, seuls les membres du rôle (appelé rôle de synchronisation) peuvent accéder à la table de données modifiée.

Vous pouvez rencontrer 3 situations différentes :

1. La table a une clé primaire :

2. La table utilise un index unique, (supports_net_changes = 1 ; et précisez l’index à l’aide du paramètre @index_name) :

3. La table n’a ni clé primaire ni index unique non null :

Vérification de l’activation du mode CDC

Vérifions que CDC est bien activé sur nos tables. La commande ci-dessous doit vous renvoyer la liste des tables pour lesquelles le CDC est activé :

Voilà, notre base produit les logs CDC dont nous avons besoin ! Voyons maintenant à quoi ressemble une table CDC (ci-dessous la table CDC pour la table Person) :

• Les champs __$start_lsn/ __$end_lsn :

LSN signifie Last Sequence Number. Chaque enregistrement dans le journal des transactions est identifié de manière unique par un LSN.

Les champs __$start_lsn et __$end_lsn affichent le numéro de séquence du journal de validation (LSN) attribué par le moteur SQL Server à la modification enregistrée.

Pour retourner la valeur de date et d’heure de ce champ :

sys.fn_cdc_map_lsn_to_time(__$start_lsn)

• Le champ __$operation indique quelle est l’opération effectuée :
  • 1 pour DELETE
  • 2 pour INSERT
  • 3 pour UPDATE (valeur avant modification)
  • 4 pour UPDATE (valeur après modification)

• Le champ __$update_mask montre, via un bitmap, quelles colonnes ont été mises à jour dans l’opération DML qui a été spécifiée par _$operation.

Avant d’aborder les différentes ressources à déployer pour notre environnement Azure Cloud, voyons 2 types de jobs qui sont créés par SQL Server pour gérer le cycle de vie du CDC.

 Cleanup job et Capture job

Deux types de jobs sont à connaître lorsque l’on manipule le CDC : Le Capture job et le Cleanup job.

 Capture job

Le Capture job est un job chargé de pousser les changements DML dans la table CDC. Ce job est créé automatiquement par base de données dans SQL server lorsque le CDC est activé sur la base. Le Capture Job lit les logs de transaction et écrit les changements DML dans la table CDC correspondante de manière asynchrone.

 Cleanup job

Le Cleanup job est un job chargé de nettoyer les enregistrements de la table CDC. Ce job est créé automatiquement par le SQL server pour minimiser le nombre d’enregistrements des tables CDC.

A noter : la valeur de rétention est de 4320 minutes (3 jours). Il peut être utile de bénéficier d’un délai de rétention un peu plus élevé pour votre projet :

En cas de modification, le redémarrage du cleanup job est nécessaire :

• Pour stopper le cleanupjob :

• Pour le redémarrer :

Si vous souhaitez approfondir le sujet, nous vous invitons à consulter les documentations suivantes :

• Sur l’activation du CDC sur une base SQL Server :
  • https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver15
  • https://debezium.io/documentation/reference/connectors/sqlserver.html#_enabling_cdc_on_the_sql_server_database

• Sur les Capture et Cleanup jobs : https://sqlzealots.com/2019/04/07/cdc-jobs-in-sql-server-capture-and-cleanup/

Avant de passer à la prochaine partie de notre projet qui nous permettra de traiter ces données CDC, nous allons déployer les ressources nécessaires pour notre environnement Cloud Azure.

Prérequis : création des ressources Azure nécessaires

Pour traiter nos données CDC, nous aurons besoin de 3 ressources Azure :

• Un Azure Event Hubs Namespace, dans lequel Kafka poussera les messages (évènements = données CDC) ;
• Un Azure Databricks, qui ira chercher les évènements dans le Event Hubs et les traitera ;
• Un Azure Data Lake Gen 2, dans lequel le Databricks stockera les données après traitement.

Création du Azure Event Hubs Namespace

Un compte de stockage Azure est un magasin à usage général hautement disponible, sécurisé et massivement évolutif pour les données non structurées, telles que les fichiers et les objets blob, et les données structurées autour des tables clé-valeur.

Azure Data Lake Storage Gen2 est un ensemble de fonctionnalités dédiées à l’analyse Big Data, basé sur le stockage d’objets blob Azure.

Configuration minimum pour le projet : activer le Hierachical Namespace lors de la création du storage account.

 Création du Azure Databricks

Azure Databricks est une plateforme d’analyse de données optimisée pour la plateforme de services Cloud Microsoft Azure.

Vous pouvez suivre ce tutoriel pour créer votre cluster Databricks et effectuer le point de montage vers le Data Lake que vous venez de créer : https://docs.microsoft.com/fr-fr/azure/storage/blobs/data-lake-storage-use-databricks-spark

Configuration minimum pour le projet : installer la bibliothèque Azure EventHubs Connector pour Apache Spark sur le Cluster Databricks créé.

Activation du CDC sur une base SQL : et après ?

La première partie de ce projet qui nous permettra de reproduire nos tables SQL Server on-premise dans un environnement Cloud est terminée.

Nous avons donc :

• Activé la fonctionnalité CDC sur les tables ciblées dans notre base de données SQL Server ;
• Déployé les ressources de notre environnement Cloud qui vont permettre traiter les données CDC.

Dans un prochain article, nous verrons :

• Comment utiliser Kafka et le connecteur Debezium pour remonter ces données CDC de notre base SQL on-premise pour les pousser vers notre environnement Cloud via Azure Event Hubs ;
• Comment utiliser Azure Databricks pour consommer les messages CDC contenus dans Azure Event Hubs et reproduire nos tables SQL dans un Azure Data Lake Storage Gen2.

Rendez-vous le 23 novembre pour la suite de cet article !