TripPin 6. rész – Séma

Ez a többrészes oktatóanyag a Power Query új adatforrásbővítményének létrehozását ismerteti. Az oktatóanyagot egymás után kell elvégezni – minden lecke az előző leckékben létrehozott összekötőre épül, és növekményesen új képességeket ad hozzá az összekötőhöz.

Ebben a leckében a következőt fogja:

Az OData-szolgáltatások egyik nagy előnye a standard REST API-val szemben a $metadata definíciója. A $metadata dokumentum ismerteti a szolgáltatásban található adatokat, beleértve az összes entitás (tábla) és mező (oszlop) sémáját. A OData.Feed függvény ezzel a sémadefinícióval automatikusan beállítja az adattípus-információkat, így ahelyett, hogy az összes szöveg- és számmezőt lekérné (ahogyan ön is Json.Documenttenné), a végfelhasználók dátumokat, egész számokat, időpontokat és egyebeket kapnak, így jobb általános felhasználói élményt biztosíthatnak.

Sok REST API nem tudja programozott módon meghatározni a sémáját. Ezekben az esetekben sémadefiníciókat kell tartalmaznia az összekötőben. Ebben a leckében egy egyszerű, rögzített sémát fog definiálni az egyes táblákhoz, és kikényszeríti a sémát a szolgáltatásból beolvasott adatokra.

Általánosságban elmondható, hogy az összekötő által visszaadott adatok sémájának kényszerítése több előnnyel is jár, például:

• A megfelelő adattípusok beállítása
• Olyan oszlopok eltávolítása, amelyeket nem kell megjeleníteni a végfelhasználók számára (például belső azonosítók vagy állapotinformációk)
• Annak biztosítása, hogy minden adatoldal azonos alakú legyen, ha hozzáadja a válaszból esetleg hiányzó oszlopokat (a REST API-k gyakran azt jelzik, hogy egy mező null értékű)

A meglévő séma megtekintése a Table.Schema használatával

Az előző leckében létrehozott összekötő három táblát jelenít meg a TripPin szolgáltatásból– AirportsAirlinesés People. Futtassa a következő lekérdezést a Airlines tábla megtekintéséhez:

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

Az eredményekben négy oszlop jelenik meg:

• @odata.id
• @odata.editLink
• AirlineCode
• Név

A "@odata.*" oszlopok az OData protokoll részét képezik, és nem olyasmit, amit az összekötő végfelhasználóinak meg kellene jelenítenie. AirlineCode és Name az a két oszlop, amelyet meg szeretne tartani. Ha megtekinti a táblázat sémáját (a praktikus Table.Schema függvény használatával), láthatja, hogy a táblázat összes oszlopa rendelkezik adattípussal Any.Type.

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

A Table.Schema számos metaadatot ad vissza a tábla oszlopairól, beleértve a neveket, a pozíciókat, a típusinformációkat és számos speciális tulajdonságot, például a pontosságot, a méretezést és a MaxLengthet. A jövőbeli leckék tervezési mintákat biztosítanak ezeknek a speciális tulajdonságoknak a beállításához, de egyelőre csak az írott típussal (TypeName), a primitív típussal (Kind) kell foglalkoznia, és hogy az oszlop értéke null (IsNullable-e).

Egyszerű sématábla definiálása

A sématábla két oszlopból áll:

A tábla kemény kódolt sématáblája a Airlines következőre textállítja az oszlopokat és Name az AirlineCode oszlopokat, és így néz ki:

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

A Airports tábla négy megtartandó mezővel rendelkezik (beleértve a típus recordegyikét):

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

Végül a People táblázat hét mezőt tartalmaz, köztük listákat (Emails, AddressInfo), null értékű oszlopot (Gender) és egy írott (Concurrency) típusú oszlopot.

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

A SchemaTransformTable segédfüggvény

Az SchemaTransformTable alábbiakban ismertetett segédfüggvény a sémák kikényszerítésére szolgál az adatokon. A következő paramétereket veszi igénybe:

A függvény logikája a következőképpen néz ki:

1. Állapítsa meg, hogy vannak-e hiányzó oszlopok a forrástáblából.
2. Állapítsa meg, hogy vannak-e további oszlopok.
3. Figyelmen kívül hagyja a strukturált oszlopokat (típus list, recordés ) és tablea következőre type anybeállított oszlopokat.
4. Az egyes oszloptípusok beállításához használja a Table.TransformColumnTypes függvényt.
5. Az oszlopok átrendezése a sématáblában megjelenő sorrend alapján.
6. Állítsa be magát a táblát a Value.ReplaceType használatával.

A következő segédkód másolható és beilleszthető a bővítménybe:

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;

A TripPin-összekötő frissítése

