TripPin del 6 – skema

Dette selvstudium i flere dele dækker oprettelsen af en ny datakildeudvidelse til Power Query. Selvstudiet er beregnet til at blive udført sekventielt – hver lektion bygger på den connector, der blev oprettet i tidligere lektioner, og føjer trinvist nye funktioner til din connector.

I denne lektion skal du:

• Definer et fast skema for en REST API
• Dynamisk angivelse af datatyper for kolonner
• Gennemtving en tabelstruktur for at undgå transformationsfejl på grund af manglende kolonner
• Skjul kolonner fra resultatsættet

En af de store fordele ved en OData-tjeneste i forhold til en REST-STANDARD-API er dens $metadata definition. I dokumentet $metadata beskrives de data, der blev fundet i denne tjeneste, herunder skemaet for alle enheder (tabeller) og felter (kolonner). Funktionen OData.Feed bruger denne skemadefinition til automatisk at angive oplysninger om datatyper – så i stedet for at hente alle tekst- og talfelter (som du ville fra Json.Document), får slutbrugerne datoer, heltal, klokkeslæt osv., hvilket giver en bedre samlet brugeroplevelse.

Mange REST API'er har ikke mulighed for at bestemme deres skema programmeringsmæssigt. I disse tilfælde skal du inkludere skemadefinitioner i din connector. I denne lektion skal du definere et simpelt, hardcoded skema for hver af tabellerne og gennemtvinge skemaet på de data, du læser fra tjenesten.

Bemærk

Den fremgangsmåde, der er beskrevet her, bør fungere for mange REST-tjenester. Fremtidige lektioner bygger på denne fremgangsmåde ved rekursivt at gennemtvinge skemaer på strukturerede kolonner (post, liste, tabel) og levere eksempelimplementeringer, der kan generere en skematabel fra CSDL- eller JSON-skemadokumenter programmeringsmæssigt.

Samlet set har gennemtvingelse af et skema på de data, der returneres af din connector, flere fordele, f.eks.:

• Angivelse af de korrekte datatyper
• Fjernelse af kolonner, der ikke behøver at blive vist for slutbrugere (f.eks. interne id'er eller tilstandsoplysninger)
• Sikre, at hver side med data har den samme form ved at tilføje kolonner, der kan mangle i et svar (en almindelig måde for REST API'er at angive, at et felt skal være null)

Visning af det eksisterende skema med Table.Schema

Den connector, der blev oprettet i den forrige lektion , viser tre tabeller fra Tjenesten TripPin –Airlines , Airportsog People. Kør følgende forespørgsel for at få vist tabellen Airlines :

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

I resultaterne kan du se fire kolonner returneret:

• @odata.id
• @odata.editLink
• AirlineCode
• Navn

Kolonnerne "@odata.*" er en del af OData-protokollen og ikke noget, du gerne vil have vist for slutbrugerne af din connector. AirlineCode og Name er de to kolonner, du vil beholde. Hvis du kigger på tabellens skema (ved hjælp af funktionen Table.Schema ), kan du se, at alle kolonnerne i tabellen har datatypen Any.Type.

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

Table.Schema returnerer mange metadata om kolonnerne i en tabel, herunder navne, positioner, typeoplysninger og mange avancerede egenskaber, f.eks. Præcision, Skala og MaxLength. Fremtidige lektioner giver designmønstre til angivelse af disse avancerede egenskaber, men indtil videre behøver du kun at bekymre dig om den tilskrevne type (TypeName), primitiv type (Kind), og om kolonneværdien kan være null (IsNullable).

Definition af en enkel skematabel

Skematabellen består af to kolonner:

Column	Oplysninger
Navn	Navnet på kolonnen. Dette skal svare til navnet i de resultater, der returneres af tjenesten.
Skriv	Den M-datatype, du vil angive. Dette kan være en primitiv type (text, number, datetimeosv.) eller en tilskrevet type (Int64.Type, Currency.Typeosv.).

Den hardcodede skematabel for tabellen angiver kolonnerne AirlineCodeAirlines og Name til textog ser sådan ud:

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

Tabellen Airports indeholder fire felter, du vil bevare (herunder en af typen record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    });

Endelig indeholder tabellen People syv felter, herunder lister (Emails, ), en kolonne, AddressInfoder kan være null (Gender), og en kolonne med en tilskrevet 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}
    })

Funktionen SchemaTransformTable-hjælpefunktion

Den SchemaTransformTable hjælpefunktion, der er beskrevet nedenfor, bruges til at gennemtvinge skemaer på dine data. Det kræver følgende parametre:

Parameter	Type	Description
table	table	Den tabel med data, du vil gennemtvinge skemaet på.
skema	table	Den skematabel, der skal læses kolonneoplysninger fra, med følgende type: type table [Name = text, Type = type].
enforceSchema	Nummer	(valgfrit) En optælling, der styrer funktionens funktionsmåde. Standardværdien (EnforceSchema.Strict = 1) sikrer, at outputtabellen stemmer overens med den skematabel, der blev angivet, ved at tilføje manglende kolonner og fjerne ekstra kolonner. Indstillingen EnforceSchema.IgnoreExtraColumns = 2 kan bruges til at bevare ekstra kolonner i resultatet. Når EnforceSchema.IgnoreMissingColumns = 3 bruges, ignoreres både manglende kolonner og ekstra kolonner.

Logikken for denne funktion ser nogenlunde sådan ud:

1. Find ud af, om der mangler kolonner fra kildetabellen.
2. Find ud af, om der er ekstra kolonner.
3. Ignorer strukturerede kolonner (af typen list, recordog table), og kolonner, der er angivet til type any.
4. Brug Table.TransformColumnTypes til at angive hver kolonnetype.
5. Omarranger kolonner baseret på den rækkefølge, de vises i skematabellen.
6. Angiv typen på selve tabellen ved hjælp af Value.ReplaceType.

