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 de la base de données SQL serverless intégrée pour explorer les données dans le lac de données. Toutefois, vous pouvez parfois créer une base de données personnalisée qui contient des objets qui facilitent l’utilisation de données externes dans le lac de données que vous devez interroger fréquemment.

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. L’une des considérations consiste à définir le classement de votre base de données afin qu’elle prenne en charge la conversion de données texte dans des fichiers en Transact-SQL types de données 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 encodées UTF-8 dans des 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 pouvez dans la base de données master ; mais si vous envisagez d’interroger des données dans le même emplacement fréquemment, il est plus efficace de définir une source de données externe qui fait référence à cet emplacement. Par exemple, le code suivant crée une source de données nommée fichiers pour le dossier https://mydatalake.blob.core.windows.net/data/files/ hypothétique :

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 d’accès relatif aux dossiers ou 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 dans le dossier commandes, qui est un sous-dossier des fichiers dossier 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 affecter 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 aux données directement dans le compte de stockage. Par exemple, le code suivant crée des informations d’identification qui utilisent une signature d’accès partagé (SAP) 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

Création d’un format de fichier externe

Bien qu’une source de données externe simplifie le code nécessaire pour accéder aux fichiers avec la fonction OPENROWSET, vous devez toujours fournir des détails de format pour l’accès au fichier ; 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 suit :

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Après avoir créé des formats de fichiers pour les fichiers de données spécifiques que vous devez utiliser, vous pouvez utiliser le format de fichier pour créer des tables externes, comme indiqué ci-dessous.

Création d’une table externe

Lorsque vous devez effectuer une grande quantité d’analyse ou de création de rapports à partir de fichiers dans le 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 d’accès aux fichiers. Pour simplifier l’accès aux données, vous pouvez encapsuler les fichiers dans une table externe ; quels utilisateurs et applications de création de rapports peuvent interroger à l’aide d’une instruction SQL SELECT standard comme 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 y compris une clause WITH spécifiant la source de données externe, le chemin d’accès 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 dans un lac de données, ce qui facilite l’accès de nombreux analystes de données et outils de création de rapports aux données à l’aide de la sémantique de requête SQL standard.