Most az alábbi módosításokat hajtja végre az összekötőn az új sémakényszerítési kód használatához.

1. Adjon meg egy fő sématáblát (SchemaTable), amely az összes sémadefiníciót tartalmazza.
2. Frissítse a TripPin.Feed, GetPageés GetAllPagesByNextLink fogadja el a paramétert schema .
3. A séma kényszerítése a következőben: GetPage.
4. Frissítse a navigációs táblázat kódját úgy, hogy az egyes táblákat egy új függvény (GetEntity)hívásával körbefuttathassa – ez nagyobb rugalmasságot biztosít a tábladefiníciók jövőbeni módosításához.

Fősématábla

Most egyetlen táblába összesíti a sémadefiníciókat, és hozzáad egy segédfüggvényt (GetSchemaForEntity), amellyel entitásnév alapján keresheti meg a definíciót (például 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 &"'";

Sématámogatás hozzáadása adatfüggvényekhez

Most hozzáad egy opcionális schema paramétert a , GetPageés GetAllPagesByNextLink függvényekhezTripPin.Feed. Ez lehetővé teszi a séma átadását (ha szeretné) a lapozófüggvényekre, ahol a rendszer alkalmazza a szolgáltatásból visszakapott eredményekre.

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

A függvények összes hívását is frissítenie kell, hogy meggyőződjön arról, hogy helyesen adja át a sémát.

A séma kényszerítése

A tényleges sémaérvényesítés a GetPage függvényben lesz végrehajtva.

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

A GetEntity függvény hozzáadása

A GetEntity függvény becsomagolja a TripPin.Feed hívását. Megkeres egy sémadefiníciót az entitás neve alapján, és létrehozza a teljes kérelem URL-címét.

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;

Ezután a függvényt TripPinNavTable ahelyett, hogy az összes hívást beágyazott módon indítja el, frissíteni fogja a függvényt GetEntity. Ennek fő előnye, hogy lehetővé teszi az entitások kódjának módosítását anélkül, hogy hozzá kellene nyúlnia a navigációs tábla logikájához.

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;

Végső összeállítás

Miután az összes kódmódosítást végrehajtotta, fordítsa le és futtassa újra a Airlines táblát hívó Table.Schema tesztlekérdezéseket.

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

Most már láthatja, hogy a Airlines táblában csak a sémában definiált két oszlop található:

Ha ugyanazt a kódot futtatja a Kapcsolatok táblán...

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

Látni fogja, hogy a használt írásvédett típus (Int64.Type) is helyesen lett beállítva.

Fontos megjegyezni, hogy ez a megvalósítás SchemaTransformTable nem módosítja list a típusokat és record az oszlopokat, de az oszlopok és AddressInfo az Emails oszlopok továbbra is a következőképpen vannak begépelvelist. Ennek az az oka Json.Document , hogy a JSON-tömbök megfelelően lesznek leképezve M-listákra és JSON-objektumokra M rekordokra. Ha ki szeretné bontani a listát vagy a rekordoszlopot a Power Queryben, láthatja, hogy az összes kibontott oszlop bármelyik típusú lesz. A jövőbeli oktatóanyagok továbbfejlesztik az implementációt, hogy rekurzív módon állítsa be a beágyazott összetett típusok típusadatait.

Összegzés

Ez az oktatóanyag egy rest szolgáltatásból visszaadott JSON-adatokra vonatkozó séma kényszerítését szolgáló minta-implementációt biztosított. Bár ez a minta egy egyszerű, merevlemezes sématábla-formátumot használ, a módszer kibővíthető egy sématábla definíciójának dinamikus létrehozásával egy másik forrásból, például egy JSON-sémafájlból vagy az adatforrás által közzétett metaadat-szolgáltatásból/végpontból.

Az oszloptípusok (és értékek) módosítása mellett a kód a megfelelő típusinformációkat is beállítja a táblán. Ha ezt a típusinformációt a Power Queryben futtatja, a felhasználói élmény mindig megpróbál típusadatokat kikövetkeztetni a megfelelő felhasználói üzenetsorok végfelhasználónak való megjelenítéséhez, és a következtetési hívások végül más hívásokat is elindíthatnak a mögöttes adat API-k felé.

Ha az előző lecke TripPin-összekötőjével tekinti meg a Kapcsolatok táblát, láthatja, hogy az összes oszlop rendelkezik egy "type any" ikonnal (még a listákat tartalmazó oszlopokkal is):

Ha ugyanazt a lekérdezést futtatja a TripPin-összekötővel ebben a leckében, láthatja, hogy a típusadatok megfelelően jelennek meg.

Következő lépések

TripPin 7. rész – Speciális séma M típusok használatával