Accedere allo storage esterno usando il pool SQL serverless in Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse è un data warehouse relazionale su scala aziendale su una base data lake, con un'architettura futura, un'intelligenza artificiale predefinita e nuove funzionalità. Se non si ha familiarità con il data warehousing, iniziare con Fabric Data Warehouse. I carichi di lavoro esistenti del pool SQL dedicated possono eseguire l'aggiornamento a Fabric per accedere a nuove funzionalità tra data science, analisi in tempo reale e creazione di report.

• Inizia una prova gratuita di Fabric.
• Assistente alla migrazione per Fabric Data Warehouse.

Questo articolo descrive come gli utenti possono leggere i dati dai file archiviati in Archiviazione di Azure nel pool SQL serverless. Gli utenti hanno le opzioni seguenti per accedere all'archiviazione:

• funzione OPENROWSET che permette 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 diversi metodi di autenticazione come l'autenticazione pass-through di Microsoft Entra (impostazione predefinita per le entità di Microsoft Entra) e l'autenticazione SAS (impostazione predefinita per le entità SQL).

Query di file con OPENROWSET

OPENROWSET consente agli utenti di eseguire query su file esterni su archiviazione Azure se hanno accesso a tale spazio. Un utente connesso al pool SQL serverless deve usare la query seguente per leggere il contenuto dei file nella risorsa di archiviazione Azure:

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:

• L'utente di Microsoft Entra - OPENROWSET userà l'identità di Microsoft Entra del chiamante per accedere ad Archiviazione di Azure o accedere a Storage con accesso anonimo.
• Utente SQL: OPENROWSET accederà all'archiviazione con accesso anonimo o potrà essere impersonato utilizzando il token SAS o l'Identità Gestita dell'area di lavoro.

impersonificazione

I principali SQL possono anche usare OPENROWSET per eseguire direttamente query sui file protetti con token SAS o Identità gestita del workspace. Se un utente SQL esegue questa funzione, un potente utente con autorizzazione ALTER ANY CREDENTIAL deve creare una credenziale con ambito server che corrisponda all'URL nella funzione (usando il nome di archiviazione e il contenitore) e concedere l'autorizzazione REFERENCES per questa credenziale 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 o l'utente SQL non dispone dell'autorizzazione di riferimento per questa credenziale, verrà restituito l'errore. I principali SQL non possono impersonare usando alcune identità di Microsoft Entra.

Accesso diretto

Non è necessaria alcuna configurazione aggiuntiva per consentire agli utenti di Microsoft Entra di accedere ai file usando le proprie identità. Qualsiasi utente può accedere all'archiviazione Azure che consente l'accesso anonimo (non è necessaria alcuna configurazione aggiuntiva).

Annotazioni

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

Eseguire query sulle origini dati con OPENROWSET

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

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

L'utente che esegue questa query deve essere in grado di accedere ai file. Gli utenti devono essere impersonati usando il token SAS o l'Identità gestita del workspace se non possono accedere direttamente ai file usando l'identità Microsoft Entra o l'accesso anonimo.

impersonificazione

DATABASE SCOPED CREDENTIAL specifica come accedere ai file nell'origine dati a cui si fa riferimento (attualmente SAS e Identità Gestita). Gli utenti esperti con autorizzazioni CONTROL DATABASE devono creare DATABASE SCOPED CREDENTIAL che verranno utilizzate per accedere allo storage e EXTERNAL DATA SOURCE che specificano l'URL della fonte dei dati e le credenziali da utilizzare:

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&sp=rwac&se=2017-02-01T00:55:34Z&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 per eseguire OPENROWSET:
  • ADMINISTER BULK OPERATIONS consente di accedere per eseguire la funzione OPENROWSET.
  • ADMINISTER DATABASE BULK OPERATIONS consente all'utente con ambito definito nel database di eseguire la funzione OPENROWSET.
• REFERENCES DATABASE SCOPED CREDENTIAL alle credenziali a cui si fa riferimento in EXTERNAL DATA SOURCE.

Accesso diretto

L'utente può creare una fonte di dati esterna senza credenziali che farà riferimento all'archiviazione di accesso pubblico oppure utilizzerà l'autenticazione passante di Microsoft Entra.

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

TABELLA ESTERNA

L'utente con le autorizzazioni per la lettura della tabella può accedere a file esterni usando una TABELLA ESTERNA creata su un set di cartelle e file di Archiviazione di Azure.

L'utente con autorizzazioni per creare una tabella esterna (ad esempio CREATE TABLE e ALTER ANY CREDENTIAL o REFERENCES DATABASE SCOPED CREDENTIAL) può usare il seguente script per creare una tabella sulla fonte di 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 essere in grado di accedere ai file. Gli utenti devono essere impersonati usando il token SAS o l'Identità gestita dello spazio di lavoro se non possono accedere direttamente ai file usando la loro identità Microsoft Entra o l'accesso anonimo.

impersonificazione

DATABASE SCOPED CREDENTIAL specifica come accedere ai file nell'origine dati referenziata. L'utente con autorizzazione CONTROL DATABASE deve creare DATABASE SCOPED CREDENTIAL che verrà usato per accedere all'archiviazione e all'ORIGINE DATI ESTERNA che specifica l'URL dell'origine dati e delle 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
 ) ;

Accesso diretto

L'utente può creare EXTERNAL DATA SOURCE senza CREDENTIALS che faccia riferimento all'archiviazione ad accesso pubblico oppure utilizzi l'autenticazione pass-through di Microsoft Entra.

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

Leggere i file esterni con EXTERNAL TABLE

EXTERNAL TABLE consente di leggere i 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 leggere i dati:

• SELECT autorizzazione su tabella esterna
• REFERENCES DATABASE SCOPED CREDENTIAL autorizzazione se DATA SOURCE ha CREDENTIAL

Permissions

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

Query	Autorizzazioni necessarie
OPENROWSET(BULK) senza origine dati	ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, o il login SQL deve disporre di REFERENCES CREDENTIAL::<URL> per l'archiviazione protetta con firma di accesso condiviso
OPENROWSET(BULK) con origine del dato senza credenziali	ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) con origine_dati con credenziali	REFERENCES DATABASE SCOPED CREDENTIAL e uno di ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS
CREA FONTE DATI ESTERNA	ALTER ANY EXTERNAL DATA SOURCE e REFERENCES DATABASE SCOPED CREDENTIAL
CREA TABELLA ESTERNA	CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMATe ALTER ANY EXTERNAL DATA SOURCE
SELEZIONA DALLA TABELLA ESTERNA	SELECT TABLE e REFERENCES DATABASE SCOPED CREDENTIAL
CETAS	Per creare una tabella : CREATE TABLE, ALTER ANY SCHEMAALTER ANY DATA SOURCE, e ALTER ANY EXTERNAL FILE FORMAT. Per leggere i dati: ADMINISTER BULK OPERATIONS o REFERENCES CREDENTIAL o SELECT TABLE per ogni tabella/vista/funzione nella query + autorizzazione R/W per l'archiviazione

Passaggi successivi

Ora sei pronto a continuare con i seguenti articoli della guida:

• Effettuare query sui dati nella memoria

• Eseguire query su un file CSV

• Eseguire query sui file Parquet

• Eseguire query sui file JSON

• Eseguire query su cartelle e più file

• Usare funzioni di partizionamento e metadati

• Effettuare query sui tipi annidati

• Creazione e uso di visualizzazioni