Ingerir dados com métodos SQL

Os comandos SQL fornecem uma abordagem declarativa para a ingestão de dados no Azure Databricks. Se já se sente confortável com a sintaxe SQL, estes métodos permitem-lhe criar e preencher tabelas sem precisar de escrever código procedural. As três principais técnicas de ingestão SQL —CREATE TABLE AS SELECT (CTAS) , CREATE OR REPLACE TABLE, e COPY INTO— abordam diferentes cenários de ingestão, mantendo total compatibilidade com o Unity Catalog.

Criar tabelas a partir de consultas com CTAS

A CREATE TABLE AS SELECT instrução combina a criação de tabelas e a população de dados numa única operação. É definida uma nova tabela com base nos resultados de uma consulta SELECT, tornando-a ideal para transformar dados durante o processo de ingestão.

Considere um cenário em que precisa de ingerir dados do cliente de uma fonte externa e aplicar transformações. Com o CTAS, escreves uma consulta que lê da fonte, aplica as tuas transformações e guarda os resultados numa nova tabela gerida:

CREATE TABLE catalog.schema.customers AS
SELECT 
    customer_id,
    UPPER(customer_name) AS customer_name,
    email,
    created_date
FROM external_staging.raw_customers
WHERE customer_status = 'active';

O esquema da tabela é automaticamente derivado dos resultados da consulta. Azure Databricks cria a tabela usando o formato Delta por predefinição, que fornece transações ACID, viagens temporais e desempenho otimizado.

O CTAS funciona bem para cargas iniciais de dados e migrações pontuais. Quando precisar de ler dados de ficheiros, combine o CTAS com a read_files função de valores de tabela:

CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/sales/*.parquet',
    format => 'parquet'
);

Atualizar tabelas com CRIAR OU SUBSTITUIR TABELA

Quando precisa de atualizar totalmente o conteúdo de uma tabela, CREATE OR REPLACE TABLE fornece uma solução limpa. Este comando cria uma nova tabela ou substitui completamente uma existente, preservando o histórico da tabela, os privilégios concedidos e quaisquer filtros de linha ou máscaras de coluna que tenha configurado.

Esta abordagem revela-se valiosa para atualizações periódicas de dados onde se pretende substituir todos os dados existentes:

CREATE OR REPLACE TABLE catalog.schema.daily_metrics AS
SELECT 
    report_date,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.transactions
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY report_date;

Ao contrário de eliminar e recriar uma tabela, CREATE OR REPLACE mantém os metadados e permissões da tabela. Os utilizadores e aplicações a jusante continuam a aceder à tabela sem reconfiguração.

Também pode usar este comando para carregar dados diretamente a partir de ficheiros. O esquema é automaticamente inferido a partir dos resultados da consulta:

CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/products.csv',
    format => 'csv',
    header => true
);

Carregar ficheiros incrementalmente com COPY INTO

COPY INTO Resolve um desafio comum de ingestão: carregar ficheiros a partir do armazenamento na cloud fiavelmente e repetidamente. Ao contrário do CTAS, que é executado uma vez e cria uma tabela, COPY INTO é concebido para fluxos de trabalho contínuos de ingestão, onde novos ficheiros chegam regularmente.

O comando lê ficheiros a partir de uma localização especificada e adiciona-os a uma tabela Delta existente. A sua principal característica é a idempotência — ficheiros que já foram carregados são automaticamente saltados, mesmo em múltiplas execuções:

COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');

Antes de executar COPY INTO, a tabela alvo deve já existir. Crie-o com o esquema apropriado:

CREATE TABLE IF NOT EXISTS catalog.schema.events (
    event_id STRING,
    event_type STRING,
    event_timestamp TIMESTAMP,
    payload STRING
);

Configurar seleção de ficheiros

Quando o seu diretório fonte contém ficheiros com padrões de nomenclatura diferentes ou precisa de carregar ficheiros específicos, use as PATTERN opções ou:FILES

-- Load only files matching a pattern
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
PATTERN = 'orders_2024*.parquet';

-- Load specific files by name
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
FILES = ('orders_001.parquet', 'orders_002.parquet');

Lidar com o esquema e a qualidade dos dados

COPY INTO Fornece opções para lidar com alterações de esquema e validar dados antes do carregamento:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
    'header' = 'true',
    'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');

A mergeSchema opção permite que o esquema da tabela evolua à medida que novas colunas aparecem nos ficheiros fonte. Para validar dados sem os carregar, adicione a cláusula VALIDE:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;

Esta validação verifica se os dados podem ser analisados, corresponde ao esquema da tabela e satisfaz as restrições de anulabilidade e verificação.

Escolha o método certo

Cada método de ingestão SQL serve propósitos distintos nos seus fluxos de trabalho de engenharia de dados:

Para ingestão baseada em ficheiros que requer inferência automática de esquemas, notificações de ficheiros ou garantias de execução única, considere usar o Auto Loader como método complementar. Quando as suas necessidades de ingestão são simples e prefere SQL declarativo em vez de código procedimental, estes três métodos fornecem um conjunto completo de ferramentas para gerir o fluxo de dados no Unity Catalog.