Примеры свертывания запросов

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

Сценарий

Представьте сценарий, в котором с помощью базы данных Wide World Importers для базы данных SQL Azure Synapse Analytics необходимо создать запрос в Power Query, который подключается к fact_Sale таблице и извлекает последние 10 продаж только в следующих полях:

• Ключ продажи
• Ключ клиента
• Ключ даты счета
• Description
• Количество

Замечание

В демонстрационных целях в этой статье используется база данных, описанная в руководстве по загрузке базы данных Wide World Importers в Azure Synapse Analytics. Основное различие в этой статье заключается fact_Sale в том, что таблица содержит только данные за 2000 год, в общей сложности 3644 356 строк.

Хотя результаты могут отличаться от результатов, получаемых при следовании руководству из документации по Azure Synapse Analytics, цель этой статьи заключается в демонстрации основных концепций и того влияния, которое сворачивание запросов может оказать на ваши запросы.

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

• Нет свертывания запросов
• Частичное свертывание запросов
• Полное свертование запроса

Пример свертывания запросов отсутствует

Это важно

Запросы, использующие исключительно неструктурированные источники данных или не имеющие подсистему вычислений, например CSV-файлы или файлы Excel, не имеют возможностей свертывания запросов. Это означает, что Power Query вычисляет все необходимые преобразования данных с помощью подсистемы Power Query.

После подключения к базе данных и перехода к fact_Sale таблице выберите преобразование "Сохранить нижние строки ", найденное в группе "Уменьшить строки " на вкладке "Главная ".

После выбора этого преобразования появится новое диалоговое окно. В этом новом диалоговом окне можно ввести количество строк, которые вы хотите сохранить. В этом случае введите значение 10 и нажмите кнопку "ОК".

Подсказка

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

Затем выберите преобразование "Выбрать столбцы ", найденное в группе " Управление столбцами " на вкладке "Главная ". Затем можно выбрать столбцы, которые вы хотите сохранить из таблицы, и удалить остальные.

Наконец, в диалоговом окне "Выбор столбцов" выберите Sale Key,Customer Key, Invoice Date Keyи DescriptionQuantity столбцы, а затем нажмите кнопку "ОК".

Следующий пример кода — это полный скрипт M для созданного запроса:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

Нет свертывания запросов. Общие сведения об оценке запросов

В разделе "Примененные действия" в редакторе Power Query обратите внимание, что индикаторы свертывания запросов для Оставленные нижние строки и Выбор столбцов помечены как шаги, которые оцениваются за пределами источника данных или, другими словами, движком Power Query.

Вы можете щелкнуть правой кнопкой мыши последний шаг запроса, тот, который называется Выбрать столбцы, и выбрать параметр Просмотреть план запроса. Цель плана запроса — предоставить подробное представление о том, как выполняется запрос. Чтобы узнать больше об этой функции, перейдите к плану запроса.

Каждое поле на предыдущем изображении называется узлом. Узел представляет разбивку операции для выполнения этого запроса. Узлы, представляющие источники данных, такие как SQL Server в предыдущем примере и Value.NativeQuery узел, представляют, какая часть запроса выгружается в источник данных. Остальные узлы, в данном случае Table.LastN и Table.SelectColumns выделенные в прямоугольнике на предыдущем изображении, оцениваются подсистемой Power Query. Эти два узла представляют два добавленных преобразования: "Сохранить нижние строки " и "Выбрать столбцы". Остальные узлы представляют операции, которые происходят на уровне источника данных.

Чтобы просмотреть точный запрос, отправляемый в источник данных, выберите "Просмотреть сведения " на Value.NativeQuery узле.

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

Консультации с этим запросом источника данных помогут лучше понять историю, которую план запроса пытается передать:

• Sql.Database: этот узел представляет доступ к источнику данных. Подключается к базе данных и отправляет запросы метаданных, чтобы понять свои возможности.
• Value.NativeQuery: представляет запрос, созданный Power Query для выполнения запроса. Power Query отправляет запросы данных в виде нативного SQL запроса к источнику данных. В этом случае представляет все записи и поля (столбцы) из fact_Sale таблицы. Для этого сценария этот случай является нежелательным, так как таблица содержит миллионы строк, а интерес — только в последние 10.
• Table.LastN: когда Power Query получает все записи из fact_Sale таблицы, он использует подсистему Power Query для фильтрации таблицы и сохранения только последних 10 строк.
• Table.SelectColumns: Power Query использует выходные данные Table.LastN узла и применяет новое преобразование Table.SelectColumns, которое выбирает определенные столбцы, которые требуется сохранить из таблицы.

Для ее оценки этот запрос должен был скачать все строки и поля из fact_Sale таблицы. Этот запрос занимает в среднем 6 минут и 1 секунду для обработки в стандартном экземпляре потоков данных Power BI (который учитывает оценку и загрузку данных в потоки данных).

Пример свертывания частичных запросов

После подключения к базе данных и перехода к fact_Sale таблице сначала выберите столбцы, которые нужно сохранить из таблицы. Выберите преобразование "Выбрать столбцы ", найденное внутри группы " Управление столбцами " на вкладке "Главная ". Это преобразование помогает явно выбрать столбцы, которые нужно сохранить из таблицы и удалить остальные.

В диалоговом окне "Выбор столбцов" выберите Sale Key, Customer Key, Invoice Date Key, Description, Quantity, а затем нажмите "ОК".

Теперь вы создаете логику, которая сортирует таблицу, чтобы иметь последние продажи в нижней части таблицы. Sale Key Выберите столбец, являющийся первичным ключом и добавочной последовательностью или индексом таблицы. Сортируйте таблицу, используя только это поле в порядке возрастания из контекстного меню для столбца.

Затем выберите контекстное меню таблицы и выберите преобразование "Сохранить нижние строки ".

В поле "Сохранить нижние строки" введите значение 10 и нажмите кнопку "ОК".

Следующий пример кода — это полный скрипт M для созданного запроса:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

Пример сворачивания частичных запросов. Общие сведения об оценке запроса

Проверив область примененных шагов, вы заметите, что индикаторы свертывания запросов показывают, что последнее преобразование, которое вы добавили, Kept bottom rowsпомечается как шаг, который оценивается за пределами источника данных или, другими словами, подсистемой Power Query.

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

Каждое поле на предыдущем изображении называется узлом. Узел представляет каждый процесс, который должен произойти (слева направо), чтобы вычислить запрос. Некоторые из этих узлов можно оценить в источнике данных, а другие, такие как узел, Table.LastNпредставленный на шаге "Сохранить нижние строки ", оцениваются с помощью подсистемы Power Query.

Чтобы просмотреть точный запрос, отправляемый в источник данных, выберите "Просмотреть сведения " на Value.NativeQuery узле.

Этот запрос находится на собственном языке источника данных. В этом случае используемый язык — это SQL, и эта инструкция представляет запрос на все строки из таблицы fact_Sale, содержащие только запрашиваемые поля, упорядоченные по полю Sale Key.

Рекомендации по этому запросу источника данных помогут вам лучше понять историю, которую пытается передать полный план запросов. Порядок узлов — это последовательный процесс, который начинается с запроса данных из источника данных:

• Sql.Database: подключается к базе данных и отправляет запросы метаданных, чтобы понять свои возможности.
• Value.NativeQuery: представляет запрос, созданный Power Query для выполнения запроса. Power Query отправляет запросы данных в виде нативного SQL запроса к источнику данных. В этом случае это представляет все записи, из таблицы fact_Sale базы данных, содержащей только запрашиваемые поля, отсортированные по возрастанию по полю Sales Key.
• Table.LastN: когда Power Query получает все записи из fact_Sale таблицы, он использует подсистему Power Query для фильтрации таблицы и сохранения только последних 10 строк.

Для ее оценки этот запрос должен был скачать все строки и только необходимые поля из fact_Sale таблицы. Для обработки в стандартном экземпляре потоков данных Power BI требуется в среднем 3 и 4 секунды (которые учитывают оценку и загрузку данных в потоки данных).

Пример свертывания полного запроса

После подключения к базе данных и перехода к fact_Sale таблице начните с выбора столбцов, которые нужно сохранить из таблицы. Выберите преобразование "Выбрать столбцы ", найденное внутри группы " Управление столбцами " на вкладке "Главная ". Это преобразование помогает явно выбрать столбцы, которые нужно сохранить из таблицы и удалить остальные.

