План запросов для Power Query

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

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

Примечание.

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

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

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, представляющий шаг Сохраняемые нижние строки.

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

Примечание.

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

Идентификация свернутых узлов среди других узлов

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

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

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

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

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

   

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

   

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

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

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

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