Mois : novembre 2007

Catégories
ETL

SSIS et les UPSERTs

L’UPSERT est une fonctionnalité couramment utilisée dans les ETL à mis chemin entre l’instruction INSERT et UPDATE. Elle consiste à insérer une ligne de données si celle-ci n’existe pas en base, ou sinon à la mettre à jour. Le SQL « officiel », ainsi que SQL Server ne proposent pas d’opérateur dédié à cette opération. Il semblerait que SQL Server 2008 ait ce type d’opérateur en gestation, mais ce n’est pas l’objet de ce post.
Les méthodes de réalisation d’UPSERT font régulièrement l’objet d’analyse sur le web. Après moult lectures, et essais, voici ma méthode, efficace dans la grande majorité des cas.
L’exemple fait référence à mon précédent article sur « SSIS et les mises à jour de masse ».
Le principe est de disposer d’un flux de données réalisant
• l’insertion de masse des lignes adéquates,
• l’insertion en table temporaire des lignes à modifier
Puis une requête SQL pour réaliser la mise à jour par jointure (voir mon article « SSIS et les mises à jour de masse).
Voici le flux de contrôle :
img1.jpg

Voici le flux de données associé :

img2.jpg

On retrouve la « recherche » du nom et prénom. Il est important de noter que la gestion des erreurs de cette recherche est configurée sur « Ignorer l’échec » de la façon suivante:

img3.jpg

Cela signifie que si la recherche de la clé primaire n’aboutie pas, celle-ci est positionnée à la valeur NULL dans le flux de données.
Dès lors, il est possible de départager les lignes à insérer ou à mettre à jour avec un composant de « Fractionnement conditionnel » de la sorte :

img4.jpg

Catégories
ETL

SSIS et les mises à jour de masse

SSIS dispose d’un composant de destination OLE DB permettant les chargements rapides. Celui-ci est très efficace pour les insertions de masse.
Par contre, il n’existe pas de composant de « mises à jour à chargement rapide » de même type. La méthode standard pour réaliser les update, qui est généralement présentée dans la documentation officielle et les différents blogs est la suivante : utilisation du composant « Commande OLE DB » dans le flux de données pour la mise à jour. L’inconvénient majeur de cette méthode, est qu’un appel à la méthode « update » est réalisé à chaque ligne traitée. Cela peut être très pénalisant sur des volumes importants de données. Une méthode beaucoup plus efficace peut être employée, que je vais illustrer ici.
L’exemple est le suivant : une table « customers » contient les champs nom, prénom, date de naissance et un identifiant clé primaire. Cette table est correctement indexée sur le nom+prénom , et bien sûr sur la clé primaire. Nous allons mettre à jour la date de naissance à partir d’un fichier CSV contenant le nom, prénom et la date de naissance, et ce pour 150 000 personnes.
La méthode standard est d’avoir un flux de contrôle contenant juste un appel à un flux de données de cette forme-ci :

img1.JPG

Le flux de données est composé ainsi :

img2.JPG

Le composant « Recherche » fait une recherche sur la table Customers pour remonter la clé primaire à partir des champs nom et prénom. Le composant « Mise à jour » réalise l’update suivant : « update Customers set birthdate=? where rowkey=? » avec le mappage suivant (les colonnes de droite représentent les données provenant du flux de données, c’est-à-dire du fichier, ou bien issue de la recherche pour rowkey) :

img3.JPG

Le temps de traitement sur mon portable est de 19 secondes 391 millièmes.
Maintenant, passons à la méthode optimisée. Celle-ci consiste à insérer dans un premier temps les données avec le chargement rapide dans une table temporaire (dans un flux de données), puis dans un deuxième temps à réaliser une mise à jour de la table customers avec une jointure (dans une « tâche d’exécution de requête SQL »). Voici le flux de contrôle reprenant les deux étapes :

img4.JPG

Voici le flux de données :

img5.JPG

On retrouve les deux premières étapes déjà vues au dessus (source du fichier plat, et Recherche). Par contre, la mise à jour est remplacée par un composant de « Destination OLE DB ». Celui-ci est paramétré de la façon suivante :

img6.JPG
Enfin, la « tâche d’exécution de requête SQL » est paramétrée ainsi :
update customers set birthdate=x.birthdate from customers c,tempcustomers x where x.rowkey=c.rowkey
GO
truncate table tempcustomers
GO

