Partager via

Accéder à un stockage externe en utilisant le pool SQL serverless dans Azure Synapse Analytics

  • Article

Cet article décrit comment les utilisateurs peuvent lire des données dans des fichiers stockés sur Stockage Azure dans le pool SQL serverless. Les utilisateurs disposent des options suivantes pour accéder au stockage :

  • Fonction OPENROWSET, qui permet des requêtes ad hoc sur les fichiers dans le Stockage Azure.
  • Table externe, qui est une structure de données prédéfinie reposant sur un ensemble de fichiers externes.

L’utilisateur peut recourir à différentes méthodes d’authentification telles que l’authentification directe Microsoft Entra (option par défaut des principaux Microsoft Entra) et l’authentification SAS (option par défaut des principaux SQL).

Interroger des fichiers à l’aide d’OPENROWSET

OPENROWSET permet aux utilisateurs d’interroger des fichiers externes sur Stockage Azure s’ils ont accès au stockage. L’utilisateur qui est connecté au pool SQL serverless doit utiliser la requête suivante pour lire le contenu des fichiers sur Stockage Azure :

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

L’utilisateur peut accéder au stockage à l’aide des règles d’accès suivantes :

  • Utilisateur Microsoft Entra : OPENROWSET utilise l’identité Microsoft Entra de l’appelant pour accéder au Stockage Azure ou pour accéder au stockage avec un accès anonyme.
  • Utilisateur SQL : OPENROWSET utilise un accès anonyme pour accéder au stockage ou peut être représenté à l’aide d’un jeton SAP ou d’une identité managée de l’espace de travail.

Les principaux SQL peuvent également utiliser OPENROWSET pour interroger directement des fichiers protégés par des jetons SAS ou l’identité managée de l’espace de travail. Si un utilisateur SQL exécute cette fonction, un utilisateur avancé disposant de l’autorisation ALTER ANY CREDENTIAL doit créer des informations d’identification de portée serveur qui correspondent à l’URL dans la fonction (à l’aide du nom et du conteneur de stockage) et accorder l’autorisation REFERENCES pour ces informations d’identification à l’appelant de la fonction 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

S’il n’existe aucune information d’identification (CREDENTIAL) au niveau du serveur correspondant à l’URL, ou que l’utilisateur SQL ne dispose pas de l’autorisation REFERENCES pour ces informations d’identification, une erreur est retournée. Les principaux SQL ne peuvent pas emprunter l’identité à l’aide d’une identité Microsoft Entra.

Notes

Cette version d’OPENROWSET est conçue pour permettre d’explorer les données rapidement et aisément à l’aide de l’authentification par défaut. Pour tirer parti de l’emprunt d’identité ou de l’identité managée, utilisez OPENROWSET avec DATA_SOURCE, comme décrit dans la section suivante.

Interroger des sources de données avec OPENROWSET

OPENROWSET permet à l’utilisateur d’interroger les fichiers placés sur une source de données externe :

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

L’utilisateur qui exécute cette requête doit être en mesure d’accéder aux fichiers. Les utilisateurs doivent faire l’objet d’un emprunt d’identité à l’aide d’un jeton SAS ou d’une identité managée de l’espace de travail s’ils ne peuvent accéder directement aux fichiers avec leur identité Microsoft Entra ou leur accès anonyme.

DATABASE SCOPED CREDENTIAL spécifie comment accéder aux fichiers sur la source de données référencée (SAS et identité managée). Un utilisateur avancé disposant de l’autorisation CONTROL DATABASE doit créer DATABASE SCOPED CREDENTIAL permettant d’accéder au stockage et à EXTERNAL DATA SOURCE qui spécifie l’URL de la source de données et les informations d’identification à utiliser :

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) ;

L’appelant doit disposer de l’une des autorisations suivantes pour exécuter la fonction OPENROWSET :

  • L’une des autorisations pour exécuter OPENROWSET :
    • ADMINISTER BULK OPERATIONS permet à la connexion d’exécuter la fonction OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS permet à l’utilisateur de portée base de données d’exécuter la fonction OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL pour les informations d’identification référencées dans EXTERNAL DATA SOURCE.

EXTERNAL TABLE

L’utilisateur disposant des autorisations de lecture de table peut accéder à des fichiers externes à l’aide d’une table externe (EXTERNAL TABLE) créée par-dessus l’ensemble de dossiers et de fichiers Stockage Azure.

L’utilisateur qui dispose des autorisations de créer une table externe (par exemple CREATE TABLE et ALTER ANY CREDENTIAL ou REFERENCES DATABASE SCOPED CREDENTIAL) peut utiliser le script suivant pour créer une table sur la source de données du Stockage Azure :

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

L’utilisateur qui lit les données de cette table doit être en mesure d’accéder aux fichiers. Les utilisateurs doivent faire l’objet d’un emprunt d’identité à l’aide d’un jeton SAS ou d’une identité managée de l’espace de travail s’ils ne peuvent accéder directement aux fichiers avec leur identité Microsoft Entra ou leur accès anonyme.

DATABASE SCOPED CREDENTIAL spécifie comment accéder aux fichiers sur la source de données référencée. L’utilisateur disposant de l’autorisation CONTROL DATABASE doit créer des informations d’identification de portée base de données (DATABASE SCOPED CREDENTIAL) qui permettent d’accéder au stockage et à la source de données externe (EXTERNAL DATA SOURCE) qui spécifie l’URL de la source de données et les informations d’identification à utiliser :

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
 ) ;

Lire des fichiers externes avec EXTERNAL TABLE

EXTERNAL TABLE vous permet de lire des données à partir des fichiers référencés via une source de données à l’aide d’une instruction SQL SELECT standard :

SELECT *
FROM dbo.DimProductsExternal

L’appelant doit disposer des autorisations suivantes pour lire les données :

  • Autorisation SELECT sur la table externe
  • Autorisation REFERENCES DATABASE SCOPED CREDENTIAL si DATA SOURCE a CREDENTIAL

Autorisations

Le tableau suivant présente les autorisations nécessaires pour les opérations listées ci-dessus.

Requête Autorisations requises
OPENROWSET(BULK) sans source de données ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONSou la connexion SQL doit avoir REFERENCES CREDENTIAL::<URL> pour le stockage protégé par SAS
OPENROWSET(BULK) avec source de données sans informations d’identification ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS
OPENROWSET(BULK) avec source de données avec informations d’identification REFERENCES DATABASE SCOPED CREDENTIAL et soit ADMINISTER BULK OPERATIONS soit ADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE et REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT et ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLE et REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Pour créer une table : CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE et ALTER ANY EXTERNAL FILE FORMAT. Pour lire les données : ADMINISTER BULK OPERATIONS ou REFERENCES CREDENTIAL ou SELECT TABLE pour chaque table/vue/fonction dans la requête + autorisation de lecture/écriture sur le stockage

Étapes suivantes

Vous êtes maintenant prêt à poursuivre avec les articles de guide pratique suivants :