Condividi tramite

Eseguire query federate in PostgreSQL

  • Articolo

Questo articolo descrive come configurare la Federazione Lakehouse per eseguire query federate su dati PostgreSQL che non sono gestiti da Azure Databricks. Per altre informazioni sulla federazione di lakehouse, si veda Che cos'è Lakehouse Federation?.

Per connettersi ed eseguire query nel database PostgreSQL usando la Federazione Lakehouse, è necessario creare quanto segue nel metastore del Catalogo Unity di Azure Databricks:

  • Connessione alle query di esecuzione nel database PostgreSQL.
  • Un catalogo esterno che riflette le query eseguite sul database PostgreSQL in Unity Catalog, permettendoti di usare la sintassi delle query di Unity Catalog e gli strumenti di governance dei dati per gestire l'accesso degli utenti di Azure Databricks al database.

Operazioni preliminari

Requisiti dell'area di lavoro:

  • Area di lavoro abilitata per il Catalogo Unity.

Requisiti dell’ambiente di calcolo:

  • Connettività di rete dalla risorsa di calcolo ai sistemi di database di destinazione. Si veda Raccomandazioni di rete per Lakehouse Federation.
  • Il servizio di calcolo di Azure Databricks deve usare Databricks Runtime 13.3 LTS o versione successiva e modalità di accesso condiviso o utente singolo.
  • 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 CONNECTION nel metastore del catalogo Unity collegato all'area di lavoro.
  • Per creare un catalogo esterno, è necessario disporre dell'autorizzazione CREATE CATALOG nel metastore e essere il proprietario della connessione o disporre del privilegio CREATE FOREIGN CATALOG per la connessione.

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

Crea una connessione

Una connessione specifica un percorso e le credenziali per l'accesso 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.

Nota

È anche possibile usare l'API REST di Databricks o l'interfaccia della riga di comando di Databricks per creare una connessione. Vedi POST /api/2.1/unity-catalog/connections e i comandi del Catalogo Unity .

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

Esploratore di Cataloghi

  1. Nell'area di lavoro di Azure Databricks fare clic sull'icona CatalogoCatalogo.

  2. Nella parte superiore del riquadro Catalogo, fare clic sull'icona Aggiungi o icona più 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. Nella pagina Connessione di base della Configurazione guidata connessione immettere un nome di connessione descrittivo.

  4. Selezionare il tipo di connessione di PostgreSQL .

  5. (Facoltativo) Aggiungere un commento.

  6. Fare clic su Avanti.

  7. Nella pagina autenticazione immettere le proprietà di connessione seguenti per l'istanza di PostgreSQL.

    • Host: ad esempio, postgres-demo.lb123.us-west-2.rds.amazonaws.com
    • Porta: ad esempio 5432
    • Utente: ad esempio postgres_user
    • Password: ad esempio, password123

  8. Fare clic su Crea connessione.

  9. Nella pagina di base del catalogo, inserisci un nome per il 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 Unity Catalog.

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

  11. Fai clic su Crea catalogo.

  12. Nella pagina Access, seleziona le aree di lavoro in cui gli utenti possono accedere al catalogo che hai creato. È possibile selezionare Tutte le aree di lavoro hanno accessooppure fare clic su Assegna alle aree di lavoro, selezionare le aree di lavoro e quindi fare clic su Assegna.

  13. Modifica il proprietario che avrà la possibilità di gestire l'accesso a tutti gli oggetti nel catalogo. Inizia a digitare un principale nella casella di testo e quindi fai clic sul principale nei risultati visualizzati.

  14. Concedi privilegi sul catalogo. Clicca su Concedi:

    1. Specifica i Principals che avranno accesso agli oggetti nel catalogo. Inizia a digitare un principale nella casella di testo e quindi fai clic sul principale nei risultati visualizzati.
    2. Selezionare i set di impostazioni Privilegi da concedere a ogni entità. A tutti gli utenti dell'account è concesso BROWSE per impostazione predefinita.
      • Selezionare Data Reader dal menu a discesa per concedere read privilegi agli oggetti nel catalogo.
      • Selezionare Editor dati dal menu a discesa per concedere le autorizzazioni read e modify sugli oggetti nel catalogo.
      • Selezionare manualmente i privilegi da concedere.
    3. Fare clic su e Concedi.

  15. Fare clic su Avanti.

  16. Nella pagina dei metadati , specificare le coppie chiave-valore dei tag. Per ulteriori informazioni, consultare Applicare tag agli oggetti securabili di Unity Catalog.

  17. (Facoltativo) Aggiungere un commento.

  18. Fare clic su Salva.

SQL

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

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

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

CREATE CONNECTION <connection-name> TYPE postgresql
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 straniero

Nota

Se utilizzi l'interfaccia utente per creare una connessione all'origine dati, la creazione del catalogo esterno è già inclusa e puoi saltare questo passaggio.

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 Unity Catalog. Per creare un catalogo esterno, si utilizza 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.

È anche possibile usare l'API REST di Databricks o l'interfaccia della riga di comando di Databricks per creare un catalogo. Consultare POST /api/2.1/unity-catalog/catalogs e comandi Unity Catalog.

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

Esploratore di Cataloghi

  1. Nell'area di lavoro di Azure Databricks fare clic sull'icona CatalogoCatalogo per aprire Esplora cataloghi.

  2. Nella parte superiore del riquadro catalogo fare clic sull'icona Aggiungi o più 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 per il 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 e altre funzioni varie, ad esempio Alias, Cast, SortOrder)

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

  • Le funzioni di aggregazione seguenti: MIN, MAX, COUNT, SUM, AVG, VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP, GREATEST, LEAST, COVAR_POP, COVAR_SAMP, CORR, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXY
  • Le funzioni booleane seguenti: =, <, , <=>, >=, <=>
  • Le funzioni matematiche seguenti (non supportate se ANSI è disabilitato): +, -, *, %, /
  • Operatori vari | e~
  • Ordinamento, quando utilizzato con limite

Non sono supportati i seguenti pushdown:

  • Join
  • Funzioni finestra

Mapping di tipi di dati

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

Tipo PostgreSQL Tipo Spark
numeric DecimalType
int2 ShortType
int4 (se non firmato) IntegerType
int8, oid, xid, int4 (se firmato) LongType
float4 FloatType
precisione doppia, float8 DoubleType
char CharType
name, varchar, tid VarcharType
bpchar, character varying, json, money, point, super, text StringType
bytea, geometry, varbyte BinaryType
bit, bool BooleanType
data DateType
tabstime, time, time con fuso orario, timetz, ora senza fuso orario, timestamp con fuso orario, timestamp, timestamptz, timestamp senza fuso orario* TimestampType/TimestampNTZType
Tipo di matrice Postgresql** ArrayType

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

**Sono supportati tipi di matrice limitati.