Przeczytaj w języku angielskim

Udostępnij za pośrednictwem


Obsługa schematu

W zależności od źródła danych informacje o typach danych i nazwach kolumn mogą być podane jawnie lub nie. Interfejsy API REST OData zwykle obsługują to przy użyciu definicji $metadata, a metoda Power Query OData.Feed automatycznie obsługuje analizowanie tych informacji i stosowanie ich do danych zwróconych ze źródła OData.

Wiele interfejsów API REST nie ma sposobu programowego określania ich schematu. W takich przypadkach należy uwzględnić definicję schematu w łączniku.

Proste, zakodowane na stałe podejście

Najprostszym podejściem jest zakodowanie definicji schematu w łączniku. Jest to wystarczające w przypadku większości przypadków użycia.

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 (interfejsy API REST często wskazują, że pola powinny mieć wartość null, pomijając je całkowicie).

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

Rozważ następujący kod, który zwraca prostą tabelę z przykładowej usługi TripPin OData:

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

Uwaga

TripPin jest źródłem OData, więc realistycznie byłoby bardziej zrozumiałe, aby po prostu użyć OData.Feed automatycznej obsługi schematu funkcji. W tym przykładzie będziesz traktować źródło jako typowy interfejs API REST i używać go Web.Contents do zademonstrowania techniki trwałego kodowania schematu ręcznie.

Ta tabela jest wynikiem:

Tabela danych linii lotniczych TripPin.

Możesz użyć przydatnej Table.Schema funkcji, aby sprawdzić typ danych kolumn:

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

Wynik tabeli.Schema zastosowany do danych linii lotniczych TripPin.

Kod linii lotniczych i nazwa są typu any . Table.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. Na razie należy martwić się tylko o przypisany typ (TypeName), typ pierwotny (Kind) i czy wartość kolumny może mieć wartość null (IsNullable).

Definiowanie prostej tabeli schematu

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

Kolumna Szczegóły
Nazwisko Nazwa kolumny. Musi być zgodna z nazwą w wynikach zwróconych przez usługę.
Typ Typ danych języka M, który chcesz ustawić. Może to być typ pierwotny (tekst, liczba, data/godzina itd.) lub przypisany typ (Int64.Type, Currency.Type itd.).

Zakodowana na stałe tabela schematów dla Airlines tabeli ustawi kolumny AirlineCode i Name na text i wygląda następująco:

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

W przypadku niektórych innych punktów końcowych należy wziąć pod uwagę następujące tabele schematu:

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

Tabela People zawiera siedem pól, w tym lists (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}
    })

Wszystkie te tabele można umieścić w jednej tabeli SchemaTableschematu głównego:

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

Tabela schematów.

Funkcja pomocnika SchemaTransformTable

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

Parametr Type Opis
table table Tabela danych, na których chcesz wymusić schemat.
schema table Tabela schematu do odczytywania informacji o kolumnie z klasy o następującym typie: type table [Name = text, Type = type].
enforceSchema Liczba (opcjonalnie) Wyliczenie, które kontroluje zachowanie funkcji.
Wartość domyślna (EnforceSchema.Strict = 1) gwarantuje, że tabela wyjściowa będzie zgodna z tabelą schematu dostarczoną przez dodanie wszelkich brakujących kolumn i usunięcie dodatkowych kolumn.
Za EnforceSchema.IgnoreExtraColumns = 2 pomocą opcji można zachować dodatkowe kolumny w wyniku.
W EnforceSchema.IgnoreMissingColumns = 3 przypadku użycia zarówno brakujące kolumny, jak i dodatkowe kolumny zostaną zignorowane.

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 (typu list, recordi table) i kolumny ustawione na typ any.
  4. Użyj Table.TransformColumnTypes polecenia , 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 polecenia Value.ReplaceType.

Uwaga

Ostatni krok ustawiania typu tabeli spowoduje usunięcie konieczności wnioskowania informacji o typie w interfejsie użytkownika dodatku Power Query podczas wyświetlania wyników w edytorze zapytań, co czasami może spowodować podwójne wywołanie interfejsu API.

Zebranie wszystkich elementów

W większym kontekście kompletnego rozszerzenia obsługa schematu będzie odbywać się, gdy tabela zostanie zwrócona z interfejsu API. Zazwyczaj ta funkcja odbywa się na najniższym poziomie funkcji stronicowania (jeśli istnieje), z informacjami o jednostce przekazywanymi z tabeli nawigacji.

Ponieważ tak duża część implementacji tabel stronicowania i nawigacji jest specyficzna dla kontekstu, kompletny przykład implementacji zakodowanego na stałe mechanizmu obsługi schematu nie będzie pokazany tutaj. W tym przykładzie TripPin pokazano, jak może wyglądać kompleksowe rozwiązanie.

Zaawansowane podejście

Zakodowana implementacja omówiona powyżej dobrze sprawdza, czy schematy pozostają spójne dla prostych repsonses JSON, ale jest ograniczona do analizowania pierwszego poziomu odpowiedzi. Głęboko zagnieżdżone zestawy danych korzystają z następującego podejścia, które korzysta z typów M.

Poniżej przedstawiono szybkie odświeżanie typów w języku M ze specyfikacji języka:

Wartość typu to wartość, która klasyfikuje inne wartości. Wartość sklasyfikowana przez typ jest określana jako zgodna z tym typem. System typów M składa się z następujących rodzajów typów:

  • Typy pierwotne, które klasyfikują wartości pierwotne (binary, date, listlogicaldatetimezonetypedurationtimenullnumberrecorddatetimetext), a także zawierają wiele typów abstrakcyjnych (function, table, anyi ).none
  • Typy rekordów, które klasyfikują wartości rekordów na podstawie nazw pól i typów wartości.
  • Typy list, które klasyfikują listy przy użyciu typu podstawowego pojedynczego elementu.
  • Typy funkcji, które klasyfikują wartości funkcji na podstawie typów ich parametrów i zwracanych wartości.
  • Typy tabel, które klasyfikują wartości tabeli na podstawie nazw kolumn, typów kolumn i kluczy.
  • Typy dopuszczane wartości null, które klasyfikują wartość null oprócz wszystkich wartości sklasyfikowanych przez typ podstawowy.
  • Typy typów, które klasyfikują wartości, które są typami.

Korzystając z nieprzetworzonych danych wyjściowych JSON (i/lub wyszukując definicje w $metadata usługi), można zdefiniować następujące typy rekordów do reprezentowania typów złożonych 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
];

Zwróć uwagę, CityType jak LocationType odwołuje się do elementu i LocType do reprezentowania kolumn strukturalnych.

W przypadku jednostek najwyższego poziomu, które mają być reprezentowane jako tabele, można zdefiniować typy tabel:

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

Następnie możesz zaktualizować SchemaTable zmienną (której można użyć jako tabeli odnośników dla mapowań typu jednostki na typ), aby użyć tych nowych definicji typów:

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

Możesz polegać na typowej funkcji (Table.ChangeType), aby wymusić schemat danych, podobnie jak w SchemaTransformTable poprzednim ćwiczeniu. W przeciwieństwie do SchemaTransformTableklasy , Table.ChangeType przyjmuje rzeczywisty typ tabeli M jako argument i będzie stosować schemat rekursywnie dla wszystkich zagnieżdżonych typów. Jego podpis to:

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

Uwaga

W celu zapewnienia elastyczności funkcja może być używana w tabelach, a także na listach rekordów (w jaki sposób tabele są reprezentowane w dokumencie JSON).

Następnie należy zaktualizować kod łącznika, aby zmienić schema parametr z na table type, i dodać wywołanie do Table.ChangeTypemetody . Ponownie szczegółowe informacje na ten temat są bardzo specyficzne dla implementacji i dlatego nie warto szczegółowo przejść tutaj. Ten rozszerzony przykład łącznika TripPin przedstawia kompleksowe rozwiązanie implementujące to bardziej zaawansowane podejście do obsługi schematu.