Поддержка собственных запросов в пользовательских соединителях Power Query
Примечание
В этой статье рассматриваются расширенные темы о реализации поддержки собственных запросов для пользовательских соединителей, а также свертывания запросов поверх них. В этой статье предполагается, что у вас уже есть рабочие знания об этих понятиях.
Дополнительные сведения о пользовательских соединителях Power Query см. в статье "Обзор пакета SDK Для Power Query".
В Power Query вы можете выполнять пользовательские собственные запросы к источнику данных, чтобы получить нужные данные. Вы также можете включить возможность сохранения свертывания запросов во время этого процесса и последующих процессов преобразования, выполненных внутри Power Query.
Цель этой статьи — продемонстрировать, как реализовать такую возможность для пользовательского соединителя.
В этой статье используется в качестве отправной точки пример , использующий драйвер ODBC SQL для своего источника данных. Реализация возможностей собственного запроса в настоящее время поддерживается только для соединителей ODBC, которые соответствуют стандарту SQL-92.
В примере соединителя используется драйвер SQL Server Native Client 11.0 . Убедитесь, что этот драйвер установлен вместе с этим руководством.
Вы также можете просмотреть завершенную версию примера соединителя из папки Finish в репозитории GitHub.
SqlCapabilities
В записи примера соединителя можно найти поле записи с именем Sql92Translation
и значением PassThrough для него. Это новое поле необходимо для прохождения собственного запроса с помощью Power Query без каких-либо проверок.
SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
// Place custom overrides here
// The values below are required for the SQL Native Client ODBC driver, but might
// not be required for your data source.
SupportsTop = false,
SupportsDerivedTable = true,
Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
FractionalSecondsScale = 3,
Sql92Translation = "PassThrough"
]),
Убедитесь, что это поле отображается в соединителе перед переходом вперед. Если нет, вы столкнетесь с предупреждениями и ошибками позже, когда дело доходит до использования возможности, которая не поддерживается, так как она не объявлена соединителем.
Создайте файл соединителя (как MEZ или PQX) и загрузите его в Power BI Desktop для ручного тестирования и определите целевой объект для собственного запроса.
Примечание
В этой статье мы будем использовать пример базы данных AdventureWorks2019. Но вы можете следовать вместе с любой базой данных SQL Server по вашему выбору и внести необходимые изменения, когда речь идет о специфике выбранной базы данных.
Способ реализации поддержки собственных запросов в этой статье заключается в том, что пользователю будет предложено ввести три значения:
- Имя сервера
- Имя базы данных
- Собственный запрос на уровне базы данных
Теперь в Power BI Desktop перейдите к интерфейсу получения данных и найдите соединитель с именем SqlODBC Sample.
В диалоговом окне соединителя введите параметры для сервера и имени базы данных. Затем выберите OK.
Откроется новое окно навигатора. В навигаторе можно просмотреть собственное поведение навигации из драйвера SQL, отображающего иерархическое представление сервера и баз данных в нем. Щелкните правой кнопкой мыши базу данных AdventureWorks2019 , а затем выберите " Преобразовать данные".
Этот выбор приводит к редактору Power Query и предварительной версии того, что эффективно предназначено для собственного запроса, так как все собственные запросы должны выполняться на уровне базы данных. Проверьте строку формул последнего шага, чтобы лучше понять, как соединитель должен перейти к целевому объекту собственных запросов перед их выполнением. В этом случае строка формул отображает следующие сведения:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
Источник — это имя предыдущего шага, которое, в данном случае, является просто опубликованной функцией соединителя с переданными параметрами. Список и запись внутри нее просто помогают перемещать таблицу в определенную строку. Строка определяется критериями из записи, в которой имя поля должно быть равно AdventureWorks2019, а поле Kind должно быть равно Базе данных. После того как строка находится, [Data]
вне списка {}
power Query может получить доступ к значению в поле данных , которое в данном случае является таблицей. Вы можете вернуться к предыдущему шагу (источнику), чтобы лучше понять эту навигацию.
После этого создайте настраиваемый шаг после шага навигации, выбрав значок fx в строке формул.
Замените формулу внутри строки формул на следующую формулу и нажмите клавишу ВВОД.
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
После применения этого изменения в строке формул появится предупреждение, запрашивающее разрешение на выполнение собственного запроса к источнику данных.
Выберите "Изменить разрешение". Откроется новое диалоговое окно " Запрос собственной базы данных", которое пытается предупредить вас о возможностях выполнения собственных запросов. В этом случае мы знаем, что эта инструкция SQL безопасна, поэтому нажмите кнопку "Выполнить ", чтобы выполнить команду.
После выполнения запроса в редакторе Power Query появится предварительная версия запроса. Эта предварительная версия проверяет, что соединитель может выполнять собственные запросы.
Используя сведения, собранные из предыдущих разделов, теперь цель заключается в переводе таких сведений в код для соединителя.
Способ выполнения этого перевода заключается в добавлении нового поля записи NativeQueryProperties в запись публикации соединителя, которая в данном случае является записьюSqlODBC.Publish
. Запись NativeQueryProperties
играет важную роль в определении взаимодействия соединителя с функцией Value.NativeQuery
.
Новое поле записи состоит из двух полей:
- NavigationSteps: это поле определяет, как навигация должна выполняться или обрабатываться соединителем. Он содержит список записей, которые описывают шаги для перехода к определенным данным, которые требуется запрашивать с помощью
Value.NativeQuery
функции. В каждой записи определяется, какие параметры необходимы или необходимы, чтобы такая навигация достигла требуемого целевого объекта. - DefaultOptions: это поле помогает определить, как должны быть включены или добавлены
Value.NativeQuery
в запись параметров определенные необязательные параметры. Он предоставляет набор параметров по умолчанию, которые можно использовать при запросе источника данных.
Шаги навигации можно разделить на две группы. Первый содержит те значения, которые вводятся конечным пользователем, например имя сервера или базы данных, в данном случае. Второй содержит эти значения, производные от конкретной реализации соединителя, например имя полей, которые не отображаются пользователю во время получения данных. Эти поля могут включать Name
, Kind
Data
и другие в зависимости от реализации соединителя.
В этом случае существовал только один шаг навигации, состоящий из двух полей:
- Имя. Это поле — имя базы данных, переданной конечным пользователем. В этом случае это было
AdventureWorks2019
, но это поле всегда должно передаваться как из того, что пользователь ввел во время получения данных. - Тип: это поле содержит сведения, которые не отображаются для конечного пользователя и относятся к реализации соединителя или драйвера. В этом случае это значение определяет тип объекта. Для этой реализации это поле будет фиксированным значением, состоящим из строки
Database
.
Такие сведения будут переведены в следующий код. Этот код должен быть добавлен в качестве нового поля в SqlODBC.Publish
запись.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
Важно!
Имя полей учитывает регистр и должно использоваться, как показано в примере выше. Все данные, передаваемые полям, ConstantValue
IndexName
либо FieldDisplayName
должны быть производными от кода M соединителя.
Для значений, которые будут переданы из введенных пользователем значений, можно использовать пару FieldDisplayName
и IndexName
. Для значений, которые являются фиксированными или предопределенными и не могут передаваться конечным пользователем, можно использовать пару ConstantValue
и IndexName
. В этом смысле запись NavigationSteps состоит из двух полей:
- Индексы: определяет поля и значения, используемые для перехода к записи, содержащей целевой объект функции
Value.NativeQuery
. - FieldAccess: определяет, какое поле содержит целевой объект, который обычно является таблицей.
Поле DefaultOptions
позволяет передавать необязательные параметры Value.NativeQuery
функции при использовании собственной возможности запроса для соединителя.
Чтобы сохранить свертывание запросов после собственного запроса и предположить, что у соединителя есть возможности свертывания запросов, можно использовать следующий пример кода для EnableFolding = true
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
С помощью этих изменений создайте соединитель и загрузите его в Power BI Desktop для тестирования и проверки.
В Power BI Desktop с новым пользовательским соединителем запустите соединитель из интерфейса получения данных . При запуске соединителя вы заметите, что диалоговое окно теперь имеет длинное текстовое поле с именем Native query и в скобках имеет необходимые поля для работы. Введите те же значения для сервера, базы данных и инструкции SQL, которые вы ранее ввели при тестировании соединителя.
После нажатия кнопки "ОК" в новом диалоговом окне отображается предварительная версия таблицы выполненного собственного запроса.
Нажмите ОК. Теперь новый запрос загружается в редактор Power Query, где можно выполнить дальнейшее тестирование соединителя по мере необходимости.
Примечание
Если соединитель имеет возможности свертывания запросов и явно определен EnableFolding=true
как часть необязательной записиValue.NativeQuery
, можно дополнительно протестировать соединитель в редакторе Power Query, проверка, если дальнейшие преобразования сворачиваться обратно в источник или нет.