Virtualiser un fichier CSV avec PolyBase

Article
07/15/2024

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

SQL Server 2022 (16.x) peut interroger des données directement à partir de fichiers CSV. Ce concept, communément appelé virtualisation des données, permet aux données de rester à leur emplacement d’origine, mais il est possible de les interroger à partir d’une instance de SQL Server avec des commandes T-SQL au même titre qu’une table. Cette fonctionnalité utilise des connecteurs PolyBase et minimise le recours à la copie des données via les processus ETL.

Dans l’exemple suivant, le fichier CSV est stocké sur Stockage Blob Azure et accessible via OPENROWSET ou une table externe.

Pour plus d’informations sur la virtualisation de données, consultez Présentation de la virtualisation des données avec PolyBase.

Préconfiguration

1. Activez PolyBase dans `sp_configure` :

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Créer une base de données utilisateur

Cet exercice crée un exemple de base de données avec les paramètres et l’emplacement par défaut. Vous utilisez cet exemple de base de données vide pour utiliser les données et stocker les informations d’identification délimitées. Dans cet exemple, une nouvelle base de données vide nommée CSV_Demo est utilisée.

CREATE DATABASE [CSV_Demo];

3. Créer la clé principale et les informations d’identification délimitées à la base de données

La clé principale de la base de données dans la base de données utilisateur est requise pour chiffrer le secret des informations d’identification limitées à la base de données, blob_storage.

USE [CSV_Demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. Créer une source de données externes

Les informations d’identification relatives à la base de données sont utilisées pour des sources de données externes. Dans cet exemple, le fichier CSV réside dans le stockage Blob Azure, utilisez par conséquent le préfixe abs et la méthode d’identité SHARED ACCESS SIGNATURE. Pour plus d’informations sur les connecteurs et les préfixes, y compris les nouveaux paramètres de SQL Server 2022 (16.x), reportez-vous à CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

Par exemple, si votre compte de stockage se nomme s3sampledata et que le conteneur se nomme import, le code sera :

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

Utiliser OPENROWSET pour accéder aux données

Dans cet exemple, le fichier est nommé call_center.csv et les données démarrent sur la deuxième ligne.

Étant donné que le Blob_CSV de la source de données externe est mappée au niveau d’un conteneur. call_center.csv se trouve dans un sous-dossier appelé 2022 à la racine du conteneur. Pour interroger un fichier dans une structure de dossiers, fournissez un mappage de dossiers par rapport au paramètre LOCATION de la source de données externe.

SELECT * FROM OPENROWSET
(
    BULK '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

Interroger les données avec une table externe

CREATE EXTERNAL TABLE peut également être utilisé pour virtualiser les données CSV dans SQL Server. Les colonnes doivent être définies et fortement typées. Bien que les tables externes prennent plus d’efforts pour créer, elles offrent également des avantages supplémentaires sur la requête d’une source de données externe avec OPENROWSET. Vous pouvez :

Renforcer la définition de la saisie des données pour une colonne donnée
Définir la nullabilité
Définir LE CLASSEMENT
Créer des statistiques pour une colonne afin d’optimiser la qualité du plan de requête
Créer un modèle plus granulaire dans SQL Server pour l’accès aux données afin d’améliorer votre modèle de sécurité

Pour plus d'informations, consultez la page CREATE EXTERNAL TABLE.

Pour l’exemple suivant, on utilise la même source de données.

1. Créer un format de fichier externe

Pour définir la mise en forme du fichier, un format de fichier externe est requis. Les formats de fichiers externes sont également recommandés en raison de la réutilisation.

Dans l’exemple suivant, les données démarrent sur la deuxième ligne.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. Créer une table externe

LOCATION est le dossier et le chemin d’accès du fichier call_center.csv par rapport au chemin d’accès de l’emplacement dans la source de données externe, défini par DATA_SOURCE. Dans ce cas, le fichier se trouve dans un sous-dossier appelé 2022. Utilisez FILE_FORMAT pour spécifier le chemin d’accès au format de fichier externe csv_ff dans SQL Server.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

Partager via

Virtualiser un fichier CSV avec PolyBase

Préconfiguration

1. Activez PolyBase dans `sp_configure` :

2. Créer une base de données utilisateur

3. Créer la clé principale et les informations d’identification délimitées à la base de données

4. Créer une source de données externes

Utiliser OPENROWSET pour accéder aux données

Interroger les données avec une table externe

1. Créer un format de fichier externe

2. Créer une table externe

Commentaires

Ressources supplémentaires

Partager via

Virtualiser un fichier CSV avec PolyBase

Préconfiguration

1. Activez PolyBase dans sp_configure :

2. Créer une base de données utilisateur

3. Créer la clé principale et les informations d’identification délimitées à la base de données

4. Créer une source de données externes

Utiliser OPENROWSET pour accéder aux données

Interroger les données avec une table externe

1. Créer un format de fichier externe

2. Créer une table externe

Contenu connexe

Commentaires

Ressources supplémentaires

1. Activez PolyBase dans `sp_configure` :