Création dynamique de tables dans SQL Server avec Talend

On vous propose aujourd'hui une technique de création de tables STG (Staging) dans SQL Server sans avoir un développement pour chaque fichier.

On dispose de différents fichiers plats à charger via ETL dans une base de données SQL Server. On souhaite charger une base de données STG avec une table par fichier (1 pour 1), sans aucune transformation et outrepasser la contrainte du type dynamic de Talend qui crée la table avec le type VARCHAR(100).

Les étapes à suivre pour la création des tables

Étape 1 - Récupérer l'entête du fichier comme l'exemple ci-dessous :

Colonne1;Colonne2;Colonne3

Étape 2 - Remplacer dans l'entête le début, les séparateurs et la fin de la chaîne de caractères afin d'avoir une structure comme suit :

(Colonne1, Colonne2, Colonne3)

Étape 3 - Enregistrer le résultat dans une variable (ColumnName), ajouter une 2ème variable (TableName) par exemple avec le nom du fichier :

VAR_ColumnName = (Colonne1, Colonne2, Colonne3)
VAR_TableName = FileName

Étape 4 - Utiliser la requête suivante pour créer la table correspondant au fichier en entrée avec le type souhaité ((NVARCHAR(max)) :

--On déclare une variable SQL de style Table

DECLARE @T table (col nvarchar(max))

--On alimente la variable avec la liste de colonnes précédemment enregistrée

INSERT INTO @T VALUES "+((String)globalMap.get("ColumnName"))+"

--On déclare une variable SQL de type nvarchar avec le début de la requête :

DECLARE @Sql nvarchar(max) = 'CREATE TABLE [STG].[dbo].[" + ((String)globalMap.get("TableName")) + "]'

--On construit la requête SQL avec la liste de colonnes et le type :

--nvarchar(max)
SELECT @Sql = @Sql + '[' + col + '] nvarchar(max),'
FROM @T
SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) +');'

--On exécute la requête SQL obtenue :

EXEC(@Sql)

Étape 5 - Charger les données du fichier dans la table créée

Pour vérifier l'écriture de la requête avant exécution, on peut ajouter :

PRINT @Sql

Cette astuce permet de charger une base de données STAGING avec une structure identique aux fichiers d’entrée et le type nvarchar(max) dans un seul flux générique pour tous les fichiers (Il est possible d’adapter en fonction du besoin).

Avantages :

  • Un seul développement pour tous les fichiers
  • Pas besoin de créer un nouveau flux s’il y a un nouveau fichier
  • L’ordre des colonnes du fichier peut changer sans impacter la suite du chargement
  • Possibilité d’ajouter de nouvelles colonnes sans impacter la suite du chargement
  • Gérer le type des colonnes avec le type dynamic de Talend

Inconvénients :

  • Le fichier source doit contenir un en-tête sans caractères spéciaux (c’est-à-dire respecter les règles de nommage des tables et colonnes)
  • Le fichier ne doit pas avoir d’en-tête vide
  • Si la syntaxe d’un champ de l’entête change, la suite du chargement risque d’être impacté

Scénario avec l'ETL Talend Data Integration

Création dynamique de tables dans SQL Server avec Talend

Le composant tFileList_1 récupère tous les fichiers *.txt dans un dossier.

Création dynamique de tables dans SQL Server avec Talend

Le composant tFileInputDelimited_1 lit la 1ère ligne du fichier en cours de traitement.

Création dynamique de tables dans SQL Server avec Talend

Le tMap_1 modifie l’en-tête.

"('"+StringHandling.EREPLACE(SRC_Header.Header,";","'),('")+"')"

Le composant tSetGlobalVar_1 enregistre les variables ColumnName et TableName.

Création dynamique de tables dans SQL Server avec Talend

Le tMSSqlRow_1 exécute la requête de création de la table STG.

Le tFileInputDelimited_2 lit le contenu du fichier source avec un schéma de type dynamique.

Création dynamique de tables dans SQL Server avec Talend

Et enfin, tMSSqlOutput_1 insère les données du fichier source dans la table STG.

Création dynamique de tables dans SQL Server avec Talend

Bonus :

En utilisant une table référentielle avec la structure suivante :

REF_Source(FileName ; TableName ; Header ; Column)

Cela vous permettra de gérer vos fichiers en entrée sur le nombre de ligne d’entête, le nom des colonnes…

Nos consultants experts de Next Decision sont à votre écoute pour vous accompagner dans vos projets ! Contactez-nous !