Прочитать на английском

Поделиться через


Обработка схемы

В зависимости от источника данных сведения о типах данных и именах столбцов могут быть предоставлены явным образом. REST API OData обычно обрабатывают это с помощью определения $metadata, а метод Power Query OData.Feed автоматически обрабатывает эти сведения и применяет его к данным, возвращаемым из источника OData.

Многие ИНТЕРФЕЙСы REST API не имеют способа программно определить их схему. В этих случаях необходимо включить определение схемы в соединитель.

Простой жесткий подход

Самый простой подход заключается в жестком коде определения схемы в соединителе. Это достаточно для большинства вариантов использования.

В целом применение схемы данных, возвращаемых соединителем, имеет несколько преимуществ, таких как:

  • Задание правильных типов данных.
  • Удаление столбцов, которые не должны отображаться конечным пользователям (например, внутренним идентификаторам или сведениям о состоянии).
  • Убедитесь, что каждая страница данных имеет одну и ту же фигуру, добавляя все столбцы, которые могут быть отсутствуют в ответе (ИНТЕРФЕЙСы REST API обычно указывают, что поля должны быть пустыми, пропуская их полностью).

Просмотр существующей схемы с помощью Table.Schema

Рассмотрим следующий код, который возвращает простую таблицу из примера службы 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 для демонстрации метода жесткой кодировки схемы вручную.

Эта таблица является результатом:

Таблица данных TripPin Airlines.

С помощью удобной 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)

Результат таблицы.Schema, примененного к данным TripPin Airlines.

Как в 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 содержит семь полей, включая lists (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

Вспомогательную SchemaTransformTable функцию , описанную ниже, будут использоваться для принудительного применения схем к данным. Она принимает следующие параметры.

Параметр Тип Описание
table table Таблица данных, в которой вы хотите применить схему.
schema table Таблица схемы для чтения сведений о столбце со следующим типом: type table [Name = text, Type = type]
enforceSchema number (необязательно) Перечисление, которое управляет поведением функции.
Значение по умолчанию (EnforceSchema.Strict = 1) гарантирует, что выходная таблица будет соответствовать таблице схемы, предоставленной путем добавления отсутствующих столбцов и удаления дополнительных столбцов.
Этот EnforceSchema.IgnoreExtraColumns = 2 параметр можно использовать для сохранения дополнительных столбцов в результатах.
При EnforceSchema.IgnoreMissingColumns = 3 использовании отсутствующие столбцы и дополнительные столбцы будут игнорироваться.

Логика для этой функции выглядит примерно так:

  1. Определите, отсутствуют ли отсутствующие столбцы из исходной таблицы.
  2. Определите, есть ли дополнительные столбцы.
  3. Игнорировать структурированные столбцы (типа, recordи) и tableстолбцы, заданные для типаlistany.
  4. Используется Table.TransformColumnTypes для задания каждого типа столбца.
  5. Переупорядочение столбцов на основе порядка, который они отображаются в таблице схемы.
  6. Задайте тип для самой таблицы с помощью Value.ReplaceType.

Примечание

Последний шаг настройки типа таблицы приведет к удалению сведений о типе типов пользовательского интерфейса Power Query при просмотре результатов в редакторе запросов, что иногда может привести к двойному вызову API.

Сборка

В большем контексте полного расширения обработка схемы будет происходить, когда таблица возвращается из API. Обычно эта функция выполняется на самом низком уровне функции разбиения на страницы (если она существует), с данными сущности, передаваемыми из таблицы навигации.

Так как большая часть реализации таблиц разбиения на страницы и навигации зависит от контекста, полный пример реализации жестко закодированного механизма обработки схем не будет показан здесь. В этом примере TripPin показано, как может выглядеть комплексное решение.

Сложный подход

Жестко закодированная реализация, описанная выше, позволяет убедиться, что схемы остаются согласованными для простых повторов JSON, но это ограничивается анализом первого уровня ответа. Глубоко вложенные наборы данных могут воспользоваться следующим подходом, который использует преимущества типов M.

Ниже приведено краткое обновление типов на языке M из спецификации языка:

Значение типа — это значение, которое классифицирует другие значения. Значение, классифицируемые типом, как сообщается , соответствует такому типу. Система типов M состоит из следующих типов:

  • Примитивные типы, которые классифицируют примитивные значения (binary, recorddurationdatedatetimezonetypelisttimenulldatetimetextlogicalnumber) и включают ряд абстрактных типов (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 демонстрируется комплексное решение, реализующего этот более сложный подход к обработке схемы.