TripPin deel 6 - Schema

Deze meerdelige zelfstudie bevat informatie over het maken van een nieuwe gegevensbronextensie voor Power Query. De zelfstudie is bedoeld om opeenvolgend te worden uitgevoerd. Elke les bouwt voort op de connector die in de vorige lessen is gemaakt, en voegt incrementeel nieuwe mogelijkheden toe aan uw connector.

In deze les gaat u het volgende doen:

• Een vast schema definiëren voor een REST API
• Gegevenstypen dynamisch instellen voor kolommen
• Een tabelstructuur afdwingen om transformatiefouten te voorkomen vanwege ontbrekende kolommen
• Kolommen verbergen in de resultatenset

Een van de grote voordelen van een OData-service ten opzichte van een standaard REST API is de $metadata definitie. In het $metadata document worden de gegevens beschreven die in deze service zijn gevonden, inclusief het schema voor alle entiteiten (tabellen) en velden (kolommen). De OData.Feed functie gebruikt deze schemadefinitie om automatisch gegevenstypegegevens in te stellen, dus in plaats van alle tekst- en numerieke velden (zoals u dat zou doen Json.Document), krijgen eindgebruikers datums, gehele getallen, tijden enzovoort, zodat ze een betere algehele gebruikerservaring bieden.

Veel REST API's hebben geen manier om programmatisch hun schema te bepalen. In deze gevallen moet u schemadefinities opnemen in uw connector. In deze les definieert u een eenvoudig, vastgelegd schema voor elk van uw tabellen en dwingt u het schema af op de gegevens die u uit de service hebt gelezen.

Notitie

De hier beschreven benadering werkt voor veel REST-services. Toekomstige lessen zijn gebaseerd op deze benadering door recursief schema's af te dwingen op gestructureerde kolommen (record, lijst, tabel) en voorbeeldimplementaties te bieden die programmatisch een schematabel kunnen genereren op basis van CSDL- of JSON-schemadocumenten .

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 (een veelgebruikte manier voor REST API's om aan te geven dat een veld null moet zijn)

Het bestaande schema weergeven met Table.Schema

De connector die in de vorige les is gemaakt, bevat drie tabellen uit de TripPin-service,AirlinesAirports en People. Voer de volgende query uit om de Airlines tabel weer te geven:

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    data

In de resultaten ziet u vier kolommen die worden geretourneerd:

• @odata.id
• @odata.editLink
• AirlineCode
• Naam

De kolommen '@odata.*' maken deel uit van het OData-protocol en niet iets wat u wilt of moet weergeven aan de eindgebruikers van uw connector. AirlineCode en Name zijn de twee kolommen die u wilt behouden. Als u het schema van de tabel bekijkt (met behulp van de handige functie Table.Schema ), kunt u zien dat alle kolommen in de tabel een gegevenstype hebben Any.Type.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Table.Schema retourneert veel metagegevens over de kolommen in een tabel, waaronder namen, posities, typegegevens en veel geavanceerde eigenschappen, zoals Precisie, Schaal en MaxLength. Toekomstige lessen bieden ontwerppatronen voor het instellen van deze geavanceerde eigenschappen, maar voor nu hoeft u zich alleen bezig te houden met het ingeschreven type (TypeName), primitief type (Kind) en of de kolomwaarde null (IsNullable) kan zijn.

Een eenvoudige schematabel definiëren

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 (text, datetimenumberenzovoort) of een gescribeerd type (Int64.Type, Currency.Typeenzovoort) zijn.

De in code vastgelegde schematabel voor de tabel stelt de Airlines bijbehorende AirlineCode en Name kolommen textin en ziet er als volgt uit:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    });

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}
    });

Ten slotte bevat de People tabel zeven velden, waaronder lijsten (Emails, AddressInfo), een null-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}
    })

De helperfunctie SchemaTransformTable

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:

1. Bepaal of er ontbrekende kolommen in de brontabel zijn.
2. Bepaal of er extra kolommen zijn.
3. Gestructureerde kolommen (van het type list, recorden table) en kolommen negeren die zijn ingesteld op type any.
4. Gebruik Table.TransformColumnTypes om elk kolomtype in te stellen.
5. Kolommen opnieuw ordenen op basis van de volgorde die ze in de schematabel weergeven.
6. Stel het type in de tabel zelf in met Value.ReplaceType.

Notitie

De laatste stap voor het instellen van het tabeltype zorgt ervoor dat de Gebruikersinterface van Power Query gegevens moet afleiden bij het weergeven van de resultaten in de query-editor. Hiermee verwijdert u het probleem met dubbele aanvragen dat u aan het einde van de vorige zelfstudie hebt gezien.

De volgende helpercode kan worden gekopieerd en geplakt in uw extensie:

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
    let
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
            let
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
            in
                changedPrimatives,

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
            let
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
            in
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
                schemaNames
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                foundNames
            else
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
    in
        withType;

De TripPin-connector bijwerken

U gaat nu de volgende wijzigingen aanbrengen in uw connector om gebruik te maken van de nieuwe code voor het afdwingen van schema's.

1. Definieer een hoofdschematabel (SchemaTable) die al uw schemadefinities bevat.
2. Werk de TripPin.Feedparameter bij GetPageen GetAllPagesByNextLink accepteer een schema parameter.
3. Dwing uw schema af in GetPage.
4. Werk de code van de navigatietabel bij om elke tabel te verpakken met een aanroep naar een nieuwe functie (GetEntity). Hierdoor hebt u meer flexibiliteit om de tabeldefinities in de toekomst te bewerken.

