Accéder à un stockage externe en utilisant le pool SQL serverless dans Azure Synapse Analytics
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 :
OPENROWSETutilise 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 :
OPENROWSETutilise 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&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) ;
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 OPERATIONSpermet à la connexion d’exécuter la fonction OPENROWSET.ADMINISTER DATABASE BULK OPERATIONSpermet à l’utilisateur de portée base de données d’exécuter la fonction OPENROWSET.
REFERENCES DATABASE SCOPED CREDENTIALpour les informations d’identification référencées dansEXTERNAL 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
SELECTsur la table externe - Autorisation
REFERENCES DATABASE SCOPED CREDENTIALsiDATA SOURCEaCREDENTIAL
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 :
Commentaires
Bientôt disponible : Tout au long de l’année 2024, nous abandonnerons progressivement le mécanisme de retour d’information GitHub Issues pour le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez : https://aka.ms/ContentUserFeedback.
Soumettre et afficher des commentaires pour