Flusso di aggiornamenti delle tabelle con Change Data Capture

BigQuery Change Data Capture (CDC) aggiorna le tabelle BigQuery mediante l'elaborazione e l'applicazione di flussi di modifiche ai dati esistenti. La sincronizzazione avviene mediante operazioni di upsert ed eliminazione delle righe trasmesse in flusso in tempo reale dall'API BigQuery Storage Scrivi, che dovresti conoscere prima di procedere.

Prima di iniziare

Concedi ruoli IAM (Identity and Access Management) che concedono agli utenti le autorizzazioni necessarie per eseguire ogni attività in questo documento e assicurati che il flusso di lavoro soddisfi ogni prerequisito.

Autorizzazioni obbligatorie

Per ottenere l'autorizzazione necessaria per utilizzare l'API StorageWrite, chiedi all'amministratore di concederti il ruolo IAM Editor dati BigQuery (roles/bigquery.dataEditor). Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso.

Questo ruolo predefinito contiene l'autorizzazione bigquery.tables.updateData, necessaria per utilizzare l'API StorageWrite.

Potresti riuscire a ottenere questa autorizzazione anche con i ruoli personalizzati o altri ruoli predefiniti.

Per ulteriori informazioni su ruoli e autorizzazioni IAM in BigQuery, consulta Introduzione a IAM.

Prerequisiti

Per utilizzare BigQuery CDC, il flusso di lavoro deve soddisfare le seguenti condizioni:

  • Devi utilizzare l'API StorageWrite nel flusso predefinito.
  • Devi dichiarare le chiavi primarie per la tabella di destinazione in BigQuery. Sono supportate chiavi primarie composte con un massimo di 16 colonne.
  • Per eseguire le operazioni con le righe CDC, devono essere disponibili risorse di calcolo BigQuery sufficienti. Tieni presente che se le operazioni di modifica delle righe CDC non riescono, potresti conservare involontariamente i dati che intendevi eliminare. Per maggiori informazioni, vedi Considerazioni sull'eliminazione dei dati.

Specifica le modifiche ai record esistenti

Nella CDC di BigQuery, la pseudo-colonna _CHANGE_TYPE indica il tipo di modifica da elaborare per ogni riga. Per utilizzare la tecnologia CDC, imposta _CHANGE_TYPE quando esegui il flusso di modifiche delle righe utilizzando l'API Storage Writer. La pseudo-colonna _CHANGE_TYPE accetta solo i valori UPSERT e DELETE. Una tabella è considerata abilitata per la CDC mentre l'API StorageWrite esegue il flusso di modifiche delle righe alla tabella in questo modo.

Esempio con valori UPSERT e DELETE

Considera la seguente tabella in BigQuery:

ID Nome Retribuzione
100 Fattura 2000
101 Lucia 3000
102 Roberto 5000

Le seguenti modifiche alle righe vengono trasmesse in streaming dall'API StorageWrite:

ID Nome Retribuzione _CHANGE_TYPE
100 ELIMINA
101 Lucia 8000 UPSERT
105 Max 6000 UPSERT

La tabella aggiornata è ora la seguente:

ID Nome Retribuzione
101 Lucia 8000
102 Roberto 5000
105 Max 6000

Gestisci l'inattività della tabella

Per impostazione predefinita, ogni volta che esegui una query, BigQuery restituisce i risultati più aggiornati. Per fornire i risultati più recenti quando si esegue una query su una tabella abilitata per CDC, BigQuery deve applicare ogni modifica di righe in modalità flusso fino all'ora di inizio della query, in modo che venga eseguita la query sulla versione più aggiornata della tabella. L'applicazione di queste modifiche alle righe al momento dell'esecuzione della query ne aumenta il costo e la latenza. Tuttavia, se non hai bisogno di risultati delle query completamente aggiornati, puoi ridurre i costi e la latenza delle query impostando l'opzione max_staleness nella tabella. Se questa opzione è impostata, BigQuery applica le modifiche alle righe almeno una volta nell'intervallo definito dal valore max_staleness, consentendoti di eseguire query senza attendere l'applicazione degli aggiornamenti, a scapito dell'inattività dei dati.

