TripPin deel 6 - Schema

In deze meerdelige zelfstudie wordt het maken van een nieuwe gegevensbronextensie voor Power Query. De zelfstudie is bedoeld om opeenvolgend te worden uitgevoerd voor elke les die is gebaseerd op de connector die in de vorige lessen is gemaakt, en incrementeel nieuwe mogelijkheden toe te voegen — aan uw connector.

In deze les gaat u het volgende doen:

  • Een vast schema voor een REST API
  • Dynamisch gegevenstypen instellen voor kolommen
  • Een tabelstructuur afdwingen om transformatiefouten vanwege ontbrekende kolommen te voorkomen
  • Kolommen verbergen in de resultatenset

Een van de grote voordelen van een OData-service ten opzichte van een REST API is de $metadata definitie. In $metadata document worden de gegevens beschreven die in deze service zijn gevonden, inclusief het schema voor alle entiteiten (tabellen) en velden (kolommen). De functie gebruikt deze schemadefinitie om automatisch gegevens van het gegevenstype in te stellen, zodat eindgebruikers in plaats van alle tekst- en getalvelden (zoals u dat zouden doen van ) datums, gehele getallen, tijden, e.d. krijgen, wat een betere algehele gebruikerservaring OData.FeedJson.Document biedt.

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

Notitie

De aanpak die hier wordt beschreven, werkt voor veel REST-services. Toekomstige lessen bouwen voort op deze benadering door recursief schema's af te afdwingen op gestructureerde kolommen (record, list, table) en voorbeelden te bieden van implementaties die programmatisch een schematabel kunnen genereren op basis van CSDL- of JSON Schema-documenten.

Het afdwingen van een schema op de gegevens die door uw connector worden geretourneerd, heeft over het algemeen meerdere voordelen, zoals:

  • De juiste gegevenstypen instellen
  • Kolommen verwijderen die niet hoeven te worden weergegeven aan eindgebruikers (zoals interne ID's of statusinformatie)
  • Ervoor zorgen 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 van de TripPin-service — Airlines , en Airports People . Voer de volgende query uit om de tabel weer Airlines 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
  • Luchtvaartmaatschappijcode
  • Naam

Geen schema voor luchtvaartmaatschappijen.

De kolommen '.*' maken deel uit van het OData-protocol en niet iets dat u wilt of moet laten zien aan de eindgebruikers @odata 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), ziet u dat alle kolommen in de tabel het gegevenstype Any.Type hebben.

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

Table.Schema van de luchtvaartmaatschappij.

Table.Schema retourneert veel metagegevens over de kolommen in een tabel, waaronder namen, posities, type-informatie 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 toegekende type ( ), primitieve type ( ) en of de kolomwaarde null () kan TypeName Kind IsNullable zijn.

Een eenvoudige schematabel definiëren

Uw 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 gaat instellen. Dit kan een primitief type ( , , , etc. zijn) of een toegekend text type ( , , , number datetime Int64.Type Currency.Type etc.).

De hardcoded schematabel voor de tabel stelt de kolommen en Airlines in op , en ziet er als volgende AirlineCode Name text uit:

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

De Airports tabel heeft 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 tabel zeven velden, waaronder lijsten ( , ), een People Emails AddressInfo null-kolom ( Gender ) en een kolom met een toegekend 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 op uw gegevens af te dwingen. De volgende parameters zijn nodig:

Parameter Type Description
tabel tabel De tabel met gegevens waar u uw schema op wilt afdwingen.
schema tabel De schematabel waartabelgegevens uit worden gelezen, met het volgende type: type table [Name = text, Type = type] .
enforceSchema getal (optioneel) Een enum die het gedrag van de functie bepaalt.
De standaardwaarde ( ) zorgt ervoor dat de uitvoertabel overeen komt met de schematabel die is opgegeven door ontbrekende kolommen toe te voegen en EnforceSchema.Strict = 1 extra kolommen te verwijderen.
De EnforceSchema.IgnoreExtraColumns = 2 optie kan worden gebruikt om extra kolommen in het resultaat te behouden.
Wanneer EnforceSchema.IgnoreMissingColumns = 3 wordt gebruikt, worden zowel ontbrekende kolommen als extra kolommen genegeerd.

