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

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

  • Статья

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

Сценарий

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

Пример выходной таблицы, полученной из таблицы fact_Sale базы данных Wide World Importers 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.

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

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

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

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

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

Инструкция SQL, найденная внутри Value.NativeQuery, представляющая запрос всех полей и записей из таблицы fact_Sale в базе данных.

Этот запрос источника данных находится на собственном языке источника данных. В этом случае этот язык является 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,Invoice Date KeyCustomer 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, а не источником данных.

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

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

Инструкция SQL внутри Value.NativeQuery, представляющая запрос для всех записей, с запрошенными полями из таблицы fact_Sales в базе данных, отсортированной по возрастанию по полю

Этот запрос находится на собственном языке источника данных. В этом случае этот язык — 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,, Invoice Date KeyCustomer KeyDescriptionи столбцы, 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"

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

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

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

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

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

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

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

Диаграмма, сравнивающая время обновления без свертывания запроса с 361 секундами, частичное свертывание запросов с 184 секундами и полностью свернутый запрос с 31 секундами.

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

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

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

Передаваемые данные

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

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

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

Диаграмма с объемом строк, собранных из базы данных без свертывания запросов, частичного свертывания запросов и свертывания полного запроса.

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

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

Внимание

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

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

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

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

Пример Этикетка Узлы преобразования подсистемы Power Query
Нет свертывания запросов нет Table.LastN, Table.SelectColumns
Частичное свертывание запросов Частично Table.LastN
Свертывание полного запроса Полностью —

Диаграмма с общими преобразованиями, выполняемыми подсистемой Power Query без свертывания запросов, частичного свертывания запросов и свертывания полного запроса.

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