Questo comportamento è particolarmente utile per le dashboard e i report per i quali l'aggiornamento dei dati non è essenziale. Inoltre, è utile per la gestione dei costi, in quanto offre un maggiore controllo sulla frequenza con cui BigQuery applica le modifiche alle righe.

Query sulle tabelle con l'opzione max_staleness impostata

Quando esegui una query su una tabella con l'opzione max_staleness impostata, BigQuery restituisce il risultato in base al valore di max_staleness e all'ora in cui si è verificato l'ultimo job di applicazione, rappresentato dal timestamp upsert_stream_apply_watermark della tabella.

Considera l'esempio seguente, in cui una tabella ha l'opzione max_staleness impostata su 10 minuti e il job di applicazione più recente si è verificato al T20:

Il tempo di esecuzione della query rientra nell'intervallo di tempo massimo per l'inattività dei dati.

Se esegui una query sulla tabella al T25, la versione attuale della tabella è obsoleta per 5 minuti, ovvero inferiore all'intervallo max_staleness di 10 minuti. In questo caso, BigQuery restituisce la versione della tabella al T20, il che significa che anche i dati restituiti sono inattivi per 5 minuti.

Quando imposti l'opzione max_staleness nella tabella, BigQuery applica le modifiche alle righe in attesa almeno una volta entro l'intervallo max_staleness. In alcuni casi, tuttavia, BigQuery potrebbe non completare il processo di applicazione di queste modifiche alle righe in sospeso nell'intervallo.

Ad esempio, se esegui una query sulla tabella T35 e il processo di applicazione delle modifiche alle righe in sospeso non è stato completato, la versione corrente della tabella sarà obsoleta per 15 minuti, ossia maggiore dell'intervallo max_staleness di 10 minuti. In questo caso, al momento dell'esecuzione della query, BigQuery applica tutte le modifiche alle righe tra T20 e T35 per la query attuale, il che significa che i dati sottoposti a query sono completamente aggiornati, a scapito di una latenza aggiuntiva delle query. Questo è considerato un job di unione di runtime.

Il tempo di esecuzione della query non rientra nell'intervallo di tempo massimo per l'inattività dei dati.

Il valore max_staleness di una tabella in genere deve essere il più alto dei due seguenti valori:

  • L'inattività massima tollerabile dei dati per il tuo flusso di lavoro.
  • Il doppio del tempo massimo necessario per applicare modifiche con upserted alla tabella, più un po' di buffer aggiuntivo.

Per calcolare il tempo necessario per applicare le modifiche con upserted a una tabella esistente, utilizza la seguente query SQL per determinare la durata del 95° percentile dei job di applicazione in background, oltre a un buffer di sette minuti per consentire la conversione dello spazio di archiviazione ottimizzato per la scrittura (buffer di flusso) di BigQuery.

SELECT
  project_id,
  destination_table.dataset_id,
  destination_table.table_id,
  APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds,
  CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes
FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job
WHERE
  project_id = 'PROJECT_ID'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  AND job_id LIKE "%cdc_background%"
GROUP BY 1,2,3;

Sostituisci PROJECT_ID con l'ID del progetto contenente le tabelle BigQuery che vengono modificate dalla CDC di BigQuery.

La durata dei job di applicazione in background dipende da diversi fattori, tra cui il numero e la complessità delle operazioni CDC eseguite nell'intervallo di inattività, le dimensioni della tabella e la disponibilità delle risorse BigQuery. Per saperne di più sulla disponibilità delle risorse, consulta Dimensioni e monitoraggio delle prenotazioni BACKGROUND.

Crea una tabella con l'opzione max_staleness

Per creare una tabella con l'opzione max_staleness, utilizza l'istruzione CREATE TABLE. L'esempio seguente crea la tabella employees con un limite di max_staleness di 10 minuti:

CREATE TABLE employees (
  id INT64 PRIMARY KEY NOT ENFORCED,
  name STRING)
  CLUSTER BY
    id
  OPTIONS (
    max_staleness = INTERVAL 10 MINUTE);

