[Azure] Optimisation de vos chargement dans Azure Datawarehouse

Contexte :

Suite à la mise en place de données dans un projet Azure chez un client nous avons été confronté à des problèmes de performance lors de l’intégration des données dans notre Azure Datawarehouse

Afin de gagner du temps nous récupérons différentes interfaces sous format Csv que nous transformons en format .parquet

L’intérêt du format parquet est de gagner du temps lors de l’exploitation de ces différentes interfaces car le format parquet permet de structurer les données et de stocker les données de manière pré formaté.

Dans notre cas nous avons souhaité héberger ces données dans un service distribué comme Azure Data Lake.

Ce service est un service distribué qui permet de stocker les fichiers « formatés ». L’intérêt de ce système de stockage est de gagner du temps sur l’interrogation de ces fichiers car nous sommes sur un système distribué.

1

 

Mise en œuvre :

La transformation des fichiers csv en format parquet est réalisée via le service Azure Data Factory que nous verrons dans un autre article qui vous sera présenté plus tard.

Une fois la mise en place de ces fichiers parquet réalisé nous avons plus qu’à nous connecter via polybase sur notre Azure DataLake pour venir charger nos données de manière optimisé.

Voici un exemple de code permettant de réaliser dans un premier temps notre table externe pointant vers un fichier parquet.

Une fois cette table réalisé il ne nous reste plus qu’à créer une procédure stockée venant s’appuyer sur cette table externe pour charger les données dans notre DWH. Le Chargement s’effectue de manière optimisé car réalisé sur un environnement distribué de part en part.

Pour réaliser ces tables externes nous devons créer les étapes suivantes :

Réalisation de la connexion permettant de nous connecter au data lake depuis SQL DWH

IF NOT EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name=’ADLSCredential’)

BEGIN

       CREATE MASTER KEY;

 

       SET @sqlstmt= ‘

             CREATE DATABASE SCOPED CREDENTIAL ADLSCredential

             WITH

                    IDENTITY = ‘ + @identity + ‘

                    ,SECRET = ‘ + @secret

             ;

 

       EXECUTE sp_executesql @sqlstmt;

END

Création d’une source externe vers le Service DataLake

———————————————————————————————————-

— Create an external data source on Azure Data Lake Store

— ———————————————————————————————————-

IF NOT EXISTS (SELECT * FROM [sys].[external_data_sources] WHERE name=’AzureDataLakeStore’)

BEGIN

       CREATE EXTERNAL DATA SOURCE AzureDataLakeStore

       WITH (

             TYPE = HADOOP,

             LOCATION = ‘adl://testjpi.azuredatalakestore.net’,

             CREDENTIAL = ADLSCredential

       );

END

 

Création de la connexion vers les fichiers parquet pour que SQL Server puisse les interpréter

– ———————————————————————————————————-

— Create an external file on Azure Data Lake Store (rawdata)

— ———————————————————————————————————-

IF NOT EXISTS (SELECT * FROM [sys].[external_file_formats] WHERE name=’ParquetFile’)

BEGIN

       CREATE EXTERNAL FILE FORMAT ParquetFile

       WITH ( 

             FORMAT_TYPE = PARQUET

             , DATA_COMPRESSION = ‘org.apache.hadoop.io.compress.SnappyCodec’  

       ); 

END

 

 

Creation des tables externe
IF EXISTS (SELECT * FROM sys.tables WHERE type=’U’ AND schema_name(schema_id) = ‘dbo’ and name=’EXTTEST’)

DROP EXTERNAL TABLE EXTTEST

GO

 

CREATE EXTERNAL TABLE [EXTTEST]

(

   Id  int,

   Test nvarchar(50)

)

WITH (

DATA_SOURCE = [AzureDataLakeStore],

LOCATION = N’/data/test.parquet’,

FILE_FORMAT = [ParquetFile]

);

 

Une fois cette opération réalisé il n’y a plus qu’a venir créer notre table sur notre SQL DataWarehouse pour cela nous allons réaliser une procédure de chargement :

 

IF EXISTS (SELECT 1 FROM sys.procedures WHERE type=’P’ and name=’uspLoadTEST’ AND schema_name(schema_id) = ‘dbo’ )

DROP PROC [txt].[uspLoadTEST];

GO

 

CREATE PROC [txt].[uspLoadTEST] AS

BEGIN

 

— Create new table by loading from ADLS with Polybase

IF EXISTS (SELECT 1 FROM sys.tables where type=’U’ AND schema_name(schema_id) = ‘dbo’ and name=’TESTNew’)

DROP TABLE [TESTNew];

 

CREATE TABLE  [TESTNew]

WITH

(

DISTRIBUTION = REPLICATE – 3 modes de distribution existent (ROUND_ROBIN / REPLICATE / HASH)

)

AS

SELECT

ISNULL(CreditControlArea, ») + N’|’ + ISNULL(Languagekey, ») AS CreditControlAreaBKey

,CreditControlArea

,Languagekey            

,Longdescription       

,GETDATE() AS CreatedOn 

,USER_NAME() AS CreatedBy

FROM

[EXTTEST]

 

  

— Rebuild columnstore index

ALTER INDEX ALL ON [txt].[TEST New] REBUILD;

 

— Renaming tables to replace the old table by the new one

IF EXISTS (SELECT 1 FROM sys.tables where type=’U’ AND schema_name(schema_id) = ‘dbo’ and name=’TEST’)

BEGIN

IF EXISTS (SELECT 1 FROM sys.tables where type=’U’ AND schema_name(schema_id) = ‘dbo’ and name=’TESTOld’)

DROP TABLE [TESTOld];

 

RENAME OBJECT [TEST] TO [TESTOld];

END

 

RENAME OBJECT [TESTNew] TO [TEST];

 

IF EXISTS (SELECT 1 FROM sys.tables where type=’U’ AND schema_name(schema_id)=’dbo’ and name=’TESTOld’)

DROP TABLE [TESTOld];

 

END

Conclusion 

Dans cet article nous avons vu comment récupérer des données d’interface, comment les mettre à disposition sur un environnement distribué comme Azure DataLake et les charger dans un environnement distribué comme SQL Datawarhouse via Polybase cette méthode a pour objectif de vous faire gagner beaucoup de temps lors de vos chargement  volumineux dans Azure.

Pour aller un peu plus loin :

 

https://parquet.apache.org/documentation/latest/

http://blog.cloudera.com/blog/2016/04/benchmarking-apache-parquet-the-allstate-experience/

https://docs.microsoft.com/fr-fr/azure/sql-data-warehouse/sql-data-warehouse-get-started-load-with-polybase

 

Julien PIERRE

Consultant MSBI

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s