Virtualiser un fichier CSV avec PolyBase

  • Article

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 peuvent être interrogées à partir d’une instance SQL Server avec des commandes T-SQL comme n’importe quelle autre table. Cette fonctionnalité utilise des connecteurs PolyBase et réduit le besoin de copier des données via des 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 des données, présentation de la virtualisation des données avec PolyBase.

Préconfiguration

1. Activer 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 une clé principale et des informations d’identification délimitées à la base de données

La clé principale de base de données dans la base de données utilisateur est requise pour chiffrer le secret d’informations d’identification délimité à 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 externe

Les informations d’identification délimitées à la base de données sont utilisées pour la source de données externe. Dans cet exemple, le fichier CSV réside dans Stockage Blob Azure. Utilisez donc 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.csvet les données démarrent sur la deuxième ligne.

Étant donné que la source Blob_CSV de données externe est mappée au niveau d’un conteneur. Il 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 des 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 l’interrogation 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 CREATE EXTERNAL TABLE.

Pour l’exemple suivant, la même source de données est utilisée.

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 call_center.csv chemin d’accès du fichier 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 csv_ff format de fichier externe 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

Contenu connexe