В Выбор столбцов выберите Sale Key, Customer Key, Invoice Date Key, Description и Quantity, а затем нажмите ОК.

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

Затем выберите контекстное меню таблицы и выберите преобразование "Сохранить верхние строки ".

В поле "Сохранить верхние строки" введите значение 10 и нажмите кнопку "ОК".

Следующий пример кода — это полный скрипт M для созданного запроса:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

Пример полного свертывания запроса: Понятие оценки запроса

При проверке панели применённых шагов обратите внимание, что индикаторы сворачивания запросов показывают, что добавленные преобразования: выбор столбцов, сортировка строк и оставление верхних строк отмечены как шаги, которые оцениваются у источника данных.

Вы можете щелкнуть правой кнопкой мыши последний шаг запроса, названный Kept top rows, и выбрать параметр план запроса.

Снимок экрана плана запроса с SQL инструкцией, найденной внутри `Value.NativeQuery`, которая представляет запрос первых 10 записей таблицы "fact_Sale", отсортированных по полю "Ключ продажи" и содержащих только поля: "Ключ продажи", "Ключ клиента", "Ключ даты счета", "Описание" и "Количество".

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

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

• Sql.Database: подключается к базе данных и отправляет запросы метаданных, чтобы понять свои возможности.
• Value.NativeQuery: представляет запрос, созданный Power Query для выполнения запроса. Power Query отправляет запросы данных в виде нативного SQL запроса к источнику данных. В этом случае запрос является только для первых 10 записей таблицы fact_Sale с только необходимыми полями, отсортированных по убыванию по полю Sale Key.

Замечание

Несмотря на отсутствие предложения, которое можно использовать для ВЫБОРА нижних строк таблицы на языке T-SQL, существует предложение TOP, которое извлекает верхние строки таблицы.

Для его оценки этот запрос скачивает только 10 строк с только полями, запрошенными из fact_Sale таблицы. Этот запрос занимает в среднем 31 секунд, чтобы обрабатываться в стандартном экземпляре потоков данных Power BI (который учитывает оценку и загрузку данных в потоки данных).

Сравнение производительности

Чтобы лучше понять влияние свертывания запросов, можно обновить запросы, зафиксировать время, необходимое для полного обновления каждого запроса, а затем сравнить их. Для простоты в этой статье приведены средние сроки обновления, захваченные с помощью механизма обновления потоков данных Power BI при подключении к выделенной среде Azure Synapse Analytics с DW2000c в качестве уровня обслуживания.

Время обновления для каждого запроса было следующим образом:

Example	Этикетка	Время в секундах
Нет свертывания запросов	None	361
Частичное свертывание запросов	Частично	184
Полное свертование запроса	Полный	31

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

• Данные в транзитном режиме
• Преобразования, выполняемые подсистемой Power Query

В следующих разделах объясняется, какое влияние эти два процесса оказывают на ранее упомянутые запросы.

Данные в транзитном режиме

При выполнении запроса он пытается получить данные из источника данных в качестве одного из первых шагов. Какие данные извлекаются из источника данных, определяется механизмом сворачивания запросов. Этот механизм определяет шаги из запроса, которые можно перенести в источник данных.

В следующей таблице перечислены количество строк, запрошенных из fact_Sale таблицы базы данных. В таблице также содержится краткое описание инструкции SQL, отправленной для запроса таких данных из источника данных.

Example	Этикетка	Запрошенные строки	Description
Нет свертывания запросов	None	3644356	Запрос всех полей и всех записей из fact_Sale таблицы
Частичное свертывание запросов	Частично	3644356	Запрос всех записей из таблицы, но только обязательных полей из fact_Sale после ее сортировки по полю Sale Key
Полное свертование запроса	Полный	10	Запрос только необходимых полей и TOP 10 записей таблицы fact_Sale после сортировки по убыванию по полю Sale Key

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

Для демонстрации примеров Power Query должен был запросить более 3,6 миллиона строк из источника данных для примеров без свертывания запросов и с частичным свертыванием запросов. Для полного примера свертывания запросов он запрашивал только 10 строк. Для запрошенных полей в примере без свертывания запросов были запрошены все доступные поля из таблицы. Как для частичного, так и для полного свертывания запросов был отправлен запрос именно на те поля, которые им нужны.

Caution

