TripPin časť 6 – Schéma

Tento kurz s viacerými časťami sa zaoberá vytváraním nového rozšírenia zdroja údajov pre Power Query. Tento kurz sa má uskutočniť postupne – každá lekcia vychádza z konektora vytvoreného v predchádzajúcich lekciách a postupne pridáva nové možnosti do konektora.

V tejto lekcii:

Jednou z veľkých výhod služby OData oproti štandardnému rozhraniu REST API je definícia $metadata. Dokument $metadata popisuje údaje nájdené v tejto službe vrátane schémy pre všetky jej entity (tabuľky) a polia (stĺpce). Funkcia OData.Feed používa túto definíciu schémy na automatické nastavenie informácií o type údajov, takže namiesto získavania všetkých textových a číselných polí (ako by ste zo Json.Document) koncoví používatelia získavali dátumy, celé čísla, časy a podobne, čím poskytuje lepšie používateľské prostredie.

Mnohé rozhrania REST API neobsahujú spôsob, ako programovo určiť ich schému. V týchto prípadoch bude potrebné do konektora zahrnúť definície schém. V tejto lekcii definujete jednoduchú naprogramovanú schému pre každú z tabuliek a vynucujete schému na údajoch, ktoré ste čítali zo služby.

Vcelku má vynútenie schémy na údajoch vrátených konektorom niekoľko výhod, ako napríklad:

• Nastavenie správnych typov údajov
• Odstránenie stĺpcov, ktoré nie je potrebné zobrazovať koncovým používateľom (napríklad interné ID alebo informácie o stave).
• Zabezpečením toho, aby mala každá stránka údajov rovnaký tvar, pridaním všetkých stĺpcov, ktoré môžu chýbať v odpovedi (bežný spôsob rozhrania REST API, ktorý označuje, že pole má mať hodnotu null),

Zobrazenie existujúcej schémy pomocou funkcie Table.Schema

Konektor vytvorený v predchádzajúcej lekcii zobrazuje tri tabuľky zo služby TripPin –AirlinesAirports a People. Ak chcete zobraziť tabuľku Airlines , spustite nasledujúci dotaz:

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

Vo výsledkoch sa zobrazia štyri vrátené stĺpce:

• @odata.id
• @odata.editLink
• Kód leteckej spoločnosti
• Meno

Stĺpce @odata.*sú súčasťou protokolu OData a nie je to niečo, čo by ste chceli alebo potrebovali zobraziť koncovým používateľom konektora. AirlineCode stĺpce, ktoré Name si chcete ponechať. Ak sa pozriete na schému tabuľky (pomocou užitočnej funkcie Table.Schema ), uvidíte, že všetky stĺpce v tabuľke majú typ Any.Typeúdajov .

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

Funkcia Table.Schema vráti veľa metaúdajov o stĺpcoch v tabuľke vrátane názvov, pozícií, informácií o type a mnohých pokročilých vlastností, ako sú napríklad Presnosť, Mierka a MaxLength. Budúce lekcie vám poskytnú vzory návrhu na nastavenie týchto rozšírených vlastností, ale teraz sa potrebujete len starať o pripisovaný typ (TypeName), primitívny typ (Kind) a to, či hodnota stĺpca môže byť null (IsNullable).

Definovanie jednoduchej tabuľky schémy

Tabuľka schémy sa bude zostavovať z dvoch stĺpcov:

Tabuľka s pevne zakódovanú schémou pre tabuľku Airlines nastaví jej AirlineCode stĺpce a Name na texthodnotu a bude vyzerať takto:

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

Tabuľka Airports obsahuje štyri polia, ktoré si budete chcieť ponechať (vrátane jedného typu record):

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

People Nakoniec má tabuľka sedem polí vrátane zoznamov (Emails, AddressInfo), stĺpca s povolenou hodnotou null (Gender) a stĺpca s pripísaným typom (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}
    })

Pomocná funkcia schemaTransformTable

Pomocná SchemaTransformTable funkcia popísaná nižšie sa použije na vynútenie schém na vašich údajoch. Nadobúda nasledujúce parametre:

Logika pre túto funkciu vyzerá približne takto:

1. Určte, či v zdrojovej tabuľke chýbajú stĺpce.
2. Určte, či existujú ďalšie stĺpce.
3. Ignorovať štruktúrované stĺpce (typu list, recorda table) a stĺpce nastavené na type anyhodnotu .
4. Pomocou funkcie Table.TransformColumnTypes môžete nastaviť každý typ stĺpca.
5. Zmeňte poradie stĺpcov na základe poradia, v akom sa zobrazujú v tabuľke schém.
6. Nastavte typ v samotnej tabuľke pomocou value.ReplaceType.

