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



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 :

image

  1. Copie du fichier dans un container du blob storage
  2. Déclenchement de la fonction à l’arrivée du fichier
  3. Copie du fichier dans une table SQL Database
  4. 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 ».

image

Cliquez sur « Create »

image

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 ».

clip_image010

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

image

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 :

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 :

image

Création d’une base SQL Database

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

image

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 »

clip_image024

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 :

image


Création de la table

Cliquez sur votre base SQL Database

image

Cliquez sur « Tools »

image

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

image

Entrez vos informations de connexion puis cliquez sur « Ok »

clip_image038

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

)

)

image

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 »

image

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

image

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

image

Cliquez sur « Custom function »

image

Sélectionnez “BlobTrigger C#”

image

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 »

image

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

image


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 »

image

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 »

image


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 »

image


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 »

image


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 »

image

Vous devez arriver sur la vue suivante :

clip_image091

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 »

image


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 :

image

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 :

image

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

image

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

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

image

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"

}

}

}

}

image


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 »

image

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

image

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.

image

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

image

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 »

clip_image128

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

image

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

image


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 :

image

Puis dans SSMS :

image

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

clip_image142


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é :

image

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 »

image

Vérification :

clip_image151


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 »).

clip_image153

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.

clip_image155

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 :

clip_image156

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

clip_image158

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.

clip_image159

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.

clip_image160

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

En espérant vous voir aux Microsoft experiences’17

Franck Mercier


Comments (0)

Skip to main content