Преобразование файлов данных с помощью инструкции CREATE EXTERNAL TABLE AS SELECT

  • 5 мин

Язык SQL включает множество возможностей и функций, позволяющих управлять данными. Например, с помощью SQL можно выполнять следующие действия:

  • Фильтровать строки и столбцы в наборе данных.
  • Переименовывать поля данных и преобразовывать их из одного типа данных в другой.
  • Вычислять производные поля данных.
  • Обрабатывать строковые значения.
  • Группировать и агрегировать данные.

Бессерверные пулы SQL Azure Synapse можно использовать для выполнения инструкций SQL, которые преобразуют данные и сохраняют результаты в виде файла в озере данных для дальнейшей обработки или запроса. Если вы знакомы с синтаксисом Transact-SQL, можно создать инструкцию SELECT для выполнения нужного преобразования и сохранить результаты инструкции SELECT в выбранном формате файла со схемой таблицы метаданных, к которой можно отправлять запросы SQL.

Инструкцию CREATE EXTERNAL TABLE AS SELECT (CETAS) можно использовать в выделенном пуле SQL или бессерверном пуле SQL для сохранения результатов запроса во внешней таблице, в которой хранятся данные в файле в озере данных.

Инструкция CETAS включает инструкцию SELECT, которая запрашивает и обрабатывает данные из любого допустимого источника данных (например, существующей таблицы или представления в базе данных или функции OPENROWSET, которая считывает данные на основе файлов из озера данных). Затем результаты инструкции SELECT сохраняются во внешней таблице, являющейся объектом метаданных в базе данных, которая предоставляет реляционную абстракцию по данным, хранящимся в файлах. Эта концепция представлена на следующей схеме:

Схема: инструкция CREATE EXTERNAL TABLE AS SELECT, сохраняющая результаты запроса в виде файла.

Применяя этот метод, можно использовать SQL для извлечения и преобразования данных из файлов или таблиц и хранения преобразованных результатов для последующей обработки или анализа. Последующие операции с преобразованными данными можно выполнять в реляционной таблице в базе данных пула SQL или непосредственно с базовыми файлами данных.

Создание внешних объектов базы данных для поддержки CETAS

Чтобы использовать выражения CETAS, необходимо создать следующие типы объектов в базе данных для бессерверного или выделенного пула SQL. При использовании бессерверного пула SQL создайте эти объекты в пользовательской базе данных (созданной с помощью инструкции CREATE DATABASE), а не во встроенной базе данных.

Внешний источник данных

Внешний источник данных инкапсулирует подключение к расположению файловой системы в озере данных. Затем с помощью этого подключения можно указать относительный путь, по которому сохраняются файлы данных для внешней таблицы, созданной инструкцией CETAS.

Если исходные данные для инструкции CETAS отображаются в файлах в том же пути к озеру данных, можно использовать тот же внешний источник данных в функции OPENROWSET, используемой для запроса. Кроме того, можно создать отдельный внешний источник данных для исходных файлов или использовать полный путь к файлу в функции OPENROWSET.

Чтобы создать внешний источник данных, используйте инструкцию CREATE EXTERNAL DATA SOURCE, как показано в следующем примере:

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

В предыдущем примере предполагается, что пользователи, выполняющие запросы, в которых используется внешний источник данных, будут иметь достаточно разрешений для доступа к файлам. Альтернативный подход — инкапсуляция учетных данных во внешнем источнике данных, чтобы его можно было использовать для доступа к данным файлов без предоставления всем пользователям разрешений на его чтение напрямую:

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

Совет

Помимо проверки подлинности SAS, можно определить учетные данные, использующие управляемое удостоверение (удостоверение Microsoft Entra, используемое рабочей областью Azure Synapse), конкретный субъект Microsoft Entra или сквозную проверку подлинности на основе удостоверения пользователя, выполняющего запрос (который является типом проверки подлинности по умолчанию). Дополнительные сведения об использовании учетных данных в бессерверном пуле SQL см. в статье Управление доступом к учетной записи хранения в бессерверном пуле SQL в Azure Synapse Analytics в документации по Azure Synapse Analytics.

Формат внешнего файла

Инструкция CETAS создает таблицу, где данные хранятся в файлах. Необходимо указать формат для внешних файлов.

Чтобы создать формат внешнего файла, используйте инструкцию CREATE EXTERNAL FILE FORMAT, как показано в следующем примере:

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

Совет

В этом примере файлы будут сохранены в формате Parquet. Вы также можете создавать форматы внешних файлов для других типов файлов. Дополнительные сведения см. в разделе о CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Использование инструкции CETAS

После создания внешнего источника данных и формата внешнего файла можно использовать инструкцию CETAS для преобразования данных и сохранения результатов во внешней таблице.

Предположим, что исходные данные, которые требуется преобразовать, состоят из заказов на продажу в виде текстовых файлов с разделителями-запятыми. Файлы хранятся в папке в озере данных. Вы хотите отфильтровать данные, чтобы включить только заказы, помеченные как "особый заказ", и сохранить преобразованные данные в виде файлов Parquet в другой папке в том же озере данных. Для исходных и целевых папок можно использовать один и тот же внешний источник данных, как показано в следующем примере:

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

Параметры LOCATION и BULK в предыдущем примере являются относительными путями для результатов и исходных файлов соответственно. Пути указаны относительно файловой системы, на которую ссылается внешний источник данных файлов.

Важно понимать, что необходимо использовать внешний источник данных, чтобы указать расположение, в котором будут сохранены преобразованные данные для внешней таблицы. Если исходные данные на основе файлов хранятся в той же иерархии папок, можно использовать тот же внешний источник данных. В противном случае можно использовать второй источник данных, чтобы определить подключение к исходным данным, или использовать полный путь, как показано в следующем примере:

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

Удаление внешних таблиц

Если вы больше не хотите использовать внешнюю таблицу, содержащую преобразованные данные, ее можно удалить из базы данных с помощью инструкции DROP EXTERNAL TABLE , как показано ниже:

DROP EXTERNAL TABLE SpecialOrders;

Однако важно понимать, что внешние таблицы являются абстракцией с метаданными поверх файлов, содержащих фактические данные. При удалении внешней таблицы базовые файлы не удаляются.