Configurare PolyBase per l'accesso a dati esterni in MongoDB

  • Articolo

Si applica a:SQL Server

L'articolo illustra come usare PolyBase in un'istanza di SQL Server per eseguire query sui dati esterni in MongoDB.

Prerequisiti

Se PolyBase non è stato installato, vedere Installazione di PolyBase.

Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY.

Configurare un'origine dati MongoDB esterna

Per eseguire query sui dati da un'origine dati MongoDB, è necessario creare tabelle esterne per fare riferimento ai dati esterni. In questa sezione è disponibile codice di esempio per creare queste tabelle esterne.

In questa sezione vengono usati i comandi Transact-SQL seguenti:

  1. Creare una credenziale con ambito database per l'accesso all'origine di MongoDB.

    Nell'esempio seguente viene creata la credenziale con ambito database. Prima di eseguire lo script, aggiornarlo per il proprio ambiente.

    • Sostituire <credential_name> con un nome per la credenziale.
    • Sostituire <username> con il nome utente per l'origine esterna.
    • Sostituire <password> con la password appropriata.
    CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';

    Importante

    Il connettore ODBC MongoDB per PolyBase supporta solo l'autenticazione di base e non l'autenticazione Kerberos.

  2. Creare un'origine dati esterna.

    Il seguente script crea l'origine dati esterna. Per informazioni di riferimento, vedere Creare un'origine dati esterna Prima di eseguire lo script, aggiornarlo per il proprio ambiente.

    • Aggiornare la posizione Impostare <server> e <port> per l'ambiente.
    • Sostituire <credential_name> con il nome della credenziale creata nel passaggio precedente.
    • Facoltativamente, è possibile specificare PUSHDOWN = ON o PUSHDOWN = OFF se si vuole specificare il calcolo pushdown nell'origine esterna.
    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. Eseguire una query sullo schema esterno in MongoDB.

    È possibile usare l'estensione di virtualizzazione dati per Azure Data Studio per connettersi e generare un'istruzione CREATE EXTERNAL TABLE basata sullo schema rilevato dal driver ODBC PolyBase per il driver MongoDB. È anche possibile personalizzare manualmente uno script in base all'output della stored procedure di sistema sp_data_source_objects (Transact-SQL). L'estensione di virtualizzazione dei dati per Azure Data Studio e sp_data_source_table_columns usano le stesse stored procedure interne per eseguire query sullo schema esterno.

    Per creare tabelle esterne in raccolte MongoDB che contengono matrici, è consigliabile usare l'estensione di virtualizzazione dati per Azure Data Studio. Le azioni di appiattimento vengono eseguite automaticamente dal driver. La stored procedure sp_data_source_table_columns esegue inoltre l'appiattimento automatico tramite il driver ODBC di PolyBase per il driver MongoDB.

  4. Creare una tabella esterna,

    Se si usa l'estensione di virtualizzazione dati per Azure Data Studio, è possibile ignorare questo passaggio, perché l'istruzione CREATE EXTERNAL TABLE viene generata automaticamente. Per fornire manualmente lo schema, prendere in considerazione lo script di esempio seguente per creare una tabella esterna. Per informazioni di riferimento, vedere Creare una tabella esterna

    Prima di eseguire lo script, aggiornarlo per il proprio ambiente:

    • Aggiornare i campi con il nome, il confronto e, se sono raccolte, specificare il nome della raccolta e il nome campo. Nell'esempio, friends è un tipo di dati personalizzato.
    • Aggiornare la posizione Impostare il nome del database e il nome tabella. Si noti che i nomi in tre parti non sono consentiti, quindi non è possibile crearlo per la tabella system.profile. Inoltre, non è possibile specificare una vista perché non è in grado di ottenere da esso i metadati.
    • Aggiornare l'origine dati con il nome di quello creato nel passaggio precedente.
    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. Facoltativo: Creare statistiche per una tabella esterna.

    È consigliabile creare le statistiche sulle colonne delle tabelle esterne, in particolare quelle usate per join, filtri e aggregazioni, per prestazioni ottimali delle query.

    CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;

