Часть 10 TripPin — базовое свертывание запросов

Примечание.

В настоящее время это содержимое ссылается на содержимое из устаревшей реализации для журналов в Visual Studio. Содержимое будет обновлено в ближайшем будущем, чтобы покрыть новый пакет SDK Power Query в Visual Studio Code.

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

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

• Основы свертывания запросов
• Сведения о функции Table.View
• Репликация обработчиков свертывания запросов OData для:

• $top
• $skip
• $count
• $select
• $orderby

Одним из мощных функций языка M является возможность принудительного преобразования работать в один или несколько базовых источников данных. Эта возможность называется свертывания запросов (другие инструменты и технологии также называются аналогичной функцией, как Pushdown предиката или делегирование запросов).

При создании пользовательского соединителя, использующего функцию M со встроенными возможностями свертывания запросов, например OData.Feed или Odbc.DataSource, соединитель автоматически наследует эту возможность бесплатно.

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

Чтобы узнать больше о возможностях запросов, которые может предложить служба OData, перейдите к соглашениям по URL-адресам OData версии 4.

Примечание.

Как упоминалось ранее, функция OData.Feed автоматически предоставляет возможности свертывания запросов. Так как серия TripPin обрабатывает службу OData как обычный REST API, используя web.Contents , а не OData.Feed, вам потребуется реализовать обработчики свертывания запросов самостоятельно. Для реального использования рекомендуется использовать OData.Feed по возможности.

Дополнительные сведения о свертке запросов см. в статье "Обзор оценки запросов и свертывания запросов" в Power Query .

Использование Table.View

Функция Table.View позволяет пользовательскому соединителю переопределить обработчики преобразований по умолчанию для источника данных. Реализация Table.View предоставляет функцию для одного или нескольких поддерживаемых обработчиков. Если обработчик недооценен или возвращается во время оценки, подсистема M возвращается error к обработчику по умолчанию.

Если настраиваемый соединитель использует функцию, которая не поддерживает неявное свертывание запросов, например Web.Contents, обработчики преобразования по умолчанию всегда выполняются локально. Если rest API, который вы подключаетесь к поддержке параметров запроса в рамках запроса, Table.View позволяет добавлять оптимизации, которые позволяют принудительно отправлять операции преобразования в службу.

Функция Table.View имеет следующую подпись:

Table.View(table as nullable table, handlers as record) as table

Реализация упаковывает основную функцию источника данных. Существует два обязательных обработчика для Table.View:

• GetType— возвращает ожидаемый table type результат запроса.
• GetRows— возвращает фактический table результат функции источника данных

Простейшая реализация будет похожа на следующий пример:

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

TripPinNavTable Обновите функцию для вызоваTripPin.SuperSimpleView, а неGetEntity:

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

При повторном запуске модульных тестов вы увидите, что поведение функции не изменится. В этом случае реализация Table.View просто передает вызов GetEntity. Так как вы еще не реализовали обработчики преобразований (но), исходный url параметр остается неотпученным.

Начальная реализация Table.View

Приведенная выше реализация Table.View является простой, но не очень полезной. Следующая реализация используется в качестве базового плана— она не реализует какие-либо функции свертывания, но имеет шаблон, который вам нужно сделать.

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

При просмотре вызова Table.View вы увидите дополнительную функцию-оболочку вокруг handlers записи.Diagnostics.WrapHandlers Эта вспомогающая функция найдена в модуле диагностики (которая была представлена в добавлении диагностика уроке) и предоставляет удобный способ автоматической трассировки любых ошибок, возникающих отдельными обработчиками.

Функции GetType обновляются, чтобы использовать две новые вспомогательные функции иCalculateSchemaCalculateUrl.GetRows Сейчас реализации этих функций довольно просты— обратите внимание, что они содержат части того, что ранее было сделано функцией GetEntity .

Наконец, обратите внимание, что вы определяете внутреннюю функцию (View), которая принимает state параметр. При реализации дополнительных обработчиков они рекурсивно вызывают внутреннюю View функцию, обновляя и передавая их по state мере их использования.

TripPinNavTable Обновите функцию еще раз, заменив вызов TripPin.SuperSimpleView на вызов новой TripPin.View функции и повторно запустите модульные тесты. Вы еще не увидите новые функциональные возможности, но теперь у вас есть твердые базовые показатели для тестирования.

Реализация свертывания запросов

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

