Azure function : copie de fichiers CSV dans une table SQL Database

Article
09/11/2017

Dans cet article, nous allons voir comment créer une fonction permettant de copier des fichiers CSV, qui arrivent dans un container, vers une table SQL Database. Nous allons utiliser la notion de « trigger » permettant de déclencher la fonction lorsqu’un nouveau fichier arrive dans le container. Une fois copié dans la table SQL, le fichier sera alors archivé dans un autre container.

Ci-dessous un schéma illustrant notre fonction :

Copie du fichier dans un container du blob storage
Déclenchement de la fonction à l’arrivée du fichier
Copie du fichier dans une table SQL Database
Déplacement du fichier dans un container d’archive

Prérequis

Création d’Azure Function App

Depuis le portail Azure (https://portal.azure.com), cliquez sur le bouton « + » en haut à gauche.

Puis dans « Compute », cliquez sur « Function App ».

Cliquez sur « Create »

Donnez un nom à votre groupe de ressource et choisissez votre région.

Pour cet exemple, nous créer un « App Service plan ». Le plan S1 suffit pour cet exemple.

(J’ai fait des tests en utilisant un « Consumption Plan », mais lors de fortes charges, ma fonction tombait en timeout).

Nous allons aussi créer un compte de stockage, mais vous pourrez en utiliser un déjà existant

Optionnel : si vous souhaitez avoir des indicateurs de surveillances de votre fonction, activez Application Insights.

Cliquez sur le bouton « Create ».

Vous devez voir apparaître les services suivants dans votre groupe de ressources.

Paramétrage du compte de stockage

Dans le compte de stockage que nous avons créé, nous allons maintenant créer 2 container (et une file d’attente) :

Un container « fichiers », qui va recevoir les fichiers CSV pour y être traités
Un container « archives », dans lequel les fichiers vont être déplacés après traitement

Pour la création des containers de nombreuses solutions sont possibles :

Via le portail Azure
Via un outil tel que Microsoft Azure Storage Explorer
Via PowerShell (le compte de stockage existe déjà) :

Login-AzureRMAccount

$SubscriptionName = "Compte de démos"

$ResourceGroupName = "Superfunkycalifragisexy"

$StorageAccountName = "funky69storageaccount"

$StorageContainerName = "fichiers archives"

$Keys = Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupName -Name $StorageAccountName;

$StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $Keys[0].Value;

Select-AzureRmSubscription -SubscriptionName $SubscriptionName

Get-AzureRmStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccountName

$StorageContainerName.split() | New-AzureStorageContainer -Permission off -Context $StorageContext

Si on regarde dans Microsoft Azure Storage Explorer, on doit retrouver nos 2 containers :

Création d’une base SQL Database

Depuis le portail Azure, cliquez sur le signe « + », « Databases » puis « SQL Database »

Définissez les propriétés de votre base SQL Database.

Sélectionnez le groupe de ressources que vous avez créez au début de ce lab. Dans cet exemple, choisissez : « Superfunkycalifragisexy »
Vous pouvez utiliser un serveur existant pour héberger votre base de données ou en créer un nouveau. Pour cet exemple, nous allons créer un nouveau serveur.
Dans la partie « Pricing tier », choisissez au minimum « Standard S0 »

Après la création du serveur et de la base SQL Azure, voici ci-dessous les services Azure présents dans notre groupe de ressources :

Création de la table

Cliquez sur votre base SQL Database

Cliquez sur « Tools »

Cliquez sur « Query editor (preview) », puis sur « Login »

Entrez vos informations de connexion puis cliquez sur « Ok »

Copiez le script ci-dessous, puis cliquez sur « Run » :

CREATE TABLE [dbo].[FranmerProducts](

[UniqueID] [int] IDENTITY(1,1) NOT NULL,

[ID] [int] NULL,

[Product] [nvarchar](200) NULL,

[NomDuFichier] [nchar](200) NULL,

[DateAjout] [datetime] NULL,

CONSTRAINT [PK_FranmerProducts] PRIMARY KEY NONCLUSTERED

(

[UniqueID] ASC

)

)

Pour vérifier si votre table a bien été créée, exécutez la requête suivante :

SELECT TABLE_NAME FROM <Nom de votre base SQL>.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Dans cet exemple, <Nom de votre base SQL> = Anotherloverholenyohead

Sélectionnez la requête, puis cliquez sur « Run »

Création de la fonction Azure

Nous allons créer une fonction Azure qui se déclenche séquentiellement quand un fichier arrive dans le containeur. Cette fonction aura pour but de réaliser la copie du fichier CSV dans une table SQL Database.

Une fonction est composée de plusieurs fichiers :

run.csx : c’est dans ce fichier que se trouvera l’essentiel de votre code
function.json : ce fichier contient les metadata de configuration de votre fonction
project.json : ce fichier permet l’utilisation de packages NuGet

Création de la fonction

Revenez dans votre groupe de ressources et cliquez sur votre fonction

Dans le menu de gauche, cliquer sur le signe « + » à droite de « Functions »

Cliquez sur « Custom function »

Sélectionnez “BlobTrigger C#”

Puis renseignez les champs ci-dessous :

Name your function : donnez un nom à votre fonction
Path : remplacez la valeur par défaut « samples-workitems » par le nom de votre container. Dans cet exemple c’est « fichiers »
Storage account connection: dans la liste déroulante, sélectionnez « AzureWebJobStorage »

Cliquez sur le bouton « Create »

La fonction vient d’être créée. Le fichier run.csx est maintenant ouvert.

Ajout d’une entrée et sortie

En plus du trigger, nous allons maintenant rajouter une entrée et une sortie de type blob à notre fonction

Paramétrage de l’entrée blob

Sur la gauche, cliquez sur « Integrate », « + New Input » puis sur « Azure Blob Storage ».

Cliquez sur le bouton « Select »

Définissez les paramètres de votre entrée :

Donnez un nom à votre paramètre de sortie
Dans le champ « Path », entrez la valeur « fichiers/{name} »
Dans le champ « Storage account connection », sélectionnez la valeur correspondant à votre de compte de stockage. (« AzureWebJobsStorage » dans notre exemple)
Cliquez sur le bouton « Save »

Paramétrage de la sortie blob

Sur la gauche, cliquez sur « Integrate », « + New Output » puis sélectionnez « Azure Blob Storage »

Cliquez sur le bouton « Select »

Définissez les paramètres de sortie blob

Donnez un nom à votre paramètre de sortie
Dans le champ « Path », remplacez « outcontainer/{rand-guid} » par « archives/{name} »
Dans le champ « Storage account connection », sélectionnez la valeur correspondant à votre de compte de stockage. (« AzureWebJobsStorage » dans notre exemple)
Cliquez sur le bouton « Save »

Paramétrage manuel des entrées et sorties de la fonction

La définition des liens entre les entrées et sortie de la fonction se trouve dans le fichier « function.json ».

Une des manières pour avoir accès au fichier, est de cliquer sur « Integrate » puis « Advanced editor »

Vous devez arriver sur la vue suivante :

ATTENTION ! Afin d’éviter par la suite d’avoir l’erreur suivante :

Microsoft.Azure.WebJobs.Host: Error indexing method 'Functions.CSV2SQL'. Microsoft.Azure.WebJobs.Host: Cannot bind blob to CloudBlockBlob using access Read.

Modifier le fichier en spécifiant pour les 2 clefs « direction » la valeur «inout ». ci-dessous le code du fichier modifié :

{

"bindings": [

{

"name": "myBlob",

"type": "blobTrigger",

"direction": "in",

"path": "fichiers/{name}",

"connection": "AzureWebJobsStorage"

},

{

"type": "blob",

"name": "inputBlob",

"path": "fichiers/{name}",

"connection": "AzureWebJobsStorage",

"direction": "inout"

},

{

"type": "blob",

"name": "outputBlob",

"path": "archives/{name}",

"connection": "AzureWebJobsStorage",

"direction": "inout"

}

],

"disabled": false

}

Cliquez sur le bouton « Save »

Ajout du code dans la fonction

Cliquez sur le nom de votre fonction pour revenir dans le fichier « run.csx ». Puis collez le code ci-dessous :

Remplacez les valeurs en rouge par vos informations de connexion, puis cliquez sur le bouton « Save »

#r "System.Data"

#r "System.IO.Compression.FileSystem"

#r "Microsoft.WindowsAzure.Storage"

using System;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.IO.Compression;

using Microsoft.Azure;

using Microsoft.WindowsAzure.Storage;

using Microsoft.WindowsAzure.Storage.Blob;

public static void Run(Stream myBlob,CloudBlockBlob inputBlob, CloudBlockBlob outputBlob, string name, TraceWriter log)

{

log.Info($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {myBlob.Length} Bytes");

DataTable sourceData = new DataTable();

sourceData.Columns.Add("ID");

sourceData.Columns.Add("Product");

sourceData.Columns.Add("NomDuFichier");

sourceData.Columns.Add("DateAjout");

try

{

using (var rd = new StreamReader(myBlob))

{

while (!rd.EndOfStream)

{

var splits = rd.ReadLine().Split(',');

sourceData.Rows.Add(splits[0], splits[1],name,DateTime.UtcNow);

}

}

log.Info($"Début de copie du fichier:{name} dans SQL Database avec BCP....");

SqlBulkCopy bcp = new SqlBulkCopy("Server=tcp:<Nom de votre serveur SQL>.database.windows.net,1433;Initial Catalog=<Nom de votre base de données>;User ID=<Votre compte utilisateur>;Password=<Mot de passe>;Trusted_Connection=False;Encrypt=True;");

// Modifiez le mapping entre votre fichier et votre table SQL si besoin

SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("ID", "ID");

bcp.ColumnMappings.Add(mapID);

SqlBulkCopyColumnMapping mapProduit = new SqlBulkCopyColumnMapping("Product", "Product");

bcp.ColumnMappings.Add(mapProduit);

SqlBulkCopyColumnMapping mapNomFichier = new SqlBulkCopyColumnMapping("NomDuFichier", "NomDuFichier");

bcp.ColumnMappings.Add(mapNomFichier);

SqlBulkCopyColumnMapping mapDateAjout = new SqlBulkCopyColumnMapping("DateAjout", "DateAjout");

bcp.ColumnMappings.Add(mapDateAjout);

bcp.DestinationTableName = " <Nom de votre table SQL> ";

bcp.BulkCopyTimeout = 600;

bcp.WriteToServer(sourceData);

log.Info($"Copie du fichier:{name} faite !");

log.Info($"Copie du fichier:{name} dans un container d'archives");

using (var stream = inputBlob.OpenRead())

{

outputBlob.UploadFromStream(stream);

}

log.Info($"Effacement du fichier:{name} qui vient d'être traité");

inputBlob.Delete();

}

catch (Exception ex)

{

log.Error(ex.Message);

}

}

Ci-dessous, une copie d’écran de ma fonction :

Référencement des packages NuGet

Pour que notre fonction puisse s’exécuter sans erreur, il reste à définir les packages Nugets que l’on souhaite utiliser.

Pour ce faire, il est nécessaire de créer un fichier « project.json » avec les références aux packages NuGet que l’on souhaite utiliser.

Sur la droite, cliquez sur le signe « < » pour ouvrir la fenêtre de navigation dans les fichiers

La fenêtre suivante apparaît, cliquez sur le bouton « + Add ».

Dans le champ « File name », entrez le nom de fichier « project.json »

Une fois le fichier créé, copiez le script ci-dessous, puis cliquez sur le bouton « Save »:

{

"frameworks": {

"net46":{

"dependencies": {

"WindowsAzure.Storage": "7.0.0",

"CsvHelper": "2.14.1"

}

}

}

}

Paramétrage du fichier host.json

Afin de s’assurer que la fonction s’exécutera de manière séquentielle, lors de l’arrivé d’un fichier CSV dans le container, il est nécessaire de modifier le fichier « host.json » de notre application. Ce fichier va affecter le comportement de toutes les fonction présentes dans notre application.

Les paramétrages possibles sont expliqués ici : https://github.com/Azure/azure-webjobs-sdk-script/wiki/host.json

Depuis le portail Azure, cliquez sur le nom de votre application (ici, « funky69 »), « Platform Features » puis « App Service Editor »

Dans la fenêtre qui vient de s’ouvrir, cliquez sur « host.json »

Copiez le script ci-dessous :

{

"queues": {

"maxPollingInterval": 2000,

"visibilityTimeout" : "00:00:30",

"batchSize": 1,

"maxDequeueCount": 5,

"newBatchThreshold": 1

}

}

Assurez-vous de voir apparaître le mot « SAVED » avant de quitter la fenêtre.

Redémarrez la fonction (soit un « Restart », soit un « Stop » puis « Start »)

Test de la fonction

Pour tester les fonctions je vais utiliser :

Microsoft Azure Storage Explorer
SQL Server Management Studio (SSMS)
Les fichiers csv qui sont disponibles ici

Test du trigger, de la copie et de l’archivage du fichier

Connectez-vous sur votre compte de stockage avec Microsoft Azure Storage Explorer et copiez un fichier CSV dans le container « fichiers »

Si tout va bien, les logs de la fonction doivent afficher les messages de traitements du fichier

Avec Microsoft Azure Storage Explorer, vérifiez la présence du fichier dans le dossier « archives »

Vérification de la copie dans SQL Database

Précédemment nous avons vu qu’il était possible d’interagir avec notre base SQL depuis le portail Azure. Il est aussi possible d’utiliser SSMS. Connectez-vous à votre base SQL Database avec SSMS. Pour ce faire, récupérez l’adresse de votre serveur SQL depuis le portail Azure :

Puis dans SSMS :

Vérifiez que les données ont bien été copiées dans votre table.

Pour aller plus loin

Imaginons maintenant que l’on vous demande de pouvoir archiver vos fichiers dans des dossiers dont le nom correspond à la date du jour du traitement (comme ça, par hasard ).

Pour ce faire, il suffit de modifier le fichier « function.json » dans l’éditeur avancé :

Modifiez le fichier comme illustré ci-dessous (la section concernant la sortie):

{

"type": "blob",

"name": "outputBlob",

"path": "archives/{datetime: yyyy-MM-dd-hh-mm}/{name}",

"connection": "franmermlstorage_STORAGE",

"direction": "inout"

}

Cliquez sur le bouton « Save »

Vérification :

Test de montée en charge

Pour ce test de montée en charge j’ai créé des fichiers CSV, de tailles différentes que, je vais charger en même temps dans le container avec Microsoft Azure Storage Explorer. Ces fichiers sont disponibles ici : https://1drv.ms/f/s!Am-C-ktMH9lgg8Rl6y4RjorMxsREfg

Du côté d’Azure Storage, les fichiers semblent être traités de manière séquentielle. Je dis ça, car j’ai plutôt l’impression qu’ils sont traités 2 par 2. J’ai bien entendu essayé de passer le paramètre "newBatchThreshold" du fichier « host.json » à 0, mais la fonction ne démarrait plus ☹ ( j’ai l’erreur : « We are not able to retrieve the runtime master key. Please try again later »).

J’ai regardé du côté Application Insight l’occupation de mes ressources. Le plan sélectionné semble tenir la charge même si on constate des pics de consommation au niveau du CPU.

Une fois l’exécution de la fonction terminée, environ 35 minutes pour ce test, je fais une vérification du côté de SQL Database. On retrouve le bon nombre de lignes pour chacun des 30 fichiers :

Le portail nous montre l’utilisation des DTUs de notre SQL Database, configuré ici à 20 DTUs (S1)

Lorsque j’ai fait les tests en utilisant un « App service plan » basé sur la consommation (comsuption plan), j’ai eu de nombreux timeout, même en paramétrant le fichier “host.json” comme dans cet article, et en passant le paramètre de timeout à 10 minutes (par défaut le timeout dans un plan de consommation est de 5 minutes). J’ai pu résoudre le problème en passant ma base au tier premium / premium RS 2. Soit 250 DTU. Mais je trouvais que ça faisait cher payé pour ce genre de « workload ».

Par conséquent, un autre facteur qui peut jouer, c’est le tier du SQL database utilisé. Dans cet article, j’ai utilisé le tier S1. Dans le cas de timeout ou de besoin de traitements plus rapides, vous pouvez augmenter le tier du SQL Database. Ci-dessous le résultat du test avec une base en S3. Le test a duré environ 25 minutes.

Par curiosité, j’ai fait le test avec une base en Premium RS4 (500 DTUs), l’exécution pend moins de 5 minutes. C’est aussi ça l’intérêt du Cloud, pouvoir utiliser l’élasticité en fonction des besoins.

Voici donc pour une utilisation, en mode « serverless », d’un code C# permettant la copie de fichier CSV vers une base SQL Database.

Les fichiers de la fonction se trouvent sur mon OneDrive : https://1drv.ms/f/s!Am-C-ktMH9lgg8dJjyVZ-qTw7xoAIQ

En espérant vous voir aux Microsoft experiences’17

Franck Mercier