Configurar o PolyBase para acessar dados externos no MongoDB

Artigo
05/30/2023

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 você ainda não instalou o PolyBase, veja Instalação do PolyBase.

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

Configurar uma fonte de dados externa do MongoDB

Para consultar os dados de uma fonte de dados do MongoDB, você precisa 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 no escopo do banco de dados para acessar a origem do MongoDB.

O script a seguir cria uma credencial no escopo do banco de dados. Antes de executar o script, atualize-o para seu ambiente:
- Substitua <credential_name> por um nome da 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 Conector ODBC do MongoDB para PolyBase dá suporte 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, confira CRIAR FONTE DE DADOS EXTERNA. Antes de executar o script, atualize-o para seu ambiente:
- Atualize o local. Defina o <server> e a <port> para o seu ambiente.
- Substitua <credential_name> pelo nome da credencial criada na etapa anterior.
- Como opção, informe PUSHDOWN = ON ou PUSHDOWN = OFF se deseja especificar o cálculo de aplicação 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 de Virtualização de Dados para o Azure Data Studio para se conectar e gerar uma instrução CREATE EXTERNAL TABLE com base no esquema detectado pelo Driver ODBC do PolyBase para o driver MongoDB. Você também pode personalizar manualmente um script com base na saída do procedimento armazenado do sistema sp_data_source_objects (Transact-SQL). A extensão de Virtualização de Dados para o Azure Data Studio e o 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 contêm matrizes, a recomendação é usar a extensão de Virtualização de Dados para o Azure Data Studio. As ações de nivelamento são executadas automaticamente pelo driver. 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.

Caso use a extensão de Virtualização de Dados para o Azure Data Studio, você poderá ignorar esta etapa, pois a instrução CREATE EXTERNAL TABLE será gerada para você. Para fornecer o esquema manualmente, considere o script de exemplo a seguir para criar uma tabela externa. Para referência, confira CREATE EXTERNAL TABLE.

Antes de executar o script, atualize-o para seu ambiente:
- Atualize os campos com o nome e a ordenação, indicando também se eles são coleções, e especifique o nome da coleção e o nome do campo. No exemplo, friends é um tipo de dados personalizado.
- Atualize o local. Defina o nome do banco de dados e o nome da tabela. Observe que nomes de três partes não são permitidos. Portanto, não é possível criá-lo para a tabela system.profile. Além disso, não é possível especificar uma exibição porque ela não pode obter os metadados dela.
- Atualize a fonte de dados com o nome daquela 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.

É recomendável criar estatísticas em colunas de tabelas externas, especialmente aquelas usadas para junções, filtros e agregações, a fim de ter o desempenho de consulta ideal.
```
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 que possa ser consultada por essa fonte.

Por exemplo, confira Criar uma tabela externa para MongoDB.

Opções de conexão do MongoDB

Para obter informações sobre as opções de conexão do MongoDB, confira a documentação do MongoDB: formato de URI da cadeia de conexão.

Nivelamento

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

Objetos: coleção de chave-valor não ordenada entre chaves (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 da matriz
- O SQL Server cria uma coluna por matriz para armazenar o índice do item da matriz
  - Nome da coluna: arrayname_index
  - Tipo de dados: bigint

Há vários possíveis problemas com essa técnica, dois deles sendo:

Um campo repetido vazio mascarará efetivamente os dados contidos nos campos nivelados no 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 do restaurante do conjunto de dados de amostra do MongoDB armazenada no formato JSON não relacional. Cada restaurante tem um campo de endereço aninhado e uma matriz de classificações atribuídas a ele em dias diferentes. A figura a seguir ilustra um restaurante típico com o endereço aninhado e notas aninhadas repetidas.

MongoDB flattening

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

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

As classificações da matriz serão niveladas conforme abaixo:

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

Conexão do Cosmos DB

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

Exemplos

O seguinte abaixo 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`
Cálculo de aplicação	`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óximas etapas

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

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