Eseguire query federate in Microsoft SQL Server

Questo articolo descrive come configurare Lakehouse Federation per eseguire query federate sui dati di SQL Server non gestiti da Azure Databricks. Per altre informazioni sulla federazione di lakehouse, si veda Che cos'è Lakehouse Federation?.

Per connettersi al database di SQL Server tramite Lakehouse Federation, è necessario creare quanto segue nel metastore del catalogo Unity di Azure Databricks:

• Connessione al database di SQL Server.
• Catalogo esterno che rispecchia il database di SQL Server in Unity Catalog in modo da poter usare la sintassi delle query di Unity Catalog e gli strumenti di governance dei dati per gestire l'accesso utente di Azure Databricks al database.

Lakehouse Federation supporta SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.

Operazioni preliminari

Requisiti dell'area di lavoro:

• Area di lavoro abilitata per il catalogo Unity

Requisiti dell’ambiente di calcolo:

• Connettività di rete dal cluster Databricks Runtime o dal warehouse SQL ai sistemi di database di destinazione. Si veda Raccomandazioni di rete per Lakehouse Federation.
• I cluster di Azure Databricks devono usare Databricks Runtime 13.3 LTS o versione successiva e la modalità di accesso condiviso o singolo utente.
• I warehouse SQL devono essere Pro o Serverless e devono usare la versione 2023.40 o successiva.

Autorizzazioni necessarie:

• Per creare una connessione, è necessario essere un amministratore del metastore o un utente con il privilegio CREATE CONNECTIONper il metastore del catalogo Unity collegato all'area di lavoro.
• Per creare un catalogo esterno, è necessario disporre dell'autorizzazione CREATE CATALOG per il metastore e essere il proprietario della connessione o avere il privilegio CREATE FOREIGN CATALOG per la connessione.

In ogni sezione basata su attività che segue vengono specificati requisiti di autorizzazione aggiuntivi.

• Se si prevede di eseguire l'autenticazione con OAuth, registrare un'app nell'ID Entra di Microsoft per Azure Databricks. Per informazioni dettagliate, vedere la sezione seguente.

(Facoltativo) Registrare un'app in Microsoft Entra ID for Azure Databricks

Se si vuole eseguire l'autenticazione con OAuth, seguire questo passaggio prima di creare una connessione a SQL Server. Per eseguire l'autenticazione usando invece un nome utente e una password, ignorare questa sezione.

1. Accedere al portale di Azure.
2. Nel riquadro di spostamento sinistro fare clic su Microsoft Entra ID.
3. Fare clic su Registrazioni per l'app.
4. Fare clic su Nuova registrazione. Immettere un nome per la nuova app e impostare l'URI di reindirizzamento su https://<workspace-url>/login/oauth/azure.html.
5. Fare clic su Registra.
6. Nella casella Informazioni di base copiare e archiviare l'ID applicazione (client). Questo valore verrà usato per configurare l'applicazione.
7. Fare clic su Certificati e segreti.
8. Nella scheda Segreti client selezionare Nuovo segreto client.
9. Immettere una descrizione per il segreto e una scadenza (l'impostazione predefinita è 180 giorni).
10. Fare clic su Aggiungi.
11. Copiare il valore generato per il segreto client.
12. Fare clic su Autorizzazioni API.
13. Fare clic su Aggiungi un'autorizzazione.
14. Selezionare database SQL di Azure e fare clic su user_impersonation in Autorizzazioni delegate.
15. Fare clic su Aggiungi autorizzazioni.

Crea una connessione

Una connessione specifica un percorso e le credenziali per accedere a un sistema di database esterno. Per creare una connessione, è possibile usare Esplora cataloghi o il comando SQL CREATE CONNECTION in un notebook di Azure Databricks o nell'editor di query SQL di Databricks.

Autorizzazioni necessarie: amministratore o utente metastore con il privilegio CREATE CONNECTION.

Esplora cataloghi

1. Nell'area di lavoro di Azure Databricks fare clic su Catalogo.

2. Nella parte superiore del riquadro Catalogo fare clic sull'icona Aggiungi e selezionare Aggiungi una connessione dal menu.

   In alternativa, nella pagina Accesso rapido fare clic sul pulsante Dati esterni, passare alla scheda Connessioni e fare clic su Crea connessione.

3. Immettere un nome di connessione semplice.

4. Selezionare un tipo di connessione di SQL Server.

5. Selezionare un tipo di autenticazione OAuth o Nome utente e password.

6. Immettere le proprietà di connessione seguenti per l'istanza di SQL Server, a seconda del metodo di autenticazione:

   • Host: SQL Server.
   • (Autenticazione di base) Porto
   • (Autenticazione di base) trustServerCertificate: l'impostazione predefinita è false. Se impostato su true, il livello di trasporto usa SSL per crittografare il canale e ignora la catena di certificati per convalidare l'attendibilità. Lasciare questo valore impostato sul valore predefinito, a meno che non sia necessario ignorare la convalida dell'attendibilità.
   • (Autenticazione di base) Utente
   • (Autenticazione di base) Parola d’ordine
   • (OAuth) Endpoint di autorizzazione: l'endpoint di autorizzazione di Azure Entra nel formato https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/authorize.
   • (OAuth) ID client dall'app creata.
   • (OAuth) Segreto client dal segreto client creato.
   • (OAuth) Ambito client: immettere il valore seguente senza modifiche: https://database.windows.net/.default offline_access.
   • (OAuth) Viene richiesto di accedere a Accedi con l'ID Entra di Azure. Immettere il nome utente e la password di Azure. Dopo aver eseguito il reindirizzamento alla pagina di creazione della connessione, il codice di autorizzazione viene popolato nell'interfaccia utente.

7. (Facoltativo) Fare clic su Test connessione per verificare che funzioni.

8. (Facoltativo) Aggiungere un commento.

9. Fai clic su Crea.

SQL

Eseguire il seguente comando in un notebook o nell'editor di query SQL di Databricks.

CREATE CONNECTION <connection-name> TYPE sqlserver
OPTIONS (
  host '<hostname>',
  port '<port>',
  user '<user>',
  password '<password>'
);

Consigliamo di usare segreti di Azure Databricks anziché stringhe di testo non crittografato per valori sensibili come le credenziali. Ad esempio:

CREATE CONNECTION <connection-name> TYPE sqlserver
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>')
)

