Caricare dati PostgreSQL in BigQuery

Puoi caricare i dati da PostgreSQL a BigQuery utilizzando il connettore BigQuery Data Transfer Service per PostgreSQL. Il connettore supporta le istanze PostgreSQL ospitate nel tuo ambiente on-premise, Cloud SQL e altri provider cloud pubblico come Amazon Web Services (AWS) e Microsoft Azure. Con BigQuery Data Transfer Service, puoi pianificare job di trasferimento ricorrenti che aggiungono i dati più recenti da PostgreSQL a BigQuery.

Limitazioni

I trasferimenti di dati PostgreSQL sono soggetti alle seguenti limitazioni:

• Il numero massimo di esecuzioni di trasferimento simultanee a un singolo database PostgreSQL è determinato dal numero massimo di connessioni simultanee supportate dal database PostgreSQL. Il numero di job di trasferimento simultanei deve essere limitato a un valore inferiore al numero massimo di connessioni simultanee supportate dal database PostgreSQL.

• Una singola configurazione di trasferimento può supportare una sola esecuzione di trasferimento dei dati in un determinato momento. Quando viene pianificato l'esecuzione di un secondo trasferimento di dati prima del completamento del primo, viene completato solo il primo trasferimento di dati, mentre gli altri trasferimenti di dati che si sovrappongono al primo vengono ignorati.

  Per evitare trasferimenti ignorati all'interno di una singola configurazione di trasferimento, ti consigliamo di aumentare la durata di tempo tra i trasferimenti di grandi quantità di dati configurando la frequenza di ripetizione.

• Durante un trasferimento di dati, il connettore PostgreSQL identifica le colonne chiave indicizzate e partizionate per trasferire i dati in batch paralleli. Per questo motivo, ti consigliamo di specificare le colonne della chiave primaria o di utilizzare colonne indexate nella tabella per migliorare il rendimento e ridurre il tasso di errore nei trasferimenti di dati. Considera quanto segue:

  • Se hai vincoli di indice o di chiave primaria, per la creazione di batch paralleli sono supportati solo i seguenti tipi di colonne:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • I trasferimenti di dati PostgreSQL che non utilizzano chiave primaria o colonne indicizzate non possono supportare più di 2.000.000 di record per tabella.

Limitazioni del trasferimento incrementale

• Puoi scegliere solo TIMESTAMP colonne come colonne filigrana.
• L'importazione incrementale è supportata solo per gli asset con colonne della filigrana valide.
• I valori in una colonna filigrana devono essere monotonicamente crescenti.
• I trasferimenti incrementali non possono sincronizzare le operazioni di eliminazione nella tabella di origine.
• Una singola configurazione di trasferimento può supportare solo l'importazione incrementale o completa.
• Non puoi aggiornare gli oggetti nell'elenco asset dopo la prima esecuzione dell'importazione incrementale.
• Non puoi modificare la modalità di scrittura in una configurazione di trasferimento dopo la prima esecuzione dell'importazione incrementale.
• Non puoi modificare la colonna della filigrana o la chiave primaria dopo la prima esecuzione dell'importazione incrementale.
• La tabella BigQuery di destinazione è in cluster utilizzando la chiave primaria fornita ed è soggetta alle limitazioni delle tabelle in cluster.
• Quando aggiorni una configurazione di trasferimento esistente alla modalità di importazione incrementale per la prima volta, il primo trasferimento di dati dopo l'aggiornamento trasferisce tutti i dati disponibili dall'origine dati. I trasferimenti incrementali successivi dei dati trasferiranno solo le righe nuove e aggiornate dall'origine dati.
• Ti consigliamo di creare indici nella colonna della filigrana. Questo connettore utilizza le colonne filigrana per i filtri nei trasferimenti incrementali, pertanto l'indicizzazione di queste colonne può migliorare il rendimento.

Opzioni di importazione dei dati

Le sezioni seguenti forniscono informazioni sulle opzioni di importazione dati quando configuri un trasferimento di dati PostgreSQL.

Configurazione TLS

Il connettore PostgreSQL supporta la configurazione della sicurezza a livello di trasporto (TLS) per criptare i trasferimenti di dati in BigQuery. Il connettore PostgreSQL supporta le seguenti configurazioni TLS:

• La modalità Cripta i dati e verifica CA e nome host. Questa modalità esegue una convalida completa del server utilizzando TLS sul protocollo TCPS. Cripta tutti i dati in transito e verifica che il certificato del server di database sia firmato da un'autorità di certificazione (CA) attendibile. Questa modalità verifica anche che il nome host a cui ti connetti corrisponda esattamente al nome comune (CN) o a un nome alternativo del soggetto (SAN) sul certificato del server. Questa modalità impedisce agli autori di attacchi di utilizzare un certificato valido per un dominio diverso per impersonare il tuo server di database.

  Se il nome host non corrisponde al CN o al SAN del certificato, la connessione non va a buon fine. Devi configurare una risoluzione DNS in modo che corrisponda al certificato o utilizzare una modalità di sicurezza diversa. Utilizza questa modalità per l'opzione più sicura per prevenire attacchi man-in-the-middle (MITM).