Modelschematabel

U gaat nu uw schemadefinities samenvoegen in één tabel en een helperfunctie (GetSchemaForEntity) toevoegen waarmee u de definitie kunt opzoeken op basis van een entiteitsnaam (bijvoorbeeld GetSchemaForEntity("Airlines")).

SchemaTable = #table({"Entity", "SchemaTable"}, {
    {"Airlines", #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    })},    
    
    {"Airports", #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    })},

    {"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}
    })}
});

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

Schemaondersteuning toevoegen aan gegevensfuncties

U voegt nu een optionele schema parameter toe aan de TripPin.Feed, GetPageen GetAllPagesByNextLink functies. Hiermee kunt u het schema (wanneer u wilt) doorgeven aan de wisselfuncties, waar het wordt toegepast op de resultaten die u van de service ontvangt.

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

U werkt ook alle aanroepen naar deze functies bij om ervoor te zorgen dat u het schema correct doorgeeft.

Het schema afdwingen

De daadwerkelijke afdwinging van het schema wordt uitgevoerd in uw GetPage functie.

GetPage = (url as text, optional schema as table) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
    in
        withSchema meta [NextLink = nextLink];

Notitie

Deze GetPage implementatie maakt gebruik van Table.FromRecords om de lijst met records in het JSON-antwoord te converteren naar een tabel. Een belangrijk nadeel van het gebruik van Table.FromRecords is dat alle records in de lijst dezelfde set velden hebben. Dit werkt voor de TripPin-service, omdat de OData-records dezelfde velden bevatten, maar dit is mogelijk niet het geval voor alle REST API's. Een krachtigere implementatie zou een combinatie van Table.FromList en Table.ExpandRecordColumn gebruiken. Latere zelfstudies wijzigen de implementatie om de kolomlijst op te halen uit de schematabel, zodat er geen kolommen verloren gaan of ontbreken tijdens de JSON naar M-vertaling.

De functie GetEntity toevoegen

De GetEntity functie verpakt uw aanroep naar TripPin.Feed. Er wordt een schemadefinitie opgezoekd op basis van de naam van de entiteit en de volledige aanvraag-URL gebouwd.

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)
    in
        result;

Vervolgens werkt u uw TripPinNavTable functie bij om aan te roepen GetEntity, in plaats van alle aanroepen inline te plaatsen. Het belangrijkste voordeel hiervan is dat u kunt doorgaan met het wijzigen van de code voor het bouwen van entiteiten, zonder dat u de logica van de navigatietabel hoeft aan te raken.

TripPinNavTable = (url as text) as table =>
    let
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

Alles samenvoegen

Zodra alle codewijzigingen zijn aangebracht, compileert en voert u de testquery opnieuw uit die de tabel Airlines aanroept Table.Schema .

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

U ziet nu dat de tabel Airlines alleen de twee kolommen bevat die u in het schema hebt gedefinieerd:

Als u dezelfde code uitvoert op de Mensen tabel...

let
    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]
in
    Table.Schema(data)

U ziet dat het getscribeerde type dat u hebt gebruikt (Int64.Type) ook correct is ingesteld.

Een belangrijk aandachtspunt is dat deze implementatie SchemaTransformTable de typen en listrecord kolommen niet wijzigt, maar dat de Emails en AddressInfo kolommen nog steeds worden getypt als list. Dit komt doordat Json.Document JSON-matrices correct worden toegewezen aan M-lijsten en JSON-objecten aan M-records. Als u de lijst- of recordkolom in Power Query uitvouwt, ziet u dat alle uitgevouwen kolommen van het type zijn. Toekomstige zelfstudies verbeteren de implementatie om recursief typegegevens in te stellen voor geneste complexe typen.

Conclusie

Deze zelfstudie biedt een voorbeeld-implementatie voor het afdwingen van een schema voor JSON-gegevens die zijn geretourneerd door een REST-service. Hoewel in dit voorbeeld een eenvoudige in code vastgelegde schematabelindeling wordt gebruikt, kan de benadering worden uitgebreid door dynamisch een schematabeldefinitie te maken vanuit een andere bron, zoals een JSON-schemabestand of metagegevensservice/eindpunt dat door de gegevensbron wordt weergegeven.

Naast het wijzigen van kolomtypen (en waarden) stelt uw code ook de juiste typegegevens in de tabel zelf in. Het instellen van dit type informatie biedt voordelen bij het uitvoeren in Power Query, omdat de gebruikerservaring altijd probeert typegegevens af te leiden om de juiste UI-wachtrijen weer te geven aan de eindgebruiker, en de deductieaanroepen kunnen uiteindelijk andere aanroepen naar de onderliggende gegevens-API's activeren.

Als u de Mensen tabel bekijkt met behulp van de TripPin-connector uit de vorige les, ziet u dat alle kolommen een pictogram 'type any' hebben (zelfs de kolommen die lijsten bevatten):

Als u dezelfde query uitvoert met de TripPin-connector uit deze les, ziet u nu dat de typegegevens correct worden weergegeven.

Volgende stappen

TripPin deel 7 - Geavanceerd schema met M-typen