Bemærk

Det sidste trin til at angive tabeltypen fjerner behovet for, at Power Query-brugergrænsefladen udleder typeoplysninger, når resultaterne vises i forespørgselseditoren. Dette fjerner det dobbeltanmodningsproblem, du så i slutningen af det forrige selvstudium.

Følgende hjælpekode kan kopieres og indsættes i din udvidelse:

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;

Opdaterer TripPin-connectoren

Du skal nu foretage følgende ændringer af din connector for at gøre brug af den nye kode til håndhævelse af skemaer.

1. Definer en masterskematabel (SchemaTable), der indeholder alle dine skemadefinitioner.
2. TripPin.FeedOpdater , GetPageog GetAllPagesByNextLink for at acceptere en schema parameter.
3. Gennemtving dit skema i GetPage.
4. Opdater navigationstabelkoden for at ombryde hver tabel med et kald til en ny funktion (GetEntity) – det giver dig større fleksibilitet til at manipulere tabeldefinitionerne i fremtiden.

Masterskematabel

Du skal nu konsolidere dine skemadefinitioner i en enkelt tabel og tilføje en hjælpefunktion (GetSchemaForEntity), der giver dig mulighed for at slå definitionen op baseret på et objektnavn (f.eks. 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 &"'";

Tilføjelse af skemaunderstøtter til datafunktioner

Du skal nu føje en valgfri schema parameter til funktionerne TripPin.Feed, GetPageog GetAllPagesByNextLink . Dette giver dig mulighed for at overføre skemaet (når du vil) til sidefunktionerne, hvor det anvendes på de resultater, du får tilbage fra tjenesten.

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 => ...

Du skal også opdatere alle kald til disse funktioner for at sikre, at du overfører skemaet korrekt.

Gennemtvingelse af skemaet

Den faktiske håndhævelse af skemaet udføres i din GetPage funktion.

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];

Bemærk

Denne GetPage implementering bruger Table.FromRecords til at konvertere listen over poster i JSON-svaret til en tabel. En stor ulempe ved at bruge Table.FromRecords er, at det antages , at alle poster på listen har det samme sæt felter. Dette fungerer for TripPin-tjenesten, da OData-posterne er garanteret til at indeholde de samme felter, men det er muligvis ikke tilfældet for alle REST API'er. En mere robust implementering ville bruge en kombination af Table.FromList og Table.ExpandRecordColumn. Senere selvstudier ændrer implementeringen for at hente kolonnelisten fra skematabellen, så det sikres, at ingen kolonner går tabt eller mangler under JSON til M-oversættelsen.

Tilføjelse af funktionen GetEntity

Funktionen GetEntity ombryder dit opkald til TripPin.Feed. Den slår en skemadefinition op, der er baseret på enhedsnavnet, og opretter den fulde URL-adresse til anmodningen.

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;

Du skal derefter opdatere din TripPinNavTable funktion til at kalde GetEntityi stedet for at foretage alle opkaldene indbygget. Den største fordel ved dette er, at du kan fortsætte med at ændre enhedsopbygningskoden uden at skulle røre ved logikken i din navigationstabel.

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;

Sætte det hele sammen

Når alle kodeændringerne er foretaget, skal du kompilere og køre testforespørgslen igen, der kalder Table.Schema tabellen Airlines.

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

Nu kan du se, at tabellen Airlines kun har de to kolonner, du har defineret i skemaet:

Hvis du kører den samme kode i forhold til tabellen Mennesker...

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

Du kan se, at den tilskrevne type, du brugte (Int64.Type), også er angivet korrekt.

Det er vigtigt at bemærke, at denne implementering af SchemaTransformTable ikke ændrer typerne af list og record kolonner, men kolonnerne Emails og AddressInfo stadig skrives som list. Dette skyldes, Json.Document at JSON-matrixer knyttes korrekt til M-lister og JSON-objekter til M-poster. Hvis du udvider listen eller postkolonnen i Power Query, kan du se, at alle de udvidede kolonner vil være af typen enhver. Fremtidige selvstudier forbedrer implementeringen for at angive typeoplysninger rekursivt for indlejrede komplekse typer.

Konklusion

Dette selvstudium indeholdt et eksempel på implementering til gennemtvingelse af et skema på JSON-data, der returneres fra en REST-tjeneste. Selvom dette eksempel bruger et simpelt hardcoded skematabelformat, kan tilgangen udvides ved dynamisk at oprette en skematabeldefinition fra en anden kilde, f.eks. en JSON-skemafil eller metadatatjeneste/slutpunkt, der vises af datakilden.

Ud over at ændre kolonnetyper (og værdier) angiver din kode også de korrekte typeoplysninger i selve tabellen. Angivelse af disse typeoplysninger gavner ydeevnen, når der køres i Power Query, da brugeroplevelsen altid forsøger at udlede typeoplysninger for at vise de rette brugergrænsefladekøer til slutbrugeren, og de endelige kald kan ende med at udløse andre kald til de underliggende data-API'er.

Hvis du får vist tabellen Mennesker ved hjælp af TripPin-connectoren fra den forrige lektion, kan du se, at alle kolonnerne har et ikon af typen 'type any' (selv de kolonner, der indeholder lister):

Hvis du kører den samme forespørgsel med TripPin-connectoren fra denne lektion, kan du nu se, at typeoplysningerne vises korrekt.

Næste trin

TripPin Del 7 – Avanceret skema med M-typer