TripPin,osa 6 – Rakenne

Tässä moniosaisessa opetusohjelmassa käsitellään uuden tietolähdelaajennuksen luomista Power Querylle. Opetusohjelma on tarkoitus tehdä järjestyksessä – jokainen oppitunti perustuu aiemmilla oppitunneilla luotuun liittimeen ja lisää uusia ominaisuuksia liittimeen.

Tällä oppitunnilla:

• Kiinteän rakenteen määrittäminen REST-ohjelmointirajapinnalle
• Sarakkeiden tietotyyppien määrittäminen dynaamisesti
• Pakota taulukon rakenne, jotta voit välttää muunnosvirheet puuttuvien sarakkeiden vuoksi
• Piilota tulosjoukon sarakkeet

Yksi OData-palvelun suurista eduista VAKIO-REST-ohjelmointirajapinnan kanssa on sen $metadata määritys. $metadata asiakirjassa kuvataan tässä palvelussa löytyneet tiedot, mukaan lukien sen entiteettien (taulukoiden) ja kenttien (sarakkeet) rakenne. Funktio OData.Feed määrittää tietotyypin tiedot automaattisesti tämän rakenteen määrityksen avulla. Sen sijaan, että se saisi kaikki teksti- ja numerokentät (kuten tekisit ), loppukäyttäjät Json.Documentsaavat päivämäärät, kokonaisluvut, ajat ja niin edelleen, mikä parantaa yleistä käyttökokemusta.

Monet REST-ohjelmointirajapinnat eivät pysty määrittämään niiden rakennetta ohjelmallisesti. Näissä tapauksissa sinun on sisällytettävä rakennemääritelmiä liittimeesi. Tällä oppitunnilla määrität yksinkertaisen, kiinteästi koodatun rakenteen kullekin taulukolle ja toteutat rakenteen palvelusta lukemillesi tiedoille.

Muistiinpano

Tässä kuvatun lähestymistavan pitäisi toimia monissa REST-palveluissa. Tulevat opetukset perustuvat tähän lähestymistapaan pakottamalla rekursiivisesti rakennesarakkeiden rakenteet (tietueet, luettelot, taulukot) ja tarjoavat mallitoteutuksia, jotka voivat ohjelmallisesti luoda rakennetaulukon CSDL- tai JSON-rakenneasiakirjoista .

Rakenteen pakottaminen liittimesi palauttamille tiedoille tarjoaa yleensä useita etuja, kuten:

• Oikeiden tietotyyppien määrittäminen
• Poistetaan sarakkeet, joita ei tarvitse näyttää käyttäjille (kuten sisäiset tunnukset tai tilan tiedot).
• Varmista, että kaikilla tietosivuilla on sama muoto, lisäämällä ne sarakkeet, jotka saattavat puuttua vastauksesta (rest-ohjelmointirajapintojen yleinen tapa osoittaa, että kentän on oltava tyhjäarvo)

Aiemmin luodun rakenteen tarkasteleminen Table.Schema-kohteen avulla

Edellisellä oppitunnilla luotu liitin näyttää kolme taulukkoa TripPin-palvelusta:Airlines ja AirportsPeople. Tarkastele taulukkoa suorittamalla Airlines seuraava kysely:

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

Tuloksissa näkyy neljä palautettua saraketta:

• @odata.id
• @odata.editLink
• Lentokoodi
• Nimi

@odata.*-sarakkeet ovat osa OData-protokollaa, eivät sellaisia, joita haluaisit tai jotka pitäisi näyttää liittimesi loppukäyttäjille. AirlineCode ja Name ovat ne kaksi saraketta, jotka haluat säilyttää. Jos tarkastelet taulukon rakennetta (käyttämällä kätevää Table.Schema-funktiota ), voit nähdä, että kaikilla taulukon sarakkeilla on tietotyyppi Any.Type.

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

Table.Schema palauttaa useita metatietoja taulukon sarakkeista, mukaan lukien nimet, sijainnit, tyyppitiedot ja monia lisäominaisuuksia, kuten Tarkkuus, Skaalaus ja MaxLength. Tulevat oppitunnit tarjoavat suunnittelumalleja näiden kehittyneiden ominaisuuksien määrittämiseen, mutta toistaiseksi sinun tarvitsee huolehtia vain määritetystä tyypistä (TypeName), primitiivityypistä (Kind) ja siitä, voiko sarakkeen arvo olla tyhjäarvo (IsNullable).

Yksinkertaisen rakennetaulukon määrittäminen

Rakennetaulukkosi koostuu kahdesta sarakkeesta:

Column	Tietoja
Nimi	Sarakkeen nimi. Tämän on vastattava palvelun palauttamien tulosten nimeä.
Tyyppi	M-tietotyyppi, jonka aiot määrittää. Tämä voi olla primitiivityyppi (text, number, datetimeja niin edelleen) tai määritetty tyyppi (Int64.Type, Currency.Typeja niin edelleen).

Taulukon pysyväiskoodattu rakennetaulukko Airlines määrittää sen AirlineCode ja Name sarakkeiden arvoksi text, ja se näyttää tältä:

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

Airports Taulukossa on neljä kenttää, jotka haluat säilyttää (joista yksi on tyyppiä record):

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

People Taulukossa on lopuksi seitsemän kenttää, mukaan lukien luettelot (Emails, AddressInfo), tyhjäarvoja salliva sarake (Gender) ja sarake, jonka on määritetty tyyppi (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}
    })

SchemaTransformTable-aputoimintofunktio

SchemaTransformTable Alla kuvattua apufunktiota käytetään tietoihisi perustuvien rakenteet. Se käyttää seuraavia parametreja:

Parametri	Tyyppi	Kuvaus
table	table	Tietotaulukko, jossa haluat ottaa rakenteen käyttöön.
rakenne	table	Rakennetaulukko, josta sarakkeen tiedot luetaan, käyttäen seuraavaa tyyppiä: type table [Name = text, Type = type].
enforceSchema	luku	(valinnainen) Luettelointi, joka ohjaa funktion toimintaa. Oletusarvo (EnforceSchema.Strict = 1) varmistaa, että tulostetaulukko vastaa rakennetaulukkoa, joka annettiin lisäämällä puuttuvat sarakkeet ja poistamalla ylimääräiset sarakkeet. -asetuksen EnforceSchema.IgnoreExtraColumns = 2 avulla voidaan säilyttää ylimääräiset sarakkeet tuloksessa. Kun EnforceSchema.IgnoreMissingColumns = 3 käytössä on, sekä puuttuvat sarakkeet että ylimääräiset sarakkeet ohitetaan.

Tämän funktion logiikka näyttää suunnilleen tältä:

1. Selvitä, onko lähdetaulukosta puuttuvia sarakkeita.
2. Selvitä, onko ylimääräisiä sarakkeita.
3. Ohita jäsennettyjä sarakkeita (tyyppiä list, recordja ) ja tablesarakkeita, joiden arvona on type any.
4. Määritä kukin saraketyyppi Table.TransformColumnTypes-funktion avulla.
5. Järjestä sarakkeet uudelleen sen järjestyksen perusteella, jossa ne näkyvät rakennetaulukossa.
6. Määritä tyyppi itse taulukossa käyttämällä Value.ReplaceType-funktiota.

Muistiinpano

Taulukkotyypin määrittämisen viimeinen vaihe poistaa Power Queryn käyttöliittymän tarpeen päätellä tietojen tyyppi, kun tuloksia tarkastellaan kyselyeditorissa. Tämä poistaa kaksoispyyntö-ongelman, jonka näit edellisen opetusohjelman lopussa.

Seuraava apukoodi voidaan kopioida ja liittää laajennukseen:

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;

TripPin-liittimen päivittäminen

Teet nyt seuraavat muutokset liittimeen, jotta voit hyödyntää uutta rakenteen pakottamiskoodia.

1. Määritä päärakennetaulukko (SchemaTable), joka sisältää kaikki rakennemäärityksesi.
2. Päivitä - TripPin.Feed, GetPage- ja GetAllPagesByNextLink -parametrin hyväksymiseksi schema .
3. Ota rakenne käyttöön kohteessa GetPage.
4. Päivitä siirtymistaulukon koodi rivittääksesi kunkin taulukon kutsulla uuteen funktioon (GetEntity)– tämä antaa enemmän joustavuutta taulukon määritelmien käsittelyyn jatkossa.

Päärakennetaulukko