Modifica l'opzione max_staleness per una tabella esistente

Per aggiungere o modificare un limite di max_staleness in una tabella esistente, utilizza l'istruzione ALTER TABLE. L'esempio seguente modifica il limite max_staleness della tabella employees in 15 minuti:

ALTER TABLE employees
SET OPTIONS (
  max_staleness = INTERVAL 15 MINUTE);

Determina il valore max_staleness attuale di una tabella

Per determinare il valore max_staleness attuale di una tabella, esegui una query sulla vista INFORMATION_SCHEMA.TABLE_OPTIONS. L'esempio seguente controlla il valore attuale di max_staleness della tabella mytable:

SELECT
  option_name,
  option_value
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
  option_name = 'max_staleness'
  AND table_name = 'TABLE_NAME';

Sostituisci quanto segue:

  • DATASET_NAME: il nome del set di dati in cui si trova la tabella abilitata per CDC.
  • TABLE_NAME: il nome della tabella abilitata per CDC.

I risultati mostrano che il valore max_staleness è 10 minuti:

+---------------------+--------------+
| Row |  option_name  | option_value |
+---------------------+--------------+
|  1  | max_staleness | 0-0 0 0:10:0 |
+---------------------+--------------+

Monitora l'avanzamento dell'operazione di upsert della tabella

Per monitorare lo stato di una tabella e verificare quando sono state applicate l'ultima volta le modifiche alle righe, esegui una query sulla vista INFORMATION_SCHEMA.TABLES per ottenere il timestamp upsert_stream_apply_watermark.

L'esempio seguente controlla il valore upsert_stream_apply_watermark della tabella mytable:

SELECT upsert_stream_apply_watermark
FROM DATASET_NAME.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'TABLE_NAME';

Sostituisci quanto segue:

  • DATASET_NAME: il nome del set di dati in cui si trova la tabella abilitata per CDC.
  • TABLE_NAME: il nome della tabella abilitata per CDC.

Il risultato è simile al seguente:

[{
 "upsert_stream_apply_watermark": "2022-09-15T04:17:19.909Z"
}]

Le operazioni di upsert vengono eseguite dall'account di servizio bigquery-adminbot@system.gserviceaccount.com e vengono visualizzate nella cronologia del job del progetto contenente la tabella abilitata per CDC.

Gestisci gli ordini personalizzati

Quando si esegue un flusso di upsert in BigQuery, il comportamento predefinito dell'ordinamento di record con chiavi primarie identiche è determinato dall'ora del sistema BigQuery in cui il record è stato importato in BigQuery. In altre parole, il record importato più di recente con il timestamp più recente ha la precedenza sul record importato in precedenza con un timestamp precedente. Ciò potrebbe non essere sufficiente per alcuni casi d'uso, ad esempio quelli in cui possono verificarsiupert molto frequenti per la stessa chiave primaria in una finestra di tempo molto breve. In questi scenari, potrebbe essere necessaria una chiave di ordinamento fornita dall'utente.

Per configurare le chiavi di ordinamento fornite dall'utente, viene utilizzata la pseudo-colonna _CHANGE_SEQUENCE_NUMBER per indicare l'ordine in cui BigQuery deve applicare i record, in base al valore _CHANGE_SEQUENCE_NUMBER più grande tra due record corrispondenti con la stessa chiave primaria. La pseudo-colonna _CHANGE_SEQUENCE_NUMBER è una colonna facoltativa e accetta solo valori in un formato fisso STRING.

Formato _CHANGE_SEQUENCE_NUMBER

La pseudo-colonna _CHANGE_SEQUENCE_NUMBER accetta solo valori STRING, scritti in un formato fisso. Questo formato fisso utilizza valori STRING scritti in formato esadecimale, separati in sezioni da una barra /. Ogni sezione può essere espressa in massimo 16 caratteri esadecimali e sono consentite fino a quattro sezioni per _CHANGE_SEQUENCE_NUMBER. L'intervallo consentito di _CHANGE_SEQUENCE_NUMBER supporta valori compresi tra 0/0/0/0 e FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF. I valori _CHANGE_SEQUENCE_NUMBER supportano caratteri maiuscoli e minuscoli.