• La modalità Cripta i dati e verifica solo la CA. Questa modalità cripta tutti i dati utilizzando TLS sul protocollo TCPS e verifica che il certificato del server sia firmato da una CA di cui il client si fida. Tuttavia, questa modalità non verifica il nome host del server. Questa modalità si connette correttamente purché il certificato sia valido ed emesso da una CA attendibile, indipendentemente dal fatto che il nome host nel certificato corrisponda al nome host a cui ti stai connettendo.

  Utilizza questa modalità se vuoi assicurarti di connetterti a un server il cui certificato è firmato da una CA attendibile, ma il nome host non è verificabile o non hai il controllo sulla configurazione del nome host.

• Modalità Solo crittografia. Questa modalità cripta tutti i dati trasferiti tra il client e il server. Non esegue alcuna convalida di certificati o nomi host.

  Questa modalità fornisce un certo livello di sicurezza proteggendo i dati in transito, ma può essere vulnerabile agli attacchi PITM.

  Utilizza questa modalità se devi assicurarti che tutti i dati siano criptati, ma non puoi o non vuoi verificare l'identità del server. Ti consigliamo di utilizzare questa modalità quando lavori con VPC privati.

• La modalità Nessuna crittografia o verifica. Questa modalità non cripta alcun dato e non esegue alcuna verifica di certificati o nomi host. Tutti i dati vengono inviati come testo normale.

  Non consigliamo di utilizzare questa modalità in un ambiente in cui vengono gestiti dati sensibili. Consigliamo di utilizzare questa modalità solo per scopi di test su una rete isolata in cui la sicurezza non è un problema.

Certificato del server attendibile (PEM)

Se utilizzi la modalità Cripta i dati e verifica CA e nome host o la modalità Cripta i dati e verifica CA, puoi anche fornire uno o più certificati codificati in formato PEM. Questi certificati sono necessari in alcuni scenari in cui BigQuery Data Transfer Service deve verificare l'identità del server di database durante la connessione TLS:

