Przekształcanie plików danych za pomocą instrukcji CREATE EXTERNAL TABLE AS SELECT

5 min

Język SQL zawiera wiele funkcji i funkcji, które umożliwiają manipulowanie danymi. Na przykład możesz użyć języka SQL do:

Filtrowanie wierszy i kolumn w zestawie danych.
Zmienianie nazw pól danych i konwertowanie między typami danych.
Oblicz pola danych pochodnych.
Manipulowanie wartościami ciągów.
Grupuj i agreguj dane.

Pule SQL bezserwerowe usługi Azure Synapse mogą służyć do uruchamiania instrukcji SQL, które przekształcają dane i utrwalają wyniki jako plik w usłudze Data Lake w celu dalszego przetwarzania lub wykonywania zapytań. Jeśli znasz składnię języka Transact-SQL, możesz utworzyć instrukcję SELECT, która stosuje określoną transformację, którą cię interesuje, i zapisać wyniki instrukcji SELECT w wybranym formacie pliku ze schematem tabeli metadanych, który można wykonywać zapytania przy użyciu języka SQL.

Możesz użyć instrukcji CREATE EXTERNAL TABLE AS SELECT (CETAS) w dedykowanej puli SQL lub bezserwerowej puli SQL, aby utrwały wyniki zapytania w tabeli zewnętrznej, która przechowuje swoje dane w pliku w usłudze Data Lake.

Instrukcja CETAS zawiera instrukcję SELECT, która wykonuje zapytania i manipuluje danymi z dowolnego prawidłowego źródła danych (które może być istniejącą tabelą lub widokiem w bazie danych lub funkcją OPENROWSET, która odczytuje dane oparte na plikach z magazynu data lake). Wyniki instrukcji SELECT są następnie utrwalane w tabeli zewnętrznej, która jest obiektem metadanych w bazie danych, która zapewnia abstrakcję relacyjną na danych przechowywanych w plikach. Na poniższym diagramie przedstawiono wizualnie koncepcję:

Diagram przedstawiający instrukcję CREATE EXTERNAL TABLE AS SELECT zapisując wyniki zapytania jako plik.

Stosując tę technikę, można użyć języka SQL do wyodrębniania i przekształcania danych z plików lub tabel oraz przechowywania przekształconych wyników na potrzeby przetwarzania podrzędnego lub analizy. Kolejne operacje na przekształconych danych można wykonywać względem tabeli relacyjnej w bazie danych puli SQL lub bezpośrednio względem bazowych plików danych.

Tworzenie zewnętrznych obiektów bazy danych w celu obsługi instrukcji CETAS

Aby użyć wyrażeń CETAS, należy utworzyć następujące typy obiektów w bazie danych dla bezserwerowej lub dedykowanej puli SQL. W przypadku korzystania z bezserwerowej puli SQL utwórz te obiekty w niestandardowej bazie danych (utworzonej przy użyciu instrukcji CREATE DATABASE ), a nie wbudowanej bazy danych.

Zewnętrzne źródło danych

Zewnętrzne źródło danych hermetyzuje połączenie z lokalizacją systemu plików w usłudze Data Lake. Następnie możesz użyć tego połączenia, aby określić ścieżkę względną, w której zapisywane są pliki danych dla tabeli zewnętrznej utworzonej przez instrukcję CETAS.

Jeśli dane źródłowe instrukcji CETAS są w plikach w tej samej ścieżce typu data lake, możesz użyć tego samego zewnętrznego źródła danych w funkcji OPENROWSET używanej do wykonywania zapytań. Alternatywnie możesz utworzyć oddzielne zewnętrzne źródło danych dla plików źródłowych lub użyć w pełni kwalifikowanej ścieżki pliku w funkcji OPENROWSET.

Aby utworzyć zewnętrzne źródło danych, użyj instrukcji CREATE EXTERNAL DATA SOURCE , jak pokazano w tym przykładzie:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

W poprzednim przykładzie przyjęto założenie, że użytkownicy uruchamiający zapytania korzystające z zewnętrznego źródła danych będą mieli wystarczające uprawnienia dostępu do plików. Alternatywną metodą jest hermetyzowanie poświadczeń w zewnętrznym źródle danych, aby można było go użyć do uzyskiwania dostępu do danych plików bez udzielania wszystkim użytkownikom uprawnień do ich bezpośredniego odczytu:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Napiwek

Oprócz uwierzytelniania za pomocą sygnatury dostępu współdzielonego można zdefiniować poświadczenia korzystające z tożsamości zarządzanej (tożsamość firmy Microsoft entra używana przez obszar roboczy usługi Azure Synapse), określony podmiot zabezpieczeń firmy Microsoft lub uwierzytelnianie przekazywane na podstawie tożsamości użytkownika uruchamiającego zapytanie (który jest domyślnym typem uwierzytelniania). Aby dowiedzieć się więcej na temat używania poświadczeń w bezserwerowej puli SQL, zobacz artykuł Kontrola dostępu do konta magazynu dla bezserwerowej puli SQL w usłudze Azure Synapse Analytics w dokumentacji usługi Azure Synapse Analytics .

Format pliku zewnętrznego

Instrukcja CETAS tworzy tabelę z danymi przechowywanymi w plikach. Musisz określić format plików, które chcesz utworzyć jako format pliku zewnętrznego.

Aby utworzyć format pliku zewnętrznego, użyj instrukcji CREATE EXTERNAL FILE FORMAT , jak pokazano w tym przykładzie:

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Napiwek

W tym przykładzie pliki zostaną zapisane w formacie Parquet. Można również tworzyć zewnętrzne formaty plików dla innych typów plików. Aby uzyskać szczegółowe informacje, zobacz CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Korzystanie z instrukcji CETAS

Po utworzeniu zewnętrznego źródła danych i formatu pliku zewnętrznego można użyć instrukcji CETAS, aby przekształcić dane i zapisać wyniki w tabeli zewnętrznej.

Załóżmy na przykład, że dane źródłowe, które chcesz przekształcić, składają się z zamówień sprzedaży w plikach tekstowych rozdzielanych przecinkami, które są przechowywane w folderze w usłudze Data Lake. Chcesz przefiltrować dane, aby uwzględnić tylko zamówienia oznaczone jako "zamówienie specjalne" i zapisać przekształcone dane jako pliki Parquet w innym folderze w tym samym magazynie data lake. Możesz użyć tego samego zewnętrznego źródła danych zarówno dla folderów źródłowych, jak i docelowych, jak pokazano w tym przykładzie:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Parametry LOCATION i BULK w poprzednim przykładzie są ścieżkami względnymi odpowiednio dla wyników i plików źródłowych. Ścieżki są względne względem lokalizacji systemu plików, do których odwołują się pliki zewnętrznego źródła danych.

Ważnym punktem do zrozumienia jest to, że należy użyć zewnętrznego źródła danych, aby określić lokalizację, w której mają zostać zapisane przekształcone dane dla tabeli zewnętrznej. Gdy dane źródłowe oparte na plikach są przechowywane w tej samej hierarchii folderów, można użyć tego samego zewnętrznego źródła danych. W przeciwnym razie możesz użyć drugiego źródła danych, aby zdefiniować połączenie z danymi źródłowymi lub użyć w pełni kwalifikowanej ścieżki, jak pokazano w tym przykładzie:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Usuwanie tabel zewnętrznych

Jeśli nie potrzebujesz już tabeli zewnętrznej zawierającej przekształcone dane, możesz ją usunąć z bazy danych przy użyciu DROP EXTERNAL TABLE instrukcji , jak pokazano poniżej:

DROP EXTERNAL TABLE SpecialOrders;