Ручной способ проверки поведения свертывания заключается в просмотре URL-адресов запросов модульных тестов с помощью инструмента, например Fiddler. Кроме того, журнал диагностики, добавленный для TripPin.Feed отправки полного URL-адреса, который должен включать параметры строки запроса OData, добавляемые обработчиками.

Автоматический способ проверки свертывания запросов — принудительно выполнить модульное тестирование, если запрос не полностью сворачиваться. Это можно сделать, открыв свойства проекта и установив значение Error on Folding Failure to True. Если этот параметр включен, любой запрос, требующий локальной обработки, приводит к следующей ошибке:

Не удалось сложить выражение в источник. Попробуйте более простое выражение.

Это можно проверить, добавив новый Fact в файл модульного теста, содержащий одно или несколько преобразований таблицы.

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

Примечание.

Параметр "Ошибка при сворачивании сбоя " — это подход "все или ничего". Если вы хотите протестировать запросы, которые не предназначены для свертывания в рамках модульных тестов, необходимо добавить определенную условную логику, чтобы включить или отключить тесты соответствующим образом.

Остальные разделы этого руководства добавляют новый обработчик Table.View . Вы принимаете подход к разработке на основе тестов (TDD), где сначала добавляются неудачные модульные тесты, а затем реализуется код M для их разрешения.

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

• Добавление обработчика в Table.View , которое обновляет state запись.
• Изменение CalculateUrl для получения значений state из url-адреса и /или параметров строки запроса.

Обработка table.FirstN с помощью OnTake

Обработчик OnTake получает count параметр, который является максимальным числом строк для получения.GetRows В терминах OData это можно преобразовать в параметр запроса $top .

Вы используете следующие модульные тесты:

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

Эти тесты используют Table.FirstN для фильтрации в результирующем наборе до первого числа строк X. Если выполнить эти тесты с ошибкой при сворачивании сбойFalse (по умолчанию), тесты должны выполняться успешно, но если вы запускаете Fiddler (или проверка журналы трассировки), обратите внимание, что запрос, который отправляется, не содержит параметров запроса OData.

Если задана ошибка при сворачиванииTrue, тесты завершаются ошибкойPlease try a simpler expression.. Чтобы устранить эту ошибку, необходимо определить первый обработчик Table.View для OnTake.

Обработчик OnTake выглядит следующим образом:

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

Функция CalculateUrl обновляется, чтобы извлечь Top значение из state записи и задать правильный параметр в строке запроса.

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

Повторное выполнение модульных тестов обратите внимание, что URL-адрес, к которому вы обращаетесь, теперь содержит $top параметр. Из-за кодирования $top URL-адреса отображается как %24top, но служба OData достаточно умна, чтобы автоматически преобразовать ее.

Обработка table.Skip с помощью OnSkip

Обработчик OnSkip очень похож OnTake. Он получает count параметр, который является числом строк, пропускаемых из результирующий набор. Этот обработчик хорошо преобразуется в параметр запроса OData $skip .

Модульные тесты:

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

Реализация:

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

Сопоставление обновлений для CalculateUrl:

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

Дополнительные сведения: Table.Skip

Обработка Table.SelectColumns с помощью OnSelectColumns

Обработчик OnSelectColumns вызывается, когда пользователь выбирает или удаляет столбцы из результирующего набора. Обработчик получает listtext значения, представляющие один или несколько столбцов, которые нужно выбрать.

В терминах OData эта операция сопоставляется с параметром запроса $select .

Преимущество выбора свертывания столбца становится очевидным при работе с таблицами со многими столбцами. Оператор $select удаляет неизбранные столбцы из результирующего набора, что приводит к более эффективным запросам.

Модульные тесты:

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

Первые два теста выбирают разные числа столбцов с помощью Table.SelectColumns и включают вызов Table.FirstN , чтобы упростить тестовый случай.

Примечание.

