Trasmetti gli aggiornamenti delle tabelle con Change Data Capture

BigQuery Change Data Capture (CDC) aggiorna le tabelle BigQuery elaborando e applicando modifiche in flussi ai dati esistenti. Questa sincronizzazione viene eseguita mediante operazioni di upsert ed eliminazione delle righe, trasmesse in tempo reale dall'API BigQuery Storage Write, 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 Storage Write, 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 Storage Write.

Potresti anche essere in grado di ottenere questa autorizzazione con i ruoli personalizzati o altri ruoli predefiniti.

Per ulteriori informazioni sui ruoli e sulle 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 Storage Write nel stream predefinito.
  • Devi dichiarare le chiavi primarie per la tabella di destinazione in BigQuery. Sono supportate chiavi primarie composte che contengono fino a 16 colonne.
  • Devono essere disponibili risorse di calcolo BigQuery sufficienti per eseguire le operazioni sulle righe CDC. Tieni presente che se le operazioni di modifica della riga CDC non vanno a buon fine, potresti conservare involontariamente i dati che volevi eliminare. Per maggiori informazioni, consulta Considerazioni sui dati eliminati.

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 CDC, imposta _CHANGE_TYPE quando trasmetti in streaming le modifiche delle righe utilizzando l'API Storage Write. La pseudo-colonna _CHANGE_TYPE accetta solo i valori UPSERT e DELETE. Una tabella è considerata abilitata per CDC mentre l'API Storage Write trasmette in streaming le modifiche delle righe alla tabella in questo modo.

Esempio con i valori UPSERT e DELETE

Considera la seguente tabella in BigQuery:

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

L'API Storage Write trasmette un flusso di queste modifiche alle righe:

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 Enrico 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ù aggiornati durante l'esecuzione di query su una tabella abilitata per CDC, BigQuery deve applicare ogni modifica alla riga trasmessa fino all'ora di inizio della query, in modo da eseguire la query sulla versione più aggiornata della tabella. L'applicazione di queste modifiche alle righe in fase di esecuzione delle query aumenta la latenza e il costo delle query. 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 costo di alcuni dati inattivi.

Questo comportamento è particolarmente utile per le dashboard e i report per i quali l'aggiornamento dei dati non è essenziale. È utile anche 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 max_staleness e all'ora in cui è stato eseguito l'ultimo job di applicazione, rappresentato dal timestamp upsert_stream_apply_watermark della tabella.

Considera l'esempio seguente, in cui l'opzione max_staleness è impostata su 10 minuti in una tabella 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 corrente della tabella è obsoleta di 5 minuti, ossia 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 di 5 minuti.

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

Ad esempio, se esegui una query sulla tabella al T35 e il processo di applicazione delle modifiche alle righe in attesa non è stato completato, la versione corrente della tabella risulta obsoleta di 15 minuti, ossia maggiore dell'intervallo di 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 corrente, il che significa che i dati oggetto della query sono completamente aggiornati, al costo di una certa latenza aggiuntiva delle query. Questo è considerato un job di unione runtime.

Il tempo di esecuzione della query è al di fuori dell'intervallo di tempo massimo per l'inattività dei dati.

Il valore max_staleness di una tabella di solito deve essere il più alto tra i due seguenti valori:

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

Per calcolare il tempo necessario per applicare le modifiche con upsert 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 dell'archiviazione ottimizzata 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 modificate dalla CDC di BigQuery.

La durata dei job di applicazione in background è influenzata da diversi fattori, tra cui il numero e la complessità delle operazioni CDC emesse nell'intervallo di inattività, le dimensioni della tabella e la disponibilità delle risorse BigQuery. Per ulteriori informazioni sulla disponibilità delle risorse, consulta Dimensioni e monitoraggio delle prenotazioni IN 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 max_staleness in una tabella esistente, utilizza l'istruzione ALTER TABLE. Nell'esempio seguente viene impostato su 15 minuti il limite max_staleness della tabella employees:

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

Determinare l'attuale valore max_staleness di una tabella

Per determinare il valore max_staleness corrente di una tabella, esegui una query sulla vista INFORMATION_SCHEMA.TABLE_OPTIONS. L'esempio seguente controlla l'attuale valore 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 di 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 modifica alle righe, esegui una query nella 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 dei job del progetto contenente la tabella abilitata per CDC.

Configura una prenotazione BigQuery per l'utilizzo con CDC

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