Per informazioni sulla configurazione dei segreti, vedere Gestione dei segreti.

Creare un catalogo esterno

Un catalogo esterno esegue il mirroring di un database in un sistema di dati esterno in modo da poter eseguire query e gestire l'accesso ai dati in tale database usando Azure Databricks e catalogo Unity. Per creare un catalogo esterno, usare una connessione all'origine dati già definita.

Per creare un catalogo esterno, è possibile usare Esplora cataloghi o il comando SQL CREATE FOREIGN CATALOG in un notebook di Azure Databricks o nell'editor di query SQL.

Autorizzazioni necessarie: autorizzazione CREATE CATALOG per il metastore e la proprietà della connessione o il privilegio CREATE FOREIGN CATALOG per la connessione.

Esplora cataloghi

1. Nell'area di lavoro di Azure Databricks fare clic su Catalogo per aprire Esplora cataloghi.

2. Nella parte superiore del riquadro Catalogo fare clic sull'icona Aggiungi e selezionare Aggiungi un catalogo dal menu.

   In alternativa, nella pagina Accesso rapido fare clic sul pulsante Cataloghi e quindi sul pulsante Crea catalogo.

3. Seguire le istruzioni per la creazione di cataloghi stranieri in Creare cataloghi.

SQL

Eseguire il seguente comando SQL in un notebook o nell'editor di query SQL. Gli elementi tra parentesi sono facoltativi. Sostituire i valori segnaposto;

• <catalog-name>: nome del catalogo in Azure Databricks.
• <connection-name>: oggetto connessione che specifica l'origine dati, il percorso e le credenziali di accesso.
• <database-name>: nome del database di cui si vuole eseguire il mirroring come catalogo in Azure Databricks.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (database '<database-name>');

Pushdown supportati

I pushdown seguenti sono supportati in tutte le risorse di calcolo:

• Filtri
• Proiezioni
• Limite
• Funzioni: parziale, solo per le espressioni di filtro. (Funzioni stringa, Funzioni matematiche, Dati, Time e Timestamp e altre funzioni varie, ad esempio Alias, Cast, SortOrder)

I pushdown seguenti sono supportati in Databricks Runtime 13.3 LTS e versioni successive e nel calcolo di SQL Warehouse:

• Aggregazioni
• Operatori booleani seguenti: =, <, = <, >= , ><=>
• Le funzioni matematiche seguenti (non supportate se ANSI è disabilitato): +, -, *, %, /
• Operatori vari seguenti: ^, |, ~
• Ordinamento, se usato con limite

Non sono supportati i seguenti pushdown:

• Join
• Funzioni finestra

Mapping di tipi di dati

Quando si legge da SQL Server a Spark, i tipi di dati vengono mappati nel modo seguente:

*Quando si legge da SQL Server, SQL Server datetimes viene mappato a Spark TimestampType se preferTimestampNTZ = false (impostazione predefinita). Viene eseguito il mapping di SQL Server datetimes a TimestampNTZType se preferTimestampNTZ = true.