Если тест должен был просто вернуть имена столбцов (используя Table.ColumnNames , а не данные, запрос к службе OData никогда не будет отправлен. Это связано с тем, что вызов GetType возвращает схему, которая содержит всю информацию, которую подсистема M должна вычислить результат.

Третий тест использует параметр MissingField.Ignore , который сообщает обработчику M игнорировать все выбранные столбцы, которые не существуют в результирующем наборе. Обработчику OnSelectColumns не нужно беспокоиться об этом параметре— подсистема M обрабатывает ее автоматически (то есть отсутствующие столбцы не включены в columns список).

Примечание.

Другой параметр Table.SelectColumns, MissingField.UseNull, требует соединителя для реализации обработчика OnAddColumn. Это будет сделано в следующем занятии.

Реализация для OnSelectColumns двух действий:

• Добавляет список выбранных столбцов в объект state.
• Пересчитывает Schema значение, чтобы можно было задать правильный тип таблицы.

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

CalculateUrl обновляется, чтобы получить список столбцов из состояния и объединить их (с разделителем) для $select параметра.

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

Обработка table.Sort с помощью OnSort

Обработчик OnSort получает список записей типа:

type [ Name = text, Order = Int16.Type ]

Каждая запись содержит Name поле, указывающее имя столбца, и Order поле, равное Order.Ascending или Order.Descending.

В терминах OData эта операция сопоставляется с параметром запроса $orderby . Синтаксис $orderby имеет имя столбца, за которым следует asc или desc указывает порядок возрастания или убывания. При сортировке по нескольким столбцам значения разделяются запятыми. columns Если параметр содержит несколько элементов, важно сохранить порядок их отображения.

Модульные тесты:

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

Реализация:

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

Обновления :CalculateUrl

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

Обработка Table.RowCount с помощью GetRowCount

В отличие от других обработчиков запросов, которые вы реализуете, GetRowCount обработчик возвращает одно значение — количество строк, ожидаемых в результирующем наборе. В запросе M это значение обычно будет результатом преобразования Table.RowCount .

У вас есть несколько различных вариантов обработки этого значения в рамках запроса OData:

• Параметр запроса $count, который возвращает число в виде отдельного поля в результирующем наборе.
• Сегмент пути /$count, возвращающий только общее число, в виде скалярного значения.

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

Преимущество подхода сегмента пути заключается в том, что вы получаете только одно скалярное значение в результате. Такой подход делает всю операцию более эффективной. Однако, как описано в спецификации OData, сегмент пути /$count возвращает ошибку при включении других параметров запроса, таких как $top или $skip, что ограничивает его полезность.

В этом руководстве вы реализовали GetRowCount обработчик с помощью подхода сегмента пути. Чтобы избежать ошибок, которые вы получаете, если включены другие параметры запроса, вы проверка для других значений состояния и вернули ошибку без подтверждения () при... обнаружении. Возвращая любую ошибку из обработчика Table.View , обработчик M сообщает подсистеме M, что операция не может быть сложена, и она должна вернуться к обработчику по умолчанию (что в данном случае будет считать общее количество строк).

Сначала добавьте модульный тест:

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

/$count Так как сегмент пути возвращает одно значение (в формате обычного или текстового), а не результирующий набор JSON, необходимо также добавить новую внутреннюю функцию (TripPin.Scalar) для выполнения запроса и обработки результата.

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

Затем реализация использует эту функцию (если другие параметры запроса не найдены в state):

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

Функция CalculateUrl обновляется для добавления /$count к URL-адресу, если RowCountOnly в поле задано stateполе.

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

Теперь новый Table.RowCount модульный тест должен пройти.

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

Сначала добавьте вспомогательный метод, который проверка результат try операции для свертывания ошибки.

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

Затем добавьте тест, использующий Table.RowCount и Table.FirstN , чтобы принудительно выполнить ошибку.

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

Важно отметить, что этот тест теперь возвращает ошибку, если задана falseошибка при свертке ошибки, так как Table.RowCount операция возвращается к локальному обработчику (по умолчанию). Выполнение тестов с набором ошибок при сворачивании ошибок приводит к trueTable.RowCount сбою и позволяет тесту завершиться успешно.

Заключение

Реализация Table.View для соединителя значительно усложняет код. Так как подсистема M может обрабатывать все преобразования локально, добавление обработчиков Table.View не включает новые сценарии для пользователей, но приводит к более эффективной обработке (и потенциально счастливым пользователям). Одним из основных преимуществ обработчиков Table.View является необязательное, является то, что он позволяет добавочно добавлять новые функции, не влияя на обратную совместимость для соединителя.

Для большинства соединителей важный (и базовый) обработчик для реализации — OnTake это (который преобразуется $top в OData), так как он ограничивает количество возвращаемых строк. Интерфейс Power Query всегда выполняет OnTake1000 строки при отображении предварительных версий в редакторе навигаторов и запросов, поэтому пользователи могут видеть значительные улучшения производительности при работе с большими наборами данных.