Nativement non supporté il est toutefois possible d’ajouter un Extractor / Outputter JSON au sein d’Azure Data Lake Ananytics.
Le sujet est largement traité et n’est pas l’objet de cet article qui retrace d’avantage certaines difficultés rencontrées lors de mes pérégrinations U-SQL. L’article de SQL Chick couvre très largement le sujet => https://www.sqlchick.com/entries/2017/9/4/querying-multi-structured-json-files-with-u-sql-in-azure-data-lake
Toutefois, quelques précisions :
- Dans mon usage du U-SQL actuel, je préfère garder les DLL(s) spécifiques dans Master
- Il est bon de noter qu’une fois l’enregistrement de la DLL effectuée c’est une copie de celle-ci qui sera utilisée et non pas celle présente dans votre dossier ADLS (vous pouvez donc la supprimer d’ADLS avec l’enregistrement). Par conséquent, si vous faites une mise à jour de la DLL, il sera nécessaire de droper la DLL et de l’ajouter à nouveau.
Les étapes de configuration du support JSON au sein d’Azure Datalake Analytics derrière nous, passons au requêtage.
Les exemples tournent souvent autour de cas simples comme ceux du projet présent sur Github , toutefois certains cas ne sont pas traités tels que :
- Le Json avec un noeud global qui contient toutes les données et donc dépasse la limite maximum d’un string qu’il est possible de traiter dans Azure Data Lake Analytics et qui produit le message suivant :
String size 238394 exceeds the maximum allowed size of 131072
Cela se produit par exemple avec un fichier JSON de ce type :
[{
"Key": "1",
"Type": "Site",
"SyncData": {
"LegalService": "AAAA",
"AccountingCompany": null,
"BusinessUnit": "ABCDR",
"CoreBusiness": "EN",
"Employees": [
{
"FamilyName": "toto",
"Given Name": "titi"
},
....
,
{
"FamilyName": "tutu",
"Given Name": "Fabien"
}
}]
Afin d’extraire la liste des « LegalService » nous serions tenté d’écrire :
@JsonRawData = EXTRACT SyncData string ,date DateTime FROM @InputPath USING new JsonExtractor(); @Dataset = SELECT JsonFunctions.JsonTuple(SyncData) ["LegalService"] AS LegalService FROM @JsonRawData;
Malheureusement si la taille du nœud SyncData dépasse 131072 caractères, cela résultera en erreur.
Pour régler le problème, il est possible d’utiliser le MultiLevelJsonExtractor() de la façon suivante :
@JsonRawData =
EXTRACT LegalService string,
FROM @InputPath
USING new MultiLevelJsonExtractor("SyncData",
false,
"LegalService"
);
Se faisant nous ne dépasserons plus la taille limite d’un String.
- Le nom de nos nœuds contient des caractères spéciaux, des espaces…
Il est possible d’utiliser les mêmes méthodes d’échappement disponibles dans newtonsoft ainsi qu’indiqué ici => https://www.newtonsoft.com/json/help/html/QueryJsonSelectTokenEscaped.htm
Afin de récupérer les informations du nœud « Given Name » il sera possible d’exécuter la requête suivante :
@JsonRawData =
EXTRACT GivenName string,
FROM @InputPath
USING new MultiLevelJsonExtractor("SyncData",
false,
"['Given Name']"
);
L’utilisation de [‘ autour de notre champ Given Name permet de supporter l’espace dans le nom. Vous noterez que la colonne exposée sera nommée GivenName, car lors de l’utilisation du MultiLevelJsonExtractor, le mapping n’est plus fait sur le nom mais sur l’index des colonnes déclarées.
Pour le moment voici les quelques exemples que je voulais partager avec vous.
