Modos de transação

As transações no Azure Databricks suportam dois modos:

• Não interativo (BEGIN ATOMIC): Confirma ou faz reversão automaticamente. Ideal para trabalhos agendados e sequências de instruções fixas.
• Interativo (BEGIN TRANSACTION): Permite-lhe o controlo manual do commit e do rollback. Ideal para validação, depuração e clientes JDBC.

Para requisitos e uma visão geral das transações, consulte Transações. Para prática prática com ambos os modos, veja Tutorial: Coordenar transações entre tabelas.

Transações não interativas

Transações não interativas utilizam scripts SQL com a ATOMIC palavra-chave. O bloco de instruções compostas ATOMIC executa todas as instruções como uma única unidade atómica. Todos têm sucesso juntos ou todos falham juntos.

Computação suportada: Qualquer SQL warehouse, computação serverless ou cluster que execute Databricks Runtime 18.0 e superior.

Sintaxe suportada: Suporta SQL, blocos Scala spark.sql e blocos PySpark spark.sql .

Sintaxe

BEGIN ATOMIC
  statement1;
  statement2;
  statement3;
END;

O Azure Databricks confirma automaticamente todas as alterações se todas as instruções tiverem sucesso. Se alguma instrução falhar, o Azure Databricks reverte automaticamente todas as alterações.

Utilização no editor SQL

Execute transações não interativas diretamente no Editor SQL. Seleciona todo o bloco de instruções compostas ATOMIC e executa-o como uma única instrução:

BEGIN ATOMIC
  DELETE FROM staging_sales WHERE load_date < current_date() - INTERVAL 7 DAYS;

  INSERT INTO staging_sales
  SELECT * FROM raw_sales WHERE load_date = current_date();

  MERGE INTO sales AS target
  USING staging_sales AS source
  ON target.sale_id = source.sale_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;
END;

Utilização em cadernos

Execute transações não interativas em cadernos usando células SQL ou APIs programáticas.

SQL

BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;

Python

spark.sql("""
BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;
""")

Scala

spark.sql("""
BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;
""")

Utilização em tarefas agendadas

Transações não interativas funcionam bem em trabalhos agendados porque tratam automaticamente de commit e rollback:

BEGIN ATOMIC
  -- Clear previous staging data
  DELETE FROM staging_daily_sales WHERE load_date = current_date();

  -- Load new data
  INSERT INTO staging_daily_sales
  SELECT sale_id, customer_id, amount, sale_date, current_date() as load_date
  FROM raw_sales
  WHERE sale_date = current_date() - INTERVAL 1 DAY;

  -- Validate row count (fails transaction if no data)
  IF (SELECT COUNT(*) FROM staging_daily_sales WHERE load_date = current_date()) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No sales data loaded for yesterday';
  END IF;

  -- Merge into production
  MERGE INTO daily_sales AS target
  USING staging_daily_sales AS source
  ON target.sale_id = source.sale_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;
END;

Se alguma instrução falhar, incluindo a asserção, toda a transação reverte automaticamente.

Uso com JDBC

Clientes externos podem executar transações não interativas.

JDBC

String sql = """
    BEGIN ATOMIC
      INSERT INTO orders (order_id, total) VALUES (1001, 500.00);
      UPDATE customers SET last_order = CURRENT_DATE() WHERE customer_id = 5001;
    END;
    """;

Statement stmt = conn.createStatement();
stmt.execute(sql);

Uso com API de Execução de Declarações

Execute transações não interativas usando a API de Execução de Instruções:

import requests

sql = """
BEGIN ATOMIC
  INSERT INTO sales (sale_id, amount) VALUES (3001, 750.00);
  UPDATE daily_totals SET total = total + 750.00 WHERE sale_date = CURRENT_DATE();
END;
"""

response = requests.post(
    f"{workspace_url}/api/2.0/sql/statements",
    headers={"Authorization": f"Bearer {token}"},
    json={
        "warehouse_id": warehouse_id,
        "statement": sql,
        "wait_timeout": "30s"
    }
)

Padrões ETL

Os padrões seguintes demonstram fluxos de trabalho ETL comuns usando transações não interativas.

Padrão de staging e validação

Este padrão carrega os dados numa área de staging, valida a qualidade dos dados e funde os registos validados em tabelas de produção:

BEGIN ATOMIC
  -- Load into staging
  INSERT INTO staging_customers
  SELECT * FROM external_source
  WHERE ingest_date = current_date();

  -- Validate data quality
  IF (SELECT COUNT(*) FROM staging_customers WHERE email NOT LIKE '%@%') > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email addresses found';
  END IF;

  -- Merge validated data
  MERGE INTO customers AS target
  USING staging_customers AS source
  ON target.customer_id = source.customer_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  -- Update metadata
  UPDATE etl_metadata
  SET last_load_date = current_date(),
      rows_processed = (SELECT COUNT(*) FROM staging_customers)
  WHERE table_name = 'customers';
END;

Padrão de tabela de dimensão e fatos

Este padrão atualiza as tabelas de dimensões antes de carregar tabelas de factos para manter a integridade referencial:

BEGIN ATOMIC
  -- Update dimension tables first
  MERGE INTO dim_products AS target
  USING staging_products AS source
  ON target.product_id = source.product_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  MERGE INTO dim_customers AS target
  USING staging_customers AS source
  ON target.customer_id = source.customer_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  -- Then load fact table with foreign key references
  INSERT INTO fact_sales
  SELECT s.sale_id, p.product_key, c.customer_key, s.sale_amount, s.sale_date
  FROM staging_sales s
  JOIN dim_products p ON s.product_id = p.product_id
  JOIN dim_customers c ON s.customer_id = c.customer_id;
END;

Tratamento de erros

Quando uma instrução falha dentro de um bloco BEGIN ATOMIC ... END;, Azure Databricks reverte todas as alterações e devolve uma mensagem de erro.

Dicas para depuração:

1. Revise a mensagem de erro para identificar qual a instrução que falhou.
2. Teste instruções individualmente fora do bloco de transação.
3. Adicionar verificações de validação usando SIGNAL para falhar com mensagens de erro personalizadas.
4. Consulte o histórico de transações para obter contexto adicional.

Transações interativas

As transações interativas dão-lhe controlo explícito sobre os limites das transações. Começas manualmente uma transação, executas instruções e confirmas ou reverte explicitamente.

Computação suportada: apenas armazéns SQL .

Sintaxe suportada: apenas SQL.

Sintaxe

Comprometer-se:

BEGIN TRANSACTION;
statement1;
statement2;
COMMIT;

Para recuar:

BEGIN TRANSACTION;
statement1;
statement2;
ROLLBACK;

Valide antes de confirmar

Use transações interativas para validar os resultados antes de comprometer:

BEGIN TRANSACTION;

-- Load staging data
INSERT INTO staging_customers
SELECT * FROM external_customers
WHERE load_date = current_date();

-- Validate and commit or rollback
BEGIN
  DECLARE duplicate_count INT;
  SET duplicate_count = (
    SELECT COUNT(*) FROM (
      SELECT customer_id, COUNT(*) as cnt
      FROM staging_customers
      WHERE load_date = current_date()
      GROUP BY customer_id
      HAVING COUNT(*) > 1
    )
  );

  IF duplicate_count > 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate customers found in staging data';
  ELSE
    MERGE INTO customers AS target
    USING staging_customers AS source
    ON target.customer_id = source.customer_id
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *;
    COMMIT;
  END IF;
END;

Reversão explícita

Reverter uma transação quando a validação falha ou a lógica de negócio exige descartar alterações:

BEGIN TRANSACTION;

UPDATE inventory
SET quantity = quantity - 50
WHERE product_id = 2001;

-- Check if quantity would go negative
BEGIN
  DECLARE new_quantity INT;
  SET new_quantity = (SELECT quantity FROM inventory WHERE product_id = 2001);

  IF new_quantity < 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient inventory for product 2001';
  ELSE
    COMMIT;
  END IF;
END;

Uso com JDBC

O driver JDBC suporta executar instruções DML usando executeUpdate() dentro de transações. Para uma lista de instruções DML suportadas, veja Operações suportadas.

Os clientes JDBC utilizam transações interativas desativando o modo de auto-commit:

Connection conn = DriverManager.getConnection(jdbcUrl, properties);

try {
    conn.setAutoCommit(false);  // Start transaction mode
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("INSERT INTO accounts (account_id, balance) VALUES (1001, 5000)");
    stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001");

    conn.commit();  // Commit the transaction

} catch (SQLException e) {
    conn.rollback();  // Roll back on error
    throw e;
} finally {
    conn.close();
}

Operações JDBC não suportadas

As seguintes operações JDBC não são suportadas em transações interativas:

Utilização com ODBC

O driver ODBC suporta a execução de instruções DML usando SQLExecute() e SQLExecDirect() dentro de transações. Para uma lista de instruções DML suportadas, veja Operações suportadas.

Os clientes ODBC podem usar transações interativas com o driver ODBC do Azure Databricks, utilizando funções padrão de gestão de transações ODBC.

Operações ODBC não suportadas

As seguintes operações ODBC não são suportadas em transações interativas:

Use com o Databricks SQL Connector para Python

O Databricks SQL Connector para Python suporta a execução de instruções DML usando cursor.execute() dentro das transações. Para uma lista de instruções DML suportadas, veja Operações suportadas.

Python aplicações podem usar transações interativas com o Conector SQL Databricks para Python definindo autocommit=False:

from databricks import sql

with sql.connect(
    server_hostname="dbc-a1b2345c-d6e7.cloud.databricks.com",
    http_path="sql/1.0/warehouses/abc123def456",
    access_token="your-access-token",
    autocommit=False
) as connection:
    with connection.cursor() as cursor:
        cursor.execute("INSERT INTO accounts (account_id, balance) VALUES (1001, 5000)")
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001")
        connection.commit()

Operações de conector de Python não suportadas

As seguintes operações de conector Python não são suportadas em transações interativas:

Limitações do driver para transações interativas

As seguintes limitações aplicam-se a todos os condutores ao utilizar transações interativas.

As operações de metadados não são suportadas dentro de transações interativas. As seguintes operações podem falhar numa transação, independentemente do driver ou protocolo:

A Databricks recomenda que execute estas operações de metadados fora das transações.

Comportamento de isolamento

Alterações não comprometidas numa transação interativa só são visíveis para a sua sessão. As alterações só são visíveis para outras sessões depois de executar COMMIT;.

1. Para verificar o isolamento, crie a tabela de exemplo caso esta não exista:

CREATE TABLE IF NOT EXISTS sample_accounts (
  id INT,
  account_name STRING,
  balance DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

1. Nessa mesma sessão, inicia uma transação e faz uma alteração:

   BEGIN TRANSACTION;
   INSERT INTO sample_accounts VALUES (10, 'Test', 100.00);
   

2. Numa aba separada do Editor SQL ou numa sessão de caderno (não numa célula nova no mesmo caderno), consulta a tabela:

   -- Run this in the SECOND session
   SELECT * FROM sample_accounts WHERE id = 10;
   

   Isto devolve 0 linhas, porque a alteração não confirmada não é visível fora da sua primeira sessão.

3. Regresse à sua primeira sessão e comprometa-se:

   COMMIT;
   

4. Consulta da segunda sessão novamente:

   -- Run this in the SECOND session
   SELECT * FROM sample_accounts WHERE id = 10;
   

   A linha é visível devido à transação ter sido concluída.

Este isolamento impede que outros utilizadores leiam dados que possam ser revertidos.

Escolha um modo de transação

Recursos adicionais

• Tutorial: Coordenar transações entre tabelas
• Transações
• Catálogo de commits
• Níveis de isolamento e conflitos de escrita
• Instrução composta ATOMIC (transações não interativas): Executar múltiplas instruções SQL como uma única transação atómica com commit e rollback automáticos.
• INICIAR TRANSAÇÃO (transações interativas): Iniciar uma transação interativa com controlo manual de confirmação e reversão.
• COMMIT: Confirmar uma transação em modo interativo e tornar todas as alterações permanentes.
• ROLLBACK: Reverter uma transação interativa e descartar todas as alterações.