Schéma spracovania

V závislosti od zdroja údajov môžu alebo nemusia byť informácie o typoch údajov a názvoch stĺpcov uvedené explicitne. Rozhrania OData REST API to zvyčajne spracovávajú pomocou definície $metadata a metóda Power Query automaticky spracováva tieto informácie a používa ich na údaje vrátené zo OData.Feed zdroja OData.

Mnohé rozhrania REST API nemajú možnosť programovania na určenie schémy. V týchto prípadoch budete musieť do konektora zahrnúť definíciu schémy.

Jednoduchý pevný kódovaný prístup

Najjednoduchším prístupom je pevné kódovanie definície schémy do konektora. To je postačujúce pre väčšinu prípadov použitia.

Vy presadzovanie schémy údajov vrátených konektorom má celkovo viacero výhod, ako napríklad:

  • Nastavenie správnych typov údajov.
  • Odstránenie stĺpcov, ktoré nie je potrebné zobraziť koncovým používateľom (napríklad internými identifikátormi alebo informáciami o stave).
  • Zabezpečením toho, že každá strana údajov má rovnaký tvar pridaním všetkých stĺpcov, ktoré môžu chýbať v odpovedi (rozhrania REST API zvyčajne označujú, že polia by mali mať hodnotu null vynechaním úplne).

Zobrazenie existujúcej schémy pomocou Table.Schema

Pozrite si nasledujúci kód, ktorý vracia jednoduchú tabuľku zo služby ukážky TripPin OData:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    asTable

Poznámka

TripPin je zdroj OData, takže realisticky by bolo dôležitejšie jednoducho použiť OData.Feed automatické spracovanie schém vo funkcii. V tomto príklade sa so zdrojom bude zaobchádzať ako s typickým rozhraním REST API a pomocou metódy na znázornenie techniky ťažko Web.Contents kódovania schémy ručne.

Táto tabuľka je výsledkom:

Tabuľka údajov TripPin Airline.

Pomocou tejto užitočnéj Table.Schema funkcie môžete skontrolovať typ údajov v stĺpcoch:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    Table.Schema(asTable)

Výsledok tabuľky Table.Schema použitý na údaje TripPin Airline.

Kód leteckých spoločností aj Názov sú any typu. Table.Schema Vráti množstvo metaúdajov o stĺpcoch v tabuľke vrátane názvov, pozícií, typov informácií a mnohých pokročilých vlastností, ako sú presnosť, mierka a maxLength. Teraz by ste sa mali zapýtať len na pripísaný typ ( ), primitívny typ ( ) a na to, či hodnota TypeName stĺpca môže byť null ( Kind IsNullable ).

Definovanie jednoduchej tabuľky schémy

Tabuľka schémy sa skladá z dvoch stĺpcov:

Stĺpec Podrobnosti
Name Názov stĺpca. Tento názov sa musí zhodovať s názvom vo výsledkoch vrátených službaou.
Typ Typ údajov M, ktorý budete nastaviť. Môže ísť o primitívny typ (text, číslo, dátum a čas atď.) alebo pripísaný typ (Int64.Type, Currency.Type atď.).

Tabuľka s pevnou schémou pre Airlines tabuľku nastaví jej AirlineCode stĺpce Name a a bude text vyzerať takto:

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

Pri pohľade na ďalšie koncové body zvážte nasledujúce tabuľky schém:

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

Tabuľka má sedem polí vrátane stĺpcov s ( , ), a People list Emails AddressInfo nulovateľných stĺpcov ( Gender ) a stĺpca s pripísateľný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}
    })

Všetky tieto tabuľky môžete vložiť do jednej hlavnej tabuľky SchemaTable schémy:

SchemaTable = #table({"Entity", "SchemaTable"}, {
        {"Airlines", Airlines},
        {"Airports", Airports},
        {"People", People}
    })

Tabuľka schém.

Funkcia Pomocníka SchemaTransformTable

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

Parameter Typ Description
tabuľka tabuľka Tabuľka údajov, ktorú budete chcieť vynútiť pomocou schémy.
Schémy tabuľka Tabuľka schémy, z ktorá sa má čítať informácie o stĺpci, s nasledujúcim typom: type table [Name = text, Type = type] .
enforceSchema Číslo (voliteľné) Enuum, ktoré ovláda správanie funkcie.
Predvolená hodnota ( ) zabezpečí, že výstupná tabuľka bude zodpovedať tabuľke schémy, ktorá bola poskytnutá pridaním chýbajúcich EnforceSchema.Strict = 1 stĺpcov, a odstráni ďalšie stĺpce.
Túto EnforceSchema.IgnoreExtraColumns = 2 možnosť možno použiť na zachovanie ďalších stĺpcov vo výsledku.
Keď EnforceSchema.IgnoreMissingColumns = 3 sa použije, chýbajúce stĺpce aj ďalšie stĺpce sa budú ignorovať.

