Udostępnij za pośrednictwem

Wirtualizacja pliku CSV za pomocą technologii PolyBase

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje

Program SQL Server 2022 (16.x) może wysyłać zapytania dotyczące danych bezpośrednio z plików CSV. Ta koncepcja, często nazywana wirtualizacją danych, umożliwia zachowanie danych w oryginalnej lokalizacji, ale można wykonywać zapytania z wystąpienia programu SQL Server za pomocą poleceń języka T-SQL, takich jak każda inna tabela. Ta funkcja używa łączników PolyBase i minimalizuje potrzebę kopiowania danych za pośrednictwem procesów ETL.

W poniższym przykładzie plik CSV jest przechowywany w usłudze Azure Blob Storage i dostępny za pośrednictwem zestawu OPENROWSET lub tabeli zewnętrznej.

Aby uzyskać więcej informacji na temat wirtualizacji danych, zobacz Wprowadzenie do wirtualizacji danych za pomocą technologii PolyBase.

Wstępna konfiguracja

1. Włączanie technologii PolyBase w programie sp_configure

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

RECONFIGURE;

2. Tworzenie bazy danych użytkownika

W tym ćwiczeniu zostanie utworzona przykładowa baza danych z domyślnymi ustawieniami i lokalizacją. Ta pusta przykładowa baza danych służy do pracy z danymi i przechowywania poświadczeń o określonym zakresie. W tym przykładzie jest używana nowa pusta baza danych o nazwie CSV_Demo .

CREATE DATABASE [CSV_Demo];

3. Utwórz klucz główny i poświadczenie o zakresie bazy danych

Klucz główny bazy danych w bazie danych użytkownika jest wymagany do szyfrowania klucza tajnego poświadczeń o określonym zakresie bazy danych. 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. Tworzenie zewnętrznego źródła danych

Poświadczenie ograniczone do bazy danych jest używane dla zewnętrznego źródła danych. W tym przykładzie plik CSV znajduje się w usłudze Azure Blob Storage, dlatego użyj prefiksu absSHARED ACCESS SIGNATURE i metody tożsamości. Aby uzyskać więcej informacji na temat łączników i prefiksów, w tym nowych ustawień dla programu SQL Server 2022 (16.x), zobacz CREATE EXTERNAL DATA SOURCE (TWORZENIE ZEWNĘTRZNEGO ŹRÓDŁA DANYCH).

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

Jeśli na przykład konto magazynu ma nazwę s3sampledata , a kontener ma nazwę import, kod będzie:

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

Uzyskiwanie dostępu do danych przy użyciu zestawu OPENROWSET

W tym przykładzie plik ma nazwę call_center.csv, a dane zaczynają się w drugim wierszu.

Ponieważ zewnętrzne źródło Blob_CSV danych jest mapowane na poziom kontenera. Obiekt call_center.csv znajduje się w podfolderze o nazwie 2022 w katalogu głównym kontenera. Aby wysłać zapytanie do pliku w strukturze folderów, podaj mapowanie folderów względem parametru LOCATION zewnętrznego źródła danych.

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

Wykonywanie zapytań dotyczących danych za pomocą tabeli zewnętrznej

Funkcja CREATE EXTERNAL TABLE może również służyć do wirtualizacji danych CSV w programie SQL Server. Kolumny muszą być zdefiniowane i silnie typizowane. Chociaż tabele zewnętrzne wymagają więcej wysiłku przy tworzeniu, zapewniają również dodatkowe korzyści w porównaniu do wykonywania zapytań względem zewnętrznego źródła danych za pomocą OPENROWSET. Masz następujące możliwości:

  • Wzmacnianie definicji wpisywania danych dla danej kolumny
  • Definiowanie wartości nullowalnych
  • Definicja sortowania
  • Tworzenie statystyk dla kolumny w celu optymalizacji jakości planu zapytania
  • Tworzenie bardziej szczegółowego modelu w programie SQL Server na potrzeby dostępu do danych w celu zwiększenia modelu zabezpieczeń

Aby uzyskać więcej informacji, zobacz CREATE EXTERNAL TABLE (TWORZENIE TABELI ZEWNĘTRZNEJ).

W poniższym przykładzie jest używane to samo źródło danych.

1. Tworzenie formatu pliku zewnętrznego

Aby zdefiniować formatowanie pliku, wymagany jest format pliku zewnętrznego. Formaty plików zewnętrznych są również zalecane ze względu na możliwość ponownego zastosowania.

W poniższym przykładzie dane rozpoczynają się w drugim wierszu.

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

2. Tworzenie tabeli zewnętrznej

LOCATION to folder i ścieżka pliku call_center.csv względem ścieżki folderu lokalizacji w zewnętrznym źródle danych zdefiniowanym przez DATA_SOURCE. W takim przypadku plik znajduje się w podfolderze o nazwie 2022. Użyj FILE_FORMAT, aby określić ścieżkę do formatu pliku zewnętrznego csv_ff w programie 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

Treści powiązane

  • Last updated on