Condividi tramite

Modalità di transazione

Importante

Le transazioni che scrivono nelle tabelle Delta gestite del catalogo Unity sono in anteprima pubblica.

Le transazioni che scrivono nelle tabelle Iceberg gestite dal catalogo Unity sono in anteprima privata. Per partecipare a questa anteprima, inviare il modulo di iscrizione per l'anteprima delle tabelle Iceberg gestite.

Le transazioni supportano due modalità: non interattive e interattive. Questa pagina illustra quando usare ogni modalità e include esempi di implementazione.

Per i requisiti e una panoramica delle transazioni, vedere Transazioni. Per una pratica pratica con entrambe le modalità, vedere Esercitazione: Coordinare le transazioni tra tabelle.

Annotazioni

Tutte le tabelle scritte in in un'istruzione multipla, una transazione a più tabelle deve:

Transazioni non interattive

Le transazioni non interattive usano script SQL con la ATOMIC parola chiave . Il blocco di istruzione composta ATOMIC esegue tutte le istruzioni come una singola unità atomica. Tutti riescono insieme o tutti falliscono insieme.

Calcolo supportato: qualsiasi SQL Warehouse, Serverless Compute o cluster in esecuzione con Databricks Runtime 18.0 e versioni successive.

Sintassi supportata: supporta i blocchi SQL, Scala spark.sql e PySpark spark.sql .

Annotazioni

È possibile usare transazioni non interattive all'interno di forEachBatch Structured Streaming chiamando spark.sql("BEGIN ATOMIC ... END;"). Tuttavia, i checkpoint Structured Streaming non avanzano transazionalmente.

Sintassi

BEGIN ATOMIC
  statement1;
  statement2;
  statement3;
END;

Azure Databricks esegue automaticamente il commit di tutte le modifiche se tutte le istruzioni hanno esito positivo. Se un'istruzione ha esito negativo, Azure Databricks esegue automaticamente il rollback di tutte le modifiche.

Usare nell'editor SQL

Eseguire transazioni non interattive direttamente nell'editor SQL. Selezionare l'intero blocco di istruzioni composte ATOMIC ed eseguirlo come singola istruzione:

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;

Usare nei notebook

Eseguire transazioni non interattive nei notebook usando celle SQL o API a livello di codice.

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

Usare nei processi pianificati

Le transazioni non interattive funzionano bene nei processi pianificati perché gestiscono automaticamente il commit e il 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 un'istruzione ha esito negativo, inclusa l'asserzione, viene eseguito automaticamente il rollback dell'intera transazione.

Usare con JDBC

I client esterni possono eseguire transazioni non interattive.

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

Usare con l'API di esecuzione delle istruzioni

Eseguire transazioni non interattive usando il Statement Execution API:

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"
    }
)

Modelli ETL

I modelli seguenti illustrano flussi di lavoro ETL comuni che usano transazioni non interattive.

Modello di gestione temporanea e convalida

Questo modello carica i dati in un'area di gestione temporanea, convalida la qualità dei dati e unisce i record convalidati nelle tabelle di produzione:

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;

Modello di tabella delle dimensioni e dei fatti

Questo modello aggiorna le tabelle delle dimensioni prima di caricare le tabelle dei fatti per mantenere l'integrità referenziale:

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;

Gestione degli errori

Quando un'istruzione ha esito negativo all'interno di un BEGIN ATOMIC ... END; blocco, Azure Databricks esegue il rollback di tutte le modifiche e restituisce un messaggio di errore.

Suggerimenti per il debug:

  1. Esaminare il messaggio di errore per identificare quale istruzione è fallita.
  2. Verificare le istruzioni singolarmente all'esterno del blocco di transazione.
  3. Aggiungere controlli di convalida usando SIGNAL per non riuscire con messaggi di errore personalizzati.
  4. Eseguire query sulla cronologia delle transazioni per un contesto aggiuntivo.

Transazioni interattive

Le transazioni interattive offrono un controllo esplicito sui limiti delle transazioni. Si avvia manualmente una transazione, si eseguono le istruzioni e si esegue il commit o il rollback esplicito.

Calcolo supportato: solo SQL Warehouse.

Sintassi supportata: solo SQL.

Sintassi

BEGIN TRANSACTION;

statement1;
statement2;

COMMIT;
-- or: ROLLBACK;

Convalida prima di eseguire il commit

Usare transazioni interattive per convalidare i risultati prima del commit:

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;

Rollback esplicito

Eseguire il rollback di una transazione quando la convalida non riesce o la logica di business richiede l'eliminazione delle modifiche:

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;

Usare con JDBC

Il driver JDBC supporta l'esecuzione di istruzioni DML usando executeUpdate() all'interno delle transazioni. Per un elenco delle istruzioni DML supportate, vedere Operazioni supportate.

I client JDBC usano transazioni interattive disabilitando la modalità di commit automatico:

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

Operazioni JDBC non supportate

Le operazioni JDBC seguenti non sono supportate nelle transazioni interattive:

Categoria Non supportato
Cambio di catalogo o schema Connection.setCatalog() e Connection.setSchema()
Modifiche alla configurazione della sessione Connection.setClientInfo() per proprietà a livello di sessione, come TIMEZONE e ANSI_MODE
Tutti i databaseMetaData (tutti i protocolli) Tutti i DatabaseMetaData.* metodi
Metadati PreparedStatement PreparedStatement.getMetaData()
Procedure memorizzate CALL procedure_name()

Usare con ODBC

Il driver ODBC supporta l'esecuzione di istruzioni DML tramite SQLExecute() e SQLExecDirect() all'interno di transazioni. Per un elenco delle istruzioni DML supportate, vedere Operazioni supportate.

I client ODBC possono usare transazioni interattive con il driver ODBC di Azure Databricks usando le funzioni standard di gestione delle transazioni ODBC.

Operazioni ODBC non supportate

Le operazioni ODBC seguenti non sono supportate nelle transazioni interattive:

Categoria Non supportato
Tutte le funzioni del catalogo SQLTables, SQLColumns, SQLStatistics, SQLSpecialColumns, SQLPrimaryKeys, SQLForeignKeys, SQLTablePrivileges, SQLColumnPrivileges, SQLProcedures, SQLProcedureColumns
Impostazione degli attributi di connessione Cambio del catalogo, modifiche al livello di isolamento e modifiche alla modalità di accesso tramite SQLSetConnectAttr()
Traduzione SQL SQLNativeSql

Usare con il connettore SQL di Databricks per Python

Il connettore SQL di Databricks per Python supporta l'esecuzione di istruzioni DML usando cursor.execute() all'interno delle transazioni. Per un elenco delle istruzioni DML supportate, vedere Operazioni supportate.

Le applicazioni Python possono usare transazioni interattive con il connettore SQL di Databricks per Python impostando 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()

Operazioni del connettore Python non supportate

Le operazioni del connettore Python seguenti non sono supportate nelle transazioni interattive:

Categoria Non supportato
Tutti i metadati cursor.catalogs(), cursor.schemas(), cursor.tables()cursor.columns()

Limitazioni del driver per le transazioni interattive

Le limitazioni seguenti si applicano a tutti i driver quando si usano transazioni interattive.

Le operazioni sui metadati non sono supportate all'interno di transazioni interattive. Le operazioni seguenti potrebbero non riuscire all'interno di una transazione indipendentemente dal driver o dal protocollo:

Driver/protocollo Tipo Methods
JDBC DatabaseMetaData getCatalogs(), getSchemas(), getTables(), , getColumns(), getTypeInfo()
ODBC Funzioni del catalogo SQLTables, SQLColumns, SQLGetTypeInfo
Connettore Python Metodi di metadati cursor.catalogs(), cursor.schemas(), cursor.tables()cursor.columns()
SQL Comandi di metadati SHOW TABLES, SHOW DATABASES, DESCRIBE TABLE, , USE CATALOG, USE SCHEMA
SQL information_schema SELECT interrogazioni contro information_schema tabelle

Eseguire tutte le operazioni sui metadati all'esterno delle transazioni.

Avviso

L'esecuzione di transazioni su più thread in un singolo oggetto connessione driver comporta un comportamento non definito. Eseguire una sola transazione alla volta su ogni oggetto connessione.

Comportamento di isolamento

Le modifiche di cui non è stato eseguito il commit in una transazione interattiva sono visibili solo alla sessione. Altre sessioni visualizzano lo stato della tabella come prima dell'inizio della transazione.

Annotazioni

Le transazioni interattive usano un rilevamento dei conflitti più conservativo rispetto alle transazioni non interattive e possono essere in conflitto a livello di tabella (ad eccezione degli apponimenti non condizionali). Per il rilevamento dei conflitti a livello di riga, usare transazioni non interattive (BEGIN ATOMIC ... END;).

  1. Per verificare l'isolamento, creare la tabella di esempio se non esiste:
CREATE TABLE IF NOT EXISTS sample_accounts (
  id INT,
  account_name STRING,
  balance DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

  1. Nella stessa sessione avviare una transazione e apportare una modifica:

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

  2. In una scheda o in una sessione del notebook separata dell'editor SQL (non una nuova cella nello stesso notebook), eseguire una query sulla tabella:

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

    Restituisce 0 righe perché la modifica di cui non è stato eseguito il commit non è visibile all'esterno della prima sessione.

  3. Tornare alla prima sessione ed eseguire il commit:

    COMMIT;

  4. Eseguire di nuovo una query dalla seconda sessione:

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

    La riga è visibile perché la transazione è stata confermata.

Questo isolamento impedisce ad altri utenti di leggere i dati che potrebbero essere annullati.

Scegliere una modalità di transazione

Scenario Modalità consigliata
Processi ETL pianificati Non interattivo: il commit automatico o il rollback semplifica la gestione degli errori
Sequenze di dichiarazioni fisse Non interattiva: sintassi più semplice, nessun commit manuale necessario
Convalida dei dati prima del commit Interattivo: controllare i risultati e decidere se eseguire il commit
Applicazioni JDBC che richiedono il controllo manuale Interattivo: modelli di transazione di database standard

Passaggi successivi

  • Last updated on