Обработка схемы
В зависимости от источника данных сведения о типах данных и именах столбцов могут быть предоставлены явным образом. REST API OData обычно обрабатывают это с помощью определения $metadata, а метод Power Query OData.Feed
автоматически обрабатывает эти сведения и применяет его к данным, возвращаемым из источника OData.
Многие ИНТЕРФЕЙСы REST API не имеют способа программно определить их схему. В этих случаях необходимо включить определение схемы в соединитель.
Самый простой подход заключается в жестком коде определения схемы в соединителе. Это достаточно для большинства вариантов использования.
В целом применение схемы данных, возвращаемых соединителем, имеет несколько преимуществ, таких как:
- Задание правильных типов данных.
- Удаление столбцов, которые не должны отображаться конечным пользователям (например, внутренним идентификаторам или сведениям о состоянии).
- Убедитесь, что каждая страница данных имеет одну и ту же фигуру, добавляя все столбцы, которые могут быть отсутствуют в ответе (ИНТЕРФЕЙСы REST API обычно указывают, что поля должны быть пустыми, пропуская их полностью).
Рассмотрим следующий код, который возвращает простую таблицу из примера службы TripPin OData:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
Примечание
TripPin — это источник OData, так что реалистично было бы целесообразно просто использовать OData.Feed
автоматическую обработку схемы функции. В этом примере вы будете рассматривать источник как типичный REST API и использовать Web.Contents
для демонстрации метода жесткой кодировки схемы вручную.
Эта таблица является результатом:
С помощью удобной Table.Schema
функции можно проверить тип данных столбцов:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)
Как в airlineCode, так и Name имеют any
тип. Table.Schema
возвращает много метаданных о столбцах в таблице, включая имена, позиции, сведения о типе и множество дополнительных свойств, таких как точность, масштабирование и MaxLength. В настоящее время вам следует беспокоиться только о том, что тип подписки (TypeName
), примитивный тип (Kind
) и может ли значение столбца иметь значение NULL (IsNullable
).
Таблица схемы будет состоять из двух столбцов:
Column | Сведения |
---|---|
Имя. | Имя столбца. Это должно соответствовать имени в результатах, возвращаемых службой. |
Тип | Тип данных M, который вы собираетесь задать. Это может быть примитивный тип (текст, число, дата и т. д.) или указанный тип (Int64.Type, Currency.Type и т. д.). |
Таблица схемы с жесткой кодировкой для таблицы задает для нее Airlines
и AirlineCode
Name
столбцы text
и выглядит следующим образом:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
При просмотре некоторых других конечных точек рассмотрим следующие таблицы схем:
В Airports
таблице есть четыре поля, которые вы хотите сохранить (включая один из типов record
):
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
Таблица People
содержит семь полей, включая list
s (Emails
, ), AddressInfo
столбец, допускающий значение NULL (Gender
), и столбец с указанным типом (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}
})
Все эти таблицы можно поместить в одну главную таблицу SchemaTable
схемы:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
Вспомогательную SchemaTransformTable
функцию , описанную ниже, будут использоваться для принудительного применения схем к данным. Она принимает следующие параметры.
Параметр | Тип | Описание |
---|---|---|
table | table | Таблица данных, в которой вы хотите применить схему. |
schema | table | Таблица схемы для чтения сведений о столбце со следующим типом: type table [Name = text, Type = type] |
enforceSchema | number | (необязательно) Перечисление, которое управляет поведением функции. Значение по умолчанию ( EnforceSchema.Strict = 1 ) гарантирует, что выходная таблица будет соответствовать таблице схемы, предоставленной путем добавления отсутствующих столбцов и удаления дополнительных столбцов. Этот EnforceSchema.IgnoreExtraColumns = 2 параметр можно использовать для сохранения дополнительных столбцов в результатах. При EnforceSchema.IgnoreMissingColumns = 3 использовании отсутствующие столбцы и дополнительные столбцы будут игнорироваться. |
Логика для этой функции выглядит примерно так:
- Определите, отсутствуют ли отсутствующие столбцы из исходной таблицы.
- Определите, есть ли дополнительные столбцы.
- Игнорировать структурированные столбцы (типа,
record
и) иtable
столбцы, заданные для типаlist
any
. - Используется
Table.TransformColumnTypes
для задания каждого типа столбца. - Переупорядочение столбцов на основе порядка, который они отображаются в таблице схемы.
- Задайте тип для самой таблицы с помощью
Value.ReplaceType
.
Примечание
Последний шаг настройки типа таблицы приведет к удалению сведений о типе типов пользовательского интерфейса Power Query при просмотре результатов в редакторе запросов, что иногда может привести к двойному вызову API.
В большем контексте полного расширения обработка схемы будет происходить, когда таблица возвращается из API. Обычно эта функция выполняется на самом низком уровне функции разбиения на страницы (если она существует), с данными сущности, передаваемыми из таблицы навигации.
Так как большая часть реализации таблиц разбиения на страницы и навигации зависит от контекста, полный пример реализации жестко закодированного механизма обработки схем не будет показан здесь. В этом примере TripPin показано, как может выглядеть комплексное решение.
Жестко закодированная реализация, описанная выше, позволяет убедиться, что схемы остаются согласованными для простых повторов JSON, но это ограничивается анализом первого уровня ответа. Глубоко вложенные наборы данных могут воспользоваться следующим подходом, который использует преимущества типов M.
Ниже приведено краткое обновление типов на языке M из спецификации языка:
Значение типа — это значение, которое классифицирует другие значения. Значение, классифицируемые типом, как сообщается , соответствует такому типу. Система типов M состоит из следующих типов:
- Примитивные типы, которые классифицируют примитивные значения (
binary
,record
duration
date
datetimezone
type
list
time
null
datetime
text
logical
number
) и включают ряд абстрактных типов (function
,table
,any
и).none
- Типы записей, которые классифицируют значения записей на основе имен полей и типов значений.
- Типы списков, которые классифицируют списки с использованием базового типа одного элемента.
- Типы функций, которые классифицируют значения функций на основе типов их параметров и возвращаемых значений.
- Типы таблиц, которые классифицируют значения таблиц на основе имен столбцов, типов столбцов и ключей.
- Типы, допускающие значение NULL, которые классифицируют значение NULL в дополнение ко всем значениям, классифицированным по базовому типу.
- Типы типов, которые классифицируют значения, которые являются типами.
Используя необработанные выходные данные JSON(и/или путем поиска определений в $metadata службы), можно определить следующие типы записей для представления сложных типов 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
];
Обратите внимание, как LocationType
ссылается на CityType
структурированные столбцы и LocType
представлять их структуру.
Для сущностей верхнего уровня, которые вы хотите представить в виде таблиц, можно определить типы таблиц:
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
];
Затем можно обновить SchemaTable
переменную (которую можно использовать в качестве таблицы подстановки для сопоставлений типов сущностей) для использования этих новых определений типов:
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
Вы можете полагаться на общую функцию (Table.ChangeType
) для принудительного применения схемы к данным, как и в SchemaTransformTable
предыдущем упражнении. В отличие SchemaTransformTable
от того, Table.ChangeType
принимает фактический тип таблицы M в качестве аргумента и будет применять схему рекурсивно для всех вложенных типов. Его сигнатура:
Table.ChangeType = (table, tableType as type) as nullable table => ...
Примечание
Для гибкости функцию можно использовать в таблицах, а также списки записей (как таблицы представлены в документе JSON).
Затем необходимо обновить код соединителя, чтобы изменить schema
параметр с a table
на и type
добавить вызов Table.ChangeType
. Опять же, подробные сведения об этом очень характерны для реализации и поэтому не стоит подробно идти здесь. В этом расширенном примере соединителя TripPin демонстрируется комплексное решение, реализующего этот более сложный подход к обработке схемы.