Nasledujúci pomocný kód môžete skopírovať a prilepiť do vášho rozšírenia:

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;

Aktualizácia konektora TripPin

Teraz v konektore vykonáte nasledujúce zmeny, aby ste mohli využiť nový kód uplatnenia schém.

1. Definujte hlavnú tabuľku schémy (SchemaTable), ktorá obsahuje všetky definície vašej schémy.
2. Aktualizujte TripPin.Feedparameter , GetPagea na GetAllPagesByNextLink prijatie parametra schema .
3. Vynútiť schému v GetPageslužbe .
4. Aktualizujte kód navigačnej tabuľky tak, aby každú tabuľku zabalil volaním novej funkcie (GetEntity) – tým získate väčšiu flexibilitu pri manipulácii s definíciami tabuľky v budúcnosti.

Tabuľka hlavnej schémy

Teraz konsolidujete definície schémy do jednej tabuľky a pridáte pomocnú funkciu (GetSchemaForEntity), ktorá vám umožní vyhľadať definíciu založenú na názve entity (napríklad 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 &"'";

Pridanie podpory schémy do údajových funkcií

Teraz pridáte voliteľný schema parameter do TripPin.Feedfunkcií , GetPagea GetAllPagesByNextLink . To vám umožní odovzdať schému (ak chcete) stránkovacím funkciám, kde sa použije na výsledky, ktoré získate späť zo služby.

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

Tiež aktualizujete všetky volania týchto funkcií, aby ste sa uistili, že schému prenesiete správne.

Vynucovanie schémy

Skutočné vynútenie schémy sa vykoná vo vašej GetPage funkcii.

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

Pridanie funkcie GetEntity

Funkcia GetEntity zalomí vaše volanie do TripPin.Feed. Vyhľadá definíciu schémy založenú na názve entity a vytvorí úplnú URL adresu požiadavky.

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;

Potom svoju funkciu aktualizujete TripPinNavTable tak, aby volala GetEntity, a nie všetky volania boli vložené. Hlavnou výhodou tohto problému je, že vám umožní pokračovať v úpravách kódu vytvárania entity bez toho, aby ste sa museli dotknúť logiky navigačnej tabuľky.

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;

Zhrnutie

Po vykonaní všetkých zmien kódu zostavte a znova spustite testovací dotaz, ktorý volá Table.Schema tabuľku Airlines.

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

Teraz vidíte, že tabuľka Airlines má iba dva stĺpce, ktoré ste definovali v jej schéme:

Ak spustíte rovnaký kód voči tabuľke Ľudia...

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

Uvidíte, že správne nastavený bol aj pripisovaný typ, ktorý ste použili (Int64.Type).

Dôležité je poznamenať, že táto implementácia SchemaTransformTable neupravuje typy list stĺpcov a record , ale stĺpce Emails a AddressInfo sú stále zadané ako list. Dôvodom je, že Json.Document správne primapuje polia JSON k zoznamom jazyka M a objekty JSON k záznamom jazyka M. Ak by ste chceli rozbaliť stĺpec zoznamu alebo záznamu v doplnku Power Query, vidíte, že všetky rozbalené stĺpce budú typu ľubovoľné. Budúce kurzy zlepšia implementáciu tak, aby rekurzívne nastavovala informácie o typoch pre vnorené komplexné typy.

Záver

Tento kurz poskytol vzorovú implementáciu na vynucovanie schémy údajov JSON vrátených zo služby REST. Hoci táto ukážka používa jednoduchý formát tabuľky s naprogramovanými schémami, prístup by sa mohol rozšíriť dynamickým vytvorením definície tabuľky schémy z iného zdroja, ako je napríklad súbor schémy JSON alebo služba/koncový bod metaúdajov zverejnený zdrojom údajov.

Okrem toho, že upravíte typy stĺpcov (a hodnoty), váš kód tiež nastaví správne informácie o type v samotnej tabuľke. Nastavenie tohto typu informácií prináša výhody výkonu pri spustení doplnku Power Query, pretože používateľské prostredie sa vždy pokúša odvodiť informácie o type, aby sa koncovému používateľovi zobrazili správne fronty používateľského rozhrania, a volania záverov môžu skončiť spustením ďalších volaní rozhraní API základných údajov.

Ak zobrazíte tabuľku Ľudia pomocou konektora TripPin z predchádzajúcej lekcie, uvidíte, že všetky stĺpce majú ikonu "zadať ľubovoľné" (dokonca aj stĺpce, ktoré obsahujú zoznamy):

Keď v tejto lekcii spustíte rovnaký dotaz s konektorom TripPin, uvidíte, že informácie o type sa zobrazujú správne.

Ďalšie kroky

TripPin Part 7 – rozšírená schéma s typmi jazyka M