Настройка PolyBase для доступа к внешним данным в MongoDB

Статья
08/21/2023

Область применения: SQL Server

В этой статье описывается использование PolyBase в экземпляре SQL Server для запроса внешних данных в MongoDB.

Необходимые компоненты

Если вы не установили PolyBase, см. раздел Установка PolyBase.

Перед созданием учетных данных для базы данных в базе данных должен находиться главный ключ для защиты учетных данных. Дополнительные сведения см. в статье CREATE MASTER KEY.

Настройка внешнего источника данных MongoDB

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

В рамках этого раздела используются следующие команды Transact-SQL:

Создайте учетные данные в области базы данных для доступа к источнику MongoDB.

Следующий скрипт создает учетные данные в области базы данных. Перед запуском скрипта обновите его для своей среды:
- Замените <credential_name> именем учетных данных.
- Замените <username> именем пользователя для внешнего источника.
- Замените <password> соответствующим паролем.
```
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
```
Внимание

Соединитель ODBC MongoDB для PolyBase поддерживает только обычную проверку подлинности, но не проверку подлинности Kerberos.
Создайте внешний источник данных.

Следующий скрипт создает внешний источник данных. Для справки см. раздел CREATE EXTERNAL DATA SOURCE. Перед запуском скрипта обновите его для своей среды:
- Обновите расположение. Задайте <server> и <port> для своей среды.
- Замените <credential_name> именем учетных данных, созданных на предыдущем шаге.
- При необходимости можно указать PUSHDOWN = ON или PUSHDOWN = OFF, если вы хотите указать вычисление pushdown для внешнего источника.
```
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = '<mongodb://<server>[:<port>]>'
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] PUSHDOWN = { ON | OFF } ])
[ ; ]
```
Запрос внешней схемы в MongoDB.

Вы можете использовать расширение Виртуализации данных для Azure Data Studio для подключения и создания инструкции CREATE EXTERNAL TABLE на основе схемы, обнаруженной драйвером ODBC Driver для MongoDB PolyBase. Вы также можете вручную настроить скрипт на основе выходных данных системной хранимой процедуры sp_data_source_objects (Transact-SQL). Расширение Data Virtualization для Azure Data Studio и sp_data_source_table_columns используйте те же внутренние хранимые процедуры для запроса схемы внешней схемы.

Чтобы создать внешние таблицы для коллекций MongoDB, содержащих массивы, рекомендуется использовать расширение Виртуализации данных для Azure Data Studio. Действия по преобразованию в плоскую структуры выполняются драйвером автоматически. Хранимая sp_data_source_table_columns процедура также автоматически выполняет преобразование в плоскую структуру с помощью драйвера ODBC PolyBase для MongoDB.
Создайте внешнюю таблицу.

Если вы используете расширение Виртуализации данных для Azure Data Studio, этот шаг можно пропустить, так как создается инструкция CREATE EXTERNAL TABLE. Чтобы предоставить схему вручную, рассмотрим следующий пример сценария, чтобы создать внешнюю таблицу. Справочную информацию см. в разделе CREATE EXTERNAL TABLE.

Перед запуском скрипта обновите его для вашей среды:
- Обновите поля, указав их имена и параметры сортировки. Если они являются коллекциями, укажите имя коллекции и имя поля. В этом примере friends является пользовательским типом данных.
- Обновите расположение. Задайте имя базы данных и имя таблицы. Обратите внимание, что имена из трех частей не допускаются, поэтому их нельзя создать для таблицы system.profile. Кроме того, невозможно указать представление, поскольку оно не может получить метаданные из таблицы.
- Обновите источник данных, указав имя, созданное в предыдущем шаге.
```
CREATE EXTERNAL TABLE [MongoDbRandomData](
  [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [RandomData_friends_id] INT,
  [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
WITH (
  LOCATION='MyDb.RandomData',
  DATA_SOURCE=[MongoDb])
```
Необязательно. Создайте статистику для внешней таблицы.

Чтобы обеспечить оптимальную производительность запросов, мы советуем создать статистику столбцов внешней таблицы, особенно тех, которые используются для объединения, применения фильтров и статистических выражений.
```
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 
```

Внимание

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

Пример см. в разделе Создание внешней таблицы для MongoDB.

Параметры подключения MongoDB

Сведения о параметрах подключения MongoDB см. в документации по MongoDB в разделе Connection String URI Format (Формат URI строки подключения).

Уплощение

Преобразование в плоскую структуру доступно для вложенных и повторяющихся данных из коллекций документов MongoDB. Пользователь должен включить функцию create an external table и явным образом указать реляционную схему для коллекций документов MongoDB, которые могут содержать вложенные и повторяющиеся данные. Вложенные или повторяющиеся типы данных JSON будут преобразованы в плоскую структуру следующим образом.

Объект: коллекция неупорядоченных ключей и значений, заключенная в фигурные скобки (вложенные данные)
- SQL Server создаст столбец таблицы для каждого ключа объекта.
  - Имя столбца: objectname_keyname
Массив: упорядоченные значения, разделенные запятыми и заключенные в квадратные скобки (повторяющиеся данные)
- SQL Server добавит новую строку таблицы для каждого элемента массива.
- SQL Server создаст столбец для каждого массива, чтобы хранить индекс элементов массива.
  - Имя столбца: arrayname_index
  - Тип данных: bigint

Использование этого метода может привести к возникновению ряда проблем. Далее указаны две из них:

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

Например, SQL Server оценивает образец коллекции с набором данных по ресторанам MongoDB, сохраненный в нереляционном формате JSON. У каждого ресторана есть вложенное поле адреса и массив оценок, которые он получил в разные дни. На рисунке ниже показан стандартный ресторан с вложенным адресом и вложенными повторяющимися оценками.

Преобразование данных MongoDB в плоскую структуру

Адрес объекта будет преобразован в плоскую структуру, как показано ниже:

Вложенное поле restaurant.address.building меняется на restaurant.address_building.
Вложенное поле restaurant.address.coord меняется на restaurant.address_coord.
Вложенное поле restaurant.address.street меняется на restaurant.address_street.
Вложенное поле restaurant.address.zipcode меняется на restaurant.address_zipcode.

Массив оценок будет преобразован в плоскую структуру, как показано ниже:

grades_date	grades_grade	games_score
1393804800000	A	2
1378857600000	а	6
135898560000	а	10
1322006400000	а	9
1299715200000	Б	14

Подключение Cosmos DB

Используя API-интерфейс Mongo для Cosmos DB и соединитель PolyBase для MongoDB, вы можете создать внешнюю таблицу экземпляра Cosmos DB. Для этого выполните те же действия, что указаны выше. Учетные данные в области базы данных, а также адрес сервера, порт и строка расположения должны соответствовать серверу Cosmos DB.

Примеры

В следующем примере создается внешний источник данных со следующими параметрами:

Параметр	Значение
Имя.	`external_data_source_name`
Service	`mongodb0.example.com`
Экземпляр	`27017`
Набор реплик	`myRepl`
TLS	`true`
Вычисления pushdown	`On`

CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (LOCATION = 'mongodb://mongodb0.example.com:27017',
    CONNECTION_OPTIONS = 'replicaSet=myRepl; tls=true',
    PUSHDOWN = ON ,
    CREDENTIAL = credential_name);

Следующие шаги

Дополнительные руководства по созданию внешних источников данных и внешних таблиц в различных источниках данных см . в справочнике по PolyBase Transact-SQL.

Дополнительные сведения о PolyBase см. в статье Руководство по PolyBase.

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