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

Статья
01/02/2025

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

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

дата_оценок	оценки_оценка	счет игры
1393804800000	А	2
1378857600000	A	6
135898560000	A	10
1322006400000	A	9
1299715200000	Б	14

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

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

Примеры

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

Параметр	Значение
Имя.	`external_data_source_name`
Услуга	`mongodb0.example.com`
Экземпляр	`27017`
Набор реплик	`myRepl`
TLS	`true`
Вычисления с использованием стека	`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.

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