Transforme arquivos de dados com a instrução CREATE EXTERNAL TABLE AS SELECT

5 minutos

A linguagem SQL inclui muitos recursos e funções que permitem manipular dados. Por exemplo, você pode usar SQL para:

Filtrar linhas e colunas em um conjunto de dados.
Renomeie campos de dados e converta entre tipos de dados.
Calcular campos de dados derivados.
Manipular valores de cadeia de caracteres.
Agrupar e agregar dados.

Os pools SQL sem servidor do Azure Synapse podem ser usados para executar instruções SQL que transformam dados e persistem os resultados como um arquivo em um data lake para processamento ou consulta adicionais. Se você estiver familiarizado com a sintaxe Transact-SQL, poderá criar uma instrução SELECT que aplique a transformação específica na qual está interessado e armazenar os resultados da instrução SELECT em um formato de arquivo selecionado com um esquema de tabela de metadados que pode ser consultado usando SQL.

Você pode usar uma instrução CREATE EXTERNAL TABLE AS SELECT (CETAS) em um pool SQL dedicado ou pool SQL sem servidor para persistir os resultados de uma consulta em uma tabela externa, que armazena seus dados em um arquivo no data lake.

A instrução CETAS inclui uma instrução SELECT que consulta e manipula dados de qualquer fonte de dados válida (que pode ser uma tabela ou exibição existente em um banco de dados ou uma função OPENROWSET que lê dados baseados em arquivo do data lake). Os resultados da instrução SELECT são então persistidos em uma tabela externa, que é um objeto de metadados em um banco de dados que fornece uma abstração relacional sobre os dados armazenados em arquivos. O diagrama a seguir ilustra o conceito visualmente:

Um diagrama mostrando uma instrução CREATE EXTERNAL TABLE AS SELECT salvando os resultados da consulta como um arquivo.

Aplicando essa técnica, você pode usar SQL para extrair e transformar dados de arquivos ou tabelas e armazenar os resultados transformados para processamento ou análise downstream. As operações subsequentes nos dados transformados podem ser executadas na tabela relacional no banco de dados do pool SQL ou diretamente nos arquivos de dados subjacentes.

Criação de objetos de base de dados externos para suportar o CETAS

Para usar expressões CETAS, você deve criar os seguintes tipos de objeto em um banco de dados para um pool SQL dedicado ou sem servidor. Ao usar um pool SQL sem servidor, crie esses objetos em um banco de dados personalizado (criado usando a CREATE DATABASE instrução), não no banco de dados interno.

Fonte de dados externa

Uma fonte de dados externa encapsula uma conexão com um local do sistema de arquivos em um data lake. Em seguida, você pode usar essa conexão para especificar um caminho relativo no qual os arquivos de dados para a tabela externa criada pela instrução CETAS são salvos.

Se os dados de origem da instrução CETAS estiverem em arquivos no mesmo caminho do data lake, você poderá usar a mesma fonte de dados externa na função OPENROWSET usada para consultá-la. Como alternativa, você pode criar uma fonte de dados externa separada para os arquivos de origem ou usar um caminho de arquivo totalmente qualificado na função OPENROWSET.

Para criar uma fonte de dados externa, use a CREATE EXTERNAL DATA SOURCE instrução, conforme mostrado neste exemplo:

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

O exemplo anterior pressupõe que os usuários que executam consultas que usam a fonte de dados externa terão permissões suficientes para acessar os arquivos. Uma abordagem alternativa é encapsular uma credencial na fonte de dados externa para que ela possa ser usada para acessar dados de arquivo sem conceder a todos os usuários permissões para lê-la diretamente:

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

Gorjeta

Além da autenticação SAS, você pode definir credenciais que usam identidade gerenciada (a identidade do Microsoft Entra usada pelo espaço de trabalho do Azure Synapse), uma entidade de segurança específica do Microsoft Entra ou autenticação de passagem com base na identidade do usuário que executa a consulta (que é o tipo padrão de autenticação). Para saber mais sobre como usar credenciais em um pool SQL sem servidor, consulte o artigo Controlar o acesso à conta de armazenamento para pool SQL sem servidor no Azure Synapse Analytics na documentação do Azure Synapse Analytics.

Formato de ficheiro externo

A declaração CETAS cria uma tabela com os seus dados armazenados em ficheiros. Você deve especificar o formato dos arquivos que deseja criar como um formato de arquivo externo.

Para criar um formato de arquivo externo, use a CREATE EXTERNAL FILE FORMAT instrução, conforme mostrado neste exemplo:

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

Gorjeta

Neste exemplo, os arquivos serão salvos no formato Parquet. Você também pode criar formatos de arquivo externos para outros tipos de arquivo. Consulte CREATE EXTERNAL FILE FORMAT (Transact-SQL) para obter detalhes.

Utilização da declaração CETAS

Depois de criar uma fonte de dados externa e um formato de arquivo externo, você pode usar a instrução CETAS para transformar dados e armazenar os resultados em uma tabela externa.

Por exemplo, suponha que os dados de origem que você deseja transformar consistem em ordens de venda em arquivos de texto delimitados por vírgulas armazenados em uma pasta em um data lake. Você deseja filtrar os dados para incluir apenas pedidos marcados como "ordem especial" e salvar os dados transformados como arquivos Parquet em uma pasta diferente no mesmo data lake. Você pode usar a mesma fonte de dados externa para as pastas de origem e de destino, conforme mostrado neste exemplo:

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

Os LOCATION parâmetros e BULK no exemplo anterior são caminhos relativos para os resultados e arquivos de origem, respectivamente. Os caminhos são relativos ao local do sistema de arquivos referenciado pela fonte de dados externa dos arquivos .

Um ponto importante a entender é que você deve usar uma fonte de dados externa para especificar o local onde os dados transformados para a tabela externa devem ser salvos. Quando os dados de origem baseados em arquivo são armazenados na mesma hierarquia de pastas, você pode usar a mesma fonte de dados externa. Caso contrário, você pode usar uma segunda fonte de dados para definir uma conexão com os dados de origem ou usar o caminho totalmente qualificado, conforme mostrado neste exemplo:

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

Descartando mesas externas

Se você não precisar mais da tabela externa que contém os dados transformados, poderá soltá-la do banco de dados usando a DROP EXTERNAL TABLE instrução, conforme mostrado aqui:

DROP EXTERNAL TABLE SpecialOrders;