È possibile esprimere le chiavi di ordinamento di base utilizzando un'unica sezione. Ad esempio, per ordinare le chiavi esclusivamente in base al timestamp di elaborazione di un record proveniente da un server applicazioni, potresti utilizzare una sezione: '2024-04-30 11:19:44 UTC', espressa come esadecimale convertendo il timestamp nei millisecondi dall'epoca, in questo caso '18F2EBB6480'. La logica per convertire i dati in esadecimale è responsabilità del client che invia la scrittura a BigQuery utilizzando l'API Storage Scrivi.

Il supporto di più sezioni consente di combinare diversi valori logici di elaborazione in un'unica chiave per casi d'uso più complessi. Ad esempio, per ordinare le chiavi in base al timestamp di elaborazione di un record da un server delle applicazioni, a un numero di sequenza di log e allo stato del record, puoi utilizzare tre sezioni: '2024-04-30 11:19:44 UTC' / '123' / 'complete', ciascuna espressa in formato esadecimale. L'ordine delle sezioni è un aspetto importante per il ranking della logica di elaborazione. BigQuery confronta i valori di _CHANGE_SEQUENCE_NUMBER confrontando la prima sezione e poi la sezione successiva solo se le sezioni precedenti erano uguali.

BigQuery utilizza _CHANGE_SEQUENCE_NUMBER per eseguire l'ordinamento confrontando due o più campi _CHANGE_SEQUENCE_NUMBER come valori numerici non firmati. Considera i seguenti esempi di confronto di _CHANGE_SEQUENCE_NUMBER e i relativi risultati di precedenza:

  • Esempio 1:

    • Record 1: _CHANGE_SEQUENCE_NUMBER = "77"
    • Record 2: _CHANGE_SEQUENCE_NUMBER = "7B"

    Risultato: il record 2 è considerato il record più recente perché "7B" > "77" (ad es. "123" > "119")

  • Esempio 2:

    • Record 1: _CHANGE_SEQUENCE_NUMBER = "FFF/B"
    • Record 2: _CHANGE_SEQUENCE_NUMBER = "FFF/ABC"

    Risultato: il record 2 viene considerato il record più recente perché "FFF/ABC" > "FFF/B" (ad es. "4095/2748" > "4095/11")

  • Esempio 3:

    • Record 1: _CHANGE_SEQUENCE_NUMBER = "BA/FFFFFFFF"
    • Record 2: _CHANGE_SEQUENCE_NUMBER = "ABC"

    Risultato: il record 2 è considerato il record più recente perché "ABC" > "BA/FFFFFFFF" (ad es. "2748" > "186/4294967295")

  • Esempio 4:

    • Record 1: _CHANGE_SEQUENCE_NUMBER = "FFF/ABC"
    • Record 2: _CHANGE_SEQUENCE_NUMBER = "ABC"

    Risultato: il record 1 viene considerato il record più recente perché "FFF/ABC" > "ABC" (ad es. "4095/2748" > "2748")

Se due valori _CHANGE_SEQUENCE_NUMBER sono identici, il record con il tempo di importazione del sistema BigQuery più recente ha la precedenza sui record importati in precedenza.

Configura una prenotazione BigQuery da utilizzare con CDC

Puoi utilizzare le prenotazioni BigQuery per allocare risorse di calcolo BigQuery dedicate per le operazioni di modifica delle righe CDC. Le prenotazioni ti consentono di impostare un limite al costo dell'esecuzione di queste operazioni. Questo approccio è particolarmente utile per i flussi di lavoro con frequenti operazioni CDC su tabelle di grandi dimensioni, che altrimenti avrebbero costi on demand elevati a causa dell'elevato numero di byte elaborati durante l'esecuzione di ciascuna operazione.