I job BigQuery CDC che applicano modifiche in sospeso alle righe 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 in fase di esecuzione della query utilizzano il tipo di assegnazione QUERY. I job in background di BigQuery CDC eseguiti senza assegnazione di BACKGROUND sfruttano i prezzi on demand. Questa considerazione è importante quando si progetta la strategia di gestione dei carichi di lavoro per BigQuery CDC.

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 Stabilire la dimensione e monitorare le prenotazioni 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 che vengono modificate dalla CDC di BigQuery. Questo progetto è assegnato alla prenotazione.

Dimensiona e monitora BACKGROUND prenotazioni

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 il tempo di elaborazione delle operazioni di modifica delle righe CDC. Per dimensionare con precisione 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 sui dati eliminati

  • Le operazioni CDC di BigQuery sfruttano le risorse di calcolo di BigQuery. Se le operazioni CDC sono configurate per l'utilizzo della fatturazione on demand, le operazioni CDC vengono eseguite regolarmente utilizzando le 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 nella prenotazione configurata, l'elaborazione delle operazioni CDC, inclusa l'eliminazione, potrebbe richiedere più tempo del previsto.
  • Un'operazione DELETE CDC viene considerata applicata solo quando il timestamp upsert_stream_apply_watermark ha superato il timestamp in cui l'API Storage Write ha trasmesso l'operazione in streaming. Una volta applicata l'operazione, inizia il processo di eliminazione dei dati di Google Cloud standard. Per maggiori informazioni sul timestamp upsert_stream_apply_watermark, consulta la sezione Monitorare l'avanzamento dell'operazione di upsert della tabella.

Limitazioni

Prezzi di BigQuery CDC

BigQuery CDC utilizza l'API Storage Write per l'importazione dei dati, l'archiviazione BigQuery per l'archiviazione dei dati e il computing BigQuery per le operazioni di modifica delle righe, tutte con costi aggiuntivi. Per informazioni sui prezzi, consulta la pagina relativa ai 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 della CDC di BigQuery potrebbe essere importante per i flussi di lavoro con grandi quantità di dati, una configurazione di max_staleness ridotta o dati che cambiano frequentemente.

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. Tuttavia, i prezzi di computing di BigQuery possono essere più difficili da stimare, in quanto sono correlati al consumo delle risorse di calcolo utilizzate per eseguire i job BigQuery CDC.

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 riga trasmesse di recente alla tabella abilitata per CDC.
  • Job di query: query GoogleSQL che vengono eseguite all'interno della finestra max_staleness e lette solo dalla tabella di riferimento CDC.
  • Job di unione runtime: job che vengono attivati da query GoogleSQL ad hoc eseguite al di fuori della finestra di max_staleness. Questi job devono eseguire un'unione immediata della tabella di riferimento CDC e delle modifiche recenti alle righe trasmesse in streaming durante il runtime delle query.

Tutti e tre i tipi di job CDC di BigQuery sfruttano il clustering di BigQuery, ma solo i job di query sfruttano il partizionamento di BigQuery. I job di applicazione in background e i job di unione di runtime non possono utilizzare il partizionamento perché, quando si applicano modifiche di riga trasmesse di recente, non esiste alcuna garanzia su quale partizione della tabella a cui 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 di runtime. Comprendere la quantità di dati che vengono letti per eseguire operazioni CDC è utile per stimare il costo totale.

Se la quantità di dati letti dalla base di riferimento della tabella è elevata, valuta la possibilità di utilizzare il modello di determinazione del prezzo della capacità di BigQuery, che non si basa sulla quantità di dati elaborati.

Best practice per i costi di BigQuery CDC

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

  • Se non necessario, evita di configurare l'opzione max_staleness di una tabella con un valore molto basso. Il valore max_staleness può aumentare la frequenza dei job di applicazione in background e di unione di runtime, che sono più costosi e più lenti dei job di query. Per indicazioni dettagliate, consulta Valore consigliato max_staleness.
  • Valuta la possibilità di configurare una prenotazione BigQuery per l'utilizzo con tabelle CDC. In caso contrario, i job di applicazione in background e i job di unione di runtime utilizzano prezzi su richiesta, il che può essere più costoso a causa di una maggiore elaborazione dei dati. Per ulteriori dettagli, scopri di più sulle prenotazioni BigQuery e segui le indicazioni su come dimensionare e monitorare una prenotazione BACKGROUND da utilizzare con BigQuery CDC.

Passaggi successivi