Accédez au stockage externe à l'aide d'un pool SQL sans serveur dans Azure Synapse Analytics.

Conseil / Astuce

Microsoft Fabric Data Warehouse est un entrepôt relationnel à l’échelle de l’entreprise sur une base de lac de données, avec une architecture future, une IA intégrée et de nouvelles fonctionnalités. Si vous débutez avec l'entreposage de données, commencez par Fabric Data Warehouse. Les charges de travail de pool SQL existantes dédicées peuvent être mises à niveau vers Fabric pour accéder à de nouvelles fonctionnalités dans la science des données, l’analytique en temps réel et la création de rapports.

Cet article explique comment les utilisateurs peuvent lire des données à partir des fichiers stockés sur stockage Azure dans un pool SQL serverless. Les utilisateurs ont les options suivantes pour accéder au stockage :

  • OPENROWSET fonction qui active les requêtes ad hoc sur les fichiers dans stockage Azure.
  • Table externe qui est une structure de données prédéfinie basée sur l’ensemble de fichiers externes.

L’utilisateur peut utiliser méthodes d’authentification différentes telles que l’authentification directe Microsoft Entra (par défaut pour les principaux Microsoft Entra) et l’authentification SAP (par défaut pour les principaux SQL).

Interroger des fichiers à l’aide d’OPENROWSET

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

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 :

  • Microsoft Entra utilisateur - OPENROWSET utilisera Microsoft Entra identité d’appelant pour accéder à stockage Azure ou accéder au stockage avec un accès anonyme.
  • Utilisateur SQL : OPENROWSET accède au stockage avec un accès anonyme ou peut utiliser un jeton SAS ou l'identité gérée de l'espace de travail.

Les principaux SQL peuvent également utiliser OPENROWSET pour interroger directement des fichiers protégés par des jetons SAP ou une identité managée de l’espace de travail. Si un utilisateur SQL exécute cette fonction, un utilisateur power avec ALTER ANY CREDENTIAL autorisation doit créer des informations d’identification au niveau du serveur qui correspondent à l’URL de la fonction (à l’aide du nom de stockage et du conteneur) 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 au niveau du serveur qui correspond à l’URL ou si l’utilisateur SQL n’a pas d’autorisation de référence pour ces informations d’identification, l’erreur est retournée. Les identités SQL ne peuvent pas se faire passer pour une autre en utilisant une identité Microsoft Entra.

Note

Cette version d’OPENROWSET est conçue pour une exploration rapide et facile des données à l’aide de l’authentification par défaut. Pour utiliser l’emprunt d’identité ou l’identité managée, utilisez OPENROWSET avec DATA_SOURCE 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 pouvoir accéder aux fichiers. Les utilisateurs doivent emprunter une identité à l'aide du jeton SAS ou d'une Identité gérée de l'espace de travail s'ils ne peuvent pas accéder directement aux fichiers avec leur identité Microsoft Entra ou un accès anonyme.

DATABASE SCOPED CREDENTIAL spécifie comment accéder aux fichiers sur la source de données référencée (actuellement SAP et Identité managée). L'utilisateur avancé avec CONTROL DATABASE autorisation doit créer DATABASE SCOPED CREDENTIAL qui sera utilisé pour 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 d’exécution d’OPENROWSET :
    • ADMINISTER BULK OPERATIONS permet à la connexion d’exécuter la fonction OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS permet à l’utilisateur à l'échelle de la base de données d’exécuter la fonction OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL aux informations d’identification référencées dans EXTERNAL DATA SOURCE.

TABLE EXTERNE

L’utilisateur disposant des autorisations de lecture de la table peut accéder aux fichiers externes à l’aide d’une TABLE EXTERNE créée sur un ensemble de dossiers et de fichiers stockage Azure.

L’utilisateur qui a permissions pour 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 en plus de stockage Azure source de données :

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 pouvoir accéder aux fichiers. Les utilisateurs doivent être impersonnés à l’aide du jeton SAS ou de l'Identité gérée de l’espace de travail s’ils ne peuvent pas accéder directement aux fichiers à l’aide de leur identité Microsoft Entra ou de l’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 une CREDENTIAL DÉLIMITÉE AU NIVEAU DE BASE DE DONNÉES qui sera utilisée pour accéder au stockage et à la SOURCE DE DONNÉES EXTERNE qui spécifie l’URL de la source de données et l’information 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 de l’instruction SQL SELECT standard :

SELECT *
FROM dbo.DimProductsExternal

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

  • SELECT autorisation SUR table externe
  • REFERENCES DATABASE SCOPED CREDENTIAL autorisation si DATA SOURCE a CREDENTIAL

Permissions

Le tableau suivant répertorie les autorisations requises pour les opérations répertoriées ci-dessus.

Query Autorisations requises
OPENROWSET(BULK) sans source de données ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, ou la connexion SQL doit avoir RÉFÉRENCES 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 et identifiants REFERENCES DATABASE SCOPED CREDENTIAL et l’un de ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS
CRÉER UNE SOURCE DE DONNÉES EXTERNE ALTER ANY EXTERNAL DATA SOURCE et REFERENCES DATABASE SCOPED CREDENTIAL
CRÉER UNE TABLE EXTERNE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT et ALTER ANY EXTERNAL DATA SOURCE
SÉLECTIONNER À PARTIR D’UNE TABLE EXTERNE SELECT TABLE et REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Pour créer une table - CREATE TABLE, , ALTER ANY SCHEMAALTER ANY DATA SOURCE, et ALTER ANY EXTERNAL FILE FORMAT. Pour lire des données : ADMINISTER BULK OPERATIONS ou REFERENCES CREDENTIALSELECT TABLE par chaque table/vue/fonction dans la requête + autorisation R/W sur le stockage

Étapes suivantes

Vous êtes maintenant prêt à continuer avec les articles how to suivants :

  • Last updated on