02 • 34 • 09 • 31 • 70
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
Le composant tFileList_1 récupère tous les fichiers *.txt dans un dossier.
Le composant tFileInputDelimited_1 lit la 1ère ligne du fichier en cours de traitement.
Le tMap_1 modifie l’en-tête.
"('"+StringHandling.EREPLACE(SRC_Header.Header,";","'),('")+"')"
Le composant tSetGlobalVar_1 enregistre les variables ColumnName et TableName.
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.
Et enfin, tMSSqlOutput_1 insère les données du fichier source dans la table STG.
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 !