Importante

Dopo aver creato un'origine dati esterna, è possibile usare il comando CREATE EXTERNAL TABLE per creare una tabella disponibile per query su tale origine.

Per un esempio, vedere Creare una tabella esterna per MongoDB.

Opzioni di connessione mongoDB

Per informazioni sulle opzioni di connessione di MongoDB, vedere il formato URI della stringa di connessione MongoDB standard.

Rendere flat

L'impostazione per l'appiattimento è abilitata per i dati nidificati e ripetuti delle raccolte di documenti MongoDB. L'utente deve abilitare create an external table e specificare in modo esplicito uno schema relazionale per le raccolte di documenti MongoDB che possono avere dati nidificati e/o ripetuti. I tipi di dati JSON nidificati/ripetuti verranno appiattiti come indicato di seguito

  • Oggetto: raccolta chiave/valore non ordinata racchiusa tra parentesi graffe (nidificata)

    • SQL Server crea una colonna della tabella per ogni chiave oggetto

      • Nome colonna: objectname_keyname

  • Matrice: valori ordinati, separati da virgole, racchiusi tra parentesi quadre (ripetute)

    • SQL Server aggiunge una nuova riga della tabella per ogni elemento della matrice

    • SQL Server crea una colonna per ogni matrice per archiviare l'indice dell'elemento matrice

      • Nome colonna: arrayname_index

      • Tipo di dati: bigint

Questa tecnica può originare vari problemi, tra cui i due seguenti:

  • Un campo ripetuto vuoto maschererà i dati contenuti nei campi flat dello stesso record

  • La presenza di più campi ripetuti può comportare una crescita esponenziale del numero di righe prodotte

A titolo di esempio SQL Server valuta la raccolta di ristoranti del dataset di esempio MongoDB archiviata in formato JSON non relazionale. Ogni ristorante dispone di un campo indirizzo nidificato e di una matrice di valutazioni che sono state assegnate in giorni diversi. La figura seguente illustra un ristorante con l'indirizzo nidificato e le valutazioni nidificate ripetute.

MongoDB flattening

L'indirizzo dell'oggetto verrà appiattito (reso flat) come indicato di seguito:

  • Il campo annidato restaurant.address.building diventa restaurant.address_building
  • Il campo annidato restaurant.address.coord diventa restaurant.address_coord
  • Il campo annidato restaurant.address.street diventa restaurant.address_street
  • Il campo annidato restaurant.address.zipcode diventa restaurant.address_zipcode

Le valutazioni della matrice vengono appiattite come indicato di seguito:

grades_date grades_grade games_score
1393804800000 Un 2
1378857600000 Un 6
135898560000 Un 10
1322006400000 Un 9
1299715200000 G 14

Connessione Cosmos DB

Usando l'api di Mongo Cosmos DB e il connettore di Mongo DB PolyBase è possibile creare una tabella esterna di un'istanza di Cosmos DB. Questa operazione viene eseguita seguendo gli stessi passaggi elencati in precedenza. Assicurarsi che la credenziale con ambito database, l'indirizzo server, la porta e la stringa di posizione riflettano quelli del server di Cosmos DB.

Esempi

L'esempio seguente crea un'origine dati esterna con i seguenti parametri:

Parametro valore
Nome external_data_source_name
Servizio mongodb0.example.com
Istanza 27017
Set di repliche myRepl
TLS true
Calcolo con distribuzione 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);

Passaggi successivi

Per altre esercitazioni sulla creazione di origini dati esterne e tabelle esterne in un'ampia gamma di origini dati, vedere le Informazioni di riferimento su Transact-SQL per PolyBase.

Per altre informazioni su PolyBase, vedere Che cos'è PolyBase?.