Configurar o PolyBase para acessar dados externos no MongoDB

Artigo
03/05/2025

Aplica-se a:SQL Server

O artigo explica como usar o PolyBase em uma instância do SQL Server para consultar dados externos no MongoDB.

Pré-requisitos

Se ainda não instalou o PolyBase, consulte instalação do PolyBase.

Antes de criar uma credencial com escopo de banco de dados, o banco de dados deve ter uma chave mestra para proteger a credencial. Para obter mais informações, consulte CREATE MASTER KEY.

Configurar uma fonte de dados externa do MongoDB

Para consultar os dados de uma fonte de dados do MongoDB, você deve criar tabelas externas para fazer referência aos dados externos. Esta seção fornece código de exemplo para criar essas tabelas externas.

Os seguintes comandos Transact-SQL são usados nesta seção:

Crie uma credencial com escopo de banco de dados para acessar a fonte do MongoDB.

O script a seguir cria uma credencial com escopo de banco de dados. Antes de executar o script, atualize-o para seu ambiente:
- Substitua <credential_name> por um nome para a credencial.
- Substitua <username> pelo nome de usuário da fonte externa.
- Substitua <password> pela senha apropriada.
```
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
```
Importante

O MongoDB ODBC Connector for PolyBase suporta apenas autenticação básica, não autenticação Kerberos.
Crie uma fonte de dados externa.

O script a seguir cria a fonte de dados externa. Para referência, consulte a fonte de dados externa criada . Antes de executar o script, atualize-o para seu ambiente:
- Atualize o local. Defina os <server> e <port> para o seu ambiente.
- Substitua <credential_name> pelo nome da credencial criada na etapa anterior.
- Opcionalmente, você pode especificar PUSHDOWN = ON ou PUSHDOWN = OFF se quiser especificar a computação pushdown para a fonte externa.
```
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = '<mongodb://<server>[:<port>]>'
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] PUSHDOWN = { ON | OFF } ])
[ ; ]
```
Consulte o esquema externo no MongoDB.

Você pode usar a extensão Data Virtualization para o Azure Data Studio para se conectar e gerar uma instrução CREATE EXTERNAL TABLE com base no esquema detetado pelo driver ODBC do PolyBase para MongoDB. Você também pode personalizar um script manualmente, baseado na saída do procedimento armazenado do sistema sp_data_source_objects (Transact-SQL). A extensão do Data Virtualization para Azure Data Studio e sp_data_source_table_columns usam os mesmos procedimentos armazenados internos para consultar o esquema externo.

Para criar tabelas externas para coleções do MongoDB que contenham matrizes, a recomendação é usar extensão do Data Virtualization para o Azure Data Studio. As ações de nivelamento são executadas automaticamente pelo motorista. O procedimento armazenado sp_data_source_table_columns também executa automaticamente o nivelamento por meio do driver ODBC do PolyBase para o driver MongoDB.
Crie uma tabela externa.

Se você usar a extensão Data Virtualization para o Azure Data Studio, poderá ignorar esta etapa, pois a instrução CREATE EXTERNAL TABLE é gerada para você. Para fornecer o esquema manualmente, considere o script de exemplo a seguir para criar uma tabela externa. Para referência, consulte CREATE EXTERNAL TABLE.

Antes de executar o script, atualize-o para o seu ambiente:
- Atualize os campos com seu nome, agrupamento e, se forem coleções, especifique o nome da coleção e o nome do campo. No exemplo, friends é um tipo de dados personalizado.
- Atualize a localização. Defina o nome do banco de dados e o nome da tabela. Observação Nomes de três partes não são permitidos, portanto, não é possível criá-los para a tabela system.profile. Além disso, não pode especificar uma vista porque ela não pode obter os metadados dela.
- Atualize a fonte de dados com o nome da que você criou na etapa anterior.
```
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])
```
Opcional: Criar estatísticas em uma tabela externa.

Recomendamos a criação de estatísticas em colunas de tabelas externas, especialmente aquelas usadas para junções, filtros e agregações, para um desempenho ideal da consulta.
```
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 
```

Importante

Depois de criar uma fonte de dados externa, você pode usar o comando CREATE EXTERNAL TABLE para criar uma tabela consultável sobre essa fonte.

Para obter um exemplo, consulte Criar uma tabela externa para o MongoDB.

Opções de conexão do MongoDB

Para obter informações sobre as opções de conexão do MongoDB, consulte documentação do MongoDB: Connection String URI Format.

Achatamento

O nivelamento está habilitado para dados aninhados e repetidos de coleções de documentos do MongoDB. O usuário é obrigado a habilitar create an external table e especificar explicitamente um esquema relacional sobre coleções de documentos do MongoDB que podem ter dados aninhados e/ou repetidos. Os tipos de dados aninhados/repetidos JSON serão nivelados da seguinte forma:

Objeto: uma coleção não ordenada de chave/valor entre colchetes (aninhada)
- O SQL Server cria uma coluna de tabela para cada chave de objeto
  - Nome da coluna: objectname_keyname
Matriz: valores ordenados, separados por vírgulas, entre colchetes (repetidos)
- O SQL Server adiciona uma nova linha de tabela para cada item de matriz
- O SQL Server cria uma coluna por matriz para armazenar o índice de itens da matriz
  - Nome da coluna: arrayname_index
  - Tipo de dados: bigint

Existem vários problemas potenciais com esta técnica, sendo dois deles:

Um campo repetido vazio mascarará efetivamente os dados contidos nos campos planos do mesmo registro
A presença de vários campos repetidos pode resultar em uma explosão do número de linhas produzidas

Como exemplo, o SQL Server avalia a coleção de restaurantes do conjunto de dados de exemplo do MongoDB armazenada no formato JSON não relacional. Cada restaurante tem um campo de endereço aninhado e uma lista de classificações que lhe foram atribuídas em dias diferentes. A figura abaixo ilustra um restaurante típico com endereços aninhados e classificações repetidas aninhadas.

normalização de MongoDB

O endereço do objeto será nivelado conforme abaixo:

O campo aninhado restaurant.address.building torna-se restaurant.address_building
O restaurant.address.coord de campo aninhado torna-se restaurant.address_coord
O campo aninhado restaurant.address.street transforma-se em restaurant.address_street
O campo aninhado restaurant.address.zipcode torna-se restaurant.address_zipcode

As classes de matriz serão niveladas como abaixo:

grades_date	notas_classificação	pontuação dos jogos
1393804800000	Um	2
1378857600000	Um	6
135898560000	Um	10
1322006400000	A	9
1299715200000	B	14

Conexão com o Cosmos DB

Usando a API Mongo do Cosmos DB e o conector PolyBase do Mongo DB, você pode criar uma tabela externa de uma instância do Cosmos DB. Isso foi feito seguindo as mesmas etapas listadas acima. Verifique se a credencial com escopo do banco de dados, o endereço do servidor, a porta e a cadeia de localização refletem os do servidor Cosmos DB.

Exemplos

O exemplo a seguir cria uma fonte de dados externa com os seguintes parâmetros:

Parâmetro	Valor
Nome	`external_data_source_name`
Serviço	`mongodb0.example.com`
Instância	`27017`
Conjunto de réplicas	`myRepl`
TLS	`true`
Computação de 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);

Próximos passos

Para obter mais tutoriais sobre como criar fontes de dados externas e tabelas externas para uma variedade de fontes de dados, consulte PolyBase Transact-SQL referência.

Para saber mais sobre o PolyBase, consulte Visão geral do SQL Server PolyBase.

Partilhar via