Retour d`expérience : MySQL vers Azure SQL Database, via

70 azure
# 206
Retour d'expérience : MySQL vers
Azure SQL Database, via Azure Data Factory
Azure Data Factory est un service Cloud de traitement de données structurées ou non structurées
provenant de sources de données variées. ADF, son surnom, permet de traiter des données en entrée et
de les déverser dans un stockage en sortie. Généralement en BigData, ce service se limite à effectuer une
copie en « quasi-un pour un » des données.
Nous verrons pourtant que cela n’est pas forcément aussi simple qu’il n’y paraît.
Loris Andaloro,
Expert Data senior
chez VISEO
Ihor Leontiev,
Architecte Azure,
MVP Azure
chez VISEO
ous parlons de « quasi-un pour un »,
car il y a forcément quelques transtypages à effectuer pour passer d'une
base de données à une autre. D’un point de
vue théorique on est face à un ETL (Extract
Transform Load), logiciel bien connu dans le
monde de la Business Intelligence, mais pas
seulement. En pratique, nous utilisons cet outil
en mode ELT (Extract Load Transform) car on
est ici dans le cadre du Cloud et du BigData :
on ne veut pas modifier les données lors de
l’import (« ingestion »), mais les enregistrer
brutes dans un Data Lake. Et si on a à les modifier (« Transform ») et analyser (« Analyse »), ce
sera ultérieurement, et potentiellement dans
quelques années (Fig 1).
Ci-contre les principaux concepts d’Azure Data
Factory (Fig 2).
Dans une instance Data Factory, vous créez un
ou plusieurs pipelines de données. Un pipeline constitue un groupe d’activités qui définit
les actions à effectuer. Par exemple, vous pouvez utiliser une « activité de copie » pour copier
des données d’une banque de données vers
une autre. De même, vous pouvez utiliser une
« activité Hive » qui exécute une requête Hive
sur un cluster Azure HDInsight afin de convertir ou d’analyser vos données. ADF prend en
charge deux types d’activités : déplacement
des données et transformation des données.
Les dataset sont des jeux de données qui peuvent être consommés ou produits par les activités. Enfin chaque exécution d’une activité produit ce que l’on appelle un data slice.
D'une part, nous allons voir comment nous avons mis en place une extraction de table depuis MySQL (On Premise) vers une base de données
Azure SQL Database (Fig 3). Et d'autre part, nous allons évoquer les difficultés, qui commencent par de nombreuses incompatibilités entre les
Vincent Thavonekham,
Responsable Stratégie Azure,
MVP Azure chez VISEO
N
programmez! - avril 2017
1
2
3
types de données, ainsi que ce qui concerne la planification des traitements.
Tout d'abord, il faut instancier ADF (Fig 4) et déployer la Gateway sur le
réseau local comme précisé dans la documentation donnée en référence
à la fin.
Ensuite, nous pouvons configurer le déplacement des données en cli-
azure
71
# 206
quant sur « Copier des données » afin de créer notre premier pipeline.
Une nouvelle page s’ouvre sur une procédure pas à pas de création de
pipeline (Fig 5). Attention, cet outil est encore en version beta, alors que
la solution ADF est en version release. Commencez par nommer le pipeline. (Fig 5)
Puis renseigner la source de données MySQL, parmi de nombreuses
autres options comme Amazon Redshift/S3, Azure Blob Storage/SQL
Datawarehouse, Cassandra, MongoDB, … (Fig 6, Fig 7)
Et choisir la table source dans l’étape « Source », onglet « EXISTING
TABLES » (Fig 8)
Ensuite, la base de données de destination (liste non exhaustive, Fig 9)
Indiquer enfin la table de destination (Fig 10)
Les premiers soucis se présentent (Fig 11) ; certains types de données
semblent ne pas être supportés, empêchant donc la création du pipeline.
7
4
8
5
9
10
6
programmez! - avril 2017
72 azure
# 206
L'astuce consiste à revenir à l’étape « Source » onglet « USE QUERY »
(Fig 12). Cela permet l'utilisation d'une requête avec des CAST au lieu de
la table (nous verrons que cela n’est pas suffisant non plus lié à un autre
point bloquant).
Voici un exemple de requête à utiliser :
SELECT
CAST(champ1 AS NVARCHAR) AS champ1,
CAST(champ2 AS Signed Integer) AS champ2,
champ3,
IF(champ4>= '1900/01/01', champ4, NULL) AS champ4
FROM table
Avec des CAST convenables sur les champs problématiques, il devient
possible de mapper toutes les colonnes sans erreurs. J’ai recensé les principales équivalences entre les types MySQL et SQL Server, ainsi que les
opérations à effectuer au niveau de la requête SELECT.
Types
MySQL
bigint
blob
char(X)
date
datetime
decimal
double
enum
float
int
longblob
longtext
mediumtext
set
smallint
text
time
timestamp
tinyint
varchar(X)
11
Type SQL
Server
recommandé
bigint
varbinary(max)
nchar(X)
date
datetime
decimal
float
nvarchar(max)
float
int or bigint
Opération à effectuer
IF(xxxx >= '1900/01/01', xxxx, NULL) as xxxx,
IF(xxxx >= '1900/01/01', xxxx, NULL) as xxxx,
CAST(xxxx AS VARCHAR)
Dépend du flag UNSIGNED MySQL : CAST
(xxxx AS SIGNED BIGINT) ou
CAST(xxxx AS SIGNED INT)
varbinary(max)
nvarchar(max)
nvarchar(max)
nvarchar(max) CAST(xxxx AS NVARCHAR)
smallint or int Dépend du flag UNSIGNED MySQL : CAST
(xxxx AS SIGNED INT) ou
CAST(xxxx AS SIGNED SMALLINT)
nvarchar(max)
time
smalldatetime
tinyint
Dépend du flag UNSIGNED MySQL : CAST
or smallint
(xxxx AS SIGNED SMALLINT)
ou CAST(xxxx AS SIGNED TINYINT)
nvarchar(X)
ADF s’améliorant rapidement, ces règles évoluent également très vite
également. Au fil des mois, nous constatons qu’il devient de plus en plus
simple d’importer les données MySQL ; donc moins de hack de conversions et cela fonctionne sans problèmes (Fig 13).
Nous poursuivons avec l’assistant de configuration jusqu’au déploiement
du pipeline, sans encombre ; cela génère un fichier JSON dans le menu
Créer et déployer. C’est la définition du pipeline que nous venons de
mettre en place.
Aussi, grâce au Cloud Explorer intégré à Visual Studio vous pouvez
récupérer votre pipeline et l’éditer au format JSON. Pour cela il faut naviguer dans l’arborescence jusqu’à votre instance ADF puis effectuer un
clic droit et choisir le menu Export to New Data Factory Project (Fig
14).
12
13
14
programmez! - avril 2017
azure
73
# 206
Il n’est en effet pas possible, pour le moment, de revenir à l’assistant Web
« Copy Activity » des écrans précédents pour éditer le pipeline.
Le dernier problème bloquant de cet assistant est que nous avions plusieurs milliers de « schema mappings » à mettre en œuvre. Impossible
donc de passer plusieurs jours à faire des clics dans le navigateur.
D’autant que nos nombreuses tentatives désespérées mettaient systématiquement le JavaScript du navigateur sur les rotules (quel que soit le
navigateur). Nous avons eu à développer un programme C# ayant la
capacité de détecter les tables source et générer ces fichiers JSON à partir d’une base source.
Autres possibilités de ADF : Orchestration,
planification et monitoring
La planification de l’heure de lancement du traitement se fait avec le
paramètre offset. A ce jour, une astuce peu documentée que Microsoft
Corp nous a suggérée pour choisir l’heure précise, consiste à configurer
le paramètre start avec systématiquement une valeur à minuit (ci-dessous 2017-02-21T00:00:00Z). Sinon le pipeline ne fonctionnera pas.
"policy": {
"timeout": "1.00:00:00",
"concurrency": 1,
"executionPriorityOrder": "NewestFirst",
"style": "StartOfInterval",
"retry": 3,
"longRetry": 0,
"longRetryInterval": "00:00:00"
},
"scheduler": {
"frequency": "Hour",
"interval": 24,
"offset": "03:00:00"
},
"name": "Activity-0-_Custom query_->[dbo]_[xxxxxxxxx]"
],
"start": "2017-02-21T00:00:00Z",
"end": "9999-09-09T00:00:00Z",
"isPaused": false,
"pipelineMode": "Scheduled"
La partie Monitoring d’ADF est très importante et correspond à un de ses
gros points forts, et obligatoire pour faire du « Data Lineage » et ainsi
auditer les données et les nombreux traitements quotidiens. Pour y accéder, cliquer l’icône « Surveiller et gérer » (Fig 4), et cela ouvre une nouvelle fenêtre. Autant dire que nous avons passé des journées entières les
yeux rivés sur les différents pipelines qui s’exécutent, avec des Logs
détaillés.
CONCLUSION
La documentation officielle de Microsoft, ainsi de que nombreuses
recherches, ont bien aidé à avancer et nous sommes actuellement en
production avec ADF. Bien que nous ayons réduit le périmètre de ce
retour d’expérience à l’unique brique ADF (afin que cela soit aisément
compréhensible), cette brique qui semble rapide et simple à appréhender peut se révéler bien plus compliquée qu’il n’y paraît.
Dans notre cas, au-delà de ces petites subtilités, j'avoue que sans l'aide
de l'équipe produit de Microsoft Corp qui développe ADF, nous n'aurions pas réussi la mise en production ; depuis, cela fonctionne comme
un charme au quotidien !
•
Références
Introduction ADF :
https://docs.microsoft.com/fr-fr/azure/data-factory/data-factory-introduction
Format JSON d’ADF :
https://docs.microsoft.com/en-us/azure/data-factory/data-factory-introduction
Configurer ADF Gateway sur le réseau local :
https://docs.microsoft.com/fr-fr/azure/data-factory/data-factory-move-data-between-onpremand-cloud
POUR ALLER PLUS LOIN
Nous avons vu dans cet article un aperçu de l’utilisation d’ADF. Il est légitime de se demander pourquoi faire autant d’efforts ?
alors qu’un ETL comme SSIS est déjà présent sur le marché depuis des années.
Au-delà du fait que SSIS ne soit pas « scalable »,
ADF va permettre d’adresser des scénarii Big Data
inaccessibles jusqu’à présent.
Dans le schéma, un élément frappant est d’une
part la répartition des zones de stockage, où cela
diffère des traditionnels BI avec un “Staging area”,
un “Operational Data Store” et un “Datawarehouse”
remplies et vidées quotidiennement. D’autre part,
ADF gère une grande variété des sources de données : les bases de données métier de l’entreprise,
le CRM, l’ERP, mais aussi les réseaux sociaux, les
services Open Data, l’Internet des objets (IoT),
etc… Enfin ADF travaille sur une fréquence des
traitements et une puissance de calculs bien supérieur: les données
sont importées, puis transformés plusieurs fois ; tantôt en (near-)temps
réel (ex IoT), tantôt en batch (ex. calcul d’indicateurs récurrents
chaque soir, ou analyse de sentiments Twitter/Facebook durant le lancement d’un produit).
Afin de répondre aux enjeux actuels de la Data et d’analyses orientées données, Microsoft investit de manière importante dans tous les
outils Data, et en particulier sur cet outil, véritable chef d’orchestre de
la Data au sein du système d’information dématérialisé de l’ « entreprise 2.0 » et de l’« Industrie 4.0 » !
programmez! - avril 2017