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

Поддержка запросов Native в пользовательских соединителях 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 вашего соединителя

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.

Снимок экрана: соединитель, найденный в интерфейсе получения данных Power BI Desktop.

В диалоговом окне соединителя введите параметры для сервера и имени базы данных. Затем нажмите кнопку "ОК".

Снимок экрана: диалоговое окно соединителя с сервером и базой данных в качестве параметров.

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

Снимок экрана: параметр преобразования данных из контекстного меню в окне навигатора.

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

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

Источник — это имя предыдущего шага, которое, в данном случае, является просто опубликованной функцией соединителя с переданными параметрами. Список и запись внутри нее просто помогают перемещать таблицу в определенную строку. Строка определяется критериями из записи, где поле Name должно быть равно AdventureWorks2019 и поле Kind должно быть равно База данных. После того как строка находится, [Data] дает возможность Power Query вне списка {} получить доступ к значению в поле Данных, которое в данном случае является таблицей. Вы можете вернуться к предыдущему шагу (источнику), чтобы лучше понять эту навигацию.

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

Тестирование нативного запроса

Создайте настраиваемый шаг после шага навигации, выбрав значок fx в строке формул после идентификации цели.

Снимок экрана: кнопка fx внутри формулы, которая используется для создания пользовательского шага.

Замените формулу в строке формул следующей формулой и нажмите клавишу Enter.

= 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, KindDataи другие в зависимости от реализации соединителя.

В этом случае существовал только один шаг навигации, состоящий из двух полей:

  • Имя. Это поле — имя базы данных, переданной конечным пользователем. В этом случае это AdventureWorks2019, но это поле всегда должно передаваться как есть из того, что пользователь ввёл во время процесса получения данных.
  • Тип: это поле содержит сведения, которые не отображаются для конечного пользователя и относятся к реализации соединителя или драйвера. В этом случае это значение определяет, к какому типу объекта должен быть доступ. Для этой реализации это поле будет фиксированным значением, состоящим из строки Database.

Такие сведения будут переведены в следующий код. Этот код следует добавлять как новое поле в запись SqlODBC.Publish.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Это важно

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

Для значений, которые будут переданы из введенных пользователем значений, можно использовать пару FieldDisplayName и IndexName. Для значений, которые являются фиксированными или предопределенными и не могут передаваться конечным пользователем, можно использовать пару ConstantValue и IndexName. В этом смысле запись NavigationSteps состоит из двух полей:

  • Индексы: определяет поля и значения, используемые для перехода к записи, содержащей целевой объект функции Value.NativeQuery .
  • FieldAccess: определяет, какое поле содержит целевой объект, который обычно является таблицей.

DefaultOptions

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

  • Last updated on