Configurar PolyBase para acceder a datos externos en MongoDB

  • Artículo

Se aplica a:SQL Server

En el artículo se explica cómo usar PolyBase en una instancia de SQL Server para consultar datos externos en MongoDB.

Requisitos previos

Si no ha instalado PolyBase, consulte Instalación de PolyBase.

Para poder crear una credencial con ámbito de base de datos, la base de datos debe tener una clave maestra para proteger la credencial. Para obtener más información, vea CREATE MASTER KEY.

Configuración de un origen de datos externos de MongoDB

Para consultar los datos de un origen de datos de MongoDB, se deben crear tablas externas que hagan referencia a los datos externos. En esta sección se proporciona código de ejemplo para crear estas tablas externas.

En esta sección se utilizan los siguientes comandos de Transact-SQL:

  1. Cree una credencial de ámbito de base de datos para acceder al origen MongoDB.

    Con el script siguiente se crea una credencial con ámbito de base de datos. Antes de ejecutar el script, actualícelo para su entorno:

    • Reemplace <credential_name> por un nombre para la credencial.
    • Reemplace <username> por el nombre de usuario del origen externo.
    • Reemplace <password> por la contraseña adecuada.
    CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';

    Importante

    El conector ODBC de MongoDB para PolyBase solo admite la autenticación básica, no la autenticación Kerberos.

  2. Cree un origen de datos externo.

    Con el script siguiente se crea el origen de datos externo. A modo de referencia, consulte CREATE EXTERNAL DATA SOURCE. Antes de ejecutar el script, actualícelo para su entorno:

    • Actualice la ubicación. Establezca los valores de <server> y <port> para su entorno.
    • Reemplace <credential_name> por el nombre de la credencial que creó en el paso anterior.
    • Opcionalmente, puede especificar PUSHDOWN = ON o PUSHDOWN = OFF si quiere especificar el cálculo de aplicación en el origen externo.
    CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = '<mongodb://<server>[:<port>]>'
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] PUSHDOWN = { ON | OFF } ])
[ ; ]

  3. Consulte el esquema externo en MongoDB.

    Puede usar la extensión de virtualización de datos para Azure Data Studio a fin de conectar y generar una instrucción CREATE EXTERNAL TABLE basada en el esquema que detecta el controlador ODBC de PolyBase para MongoDB. También puede personalizar manualmente un script basado en la salida del procedimiento almacenado del sistema sp_data_source_objects (Transact-SQL). La extensión de virtualización de datos para Azure Data Studio y sp_data_source_table_columns usan los mismos procedimientos almacenados internos a fin de consultar el esquema externo.

    Para crear tablas externas en colecciones de MongoDB que contienen matrices, se recomienda usar la extensión de virtualización de datos para Azure Data Studio. El controlador realiza automáticamente las acciones de acoplamiento. El procedimiento almacenado sp_data_source_table_columns también realiza automáticamente el acoplamiento mediante el controlador ODBC de PolyBase para MongoDB.

  4. Cree una tabla externa.

    Si usa la extensión de virtualización de datos para Azure Data Studio, puede omitir este paso, ya que la instrucción CREATE EXTERNAL TABLE se genera automáticamente. Para proporcionar el esquema manualmente, considere el siguiente script de ejemplo para crear una tabla externa. A modo de referencia, consulte CREATE EXTERNAL TABLE.

    Antes de ejecutar el script, actualícelo para su entorno:

    • Actualice los campos con su nombre, intercalación y, si son colecciones, especifique el nombre de la colección y el nombre del campo. En el ejemplo, friends es un tipo de datos personalizado.
    • Actualice la ubicación. Establezca el nombre de la base de datos y el nombre de la tabla. Tenga en cuenta que no se permiten nombres de tres partes, por lo que no se puede crear para la tabla system.profile. Tampoco puede especificar una vista porque no puede obtener sus metadatos.
    • Actualice el origen de datos con el nombre del que creó en el paso 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])

  5. Opcional: cree estadísticas en una tabla externa.

    Se recomienda crear estadísticas en las columnas de tabla externa, sobre todo en las que se usan para las combinaciones, filtros y agregados, para obtener un rendimiento óptimo de las consultas.

    CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;

Importante

Una vez que se haya creado un origen de datos externos, se puede usar el comando CREATE EXTERNAL TABLE para crear una tabla consultable mediante ese origen.

Para obtener un ejemplo, vea Crear una tabla externa para MongoDB.

Opciones de conexión de MongoDB

Para obtener información sobre las opciones de conexión de MongoDB, consulte la documentación de MongoDB sobre el formato de URI de cadena de conexión.

Acoplamiento

El acoplamiento está habilitado para los datos anidados y repetidos de las colecciones de documentos de MongoDB. El usuario debe habilitar create an external table y especificar explícitamente un esquema relacional mediante colecciones de documentos de MongoDB que podrían tener datos anidados o repetidos. Los tipos de datos JSON anidados o repetidos se acoplarán del siguiente modo:

  • Objeto: colección de pares clave-valor sin ordenar delimitada por llaves (anidadas)

    • SQL Server crea una columna de tabla para cada clave de objeto.

      • Nombre de columna: objectname_keyname

  • Matriz: valores ordenados, separados por comas, entre corchetes (repetidos)

    • SQL Server agrega una fila de tabla nueva para cada elemento de matriz.

    • SQL Server crea una columna por matriz para almacenar el índice del elemento de matriz.

      • Nombre de columna: arrayname_index

      • Tipo de datos: bigint

Hay varios problemas potenciales con esta técnica. Dos de ellos son los siguientes:

  • Un campo vacío repetido enmascarará de forma eficaz los datos contenidos en los campos planos del mismo registro.

  • La presencia de varios campos repetidos puede dar lugar a un aumento considerable del número de filas generadas.

Como ejemplo, SQL Server evalúa la colección de restaurantes del conjunto de datos de ejemplo de MongoDB almacenada en formato JSON no relacional. Cada restaurante tiene un campo de dirección anidado y una matriz de calificaciones asignada en días diferentes. En la siguiente ilustración se muestra un restaurante típico con una dirección anidada y calificaciones anidadas que se repiten.

MongoDB flattening

La dirección del objeto se acoplará de la siguiente manera:

  • El campo anidado restaurant.address.building pasa a ser restaurant.address_building.
  • El campo anidado restaurant.address.coord pasa a ser restaurant.address_coord.
  • El campo anidado restaurant.address.street pasa a ser restaurant.address_street.
  • El campo anidado restaurant.address.zipcode pasa a ser restaurant.address_zipcode.

Las calificaciones de la matriz se acoplará de la siguiente manera:

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

Conexión de Cosmos DB

Con la API de Mongo de Cosmos DB y el conector de PolyBase de Mongo DB, puede crear una tabla externa de una instancia de Cosmos DB. Esto se consigue mediante los mismos pasos indicados arriba. Asegúrese de que la credencial de ámbito de base de datos, la dirección del servidor, el puerto y la cadena de ubicación reflejen los del servidor de Cosmos DB.

Ejemplos

En el ejemplo siguiente se crea un origen de datos externo con los parámetros siguientes:

Parámetro Value
Nombre external_data_source_name
Service mongodb0.example.com
Instancia 27017
Conjunto de réplicas myRepl
TLS true
Cálculo de aplicación 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);

Pasos siguientes

Para obtener más tutoriales sobre cómo crear orígenes de datos externos y tablas externas en una variedad de orígenes de datos, consulte Referencia de Transact-SQL de PolyBase.

Para obtener más información sobre PolyBase, consulte la información general de SQL Server PolyBase.