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.
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).
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:
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)
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
).
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 list
s (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 SchemaTable
schematu głównego:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
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:
- Ustal, czy w tabeli źródłowej brakuje kolumn.
- Ustal, czy istnieją jakieś dodatkowe kolumny.
- Ignoruj kolumny ustrukturyzowane (typu
list
,record
itable
) i kolumny ustawione na typany
. - Użyj
Table.TransformColumnTypes
polecenia , aby ustawić każdy typ kolumny. - Zmień kolejność kolumn na podstawie kolejności, w której są wyświetlane w tabeli schematu.
- 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.
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.
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
,list
logical
datetimezone
type
duration
time
null
number
record
datetime
text
), a także zawierają wiele typów abstrakcyjnych (function
,table
,any
i ).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 SchemaTransformTable
klasy , 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.ChangeType
metody . 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.