Megosztás a következőn keresztül:

CSV-fájl virtualizálása a PolyBase használatával

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók

Az SQL Server 2022 (16.x) közvetlenül CSV-fájlokból tud adatokat lekérdezni. Ez az adatvirtualizálásnak nevezett fogalom lehetővé teszi, hogy az adatok az eredeti helyen maradjanak, de lekérdezhetők egy SQL Server-példányból T-SQL-parancsokkal, mint bármely más tábla. Ez a funkció PolyBase-összekötőket használ, és minimálisra csökkenti az adatok ETL-folyamatokon keresztüli másolásának szükségességét.

Az alábbi példában a CSV-fájl az Azure Blob Storage-ban van tárolva, és openROWSET-en vagy külső táblán keresztül érhető el.

Az adatvirtualizálással kapcsolatos további információkért tekintse meg az adatvirtualizálást a PolyBase használatával.

Előre konfigurálás

1. A PolyBase engedélyezése sp_configure

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

RECONFIGURE;

2. Felhasználói adatbázis létrehozása

Ez a gyakorlat létrehoz egy mintaadatbázist az alapértelmezett beállításokkal és helyekkel. Ezzel az üres mintaadatbázissal dolgozhat az adatokkal, és tárolhatja a hatókörön belüli hitelesítő adatokat. Ebben a példában egy új, üres, névvel ellátott CSV_Demo adatbázist használunk.

CREATE DATABASE [CSV_Demo];

3. Főkulcs és adatbázis hatókörű hitelesítő adatok létrehozása

A felhasználói adatbázisban lévő adatbázis főkulcsa szükséges az adatbázis hatókörébe tartozó hitelesítőadat-titkos kulcs titkosításához. 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. Külső adatforrás létrehozása

A külső adatforráshoz adatbázis-hatókörű hitelesítő adatokat használunk. Ebben a példában a CSV-fájl az Azure Blob Storage-ban található, ezért használja az előtagot abs és az identitásmetódust SHARED ACCESS SIGNATURE . Az összekötőkkel és előtagokkal kapcsolatos további információkért, beleértve az SQL Server 2022 új beállításait (16.x), tekintse meg a CREATE EXTERNAL DATA SOURCE (KÜLSŐ ADATFORRÁS LÉTREHOZÁSA) című témakört.

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

Ha például a tárfiók neve el van nevezve s3sampledata , és a tároló neve el van nevezve import, a kód a következő lesz:

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

Az adatok elérése az OPENROWSET használatával

Ebben a példában a fájl neve el van nevezve call_center.csv, és az adatok a második sorban kezdődnek.

Mivel a külső adatforrás Blob_CSV tárolószintre van leképezve. A call_center.csv tároló gyökerében lévő 2022 almappában található. Egy mappastruktúrában lévő fájl lekérdezéséhez adjon meg egy mappaleképezést a külső adatforrás LOCATION paraméteréhez képest.

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

Adatok lekérdezése külső táblával

A CREATE EXTERNAL TABLE az SQL Server CSV-adatainak virtualizálására is használható. Az oszlopokat meg kell határozni és szigorúan típusozottnak kell lenniük. Bár a külső táblák létrehozása nagyobb erőfeszítést igényel, a külső adatforrás openROWSET-lel való lekérdezése további előnyöket is biztosít. Ön megteheti:

  • Az adott oszlop adatbeviteli definíciójának megerősítése
  • Nullability definiálása
  • COLLATION definiálása
  • Statisztikai adatok létrehozása egy oszlophoz a lekérdezési terv minőségének optimalizálásához
  • Részletesebb modell létrehozása az SQL Serveren az adathozzáféréshez a biztonsági modell továbbfejlesztése érdekében

További információ: CREATE EXTERNAL TABLE.

Az alábbi példában ugyanazt az adatforrást használja a rendszer.

1. Külső fájlformátum létrehozása

A fájl formázásának meghatározásához külső fájlformátum szükséges. A külső fájlformátumok is ajánlottak az újrahasználhatóság miatt.

Az alábbi példában az adatok a második sorban kezdődnek.

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

2. Külső tábla létrehozása

A LOCATION a call_center.csv fájl mappa- és fájlútvonala a külső adatforrás helyéhez viszonyítva, amelyet a DATA_SOURCE határoz meg. Ebben az esetben a fájl egy almappában található, a neve 2022. A FILE_FORMAT használatával adja meg az csv_ff SQL Server külső fájlformátumának elérési útját.

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

Kapcsolódó tartalom

  • Last updated on