I job CDC di BigQuery che applicano modifiche di riga in sospeso nell'intervallo max_staleness sono considerati job in background e utilizzano il tipo di assegnazione BACKGROUND anziché il tipo di assegnazione QUERY. Al contrario, le query al di fuori dell'intervallo max_staleness che richiedono l'applicazione di modifiche alle righe al momento dell'esecuzione della query utilizzano il tipo di assegnazione QUERY. I job in background CDC di BigQuery eseguiti senza assegnazione BACKGROUND sfruttano i prezzi on demand. Questa considerazione è importante al momento di progettare la strategia di gestione dei carichi di lavoro per CDC di BigQuery.

Per configurare una prenotazione BigQuery da utilizzare con CDC, inizia acquistando un impegno di capacità e configurando una prenotazione nella regione in cui si trovano le tabelle BigQuery. Per indicazioni sulle dimensioni della prenotazione, consulta Dimensioni e monitoraggio delle prenotazioni di BACKGROUND. Dopo aver creato una prenotazione, assign il progetto BigQuery alla prenotazione e imposta l'opzione job_type su BACKGROUND eseguendo la seguente istruzione CREATE ASSIGNMENT:

CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

Sostituisci quanto segue:

  • ADMIN_PROJECT_ID: l'ID del progetto di amministrazione proprietario della prenotazione.
  • LOCATION: la località della prenotazione.
  • RESERVATION_NAME: il nome della prenotazione.
  • ASSIGNMENT_ID: l'ID del compito. L'ID deve essere univoco per il progetto e la località, iniziare e terminare con una lettera minuscola o un numero e contenere solo lettere minuscole, numeri e trattini.
  • PROJECT_ID: l'ID del progetto contenente le tabelle BigQuery modificate dalla CDC di BigQuery. Questo progetto è assegnato alla prenotazione.

Dimensione e monitoraggio delle prenotazioni BACKGROUND

Le prenotazioni determinano la quantità di risorse di calcolo disponibili per eseguire le operazioni di calcolo di BigQuery. Il sottodimensionamento di una prenotazione può aumentare i tempi di elaborazione delle operazioni di modifica delle righe CDC. Per dimensionare in modo accurato una prenotazione, monitora il consumo storico degli slot per il progetto che esegue le operazioni CDC eseguendo una query sulla vista INFORMATION_SCHEMA.JOBS_TIMELINE:

SELECT
  period_start,
  SUM(period_slot_ms) / (1000 * 60) AS slots_used
FROM
  REGION.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE
  DATE(job_creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND CURRENT_DATE()
  AND job_id LIKE '%cdc_background%'
GROUP BY
  period_start
ORDER BY
  period_start DESC;

Sostituisci REGION con il nome della regione in cui si trova il progetto. Ad esempio, region-us.

Considerazioni sull'eliminazione dei dati

  • Le operazioni CDC di BigQuery sfruttano le risorse di calcolo di BigQuery. Se le operazioni CDC sono configurate per utilizzare la fatturazione on demand, le operazioni CDC vengono eseguite regolarmente utilizzando risorse BigQuery interne. Se le operazioni CDC sono configurate con una prenotazione BACKGROUND, sono invece soggette alla disponibilità delle risorse della prenotazione configurata. Se non sono disponibili risorse sufficienti all'interno della prenotazione configurata, l'elaborazione delle operazioni CDC, inclusa l'eliminazione, potrebbe richiedere più tempo del previsto.
  • Un'operazione CDC DELETE viene considerata applicata solo quando il timestamp upsert_stream_apply_watermark ha passato il timestamp in corrispondenza del quale l'API Storage Scrivi ha trasmesso l'operazione. Una volta applicata l'operazione, inizia il processo di eliminazione dei dati di Google Cloud standard. Per ulteriori informazioni sul timestamp upsert_stream_apply_watermark, consulta Monitorare l'avanzamento dell'operazione di upsert della tabella.

Limitazioni

  • BigQuery CDC non esegue l'applicazione delle chiavi, quindi è essenziale che le tue chiavi primarie siano univoche.
  • Le chiavi primarie non possono superare le 16 colonne.
  • Le tabelle abilitate per CDC non possono avere più di 2000 colonne di primo livello definite dallo schema della tabella.
  • Le tabelle abilitate per CDC non supportano quanto segue:
  • Le tabelle abilitate per CDC che eseguono job di unione del runtime perché il valore max_staleness della tabella è troppo basso non può supportare quanto segue:
  • Le operazioni di esportazione di BigQuery nelle tabelle abilitate per CDC non esportano le modifiche alle righe trasmesse di recente che devono essere ancora applicate da un job in background. Per esportare la tabella completa, utilizza un'istruzione EXPORT DATA.
  • Se la query attiva un'unione del runtime su una tabella partizionata, l'intera tabella viene analizzata indipendentemente dal fatto che la query sia limitata o meno a un sottoinsieme delle partizioni.
  • Se utilizzi la versione Standard, le prenotazioni BACKGROUND non sono disponibili, quindi l'applicazione di modifiche alle righe in attesa utilizza il modello di prezzi on demand. Tuttavia, puoi eseguire query su tabelle abilitate per CDC indipendentemente dalla versione.
  • Le pseudocolonne _CHANGE_TYPE e _CHANGE_SEQUENCE_NUMBER non sono colonne interrogabili quando esegui una lettura di tabella.

Prezzi CDC di BigQuery

BigQuery CDC utilizza l'API StorageWrite per l'importazione dei dati, l'archiviazione BigQuery per l'archiviazione dei dati e l'elaborazione BigQuery per le operazioni di modifica delle righe, che comportano tutti costi. Per informazioni sui prezzi, vedi Prezzi di BigQuery.

Stima i costi CDC di BigQuery

Oltre alle best practice generali per la stima dei costi di BigQuery, la stima dei costi di BigQuery CDC può essere importante per i flussi di lavoro con grandi quantità di dati, una configurazione max_staleness ridotta o dati modificati di frequente.

I prezzi dell'importazione dati di BigQuery e i prezzi di archiviazione di BigQuery vengono calcolati direttamente in base alla quantità di dati importati e archiviati, incluse le pseudo-colonne. Tuttavia, i prezzi di calcolo di BigQuery possono essere più difficili da stimare, poiché si riferiscono al consumo di risorse di calcolo utilizzate per eseguire job CDC di BigQuery.

I job CDC di BigQuery sono suddivisi in tre categorie:

  • Job di applicazione in background: job eseguiti in background a intervalli regolari definiti dal valore max_staleness della tabella. Questi job applicano modifiche di righe trasmesse di recente alla tabella abilitata per CDC.
  • Query job: query GoogleSQL eseguite all'interno della finestra max_staleness e lette solo dalla tabella di riferimento CDC.
  • Job di unione runtime: job attivati da query GoogleSQL ad hoc eseguite all'esterno della finestra max_staleness. Questi job devono eseguire un'unione immediata della tabella di riferimento CDC e delle modifiche alle righe trasmesse di recente al runtime della query.

Tutti e tre i tipi di job CDC di BigQuery sfruttano il clustering BigQuery, ma solo i job di query sfruttano il partizionamento di BigQuery. I job di applicazione in background e i job di unione del runtime non possono utilizzare il partizionamento perché, quando si applicano modifiche di riga trasmesse di recente, non esiste alcuna garanzia a quale partizione della tabella vengono applicati gli upsert trasmessi di recente. In altre parole, la tabella di riferimento completa viene letta durante i job di applicazione in background e i job di unione del runtime. Comprendere la quantità di dati che vengono letti per eseguire le operazioni CDC è utile per stimare il costo totale.

Se la quantità di dati letti dalla base di riferimento della tabella è elevata, valuta l'utilizzo del modello di prezzi della capacità di BigQuery, che non si basa sulla quantità di dati elaborati.

Best practice per i costi di CDC di BigQuery

Oltre alle best practice generali sui costi di BigQuery, utilizza le seguenti tecniche per ottimizzare i costi delle operazioni CDC di BigQuery:

Passaggi successivi