TripPin, część 6 — schemat

Ten wieloczęściowy samouczek obejmuje tworzenie nowego rozszerzenia źródła danych dla dodatku Power Query. Samouczek ma być wykonywany sekwencyjnie — każda lekcja opiera się na łączniku utworzonym w poprzednich lekcjach, przyrostowo dodając nowe możliwości do łącznika.

W tej lekcji wykonasz następujące lekcji:

Jedną z zalet usługi OData za pośrednictwem standardowego interfejsu API REST jest jej definicja $metadata. W dokumencie $metadata opisano dane znalezione w tej usłudze, w tym schemat wszystkich jednostek (tabel) i pól (kolumn). Funkcja OData.Feed używa tej definicji schematu, aby automatycznie ustawiać informacje o typie danych — dlatego zamiast pobierać wszystkie pola tekstowe i liczbowe (takie jak z Json.Document), użytkownicy końcowi uzyskują daty, liczby całkowite, godziny itd., zapewniając lepsze ogólne środowisko użytkownika.

Wiele interfejsów API REST nie ma sposobu programowego określania ich schematu. W takich przypadkach należy uwzględnić definicje schematu w łączniku. W tej lekcji zdefiniujesz prosty, zakodowany na stałe schemat dla każdej z tabel i wymusisz schemat na danych odczytanych z usługi.

Ogólnie rzecz biorąc, wymuszanie schematu na danych zwracanych przez łącznik ma wiele korzyści, takich jak:

• Ustawianie prawidłowych typów danych
• Usuwanie kolumn, które nie muszą być wyświetlane dla użytkowników końcowych (takich jak wewnętrzne identyfikatory lub informacje o stanie)
• Upewnienie się, że każda strona danych ma ten sam kształt, dodając wszystkie kolumny, które mogą brakować w odpowiedzi (typowy sposób wskazywania, że pole powinno mieć wartość null)

Wyświetlanie istniejącego schematu za pomocą elementu Table.Schema

Łącznik utworzony w poprzedniej lekcji wyświetla trzy tabele z usługi TripPin —Airlines , Airportsi People. Uruchom następujące zapytanie, aby wyświetlić tabelę Airlines :

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

W wynikach zobaczysz cztery zwrócone kolumny:

• @odata.id
• @odata.editLink
• Kod linii lotniczych
• Nazwisko

Kolumny "@odata.*" są częścią protokołu OData, a nie coś, czego chcesz lub chcesz pokazać użytkownikom końcowym łącznika. AirlineCode i Name to dwie kolumny, które chcesz zachować. Jeśli przyjrzysz się schematowi tabeli (korzystając z przydatnej funkcji Table.Schema ), zobaczysz, że wszystkie kolumny w tabeli mają typ Any.Typedanych .

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

Tabela.Schema zwraca wiele metadanych dotyczących kolumn w tabeli, w tym nazwy, pozycje, informacje o typie i wiele zaawansowanych właściwości, takich jak Precyzja, Skala i MaxLength. Przyszłe lekcje zapewnią wzorce projektowe do ustawiania tych zaawansowanych właściwości, ale na razie potrzebujesz tylko siebie z przypisanym typem (TypeName), typem pierwotnym (Kind) i informacją, czy wartość kolumny może mieć wartość null (IsNullable).

Definiowanie prostej tabeli schematu

Tabela schematu będzie składać się z dwóch kolumn:

Zakodowana tabela schematów dla Airlines tabeli ustawia kolumny AirlineCode i na Name text, i wygląda następująco:

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

Tabela Airports zawiera cztery pola, które chcesz zachować (w tym jeden typ record):

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

People Na koniec tabela zawiera siedem pól, w tym listy (Emails, AddressInfo), kolumnę dopuszczaną do wartości null (Gender) i kolumnę z przypisanym typem (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}
    })

Funkcja pomocnika SchemaTransformTable

Funkcja SchemaTransformTable pomocnika opisana poniżej będzie używana do wymuszania schematów na danych. Przyjmuje następujące parametry:

Logika dla tej funkcji wygląda następująco:

1. Ustal, czy w tabeli źródłowej brakuje kolumn.
2. Ustal, czy istnieją jakieś dodatkowe kolumny.
3. Ignoruj kolumny ustrukturyzowane (typ list, recordi table) i kolumny ustawione na type any.
4. Użyj właściwości Table.TransformColumnTypes , aby ustawić każdy typ kolumny.
5. Zmień kolejność kolumn na podstawie kolejności, w której są wyświetlane w tabeli schematu.
6. Ustaw typ w samej tabeli przy użyciu wartości Value.ReplaceType.

Poniższy kod pomocnika można skopiować i wkleić do rozszerzenia:

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;

Aktualizowanie łącznika TripPin

Teraz wprowadzisz następujące zmiany w łączniku, aby korzystać z nowego kodu wymuszania schematu.

1. Zdefiniuj tabelę schematu głównego (SchemaTable), która zawiera wszystkie definicje schematu.
2. Zaktualizuj parametr TripPin.Feed, GetPagei GetAllPagesByNextLink , aby akceptował schema parametr .
3. Wymuś schemat w pliku GetPage.
4. Zaktualizuj kod tabeli nawigacji, aby opakowować każdą tabelę za pomocą wywołania nowej funkcji (GetEntity) — zapewni to większą elastyczność manipulowania definicjami tabel w przyszłości.

Tabela schematu głównego

Teraz skonsolidujesz definicje schematu w jedną tabelę i dodasz funkcję pomocnika (GetSchemaForEntity), która umożliwia wyszukanie definicji na podstawie nazwy jednostki (na przykład 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 &"'";

Dodawanie obsługi schematu do funkcji danych

Teraz dodasz opcjonalny schema parametr do TripPin.Feedfunkcji , GetPagei GetAllPagesByNextLink . Umożliwi to przekazanie schematu (gdy chcesz) do funkcji stronicowania, gdzie zostaną zastosowane do wyników uzyskanych z usługi.

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

Zaktualizujesz również wszystkie wywołania tych funkcji, aby upewnić się, że schemat zostanie prawidłowo przekazany.

Wymuszanie schematu

Rzeczywiste wymuszanie schematu zostanie wykonane w GetPage funkcji.

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

Dodawanie funkcji GetEntity

Funkcja GetEntity opakowuje wywołanie funkcji TripPin.Feed. Spowoduje to wyszukanie definicji schematu na podstawie nazwy jednostki i skompilowanie pełnego adresu URL żądania.

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;

Następnie zaktualizujesz TripPinNavTable funkcję tak, aby wywołała GetEntitymetodę , zamiast wprowadzać wszystkie wywołania wbudowane. Główną zaletą jest to, że umożliwi to kontynuowanie modyfikowania kodu kompilowania jednostek bez konieczności dotykania logiki tabeli nawigacji.

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;

Zebranie wszystkich elementów

Po wprowadzeniu wszystkich zmian kodu skompiluj i ponownie uruchom zapytanie testowe, które wywołuje Table.Schema tabelę Airlines.

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

Teraz zobaczysz, że tabela Airlines zawiera tylko dwie kolumny zdefiniowane w jego schemacie:

Jeśli uruchomisz ten sam kod względem tabeli Osoby...

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

Zobaczysz, że przypisany typ (Int64.Type) został również poprawnie ustawiony.

Ważne jest, aby pamiętać, że ta implementacja SchemaTransformTable nie modyfikuje typów list kolumn i record , ale Emails kolumny i AddressInfo są nadal wpisywane jako list. Jest to spowodowane tym, że Json.Document poprawnie zamapuje tablice JSON na listy M, a obiekty JSON na rekordy M. Jeśli chcesz rozwinąć listę lub kolumnę rekordów w dodatku Power Query, zobaczysz, że wszystkie rozwinięte kolumny będą miały dowolny typ. Przyszłe samouczki poprawią implementację, aby rekursywnie ustawić informacje o typie dla zagnieżdżonych typów złożonych.

Podsumowanie

W tym samouczku przedstawiono przykładową implementację wymuszania schematu na danych JSON zwracanych z usługi REST. Chociaż w tym przykładzie użyto prostego formatu tabeli schematu zakodowanego na stałe, podejście można rozszerzyć, dynamicznie tworząc definicję tabeli schematu z innego źródła, takiego jak plik schematu JSON, lub usługa metadanych/punkt końcowy uwidoczniony przez źródło danych.

Oprócz modyfikowania typów kolumn (i wartości) kod ustawia również poprawne informacje o typie w samej tabeli. Ustawienie tego typu informacji zapewnia wydajność podczas uruchamiania wewnątrz dodatku Power Query, ponieważ środowisko użytkownika zawsze próbuje wywnioskować informacje o typie, aby wyświetlić odpowiednie kolejki interfejsu użytkownika dla użytkownika końcowego, a wywołania wnioskowania mogą spowodować wyzwolenie innych wywołań do bazowych interfejsów API danych.

Jeśli wyświetlisz tabelę Osoby przy użyciu łącznika TripPin z poprzedniej lekcji, zobaczysz, że wszystkie kolumny mają ikonę "wpisz dowolny" (nawet kolumny zawierające listy):

Uruchomienie tego samego zapytania przy użyciu łącznika TripPin z tej lekcji zobaczysz teraz, że informacje o typie są wyświetlane poprawnie.

Następne kroki

TripPin — część 7 — zaawansowany schemat z typami M