План запросов для Power Query (предварительная версия)

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

В практическом примере в этой статье демонстрируется основной вариант использования и потенциальные преимущества использования функции плана запросов для просмотра шагов запроса. Примеры, используемые в этой статье, были созданы с помощью примера базы данных AdventureWorksLT для Azure SQL Server, которую можно скачать из примеров баз данных AdventureWorks.

Примечание.

Функция плана запросов для Power Query доступна только в Power Query Online.

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

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

  1. Просмотрите индикаторы свертывания запросов.
  2. Выберите шаг запроса, чтобы просмотреть план запроса.
  3. Реализуйте изменения в запросе.

Выполните следующие действия, чтобы создать запрос в собственной среде Power Query Online.

  1. В Power Query — выберите источник данных, выберите пустой запрос.

  2. Замените скрипт пустого запроса следующим запросом.

    let
      Source = Sql.Database("servername", "database"),
      Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
      #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
      #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
      #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
    in
      #"Kept bottom rows"
    
  3. Измените servername и database с правильными именами для собственной среды.

  4. (Необязательно) Если вы пытаетесь подключиться к серверу и базе данных для локальной среды, обязательно настройте шлюз для этой среды.

  5. Выберите Далее.

  6. В Редактор Power Query выберите "Настроить подключение" и укажите учетные данные в источнике данных.

Примечание.

Дополнительные сведения о подключении к SQL Server см. в базе данных SQL Server.

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

Пример запроса с включенными индикаторами свертывания запросов. Этот запрос подключается к таблице SalesOrderHeader и выбирает несколько столбцов из последних пяти заказов со значением TotalDue выше 1000.

Примечание.

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

1. Просмотр индикаторов свертывания запросов

Примечание.

Перед чтением этого раздела рекомендуется ознакомиться со статьей о свертываниях запросов.

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

Индикаторы свертывания запросов для примера запроса в области

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

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

2. Выберите шаг запроса для просмотра плана запроса

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

Диалоговое окно плана запроса, демонстрирующее представление схемы для плана запроса с узлами, подключенными по строкам.Power Query пытается оптимизировать запрос, используя преимущества отложенной оценки и свертывания запросов, как упоминание в основах свертывания запросов. Этот план запроса представляет оптимизированный перевод запроса M в собственный запрос, отправляемый в источник данных. Он также включает любые преобразования, выполняемые подсистемой Power Query. Порядок отображения узлов следует порядку запроса, начиная с последнего шага или выходных данных запроса, который представлен в левой части схемы, и в этом случае — узел Table.LastN , представляющий шаг "Хранимые нижние строки ".

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

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

Примечание.

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

Определение свернутых узлов из других узлов

Узлы на этой схеме можно определить как две группы:

  • Свернутые узлы: этот узел может быть либо Value.NativeQuery "источником данных", например Sql.Database. Они также могут быть идентифицированы с помощью удаленной метки под их именем функции.
  • Не свернутые узлы: другие операторы таблицы, такие как Table.SelectRows, Table.SelectColumnsи другие функции, которые не могут быть сложены. Их также можно идентифицировать с помощью меток полной проверки и потоковой передачи.

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

Элементы управления представлением плана запросов в нижней части диалогового окна с выбранным параметром просмотра. Вы можете выбрать представление сведений в нижней части некоторых узлов, чтобы отобразить расширенную информацию. Например, сведения о Value.NativeQuery узле отображают собственный запрос (в SQL), который будет отправлен в источник данных.

Представление сведений для узла Value.NativeQuery в плане запроса. Приведенный здесь запрос может не совпадать с тем же запросом, отправленным в источник данных, но это хорошее приближение. В этом случае он указывает, какие столбцы будут запрашиваться из таблицы SalesOrderHeader, а затем как фильтровать эту таблицу с помощью поля TotalDue, чтобы получить только строки, где значение этого поля больше 1000. Узел рядом с ним, Table.LastN, вычисляется локально подсистемой Power Query, так как она не может быть сложена.

Примечание.

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

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

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

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

Так как источник данных является базой данных SQL Server, то если цель состоит в получении последних пяти заказов из таблицы, то хорошим вариантом будет использование предложений TOP и ORDER BY в SQL. Так как в SQL нет предложения BOTTOM, Table.LastN преобразование в PowerQuery невозможно преобразовать в SQL. Вы можете удалить Table.LastN шаг и заменить его следующим:

  • Шаг сортировки по столбцу SalesOrderID в таблице, так как этот столбец определяет, какой заказ идет первым и который был введен последний.
  • Выберите первые пять строк , так как таблица была отсортирована, это преобразование выполняется так же, как если бы он был хранимой нижней строкой (Table.LastN).

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

3. Реализация изменений в запросе

Реализуйте альтернативу, описанную в предыдущем разделе:

  1. Закройте диалоговое окно плана запроса и вернитесь к Редактор Power Query.

  2. Удалите шаг "Сохранить нижние строки".

  3. Отсортируйте столбец SalesOrderID в порядке убывания.

    Сортировка столбца SalesOrderID в порядке убывания с помощью меню автофильтратора.

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

    С помощью контекстного меню таблицы выберите преобразование

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

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

Новый план запроса после внесения изменений в запрос, который теперь демонстрирует только свернутые узлы, с значением Value.NativeQuery, показывающим полную инструкцию SQL, которая оценивает запрос.

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

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