• Se utilizzi un certificato firmato da una CA privata all'interno della tua organizzazione o un certificato autofirmato, devi fornire la catena di certificati completa o il singolo certificato autofirmato. Questo è necessario per i certificati emessi dalle CA interne dei servizi di provider di cloud gestiti, come Amazon Relational Database Service (RDS).
• Se il certificato del server di database è firmato da una CA pubblica (ad esempio, Let's Encrypt, DigiCert o GlobalSign), non devi fornire un certificato. I certificati radice per queste CA pubbliche sono preinstallati e considerati attendibili da BigQuery Data Transfer Service.

Puoi specificare i certificati con codifica PEM nel campo Certificato PEM attendibile nella configurazione del trasferimento, con i seguenti requisiti:

• Il certificato deve essere una catena di certificati con codifica PEM valida.
• Il certificato deve essere completamente corretto. Eventuali certificati mancanti nella catena o contenuti errati causano l'interruzione della connessione TLS.
• Per un singolo certificato, puoi fornire un singolo certificato autofirmato dal server di database.
• Per una catena di certificati completa emessa da una CA privata, devi fornire la catena di attendibilità completa. Sono inclusi il certificato del server di database e tutti i certificati CA intermedi e radice.

Trasferimenti completi o incrementali

Puoi specificare come caricare i dati in BigQuery selezionando la preferenza di scrittura Completa o Incrementale nella configurazione del trasferimento quando configuri un trasferimento PostgreSQL. I trasferimenti incrementali sono supportati in anteprima.

In alternativa, puoi configurare un trasferimento dei dati incrementale (anteprima) per trasferire solo i dati modificati dall'ultimo trasferimento, anziché caricare l'intero set di dati a ogni trasferimento. Se hai configurato un trasferimento di dati incrementale, devi specificare le modalità di scrittura append o upsert per definire come vengono scritti i dati in BigQuery durante un trasferimento di dati incrementale. Le sezioni seguenti descrivono le modalità di scrittura disponibili.

Modalità di scrittura di accodamento

La modalità di scrittura di accodamento inserisce solo nuove righe nella tabella di destinazione. Questa opzione aggiunge rigorosamente i dati trasferiti senza verificare la presenza di record esistenti, pertanto questa modalità può potenzialmente causare la duplicazione dei dati nella tabella di destinazione.

Quando selezioni la modalità di aggiunta, devi selezionare una colonna watermark. Per consentire al connettore PostgreSQL di monitorare le modifiche nella tabella di origine, è necessaria una colonna filigrana.

Modalità di scrittura upsert

La modalità di scrittura upsert aggiorna una riga o ne inserisce una nuova nella tabella di destinazione controllando una chiave primaria. Puoi specificare una chiave primaria per consentire al connettore PostgreSQL di determinare le modifiche necessarie per mantenere la tabella di destinazione aggiornata con la tabella di origine. Se la chiave primaria specificata è presente nella tabella BigQuery di destinazione durante un trasferimento di dati, il connettore PostgreSQL aggiorna la riga con i nuovi dati della tabella di origine. Se una chiave primaria non è presente durante un trasferimento di dati, il connettore PostgreSQL inserisce una nuova riga.

Quando selezioni la modalità di upsert, devi selezionare una colonna watermark e una chiave primaria:

• Per tenere traccia delle modifiche nella tabella di origine, il connettore PostgreSQL richiede una colonna filigrana.

  Seleziona una colonna filigrana che si aggiorna ogni volta che una riga viene modificata. Ti consigliamo colonne simili alla colonna UPDATED_AT o LAST_MODIFIED.

• La chiave primaria può essere una o più colonne della tabella necessarie per il connettore PostgreSQL per determinare se deve inserire o aggiornare una riga.

  Seleziona le colonne che contengono valori non nulli univoci in tutte le righe della tabella. Ti consigliamo colonne che includano identificatori generati dal sistema, codici di riferimento univoci (ad esempio ID a incremento automatico) o ID sequenza basati sul tempo immutabili.

  Per evitare potenziali perdite o danneggiamenti dei dati, le colonne della chiave primaria che selezioni devono avere valori univoci. Se hai dubbi sull'unicità della colonna della chiave primaria che hai scelto, ti consigliamo di utilizzare la modalità di scrittura Aggiungi.

Comportamento di importazione incrementale

Quando apporti modifiche allo schema della tabella nell'origine dati, i trasferimenti incrementali dei dati da queste tabelle vengono visualizzati in BigQuery nei seguenti modi:

Prima di iniziare

• Crea un utente nel database PostgreSQL.
• Verifica di aver completato tutte le azioni necessarie per attivare BigQuery Data Transfer Service.
• Crea un set di dati BigQuery per archiviare i dati.
• Assicurati di disporre dei ruoli necessari per completare le attività descritte in questo documento.

Ruoli obbligatori

Se intendi configurare le notifiche di esecuzione del trasferimento per Pub/Sub, assicurati di disporre dell'autorizzazione pubsub.topics.setIamPolicy Identity and Access Management (IAM). Le autorizzazioni Pub/Sub non sono necessarie se configuri solo le notifiche via email. Per saperne di più, consulta Notifiche di esecuzione di BigQuery Data Transfer Service.

Per ottenere le autorizzazioni necessarie per creare un trasferimento di dati BigQuery Data Transfer Service, chiedi all'amministratore di concederti il ruolo IAM Amministratore BigQuery (roles/bigquery.admin) sul tuo progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per creare un trasferimento di dati BigQuery Data Transfer Service. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Per maggiori informazioni, vedi Concedere l'accesso a bigquery.admin.

Connessioni di rete

Se non è disponibile un indirizzo IP pubblico per la connessione al database PostgreSQL, devi configurare un collegamento di rete.

Per istruzioni dettagliate sulla configurazione di rete richiesta, consulta i seguenti documenti:

• Se esegui il trasferimento da Cloud SQL, consulta Configurare l'accesso all'istanza Cloud SQL.
• Se esegui il trasferimento da AWS, consulta Configurare la VPN AWS-Cloud de Confiance e l'allegato di rete.
• Se esegui il trasferimento da Azure, vedi Configurare la VPN Azure-Cloud de Confiance e l'allegato di rete.

Configura un trasferimento di dati PostgreSQL

Aggiungi i dati PostgreSQL a BigQuery configurando un trasferimento utilizzando una delle seguenti opzioni:

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

Per eseguire manualmente un trasferimento di dati al di fuori della pianificazione regolare, puoi avviare un'esecuzione di backfill.

Mappatura dei tipi di dati

La seguente tabella mappa i tipi di dati PostgreSQL ai corrispondenti tipi di dati BigQuery.

Risoluzione dei problemi

Se riscontri problemi durante la configurazione del trasferimento dei dati, consulta Problemi di trasferimento di PostgreSQL.

Prezzi

Per informazioni sui prezzi dei trasferimenti PostgreSQL, consulta i prezzi di Data Transfer Service.

Passaggi successivi

• Leggi una panoramica di BigQuery Data Transfer Service.
• Scopri di più su come gestire i trasferimenti, ad esempio come ottenere informazioni su una configurazione di trasferimento, elencare le configurazioni di trasferimento e visualizzare la cronologia di esecuzione di un trasferimento.
• Scopri come caricare i dati con operazioni cross-cloud.