CSV-bestand virtualiseren met PolyBase

Van toepassing op: SQL Server 2022 (16.x) en latere versies

SQL Server 2022 (16.x) kan rechtstreeks vanuit CSV-bestanden een query uitvoeren op gegevens. Met dit concept, meestal gegevensvirtualisatie genoemd, kunnen de gegevens op de oorspronkelijke locatie blijven, maar kunnen ze worden opgevraagd vanuit een SQL Server-exemplaar met T-SQL-opdrachten zoals elke andere tabel. Deze functie maakt gebruik van PolyBase-connectors en minimaliseert de noodzaak voor het kopiëren van gegevens via ETL-processen.

In het volgende voorbeeld wordt het CSV-bestand opgeslagen in Azure Blob Storage en geopend via OPENROWSET of een externe tabel.

Maak kennis met gegevensvirtualisatie met PolyBase voor meer informatie over gegevensvirtualisatie.

Vooraf configureren

1. PolyBase inschakelen in `sp_configure`

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

RECONFIGURE;

2. Een gebruikersdatabase maken

In deze oefening maakt u een voorbeelddatabase met standaardinstellingen en -locatie. Je gebruikt deze lege voorbeelddatabase om met de gegevens te werken en de gescope referentie op te slaan. In dit voorbeeld wordt een nieuwe lege database met de naam CSV_Demo gebruikt.

CREATE DATABASE [CSV_Demo];

3. Maak een hoofdsleutel en database-gerelateerde referentie

De master key van de database in de gebruikersdatabase is vereist om het credential-geheim binnen het databasebereik te versleutelen. 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. Externe gegevensbron maken

Er wordt een referentie met databasebereik gebruikt voor de externe gegevensbron. In dit voorbeeld bevindt het CSV-bestand zich in Azure Blob Storage, dus gebruik het voorvoegsel abs en de SHARED ACCESS SIGNATURE identiteitsmethode. Zie CREATE EXTERNAL DATA SOURCE voor meer informatie over de connectors en voorvoegsels, waaronder nieuwe instellingen voor SQL Server 2022 (16.x).

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

Als uw opslagaccount bijvoorbeeld een naam s3sampledata heeft en de container de naam importheeft, is de code:

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

OPENROWSET gebruiken om toegang te krijgen tot de gegevens

In dit voorbeeld heeft het bestand de naam call_center.csven de gegevens beginnen op de tweede rij.

Omdat de externe gegevensbron Blob_CSV is toegewezen aan een containerniveau. De call_center.csv bevindt zich in een submap die wordt aangeroepen 2022 in de hoofdmap van de container. Als u een query wilt uitvoeren op een bestand in een mapstructuur, geeft u een maptoewijzing op ten opzichte van de parameter LOCATION van de externe gegevensbron.

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];

Gegevens opvragen met een externe tabel

CREATE EXTERNAL TABLE kan ook worden gebruikt om de CSV-gegevens in SQL Server te virtualiseren. De kolommen moeten worden gedefinieerd en sterk getypt. Hoewel externe tabellen meer moeite kosten om te maken, bieden ze ook extra voordelen ten opzichte van het uitvoeren van query's op een externe gegevensbron met OPENROWSET. U kunt het volgende doen:

De definitie van het typen van gegevens voor een bepaalde kolom versterken
Null-waarde definiëren
COLLATIE definiëren
Statistieken maken voor een kolom om de kwaliteit van het queryplan te optimaliseren
Een gedetailleerder model maken in SQL Server voor gegevenstoegang om uw beveiligingsmodel te verbeteren

Zie CREATE EXTERNAL TABLE voor meer informatie.

In het volgende voorbeeld wordt dezelfde gegevensbron gebruikt.

1. Externe bestandsindeling maken

Als u de opmaak van het bestand wilt definiëren, is een externe bestandsindeling vereist. Externe bestandsindelingen worden ook aanbevolen vanwege herbruikbaarheid.

In het volgende voorbeeld beginnen de gegevens op de tweede rij.

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

2. Externe tabel maken

LOCATION is de map en het bestandspad van het call_center.csv bestand ten opzichte van het pad van de locatie in de externe gegevensbron, gedefinieerd door DATA_SOURCE. In dit geval bevindt het bestand zich in een submap met de naam 2022. Gebruik FILE_FORMAT om het pad naar de csv_ff externe bestandsindeling in de SQL Server op te geven.

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

Feedback

Is deze pagina nuttig?

Last updated on 2026-03-19