De logica voor deze functie ziet er als de volgende uit:

  1. Bepaal of er kolommen ontbreken in de brontabel.
  2. Bepaal of er extra kolommen zijn.
  3. Negeer gestructureerde kolommen (van het type list , en ) en kolommen die zijn ingesteld op record table type any .
  4. Gebruik Table.TransformColumnTypes om elk kolomtype in te stellen.
  5. De volgorde van kolommen wijzigen op basis van de volgorde waarin ze worden weergegeven in de schematabel.
  6. 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 Power Query geen typegegevens af te leiden bij het weergeven van de resultaten in de queryeditor. Hiermee verwijdert u het probleem met een dubbele aanvraag dat u aan het einde van de vorige zelfstudie hebt gezien.

De volgende helpercode kan worden kopieert en in uw extensie wordt gesijkt:

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 schema-afdwinging.

  1. Definieer een hoofdschematabel ( SchemaTable ) die al uw schemadefinities bevat.
  2. Werk de TripPin.Feed , en bij om een parameter te GetPage GetAllPagesByNextLink schema accepteren.
  3. Dwing uw schema af in GetPage .
  4. Werk de code van uw navigatietabel bij om elke tabel te verpakken met een aanroep naar een nieuwe functie ( ), wat u meer flexibiliteit biedt om de tabeldefinities in de GetEntity — toekomst te bewerken.

Hoofdschematabel

U voegt nu uw schemadefinities samen in één tabel en voegt een helperfunctie ( ) toe waarmee u de definitie kunt op zoeken op basis van een entiteitsnaam GetSchemaForEntity (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 functies , en TripPin.Feed GetPage GetAllPagesByNextLink . Hiermee kunt u het schema (wanneer u wilt) doorgeven aan de pagineringsfuncties, waar het wordt toegepast op de resultaten die u van de service krijgt.

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 gaat ook alle aanroepen naar deze functies bijwerken om ervoor te zorgen dat u het schema correct door geeft.

Het schema afdwingen

Het daadwerkelijke afdwingen 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];

[Opmerking] Deze GetPage implementatie maakt gebruik van Table.FromRecords om de lijst met records in het JSON-antwoord te converteren naar een tabel. Een groot nadeel van het gebruik van Table.FromRecords is dat wordt aangenomen 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 maakt gebruik van een combinatie van Table.FromList en Table.ExpandRecordColumn. In latere zelfstudies wordt de implementatie gewijzigd 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 opgevraagd 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 functie bij om aan te roepen, in plaats van alle TripPinNavTable GetEntity aanroepen inline te maken. Het belangrijkste voordeel hiervan is dat u hiermee door kunt gaan met het wijzigen van de code voor het bouwen van entiteiten, zonder dat u de logica van uw navigatietabel moet 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 samenbrengen

Zodra alle codewijzigingen zijn aangebracht, compileert en opnieuw de testquery die de Table.Schema tabel Luchtvaart aanroept.

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

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

Luchtvaartmaatschappij met schema.

Als u dezelfde code op de tabel People hebt uitgevoerd...

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

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

Personen met een schema.

Het is belangrijk om te weten dat deze implementatie van niet de typen kolommen en wijzigt, maar dat de kolommen en nog SchemaTransformTable list steeds worden record Emails AddressInfo getypt als list . Dit komt doordat Json.Document JSON-matrices correct aan M-lijsten en JSON-objecten aan M-records worden wijs. Als u de lijst of recordkolom in Power Query, ziet u dat alle uit uitgebreide kolommen van het type any zijn. In toekomstige zelfstudies wordt de implementatie verbeterd om typegegevens recursief in te stellen voor geneste complexe typen.

Conclusie

Deze zelfstudie bevat een voorbeeld van een implementatie voor het afdwingen van een schema op JSON-gegevens die worden geretourneerd door een REST-service. Hoewel in dit voorbeeld een eenvoudige, in code gecodeerde schematabelindeling wordt gebruikt, kan de aanpak worden uitgebreid door dynamisch een schematabeldefinitie te bouwen vanuit een andere bron, zoals een JSON-schemabestand of metagegevensservice/eindpunt dat beschikbaar wordt gemaakt door de gegevensbron.

Uw code wijzigt niet alleen kolomtypen (en waarden), maar stelt ook de juiste typegegevens in voor de tabel zelf. Het instellen van dit type informatie heeft voordelen voor de prestaties bij het uitvoeren binnen Power Query, omdat de gebruikerservaring altijd probeert typegegevens af te leiden om de juiste UI-wachtrijen weer te geven aan de eindgebruiker, en de deference-aanroepen kunnen uiteindelijk leiden tot extra aanroepen naar de onderliggende gegevens-API's.

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

Mensen zonder schema.

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

Personen met een schema.

Volgende stappen

TripPin Deel 7- Geavanceerd schema met M-typen