Yhdistät nyt rakennemääritykset yhdeksi taulukoksi ja lisäät aputoiminnon (GetSchemaForEntity), jonka avulla voit etsiä määrityksen entiteetin nimen perusteella (esimerkiksi 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 &"'";

Rakenteen tuen lisääminen tietofunktioihin

Lisäät nyt valinnaisen schema parametrin TripPin.Feed-, GetPage- ja GetAllPagesByNextLink -funktioihin. Sen avulla voit välittää rakenteen (kun haluat) sivutusfunktioihin, joissa sitä käytetään palvelusta saamiisi tuloksiin.

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

Päivität myös kaikki kutsut näihin funktioihin varmistaaksesi, että siirrät rakenteen oikein.

Rakenteen pakottaminen

Rakenteen todellinen pakottaminen suoritetaan -toiminnollasi GetPage .

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

Muistiinpano

Tämä GetPage toteutus käyttää Table.FromRecords-funktiota JSON-vastauksen tietueluettelon muuntamiseen taulukoksi. Table.FromRecords-funktion käyttämisen merkittävä haittapuoli on se, että oletetaan, että kaikilla luettelon tietueilla on samat kentät. Tämä toimii TripPin-palvelussa, koska OData-tietueiden on taattu sisältävän samat kentät, mutta näin ei välttämättä ole kaikissa REST-ohjelmointirajapituissa. Vankempi toteutus käyttäisi yhdistelmää Table.FromList ja Table.ExpandRecordColumn. Myöhemmissä opetusohjelmissa muutetaan toteutusta, jotta saat sarakeluettelon rakennetaulukosta ja varmistat, että JSON-käännöksen aikana ei menetetä tai puuttuu sarakkeita.

GetEntity-funktion lisääminen

Funktio GetEntity rivittää puhelusi kohteeseen TripPin.Feed. Se etsii entiteetin nimeen perustuvan rakenteen määrityksen ja luo pyynnön URL-osoitteen kokonaisuudessaan.

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;

Sen jälkeen päivität funktion TripPinNavTable kutsumaan -funktiota GetEntitysen sijaan, että kaikki kutsut soitaisit sisäisesti. Suurin etu tässä on se, että sen avulla voit jatkaa entiteetin luontikoodin muokkaamista koskematta siirtymistaulukon logiikkaa.

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;

Kaikkien kokoaminen yhteen

Kun kaikki koodimuutokset on tehty, käännä ja suorita uudelleen testikysely, joka vaatii Table.Schema Airlines-taulukon.

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

Näet nyt, että Airlines-taulukossasi on vain kaksi saraketta, jotka olet määrittänyt sen rakenteessa:

Jos suoritat saman koodin Ihmiset taulukolle...

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

Näet, että myös käyttämäsiInt64.Type () määritetty tyyppi on määritetty oikein.

Tärkeä seikka on, että tämä toteutus SchemaTransformTable ei muokkaa -listtyyppiä ja -sarakkeita, mutta - ja record AddressInfo -Emailssarakkeiden tyyppi on listedelleen . Tämä johtuu Json.Document siitä, että JSON-matriisit yhdistetään oikein M-luetteloihin ja JSON-objektit M-tietueisiin. Jos laajentaisit Luettelo- tai tietuesaraketta Power Queryssa, näkisit, että kaikki laajennetut sarakkeet olisivat minkä tahansa tyyppiä. Tulevat opetusohjelmat parantavat toteutusta ja määrittävät rekursiivisesti tyyppitiedot sisäkkäisille monimutkaisille tyypeille.

Yhteenveto

Tässä opetusohjelmassa annettiin mallitoteutuksen REST-palvelusta palautettujen JSON-tietojen rakenteen pakottamisesta. Vaikka tässä mallissa käytetään yksinkertaista kiinteästi koodattua rakennetaulukkomuotoa, lähestymistapaa voidaan laajentaa luomalla rakennetaulukon määritys dynaamisesti toisesta lähteestä, kuten JSON-rakennetiedostosta, tai tietolähteen paljastamasta metatietopalvelusta/päätepisteestä.

Sen lisäksi, että koodisi muokkaa saraketyyppejä (ja arvoja), se määrittää myös oikean tyyppiset tiedot itse taulukkoon. Tämän tyypin tietojen määrittäminen hyödyttää suorituskykyä Power Queryssa, sillä käyttäjäkokemus yrittää aina päätellä tietotyyppien kirjoittamisen niin, että käyttäjälle näytetään oikeat käyttöliittymäjonot, ja päätekutsut voivat lopulta käynnistää muita kutsuja pohjana oleviin tietojen ohjelmointirajapintoihin.

Jos tarkastelet Ihmiset-taulukkoa käyttämällä edellisen oppitunnin TripPin-liitintä, huomaat, että kaikilla sarakkeilla on "type any" -kuvake (jopa sarakkeet, jotka sisältävät luetteloita):

Kun suoritat saman kyselyn TripPin-liittimellä tältä oppitunnilta, näet nyt, että tyyppitiedot näkyvät oikein.

Seuraavat vaiheet

TripPin,osa 7 – kehittynyt rakenne M-tyypeillä