Il s’agit d’une syntaxe SQL Server (dans le cas présent, SQL Server 2005, mais fonctionne également sous SQL Server 2000). Il existe des syntaxes équivalentes en Oracle.
Le temps de traitement est passé à 5 secondes 422 millièmes, soit près de 4 fois plus rapide. L’ordre de grandeur peut d’ailleurs être parfois encore bien plus important, et il est étonnant que cette méthode ne soit pas plus souvent préconisée.

Catégories
SQL Server

SQL Server et la fonction ISNUMERIC

La fonction ISNUMERIC de SQL Server est particulièrement malicieuse, … surtout pour nous francophones.

En effet, la documentation Microsoft définie la chose suivante : « Lorsque la valeur 1 est retournée, ceci indique que l’expression peut être convertie au minimum en l’un des types numériques.

Les types numériques sont les suivants :

int numeric
bigint money
smallint smallmoney
tinyint float
decimal real

En fait, il faut bien avoir à l’esprit que les signes non purement numériques tels que +, -, ‘.’ ‘,’, $, … sont tous pris en compte. Dans la pratique, la fonction ISNUMERIC garantie que la valeur est convertible dans le type MONEY au format américain (la virgule pour séparateur de millier, même si la langue de l’utilisateur SQL Server est French).

 Ainsi le code suivant retourne une erreur :

DECLARE @test VARCHAR(10)
SET @test='$10,1'
IF ISNUMERIC(@test)=1 PRINT CONVERT(DECIMAL(18,0),@test)

La solution est de remplacer les virgules par des points, puis de faire une double conversion en passant par le type MONEY ainsi par exemple :

DECLARE @test VARCHAR(10)
SET @test='$10,1'
IF ISNUMERIC(@test)=1
  PRINT CONVERT(DECIMAL(18,0),CONVERT(MONEY,REPLACE(@test,',','.')))

Il peut donc être utile d’écrire une fonction de conversion de la sorte :

CREATE FUNCTION getNumeric(@str VARCHAR(50)) RETURNS MONEY
AS
BEGIN
  DECLARE @ret MONEY
  IF ISNUMERIC(@str)=1
    SELECT @ret=CONVERT(MONEY,REPLACE(@str,',','.'))
  RETURN @ret
END

Catégories
ETL

SSIS, remarques et recommandations générales

Mes journées sont bien remplies actuellement…cela se ressent sur mon blog. Je travaille sur six clients principaux en parallèle, les sujets techniques ne manquent pas.

Actuellement, mes activités principales concernent deux sujets : SSIS et le CTI. Le billet du jour concerne SSIS.

Il s’agit de l’ETL de Microsoft faisant parti de la suite SQL Server 2005. Si l’on regarde par la petite porte, on peut dire qu’il remplace les DTS de SQL Server 2000. En réalité, il s’agit véritablement d’un nouvel outil de développement de flux de données, intégré à Visual Studio.

Je travaille assez intensément sur le sujet depuis quelques semaines. Le sujet est globalement la reprise de données, et l’interfaçage pour un nouveau CRM d’une importante mutuelle (quelques millions de ligne à traiter). L’outil est très intéressant et peut permettre d’obtenir une bonne productivité et des performances de traitement très correct, mais comporte aussi quelques lacunes ou difficultés avec lesquelles il faut cohabiter. En voici quelques unes :

  • réutilisabilité malaisée voire impossible des flux de données
  • langage d’expression assez limité, obligeant à écrire souvent des scripts .NET => ce débranchement de contexte entraine des pertes de performance
  • éviter les composant de « destination SQL Server » => utiliser « OLE DB » avec option Fast Load. Vous éviterez bien des problèmes pour une performance quasi identique…
  • l’affinage de quelques paramètres (notamment EngineThreads, DefaultBufferMaxRows et DefaultBufferSize) est essentielle, et demande un peu de temps pour une bonne compréhension
  • la gestion de configuration demande une bonne compréhension pour être correctement réalisée
  • attention à la configuration de votre projet pour le travail d’équipe. Point qui m’a fait perdre une bonne demi-journée : le paramètre ProtectionLevel des packages est par défaut à EncryptSensitiveWithUserKey. Cela signifie que seul l’utilisateur windows ayant créé le package pourra le modifier! Il faut impérativement le changer pour un travail d’équipe (par exemple à EncryptSensitiveWithPassword).
  • si votre serveur n’est pas connecté à internet, allez lire impérativement ceci : il faut bidouiller une clé de registre windows pour que SSIS fonctionne correctement.

Enfin, la lecture d’un article comme celui-ci m’a été d’une grande aide :

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx