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 czynności:

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 do automatycznego ustawiania informacji o typie danych. Dlatego zamiast pobierać wszystkie pola tekstowe i liczbowe (tak jak w przypadku Json.Document), użytkownicy końcowi uzyskują daty, liczby całkowite, godziny itd., zapewniając lepsze ogólne doświadczenie 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 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 (typowym sposobem wskazywania pola powinno być null)

Wyświetlanie istniejącego schematu za pomocą funkcji 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 zwracane są cztery kolumny:

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

Kolumny "@odata.*" są częścią protokołu OData i nie są czymś, co chcesz lub musisz 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 ), 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 dostarczą wzorców projektowych do ustawiania tych zaawansowanych właściwości, ale na razie powinieneś skupić się tylko na przypisanym typie (TypeName), typie pierwotnym (Kind) i tym, czy wartość kolumny może być null (IsNullable).

Definiowanie prostej tabeli schematu

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

Hardkodowana tabela schematów dla tabeli Airlines ustawia swoje kolumny AirlineCode i Name do 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}
    });

Na koniec tabela zawiera siedem pól, w tym listy (Emails, AddressInfo), kolumnę nullowalną (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 pomocnicza opisana w tej sekcji SchemaTransformTable służy 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 bezpośrednio w tabeli przy użyciu 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

Aby wykorzystać nowy kod egzekwowania schematu, wprowadź następujące zmiany w łączniku.

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ę przy użyciu wywołania nowej funkcji (GetEntity). Ta funkcja zapewnia większą elastyczność manipulowania definicjami tabel w przyszłości.

Tabela schematu głównego

Teraz skonsoliduj definicje schematu w jedną tabelę i dodaj 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 dodaj opcjonalny schema parametr do TripPin.Feedfunkcji , GetPagei GetAllPagesByNextLink . Ten parametr umożliwia przekazanie schematu (gdy chcesz) do funkcji stronicowania, gdzie są one stosowane 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 => ...

Należy również zaktualizować wszystkie wywołania tych funkcji, aby upewnić się, że schemat został prawidłowo przekazany.

Wymuszanie schematu

Właściwe wymuszanie schematu jest realizowane w funkcji 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];

Dodawanie funkcji GetEntity

Funkcja GetEntity opakowuje wywołanie funkcji TripPin.Feed. Wyszukuje definicję schematu na podstawie nazwy jednostki i tworzy pełny adres 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 funkcję TripPinNavTable, aby wywołać metodę GetEntity, zamiast wykonywać wszystkie wywołania inline. Główną zaletą tej aktualizacji jest to, że umożliwia kontynuowanie modyfikowania kodu tworzenia jednostki 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;

Łączenie tego wszystkiego

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

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

Tabela Linie lotnicze zawiera teraz tylko dwie kolumny zdefiniowane w jego schemacie:

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

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

Zwróć uwagę, że użyty 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. To zachowanie występuje, ponieważ Json.Document poprawnie mapuje tablice JSON na listy M i obiekty JSON na rekordy języka M. Jeśli rozwiniesz listę lub kolumnę rekordu w dodatku Power Query, wszystkie rozwinięte kolumny będą miały typ any. Przyszłe samouczki pomogą usprawnić implementację w celu rekursywnego ustawiania informacji 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ę People przy użyciu łącznika TripPin z poprzedniej lekcji, wszystkie kolumny mają ikonę "typ dowolny" (nawet kolumny zawierające listy):

Jeśli uruchomisz to samo zapytanie z łącznikiem TripPin z tej lekcji, informacje o typie są teraz wyświetlane poprawnie.

Dalsze kroki

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