Gestion de schéma
Selon votre source de données, des informations sur les types de données et les noms de colonnes peuvent ou non être fournies explicitement. Les API REST OData gèrent généralement cette opération à l’aide de la définition $metadata, et la méthode Power Query OData.Feed
gère automatiquement l’analyse de ces informations et l’applique aux données retournées à partir d’une source OData.
De nombreuses API REST n’ont aucun moyen de déterminer leur schéma par programme. Dans ces cas, vous devez inclure une définition de schéma dans votre connecteur.
L’approche la plus simple consiste à coder en dur une définition de schéma dans votre connecteur. Cela suffit dans la plupart des cas d’usage.
Dans l’ensemble, l’application d’un schéma sur les données retournées par votre connecteur présente plusieurs avantages, par exemple :
- Définition des types de données corrects.
- Suppression de colonnes que les utilisateurs finaux n’ont pas besoin de voir (comme des ID internes ou des informations d’état).
- Garantie que chaque page de données a la même forme par l’ajout de toute colonne qui pourrait manquer dans une réponse (les API REST indiquent généralement que les champs doivent être null en les omettant complètement).
Considérez le code suivant qui retourne une table simple à partir de l’exemple de service TripPin OData:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
Notes
TripPin étant une source OData, il serait plus logique d’utiliser simplement la gestion automatique des schémas de la fonction OData.Feed
. Dans cet exemple, vous allez traiter la source comme une API REST classique, et utiliser Web.Contents
pour illustrer la technique de codage en dur manuel d’un schéma.
Cette table est le résultat :
Vous pouvez utiliser la fonction pratique Table.Schema
pour vérifier le type de données des colonnes :
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)
Les données AirlineCode et Name sont de type any
. Table.Schema
retourne beaucoup de métadonnées sur les colonnes d’une table, dont les noms, les positions, les informations de type, ainsi que de nombreuses propriétés avancées telles que Precision, Scale et MaxLength. Pour lors, vous ne devriez vous soucier que du type attribué (TypeName
), du type primitif (Kind
) et de la possibilité que la valeur de la colonne soit null (IsNullable
).
Votre table de schéma sera composée de deux colonnes :
Colonne | Détails |
---|---|
Nom | Nom de la colonne. Celui-ci doit correspondre au nom dans les résultats retournés par le service. |
Type | Type de données M que vous allez définir. Il peut s’agir d’un type primitif (texte, nombre, dateheure, etc.) ou d’un type attribué (Int64.Type, Currency.Type, etc.). |
La table de schéma codée en dur pour la table Airlines
définit ses colonnes AirlineCode
et Name
sur text
et ressemble à ceci :
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
Lorsque vous examinez certains des autres points de terminaison, tenez compte des tables de schéma suivantes :
La table Airports
comporte quatre champs que vous souhaiterez conserver (dont un de type record
) :
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
La table People
comporte sept champs, dont list
s (Emails
, AddressInfo
), une colonne nullable (Gender
) et une colonne avec un type attribué (Concurrency
) :
People = #table({"Name", "Type"}, {
{"UserName", type text},
{"FirstName", type text},
{"LastName", type text},
{"Emails", type list},
{"AddressInfo", type list},
{"Gender", type nullable text},
{"Concurrency", Int64.Type}
})
Vous pouvez placer toutes ces tables dans une seule table de schéma maître SchemaTable
:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
La fonction d’assistance SchemaTransformTable
décrite ci-dessous sera utilisée pour appliquer des schémas à vos données. Les paramètres suivants sont pris en compte :
Paramètre | Type | Description |
---|---|---|
table | table | Table de données à laquelle vous souhaitez appliquer votre schéma. |
schéma | table | Table de schéma à partir de laquelle lire les informations de colonne, avec le type suivant : type table [Name = text, Type = type] . |
enforceSchema | nombre | (facultatif) Énumération contrôlant le comportement de la fonction. La valeur par défaut ( EnforceSchema.Strict = 1 ) garantit que la table de sortie correspond à la table de schéma fournie en ajoutant toute colonne manquante et en supprimant toute colonne supplémentaire. L’option EnforceSchema.IgnoreExtraColumns = 2 permet de conserver des colonnes supplémentaires dans le résultat. Quand EnforceSchema.IgnoreMissingColumns = 3 est utilisé, les colonnes manquantes et les colonnes supplémentaires sont ignorées. |
La logique de cette fonction ressemble à ceci :
- Déterminer si des colonnes de la table source sont manquantes.
- Déterminer s’il y a des colonnes supplémentaires.
- Ignorer les colonnes structurées (de types
list
,record
ettable
) et les colonnes définies sur le typeany
. - Utiliser
Table.TransformColumnTypes
pour définir chaque type de colonne. - Réorganiser les colonnes en fonction de l’ordre dans lequel elles apparaissent dans la table de schéma.
- Définir le type sur la table proprement dite à l’aide de
Value.ReplaceType
.
Notes
La dernière étape de définition du type de table élimine la nécessité pour l’interface utilisateur de Power Query de déduire les informations de type lors de l’affichage des résultats dans l’éditeur de requête, ce qui entraîne parfois un double appel à l’API.
Dans le contexte plus large d’une extension complète, le traitement du schéma a lieu quand l’API retourne une table. En général, cette fonctionnalité se situe au niveau le plus bas de la fonction de pagination (si elle existe), les informations sur les entités étant transmises à partir d’une table de navigation.
Étant donné qu’une très grande partie de l’implémentation des tables de pagination et de navigation est spécifique du contexte, l’exemple complet d’implémentation d’un mécanisme de gestion des schémas codé en dur n’est pas présenté ici. Cet exemple TripPin montre comment une solution de bout en bout pourrait ressembler.
L’implémentation codée en dur présentée ci-dessus veille bien à ce que les schémas restent cohérents pour les réponses JSON simples, mais est limitée à l’analyse du premier niveau de la réponse. Des jeux de données profondément imbriqués bénéficieraient de l’approche suivante qui tire parti des types M.
Voici une mise à jour rapide sur les types en langage M à partir de la spécification de langage :
Une valeur de type est une valeur qui classifie d’autres valeurs. Une valeur classifiée par un type est dite conforme à ce type. Le système de type M se compose des genres de types suivants :
- Types primitifs, qui classifient des valeurs primitives (
binary
,date
,datetime
,datetimezone
,duration
,list
,logical
,null
,number
,record
,text
,time
,type
) et incluent un certain nombre de types abstraits (function
,table
,any
etnone
).- Types d’enregistrements, qui classifient les valeurs d’enregistrement en fonction des noms de champs et des types de valeurs.
- Types de listes, qui classifient les listes à l’aide d’un type de base d’élément unique.
- Types de fonctions, qui classifient les valeurs de fonction selon les types de leurs paramètres et valeurs de retour.
- Types de tables, qui classifient les valeurs de table en fonction des noms de colonnes, des types de colonnes et des clés.
- Types nullables, qui classifient la valeur null en plus de toutes les valeurs classifiées par un type de base.
- Types de types, qui classifient les valeurs qui sont des types.
En utilisant la sortie JSON brute que vous obtenez (et/ou en recherchant les définitions dans les $metadata du service), vous pouvez définir les types d’enregistrements suivants pour représenter des types complexes OData :
LocationType = type [
Address = text,
City = CityType,
Loc = LocType
];
CityType = type [
CountryRegion = text,
Name = text,
Region = text
];
LocType = type [
#"type" = text,
coordinates = {number},
crs = CrsType
];
CrsType = type [
#"type" = text,
properties = record
];
Notez comment LocationType
référence CityType
et LocType
pour représenter ses colonnes structurées.
Pour les entités de niveau supérieur que vous souhaitez représenter en tant que tables, vous pouvez définir des types de tables :
AirlinesType = type table [
AirlineCode = text,
Name = text
];
AirportsType = type table [
Name = text,
IataCode = text,
Location = LocationType
];
PeopleType = type table [
UserName = text,
FirstName = text,
LastName = text,
Emails = {text},
AddressInfo = {nullable LocationType},
Gender = nullable text,
Concurrency Int64.Type
];
Vous pouvez ensuite mettre à jour votre variable SchemaTable
(que vous pouvez utiliser comme table de recherche pour des mappages d’entités à type) afin d’utiliser ces nouvelles définitions de type :
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
Vous pouvez vous appuyer sur une fonction courant (Table.ChangeType
) pour appliquer un schéma à vos données, tout comme vous avez utilisé SchemaTransformTable
dans l’exercice précédent. Contrairement à SchemaTransformTable
, Table.ChangeType
prend un type de table M réel comme argument, et applique votre schéma de manière récursive pour tous les types imbriqués. Sa signature est :
Table.ChangeType = (table, tableType as type) as nullable table => ...
Notes
Pour plus de flexibilité, la fonction peut être utilisée sur des tables ainsi que sur des listes d’enregistrements (représentant des tables dans un document JSON).
Vous devrez ensuite mettre à jour le code du connecteur pour modifier le paramètre schema
de table
en type
, puis ajouter un appel à Table.ChangeType
. Là encore, les détails de cette opération étant très spécifiques de l’implémentation, il n’est pas utile de s’y attarder ici. Cet exemple de connecteur TripPin étendu illustre une solution de bout en bout implémentant cette approche plus sophistiquée de la gestion de schéma.