Часть 6 TripPin — схема

Статья
08/29/2023

В этом руководстве рассматривается создание нового расширения источника данных для Power Query. Это руководство предназначено для последовательного выполнения каждого урока— каждый урок, созданный на основе соединителя, созданного на предыдущих уроках, постепенно добавляя новые возможности в соединитель.

В этом уроке вы научитесь:

Определение фиксированной схемы для REST API
Динамически задавать типы данных для столбцов
Принудительное применение структуры таблицы, чтобы избежать ошибок преобразования из-за отсутствующих столбцов
Скрытие столбцов из результирующего набора

Одним из больших преимуществ службы OData по сравнению со стандартным REST API является его определение $metadata. В документе $metadata описываются данные, найденные в этой службе, включая схему для всех его сущностей (таблиц) и полей (столбцов). Эта OData.Feed функция использует это определение схемы для автоматического задания сведений о типе данных, поэтому вместо получения всех текстовых и числовых полей (например, из Json.Document), конечные пользователи получают даты, целые числа, время и т. д., обеспечивая лучший общий интерфейс пользователя.

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

Примечание.

Описанный здесь подход должен работать для многих служб REST. Будущие уроки будут опираться на этот подход, рекурсивно применяя схемы для структурированных столбцов (записей, списков, таблиц) и предоставления примеров реализаций, которые могут программно создать таблицу схем из документов схемы CSDL или JSON.

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

Настройка правильных типов данных
Удаление столбцов, которые не должны отображаться конечным пользователям (например, внутренним идентификаторам или сведениям о состоянии)
Убедитесь, что каждая страница данных имеет одну и ту же фигуру, добавляя все столбцы, которые могут быть отсутствуют в ответе (распространенный способ для REST API, указывающий, что поле должно иметь значение NULL)

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

Соединитель, созданный на предыдущем занятии, отображает три таблицы из службы TripPin, AirportsAirlinesа также People. Выполните следующий запрос, чтобы просмотреть таблицу Airlines :

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

В результатах вы увидите четыре возвращаемых столбца:

@odata.id
@odata.editLink
AirlineCode
Имя.

Авиакомпании не выполняют схему.

Столбцы "@odata.*" являются частью протокола OData, а не то, что вы хотите или должны показать конечным пользователям соединителя. AirlineCode и Name являются двумя столбцами, которые вы хотите сохранить. При просмотре схемы таблицы (с помощью удобной функции Table.Schema ) можно увидеть, что все столбцы в таблице имеют тип Any.Typeданных.

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

Airlines Table.Schema.

Table.Schema возвращает много метаданных о столбцах в таблице, включая имена, позиции, сведения о типе и множество дополнительных свойств, таких как точность, масштабирование и MaxLength. Будущие уроки предоставляют шаблоны конструктора для настройки этих расширенных свойств, но теперь вам нужно только беспокоиться о том, что у вас есть указанный тип (), примитивный тип (TypeNameKind), а также может ли значение столбца иметь значение NULL (IsNullable).

Определение простой таблицы схемы

Таблица схемы будет состоять из двух столбцов:

Column	Сведения
Имя.	Имя столбца. Это должно соответствовать имени в результатах, возвращаемых службой.
Тип	Тип данных M, который вы собираетесь задать. Это может быть примитивный тип (`text`, , `numberdatetime`и т. д.), или указанный тип (`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 таблице есть семь полей, включая списки (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}
    })

Вспомогательные функции SchemaTransformTable

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

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

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

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

Примечание.

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

Следующий вспомогательный код можно скопировать и вставить в расширение:

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;

Обновление соединителя TripPin

Теперь вы внесите следующие изменения в соединитель, чтобы использовать новый код применения схемы.

Определите таблицу главной схемы (SchemaTable), которая содержит все определения схемы.
Обновите и GetPageGetAllPagesByNextLink примите TripPin.Feedschema параметр.
Принудительное применение схемы в GetPage.
Обновите код таблицы навигации, чтобы упаковать каждую таблицу с вызовом новой функции (GetEntity)- это позволит вам более гибко управлять определениями таблиц в будущем.

Таблица главной схемы

Теперь вы консолидируете определения схемы в одну таблицу и добавите вспомогающую функцию (GetSchemaForEntity), которая позволяет искать определение на основе имени сущности (например, 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 &"'";

Добавление поддержки схемы в функции данных

Теперь вы добавите необязательный schema параметр в TripPin.Feedфункции GetPageи GetAllPagesByNextLink функции. Это позволит передать схему (если требуется) в функции разбиения по страницам, где она будет применена к результатам, которые вы вернетесь из службы.

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 => ...

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

Принудительное применение схемы

Фактическое применение схемы будет выполнено в вашей 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];

Примечание.

Эта GetPage реализация использует Table.FromRecords для преобразования списка записей в ответ JSON в таблицу. Основной недостатком использования Table.FromRecords является то, что предполагается, что все записи в списке имеют одинаковый набор полей. Это работает для службы TripPin, так как записи OData должны содержать одни и те же поля, но это может быть не так для всех REST API. Более надежная реализация будет использовать сочетание Table.FromList и Table.ExpandRecordColumn. Более поздние учебники изменят реализацию, чтобы получить список столбцов из таблицы схемы, гарантируя, что столбцы не будут потеряны или отсутствуют во время перевода JSON на M.

Добавление функции GetEntity

Функция GetEntity завернет вызов в TripPin.Feed. Он будет искать определение схемы на основе имени сущности и создать ПОЛНЫй URL-адрес запроса.

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;

Затем вы обновите TripPinNavTable функцию для вызова GetEntity, а не для всех встроенных вызовов. Основное преимущество заключается в том, что это позволит продолжать изменять код сборки сущностей, не касаясь логики таблицы навигации.

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;

Сборка

После внесения всех изменений кода скомпилируйте и повторно запустите тестовый запрос, вызывающий Table.Schema таблицу Airlines.

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

Теперь вы увидите, что таблица Airlines содержит только два столбца, определенных в схеме:

Авиакомпании со схемой.

Если вы запускаете тот же код в таблице Люди...

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

Вы увидите, что использованный вами тип (Int64.Type) также был задан правильно.

Люди со схемой.

Важно отметить, что эта реализация SchemaTransformTable не изменяет типы list и record столбцы, но EmailsAddressInfo столбцы по-прежнему типизированны.list Это связано с тем, что Json.Document будет правильно сопоставлять массивы JSON со списками M и объектами JSON с записями M. Если вы хотите развернуть список или столбец записи в Power Query, вы увидите, что все развернутые столбцы будут иметь тип любого. Будущие учебники помогут улучшить реализацию для рекурсивного задания сведений о типах для вложенных сложных типов.

Заключение

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

Помимо изменения типов столбцов (и значений), код также задает правильные сведения о типе в самой таблице. Настройка этой информации типа обеспечивает производительность при выполнении в Power Query, так как взаимодействие с пользователем всегда пытается выводить сведения о типе для отображения правильных очередей пользовательского интерфейса для конечного пользователя, а вызовы вывода могут в конечном итоге активировать другие вызовы к базовым API данных.

Если вы просматриваете таблицу Люди с помощью соединителя TripPin из предыдущего урока, вы увидите, что все столбцы имеют значок "тип любого" (даже столбцы, содержащие списки):

Люди без схемы.

При выполнении того же запроса с соединителем TripPin из этого урока вы увидите, что сведения о типе отображаются правильно.

Люди со схемой.

Следующие шаги

Часть 7 TripPin — расширенная схема с типами M

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

Часть 6 TripPin — схема

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

Определение простой таблицы схемы

Вспомогательные функции SchemaTransformTable

Обновление соединителя TripPin

Таблица главной схемы

Добавление поддержки схемы в функции данных

Принудительное применение схемы

Добавление функции GetEntity

Сборка

Заключение

Следующие шаги

Обратная связь

Дополнительные ресурсы