Afhandelingsschema
Afhankelijk van uw gegevensbron kunnen gegevenstypen en kolomnamen expliciet worden verstrekt. OData REST API's verwerken dit doorgaans met behulp van de $metadata definitie. De Power Query-methode OData.Feed
verwerkt deze informatie automatisch en past deze toe op de gegevens die worden geretourneerd uit een OData-bron.
Veel REST API's hebben geen manier om programmatisch hun schema te bepalen. In deze gevallen moet u een schemadefinitie opnemen in uw connector.
De eenvoudigste methode is om een schemadefinitie in uw connector vast te stellen. Dit is voldoende voor de meeste gebruiksvoorbeelden.
Over het algemeen heeft het afdwingen van een schema voor de gegevens die door uw connector worden geretourneerd meerdere voordelen, zoals:
- De juiste gegevenstypen instellen.
- Kolommen verwijderen die niet hoeven te worden weergegeven voor eindgebruikers (zoals interne id's of statusgegevens).
- Zorg ervoor dat elke pagina met gegevens dezelfde vorm heeft door kolommen toe te voegen die mogelijk ontbreken in een antwoord (REST API's geven meestal aan dat velden null moeten zijn door ze volledig weg te laten).
Bekijk de volgende code die een eenvoudige tabel retourneert van de TripPin OData-voorbeeldservice:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
Notitie
TripPin is een OData-bron, dus realistischer zou het zinvol zijn om gewoon de automatische schemaafhandeling van de OData.Feed
functie te gebruiken. In dit voorbeeld behandelt u de bron als een typische REST API en gebruikt u Web.Contents
om de techniek van het vooraf coderen van een schema te demonstreren.
Deze tabel is het resultaat:
U kunt de handige Table.Schema
functie gebruiken om het gegevenstype van de kolommen te controleren:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)
Zowel AirlineCode als Name zijn van het any
type. Table.Schema
retourneert veel metagegevens over de kolommen in een tabel, waaronder namen, posities, typegegevens en veel geavanceerde eigenschappen, zoals Precision, Scale en MaxLength. Op dit moment moet u zich alleen zorgen maken over het toegeschreven type (TypeName
), primitief type (Kind
) en of de kolomwaarde null (IsNullable
) kan zijn.
De schematabel bestaat uit twee kolommen:
Kolom | DETAILS |
---|---|
Naam | De naam van de kolom. Dit moet overeenkomen met de naam in de resultaten die door de service worden geretourneerd. |
Type | Het M-gegevenstype dat u wilt instellen. Dit kan een primitief type zijn (tekst, getal, datum/tijd, enzovoort) of een gescribeerd type (Int64.Type, Valuta.Type, enzovoort). |
De in code vastgelegde schematabel voor de tabel stelt de Airlines
tabel AirlineCode
en Name
kolommen text
in op en ziet er als volgt uit:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
Houd rekening met de volgende schematabellen terwijl u naar enkele van de andere eindpunten kijkt:
De Airports
tabel bevat vier velden die u wilt behouden (inclusief een van het type record
):
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
De People
tabel heeft zeven velden, waaronder list
s (Emails
, AddressInfo
), een nullable kolom (Gender
) en een kolom met een toegewezen type (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}
})
U kunt al deze tabellen in één hoofdschematabel SchemaTable
plaatsen:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
De SchemaTransformTable
helperfunctie die hieronder wordt beschreven, wordt gebruikt om schema's voor uw gegevens af te dwingen. Hiervoor worden de volgende parameters gebruikt:
Parameter | Type | Description |
---|---|---|
table | table | De tabel met gegevens waarop u uw schema wilt afdwingen. |
schema | table | De schematabel waaruit kolomgegevens moeten worden gelezen, met het volgende type: type table [Name = text, Type = type] |
enforceSchema | Nummer | (optioneel) Een opsomming waarmee het gedrag van de functie wordt bepaald. De standaardwaarde ( EnforceSchema.Strict = 1 ) zorgt ervoor dat de uitvoertabel overeenkomt met de schematabel die is opgegeven door ontbrekende kolommen toe te voegen en extra kolommen te verwijderen. De EnforceSchema.IgnoreExtraColumns = 2 optie kan worden gebruikt om extra kolommen in het resultaat te behouden. Wanneer EnforceSchema.IgnoreMissingColumns = 3 deze wordt gebruikt, worden zowel ontbrekende kolommen als extra kolommen genegeerd. |
De logica voor deze functie ziet er ongeveer als volgt uit:
- Bepaal of er ontbrekende kolommen in de brontabel zijn.
- Bepaal of er extra kolommen zijn.
- Gestructureerde kolommen negeren (van het type
list
,record
entable
) en kolommen die zijn ingesteld op typeany
. - Gebruik
Table.TransformColumnTypes
dit om elk kolomtype in te stellen. - Kolommen opnieuw ordenen op basis van de volgorde die ze in de schematabel weergeven.
- Stel het type in de tabel zelf in met behulp van
Value.ReplaceType
.
Notitie
Met de laatste stap voor het instellen van het tabeltype hoeft de Gebruikersinterface van Power Query geen typegegevens af te leiden bij het weergeven van de resultaten in de query-editor. Dit kan soms leiden tot een dubbele aanroep naar de API.
In de grotere context van een volledige extensie vindt de verwerking van het schema plaats wanneer een tabel wordt geretourneerd vanuit de API. Deze functionaliteit vindt doorgaans plaats op het laagste niveau van de pagingfunctie (indien aanwezig), met entiteitsgegevens die worden doorgegeven vanuit een navigatietabel.
Omdat veel van de implementatie van paging- en navigatietabellen contextspecifiek is, wordt hier niet het volledige voorbeeld van het implementeren van een in code vastgelegd mechanisme voor schemaafhandeling weergegeven. In dit TripPin-voorbeeld ziet u hoe een end-to-end-oplossing eruit kan zien.
De hierboven besproken vastgelegde implementatie zorgt ervoor dat schema's consistent blijven voor eenvoudige JSON-repsonses, maar het is beperkt tot het parseren van het eerste niveau van het antwoord. Diep geneste gegevenssets profiteren van de volgende benadering, die gebruikmaakt van M-typen.
Hier volgt een snelle vernieuwing van typen in de M-taal uit de taalspecificatie:
Een typewaarde is een waarde die andere waarden classificeert . Een waarde die door een type wordt geclassificeerd, wordt gezegd dat deze voldoet aan dat type. Het M-typesysteem bestaat uit de volgende typen:
- Primitieve typen, die primitieve waarden classificeren (
binary
,date
,datetime
,datetimezone
,duration
, ,list
,null
text
logical
number
record
, ,time
)type
en ook een aantal abstracte typen (function
,table
,any
en ).none
- Recordtypen, waarmee recordwaarden worden geclassificeerd op basis van veldnamen en waardetypen.
- Lijsttypen, waarmee lijsten worden geclassificeerd met één itembasistype.
- Functietypen, waarmee functiewaarden worden geclassificeerd op basis van de typen parameters en retourwaarden.
- Tabeltypen, waarmee tabelwaarden worden geclassificeerd op basis van kolomnamen, kolomtypen en sleutels.
- Null-typen, waarmee de waarde null wordt geclassificeerd naast alle waarden die zijn geclassificeerd door een basistype.
- Typetypen, waarmee waarden worden geclassificeerd die typen zijn.
Met behulp van de onbewerkte JSON-uitvoer die u krijgt (en/of door de definities op te zoeken in de $metadata van de service), kunt u de volgende recordtypen definiëren om complexe OData-typen weer te geven:
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
];
Zoals u ziet, LocationType
verwijst naar de CityType
en LocType
om de gestructureerde kolommen ervan weer te geven.
Voor de entiteiten op het hoogste niveau die u wilt weergeven als tabellen, kunt u tabeltypen definiëren:
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
];
Vervolgens kunt u uw SchemaTable
variabele (die u kunt gebruiken als opzoektabel voor entiteits-naar-type-toewijzingen) bijwerken om deze nieuwe typedefinities te gebruiken:
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
U kunt vertrouwen op een algemene functie (Table.ChangeType
) om een schema af te dwingen voor uw gegevens, net zoals u in de vorige oefening hebt gebruikt SchemaTransformTable
. In tegenstelling tot SchemaTransformTable
, Table.ChangeType
wordt een werkelijk M-tabeltype als argument gebruikt en wordt uw schema recursief toegepast voor alle geneste typen. De handtekening is:
Table.ChangeType = (table, tableType as type) as nullable table => ...
Notitie
Voor flexibiliteit kan de functie worden gebruikt voor tabellen en lijsten met records (zoals tabellen worden weergegeven in een JSON-document).
Vervolgens moet u de connectorcode bijwerken om de schema
parameter van een table
naar een type
te wijzigen en een aanroep toe te voegen aan Table.ChangeType
. Nogmaals, de details hiervoor zijn zeer implementatiespecifiek en dus niet de moeite waard om hier uitvoerig in te gaan. In dit uitgebreide Voorbeeld van een TripPin-connector ziet u een end-to-end-oplossing voor het implementeren van deze geavanceerdere benadering voor het afhandelen van schema's.