Logika pre túto funkciu vyzerá asi takto:

  1. Zistite, či v zdrojovej tabuľke chýbajú stĺpce.
  2. Určte, či sú k dispozícii ďalšie stĺpce.
  3. Ignorovať štruktúrované stĺpce (typu list , a ) a record table stĺpce nastavené na typ any .
  4. Používa Table.TransformColumnTypes sa na nastavenie každého typu stĺpca.
  5. Zmeniť poradie stĺpcov na základe poradia, v akom sa zobrazujú v tabuľke schémy.
  6. Nastavte typ tabuľky pomocou príkazu Value.ReplaceType .

Poznámka

Posledným krokom na nastavenie typu tabuľky odstránite potrebu, aby používateľské rozhranie služby Power Query pri prezeraní výsledkov v editore dotazov bolo potrebné deferovať typ informácií, čo môže niekedy viesť k dvojitému volania rozhrania API.

Spojiť všetko dokopy

V väčším kontexte úplného rozšírenia sa spracovanie schémy uskutoční, keď sa z rozhrania API vráti tabuľka. Táto funkcia sa zvyčajne vykonáva na najnižšej úrovni funkcie stránkovania (ak existuje), s informáciami o entite odovzdaných z navigačnej tabuľky.

Pretože veľká časť implementácie stránkovacích a navigačných tabuliek je špecifická pre kontext, úplný príklad implementácie pevného mechanizmu spracovania schém sa tu nebude zobrazovať. V tomto príklade TripPin je možné ukázať, ako môže vyzerať koncové riešenie.

Sofistikovaný prístup

Nekódovaná implementácia popísané vyššie dobre robí dobrú prácu, pretože schémy zostávajú konzistentné pre jednoduché opakovania JSON, ale je obmedzené na analýza prvej úrovne odpovede. Hlbšie vnorené množiny údajov by mohli využívať výhody nasledovného prístupu, ktorý využíva typy M.

Tu je rýchle obnovenie ohľadom typov v jazyku M zo špecifikácie jazyka:

Hodnota typu je hodnota, ktorá klasifikuje iné hodnoty. Znamená to, že hodnota, ktorá je klasifikovaná typom, zodpovedá danému typu. Systém typov jazyka M sa skladá z nasledujúcich druhov typov:

  • Primitívne typy, ktoré klasifikujú primitívne hodnoty ( , , , , , , , , , ) a zahŕňajú aj množstvo binary date datetime datetimezone duration list logical null number record text time type abstraktných typov ( function , , table a any none ).
  • Typy záznamu, ktoré klasifikujú hodnoty záznamu na základe názvov polí a typov hodnôt.
  • Typy zoznamu, ktoré klasifikujú zoznamy pomocou jediného základného typu položky.
  • Typy funkcií, ktoré klasifikujú hodnoty funkcií na základe typov ich parametrov a vrátených hodnôt.
  • Typy tabuľky, ktoré klasifikujú hodnoty tabuľky na základe názvov stĺpcov, typov stĺpcov a kľúčov.
  • Typy s nulovateľným hodnotou, ktoré klasifikujú hodnotu null okrem všetkých hodnôt klasifikovaných základným typom.
  • Typy typov, ktoré klasifikujú hodnoty, ktoré sú typmi.

Pomocou nespracového výstupu JSON, ktorý získate (alebo vyhľadate definície v súbore $metadataslužby ), môžete definovať nasledujúce typy záznamov, ktoré predstavujú zložité typy OData:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

Všimnite LocationType si, ako CityType odkazuje na LocType a predstavujú jeho štruktúrované stĺpce.

Pri entít najvyššej úrovni, ktoré budete chcieť reprezentovať ako Tabuľky, môžete definovať typy tabuliek:

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];
AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];
PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency  Int64.Type
];

Potom môžete aktualizovať premennú (ktorú môžete použiť ako vyhľadávaciu tabuľku pre priradenia entít k typu) tak, aby používala SchemaTable tieto nové definície typu:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType},
    {"Airports", AirportsType},
    {"People", PeopleType}
});

Môžete sa spoliehať na spoločnú funkciu ( ) a vynútiť schému na údaje, podobne ako Table.ChangeType ste používali SchemaTransformTable v staršom cvičení. Na rozdiel od , použije skutočný typ tabuľky M ako argument a použije vašu schému SchemaTransformTable Table.ChangeType rekurzívne pre všetky vnorené typy. Jej podpis je:

Table.ChangeType = (table, tableType as type) as nullable table => ...

Poznámka

Ak chcete flexibilitu, funkciu možno použiť v tabuľkách aj zoznamoch záznamov (čo je spôsob, akým sú tabuľky zastúpené v dokumente JSON).

Potom bude potrebné aktualizovať kód konektora, aby ste zmenili parameter z schema na a , a potom pridať table type volanie do Table.ChangeType . Podrobnosti, ktoré s tým treba robiť, sú však veľmi špecifické pre implementáciu, a preto tu nie je potrebné podrobne písať. Tento rozšírený príklad konektora TripPin znázorňuje koncové riešenie, ktoré implementuje tento sofistikovanejší prístup k manipulácii so schémou.