Accedere all'archiviazione esterna tramite il pool SQL serverless in Azure Synapse Analytics

  • Articolo

Questo articolo descrive come leggere i dati dai file archiviati in Archiviazione di Azure nel pool SQL serverless. Per accedere alle risorse di archiviazione, sono disponibili le opzioni seguenti:

  • Funzione OPENROWSET che abilita l'esecuzione di query ad hoc sui file in Archiviazione di Azure.
  • Tabella esterna che è una struttura di dati predefinita basata su un set di file esterni.

L'utente può usare metodi di autenticazione diversi ad esempio l'autenticazione pass-through di Microsoft Entra (impostazione predefinita per le entità di sicurezza di Microsoft Entra) e l'autenticazione tramite firma di accesso condiviso (impostazione predefinita per le entità di sicurezza SQL).

Eseguire query sui file con OPENROWSET

OPENROWSET consente agli utenti di eseguire query sui file esterni nell'archiviazione di Azure se sono autorizzati ad accedere alle risorse di archiviazione. Per leggere il contenuto dei file in Archiviazione di Azure, l'utente connesso al pool SQL serverless dovrà usare la query seguente:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

L'utente può accedere all'archiviazione usando le regole di accesso seguenti:

  • Utente di Microsoft Entra: OPENROWSET userà l'identità di Microsoft Entra del chiamante per accedere ad Archiviazione di Azure oppure accederà alla risorsa di archiviazione con l'accesso anonimo.
  • Utente SQL: OPENROWSET accederà alla risorsa di archiviazione con l'accesso anonimo oppure può essere rappresentato usando un token di firma di accesso condiviso o un'identità gestita dell'area di lavoro.

Le entità di sicurezza di SQL possono inoltre usare OPENROWSET per eseguire direttamente query sui file protetti tramite token di firma di accesso condiviso o l'identità gestita dell'area di lavoro. Se un utente di SQL esegue questa funzione, un utente Power User con autorizzazione ALTER ANY CREDENTIAL deve creare credenziali con ambito server corrispondenti all'URL nella funzione (usando il nome e il contenitore di archiviazione) e concedere l'autorizzazione REFERENCES per tali credenziali al chiamante della funzione OPENROWSET:

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Se non sono presenti credenziali a livello di server corrispondenti all'URL oppure l'utente di SQL non dispone dell'autorizzazione REFERENCES per queste credenziali, verrà restituito un errore. Le entità di sicurezza di SQL non possono essere rappresentate da un'identità di Microsoft Entra.

Nota

Questa versione di OPENROWSET è progettata per l'esplorazione rapida e semplice tramite l'autenticazione predefinita. Per sfruttare la rappresentazione o l'identità gestita, usare OPENROWSET con DATA_SOURCE, come descritto nella sezione successiva.

Eseguire query sulle origini dati con OPENROWSET

OPENROWSET consente all'utente di eseguire query sui file posizionati in un'origine dati esterna:

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

L'utente che esegue la query deve poter accedere ai file. Se non possono accedere direttamente ai file usando la propria identità di Microsoft Entra o l'accesso anonimo, gli utenti devono essere rappresentati con un token di firma di accesso condiviso o un'identità gestita dell'area di lavoro.

DATABASE SCOPED CREDENTIAL specifica la modalità per accedere ai file nell'origine dati a cui viene fatto riferimento (attualmente, la firma di accesso condiviso e l'identità gestita). Un utente Power User con l'autorizzazione CONTROL DATABASE dovrà creare l'oggetto DATABASE SCOPED CREDENTIAL che verrà usato per accedere alla risorsa di archiviazione e l'oggetto EXTERNAL DATA SOURCE che specifica l'URL dell'origine dati e le credenziali da usare:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

Il chiamante deve disporre di una delle autorizzazioni seguenti per eseguire la funzione OPENROWSET:

  • Una delle autorizzazioni seguenti per eseguire OPENROWSET:
    • ADMINISTER BULK OPERATIONS consente di accedere per eseguire la funzione OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS consente all'utente con ambito database di eseguire la funzione OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL per le credenziali a cui viene fatto riferimento in EXTERNAL DATA SOURCE.

EXTERNAL TABLE

L'utente con le autorizzazioni per la lettura della tabella può accedere ai file esterni usando un oggetto EXTERNAL TABLE creato nei file e nelle cartelle di archiviazione di Azure.

L'utente con autorizzazioni per la creazione della tabella esterna, ad esempio CREATE TABLE e ALTER ANY CREDENTIAL o REFERENCES DATABASE SCOPED CREDENTIAL, può usare lo script seguente per creare una tabella nell'origine dati di archiviazione di Azure:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

L'utente che legge i dati da questa tabella deve poter accedere ai file. Se non possono accedere direttamente ai file usando la propria identità di Microsoft Entra o l'accesso anonimo, gli utenti devono essere rappresentati con un token di firma di accesso condiviso o un'identità gestita dell'area di lavoro.

L'autorizzazione DATABASE SCOPED CREDENTIAL specifica come accedere ai file nell'origine dati a cui si fa riferimento. L'utente con l'autorizzazione CONTROL DATABASE deve creare credenziali di tipo DATABASE SCOPED CREDENTIAL che verranno usate per accedere all'archiviazione e all'origine EXTERNAL DATA SOURCE che specifica l'URL dell'origine dati e le credenziali da usare:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

Leggere file esterni con EXTERNAL TABLE

EXTERNAL TABLE consente di eseguire la lettura dei dati dai file a cui viene fatto riferimento tramite l'origine dati usando l'istruzione SQL SELECT standard:

SELECT *
FROM dbo.DimProductsExternal

Il chiamante deve disporre delle autorizzazioni seguenti per la lettura dei dati:

  • Autorizzazione SELECT per la tabella esterna
  • Autorizzazione REFERENCES DATABASE SCOPED CREDENTIAL se DATA SOURCE contiene CREDENTIAL

Autorizzazioni

Nella tabella seguente sono elencate le autorizzazioni necessarie per le operazioni indicate in precedenza.

Query Autorizzazioni necessarie
OPENROWSET(BULK) senza origine dati ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS o l'account di accesso SQL deve avere REFERENCES CREDENTIAL::<URL> per l'archiviazione protetta tramite firma di accesso condiviso
OPENROWSET(BULK) con origine dati senza credenziali ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS
OPENROWSET(BULK) con origine dati con credenziali REFERENCES DATABASE SCOPED CREDENTIAL e un'autorizzazione tra ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE e REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT e ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLE e REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Per creare la tabella: CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE e ALTER ANY EXTERNAL FILE FORMAT. Per la lettura dei dati: ADMINISTER BULK OPERATIONS o REFERENCES CREDENTIAL o SELECT TABLE per ogni tabella/vista/funzione nella query + autorizzazione di lettura/scrittura per l'archiviazione

Passaggi successivi

A questo punto è possibile continuare con gli articoli seguenti: