Créer des objets de base de données externes
Vous pouvez utiliser la fonction OPENROWSET dans les requêtes SQL qui s’exécutent dans la base de données master par défaut du pool SQL serverless intégré pour explorer les données du lac de données. Toutefois, vous pouvez avoir besoin de créer une base de données personnalisée contenant certains objets qui facilitent l’utilisation des données externes dans le lac de données dont vous avez besoin pour exécuter des requêtes fréquentes.
Création d’une base de données
Vous pouvez créer une base de données dans un pool SQL serverless comme vous le feriez dans une instance SQL Server. Vous pouvez utiliser l’interface graphique dans Synapse Studio ou une instruction CREATE DATABASE. Il est important de définir le classement de votre base de données afin qu’elle prenne en charge la conversion des données texte de fichier en types de données Transact-SQL appropriés.
L’exemple de code suivant crée une base de données nommée salesDB avec un classement qui facilite l’importation de données texte codées UTF-8 dans les colonnes VARCHAR.
CREATE DATABASE SalesDB
COLLATE Latin1_General_100_BIN2_UTF8
Création d’une source de données externe
Vous pouvez utiliser la fonction OPENROWSET avec un chemin BULK pour interroger des données de fichier à partir de votre propre base de données, tout comme vous le feriez dans la base de données master. Toutefois, si vous prévoyez d’interroger fréquemment des données situées dans un même emplacement, il sera plus efficace de définir une source de données externe qui référence cet emplacement. Par exemple, le code suivant crée une source de données nommée files pour le dossier hypothétique https://mydatalake.blob.core.windows.net/data/files/ :
CREATE EXTERNAL DATA SOURCE files
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)
L’un des avantages d’une source de données externe est que vous pouvez simplifier une requête OPENROWSET pour utiliser la combinaison de la source de données et du chemin relatif des dossiers ou des fichiers que vous souhaitez interroger :
SELECT *
FROM
OPENROWSET(
BULK 'orders/*.csv',
DATA_SOURCE = 'files',
FORMAT = 'csv',
PARSER_VERSION = '2.0'
) AS orders
Dans cet exemple, le paramètre BULK est utilisé pour spécifier le chemin relatif de tous les fichiers .csv du dossier orders, qui est un sous-dossier du dossier files référencé par la source de données.
Un autre avantage de l’utilisation d’une source de données est que vous pouvez attribuer des informations d’identification pour la source de données à utiliser lors de l’accès au stockage sous-jacent, ce qui vous permet de fournir l’accès aux données via SQL, sans autoriser les utilisateurs à accéder directement aux données du compte de stockage. Par exemple, le code suivant crée des informations d’identification qui utilisent une signature d’accès partagé (SAS) pour s’authentifier auprès du compte de stockage Azure sous-jacent hébergeant le lac de données.
CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sv=xxx...';
GO
CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
CREDENTIAL = sqlcred
);
GO
Conseil
En plus de l’authentification SAS, vous pouvez définir des informations d’identification qui utilisent l’identité managée (l’identité Microsoft Entra utilisée par votre espace de travail Azure Synapse), un principal Microsoft Entra spécifique ou une authentification directe basée sur l’identité de l’utilisateur exécutant la requête (il s’agit du type d’authentification par défaut). Pour en savoir plus sur l’utilisation des informations d’identification dans un pool SQL serverless, consultez la documentation Contrôler l’accès au compte de stockage pour le pool SQL serverless dans Azure Synapse Analytics dans la documentation Azure Synapse Analytics.
Création d’un format de fichier externe
Même si une source de données externe simplifie le code qui est nécessaire pour accéder aux fichiers avec la fonction OPENROWSET, vous devrez quand même fournir des informations de format pour les fichiers, ce qui peut inclure plusieurs paramètres pour les fichiers texte délimités. Vous pouvez encapsuler ces paramètres dans un format de fichier externe, comme ceci :
CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS(
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"'
)
);
GO
Après avoir créé des formats de fichier pour les fichiers de données que vous devez utiliser, vous pouvez utiliser le format de fichier qui permet de créer des tables externes, comme nous allons le voir ensuite.
Création d’une table externe
Lorsque vous devez effectuer un grand nombre d’analyses ou créer de nombreux rapports à partir de fichiers du lac de données, l’utilisation de la fonction OPENROWSET peut entraîner un code complexe qui inclut des sources de données et des chemins de fichiers. Pour simplifier l’accès aux données, vous pouvez encapsuler les fichiers dans une table externe que les utilisateurs et les applications de création de rapports pourront interroger à l’aide d’une instruction SQL SELECT standard, comme pour n’importe quelle autre table de base de données. Pour créer une table externe, utilisez l’instruction CREATE EXTERNAL TABLE, en spécifiant le schéma de colonne comme pour une table standard, et en incluant une clause WITH qui spécifie la source de données externe, le chemin relatif et le format de fichier externe pour vos données.
CREATE EXTERNAL TABLE dbo.products
(
product_id INT,
product_name VARCHAR(20),
list_price DECIMAL(5,2)
)
WITH
(
DATA_SOURCE = files,
LOCATION = 'products/*.csv',
FILE_FORMAT = CsvFormat
);
GO
-- query the table
SELECT * FROM dbo.products;
En créant une base de données qui contient les objets externes abordés dans cette unité, vous pouvez fournir une couche de base de données relationnelle sur des fichiers d’un lac de données, ce qui permet à de nombreux analystes de données et outils de création de rapports d’accéder facilement aux données à l’aide de la sémantique de requête standard SQL.