Рекомендуется реализовать решения добавочного обновления, использующие свертывание запросов для запросов или таблиц с большими объемами данных. Различные интеграции продуктов Power Query реализуют время ожидания для завершения длительных запросов. Некоторые источники данных также реализуют таймауты для длительных сеансов, чтобы предотвратить выполнение дорогостоящих запросов на своих серверах. Дополнительные сведения. Использование добавочного обновления с потоками данных и добавочным обновлением для семантических моделей

Преобразования, выполняемые подсистемой Power Query

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

В следующей таблице показаны узлы из планов запросов предыдущих запросов, которые были бы оценены подсистемой Power Query.

Example	Этикетка	Узлы преобразования движка Power Query
Нет свертывания запросов	None	Table.LastN, Table.SelectColumns
Частичное свертывание запросов	Частично	Table.LastN
Полное свертование запроса	Полный	—

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

Преобразования можно сгруппировать в следующие категории:

Тип оператора	Description
Удалённый	Операторы, которые являются узлами источника данных. Оценка этих операторов происходит за пределами Power Query.
Потоковая передача	Операторы являются сквозными операторами. Например, Table.SelectRows с простым фильтром обычно может фильтровать результаты по мере их прохождения через оператор и нет необходимости собирать все строки перед перемещением данных. Table.SelectColumns и Table.ReorderColumns являются другими примерами таких операторов.
Полный просмотр	Операторы, которые должны собирать все строки данных для того чтобы данные могли перейти к следующему оператору в цепочке. Например, для сортировки данных Power Query необходимо собрать все данные. Другие примеры операторов полного сканирования: Table.Groupи Table.NestedJoinTable.Pivot.

Подсказка

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

Рекомендации и предложения

• Следуйте рекомендациям при создании нового запроса, как описано в рекомендациях в Power Query.
• Используйте индикаторы свертывания запросов, чтобы проверить, какие шаги не позволяют выполнить свертку запроса. Переупорядочение их при необходимости для увеличения свертывания.
• Используйте план запроса, чтобы определить, какие преобразования происходят в обработчике Power Query для определенного шага. Попробуйте изменить существующий запрос, изменив порядок шагов. Затем снова проверьте план запроса последнего шага запроса и проверьте, выглядит ли план запроса лучше предыдущего. Например, новый план запроса имеет меньше узлов, чем предыдущий, и большинство из них — узлы "Поток", а не "полная проверка". Для источников данных, поддерживающих свертывание, все узлы в плане запроса, кроме Value.NativeQuery и узлов доступа к источникам данных, представляют преобразования, которые не сворачиваются.
• При наличии можно использовать параметр "Просмотреть собственный запрос" (или "Просмотреть запрос источника данных"), чтобы убедиться, что запрос можно сложить обратно в источник данных. Если этот параметр отключен для шага, и вы используете источник, который обычно включает его, вы создали шаг, который останавливает свертывание запросов. Если вы используете источник, который не поддерживает этот параметр, вы можете полагаться на индикаторы свертывания запросов и план запроса.
• Используйте средства диагностики запросов, чтобы лучше понять запросы, отправляемые в источник данных, когда возможности свертывания запросов доступны для соединителя.
• При объединении данных, полученных с помощью нескольких соединителей, Power Query пытается передать как можно больше обработки обоим источникам данных, соблюдая уровни конфиденциальности, определенные для каждого источника данных.
• Ознакомьтесь со статьей о уровнях конфиденциальности, чтобы защитить ваши запросы от возникновения ошибки, связанной с брандмауэром конфиденциальности данных.
• Используйте другие средства для проверки свертывания запросов с точки зрения запроса, полученного источником данных. В соответствии с примером в этой статье можно использовать Microsoft SQL Server Profiler для проверки запросов, отправленных Power Query и полученных Microsoft SQL Server.
• Если добавить новый шаг в полностью свернутый запрос, и новый шаг тоже сворачивается, Power Query может отправить новый запрос в источник данных вместо использования кэшированной версии предыдущего результата. На практике этот процесс может привести к, казалось бы, простым операциям с небольшим объемом данных, что занимает больше времени для обновления в предварительной версии, чем ожидалось. Это более продолжительное обновление связано с повторным запросом Power Query к источнику данных вместо использования локальной копии данных.