Tutorial sulla migrazione di Teradata a BigQuery
Questo documento descrive come eseguire la migrazione da Teradata a BigQuery utilizzando dati di esempio. Fornisce una proof-of-concept che ti guida attraverso il processo di trasferimento di schema e dati da un data warehouse Teradata a BigQuery.
Obiettivi
- Genera dati sintetici e caricali su Teradata.
- Eseguire la migrazione di schema e dati in BigQuery, utilizzando BigQuery Data Transfer Service (BQDT).
- Verifica che le query restituiscano gli stessi risultati su Teradata e BigQuery.
Costi
Questa guida rapida utilizza i seguenti componenti fatturabili di Google Cloud:
- BigQuery: questo tutorial archivia quasi 1 GB di dati in BigQuery ed elabora meno di 2 GB quando le query vengono eseguite una volta. Nell'ambito di utilizzo del Livello gratuito di Google Cloud, BigQuery offre alcune risorse gratuitamente entro un limite specifico. Questi limiti di utilizzo gratuito sono disponibili durante e dopo il periodo di prova gratuita. Se superi questi limiti di utilizzo e il tuo periodo di prova gratuita è terminato, ti verranno addebitati i costi in base ai prezzi indicati nella pagina Prezzi di BigQuery.
Puoi utilizzare il Calcolatore prezzi per generare una stima dei costi in base all'utilizzo previsto.
Prerequisiti
- Assicurati di disporre delle autorizzazioni di scrittura ed esecuzione in una macchina che ha accesso a internet, in modo da poter scaricare ed eseguire lo strumento di generazione dei dati.
- Assicurati di poter connettere a un database Teradata.
Assicurati che nella macchina siano installati gli strumenti client Teradata BTEQ e FastLoad. Puoi scaricare gli strumenti client di Teradata dal sito web di Teradata. Se hai bisogno di aiuto per l'installazione di questi strumenti, chiedi al tuo amministratore di sistema per maggiori dettagli su installazione, configurazione ed esecuzione. In alternativa, o in aggiunta a BTEQ, potresti eseguire le seguenti operazioni:
- Installa uno strumento con un'interfaccia grafica come DBeaver.
- Installa il driver SQL Teradata per Python per le interazioni di script con il database Teradata.
Assicurati che la macchina disponga di connettività di rete con Google Cloud affinché l'agente BigQuery Data Transfer Service possa comunicare con BigQuery e trasferire schema e dati.
Introduzione
Questa guida rapida ti guida attraverso una proof of concept di migrazione. Durante la guida rapida, genererai dati sintetici e li caricherai in Teradata. Quindi, utilizza BigQuery Data Transfer Service per spostare lo schema e i dati in BigQuery. Infine, esegui delle query su entrambi i lati per confrontare i risultati. Alla fine, lo schema e i dati di Teradata vengono mappati uno per uno in BigQuery.
Questa guida rapida è rivolta agli amministratori, agli sviluppatori e ai professionisti del data warehouse in generale interessati a un'esperienza pratica con la migrazione di schemi e dati utilizzando BigQuery Data Transfer Service.
Generazione dei dati in corso...
Il Transaction Processing Performance Council (TPC) è un'organizzazione non profit che pubblica specifiche di benchmarking. Queste specifiche sono diventate di fatto standard di settore per l'esecuzione di benchmark relativi ai dati.
La specifica TPC-H è un benchmark incentrato sul supporto decisionale. In questa guida rapida, utilizzerai parti di questa specifica per creare tabelle e generare dati sintetici come modello di data warehouse reale. Sebbene la specifica sia stata creata per il benchmarking, in questa guida rapida utilizzerai questo modello come parte del modello di migrazione, non per attività di benchmarking.
- Sul computer in cui ti connetterai a Teradata, utilizza un browser web per scaricare la versione più recente disponibile degli strumenti TPC-H dal sito web di TPC.
- Apri un terminale di comando e passa alla directory in cui hai scaricato gli strumenti.
Estrai il file ZIP scaricato. Sostituisci file-name con il nome del file che hai scaricato:
unzip file-name.zip
Viene estratta una directory il cui nome include il numero di versione degli strumenti. Questa directory include il codice sorgente TPC per lo strumento di generazione dei dati DBGEN e la specifica TPC-H stessa.
Vai alla sottodirectory
dbgen
. Utilizza il nome della directory padre corrispondente alla tua versione, come nell'esempio seguente:cd 2.18.0_rc2/dbgen
Crea un makefile utilizzando il modello fornito:
cp makefile.suite makefile
Modifica il makefile con un editor di testo. Ad esempio, utilizza vi per modificare il file:
vi makefile
Nel makefile, modifica i valori per le seguenti variabili:
CC = gcc # TDAT -> TERADATA DATABASE = TDAT MACHINE = LINUX WORKLOAD = TPCH
A seconda del tuo ambiente, i valori del compilatore C (
CC
) o diMACHINE
potrebbero essere diversi. Se necessario, rivolgiti all'amministratore di sistema.Salva le modifiche e chiudi il file.
Elabora il makefile:
make
Genera i dati TPC-H utilizzando lo strumento
dbgen
:dbgen -v
La generazione dei dati richiede un paio di minuti. Il flag
-v
(dettagliato) fa sì che il comando generi un report sull'avanzamento. Al termine della generazione dei dati, nella cartella corrente troverai otto file ASCII con estensione.tbl
. Contengono dati sintetici delimitato da barre verticali da caricare in ciascuna delle tabelle TPC-H.
Caricamento di dati di esempio su Teradata
In questa sezione, caricherai i dati generati nel tuo database Teradata.
crea il database TPC-H
Il client Teradata, denominato BTEQ (Basic Teradata Query), viene utilizzato per comunicare con uno o più server di database Teradata ed eseguire query SQL su questi sistemi. In questa sezione utilizzerai BTEQ per creare un nuovo database per le tabelle TPC-H.
Apri il client BTEQ Teradata:
bteq
Accedi a Teradata. Sostituisci teradata-ip e teradata-user con i valori corrispondenti per il tuo ambiente.
.LOGON teradata-ip/teradata-user
Crea un database denominato
tpch
con 2 GB di spazio allocato:CREATE DATABASE tpch AS PERM=2e+09;
Esci da BTEQ:
.QUIT
Carica i dati generati
In questa sezione creerai uno script FastLoad per creare e caricare le tabelle di esempio. Le definizioni della tabella sono descritte nella sezione 1.4 della specifica TPC-H. La sezione 1.2 contiene un diagramma di relazione entità dell'intero schema del database.
La procedura seguente mostra come creare la tabella lineitem
, che è la più grande e complessa delle tabelle TPC-H. Quando hai finito di utilizzare la tabella lineitem
, ripeti questa procedura per le tabelle rimanenti.
Con un editor di testo, crea un nuovo file denominato
fastload_lineitem.fl
:vi fastload_lineitem.fl
Copia il seguente script nel file, che si connette al database Teradata e crea una tabella denominata
lineitem
.Nel comando
logon
, sostituisci teradata-ip, teradata-user e teradata-pwd con i dettagli della connessione.logon teradata-ip/teradata-user,teradata-pwd; drop table tpch.lineitem; drop table tpch.error_1; drop table tpch.error_2; CREATE multiset TABLE tpch.lineitem, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( L_ORDERKEY INTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL, L_LINESTATUS CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL, L_SHIPDATE DATE FORMAT 'yyyy-mm-dd' NOT NULL, L_COMMITDATE DATE FORMAT 'yyyy-mm-dd' NOT NULL, L_RECEIPTDATE DATE FORMAT 'yyyy-mm-dd' NOT NULL, L_SHIPINSTRUCT CHAR(25) CHARACTER SET LATIN CASESPECIFIC NOT NULL, L_SHIPMODE CHAR(10) CHARACTER SET LATIN CASESPECIFIC NOT NULL, L_COMMENT VARCHAR(44) CHARACTER SET LATIN CASESPECIFIC NOT NULL) PRIMARY INDEX ( L_ORDERKEY ) PARTITION BY RANGE_N(L_COMMITDATE BETWEEN DATE '1992-01-01' AND DATE '1998-12-31' EACH INTERVAL '1' DAY);
Lo script verifica innanzitutto che la tabella
lineitem
e le tabelle degli errori temporanei non esistano, quindi procede alla creazione della tabellalineitem
.Nello stesso file, aggiungi il seguente codice, che carica i dati nella tabella appena creata. Completa tutti i campi della tabella (...all-fields...) nei tre blocchi (
define
,insert
evalues
), assicurandoti di utilizzarevarchar
come tipo di dati di caricamento.begin loading tpch.lineitem errorfiles tpch.error_1, tpch.error_2; set record vartext; define in_ORDERKEY(varchar(50)), in_PARTKEY(varchar(50)), ...all-fields... file = lineitem.tbl; insert into tpch.lineitem ( L_ORDERKEY, L_PARTKEY, ...all-fields... ) values ( :in_ORDERKEY, :in_PARTKEY, ...all-fields... ); end loading; logoff;
Lo script FastLoad carica i dati da un file nella stessa directory denominata
lineitem.tbl
, generata nella sezione precedente.Salva le modifiche e chiudi il file.
Esegui lo script FastLoad:
fastload < fastload_lineitem.fl
Ripeti questa procedura per le altre tabelle TPC-H elencate nella sezione 1.4 della specifica TPC-H. Assicurati di modificare i passaggi per ogni tabella.
Migrazione dello schema e dei dati a BigQuery
Le istruzioni su come eseguire la migrazione dello schema e dei dati in BigQuery sono disponibili in un tutorial separato: Eseguire la migrazione dei dati da Teradata. In questa sezione abbiamo incluso informazioni dettagliate su come procedere con determinati passaggi del tutorial. Una volta completati i passaggi nell'altro tutorial, torna a questo documento e passa alla sezione successiva Verifica dei risultati delle query.
crea il set di dati BigQuery
Durante le fasi iniziali di configurazione di Google Cloud, ti viene chiesto di creare un set di dati in BigQuery per conservare le tabelle dopo la migrazione. Assegna al set di dati il nome tpch
. Le query alla fine di questa guida rapida
assumono questo nome e non richiedono alcuna modifica.
# Use the bq utility to create the dataset
bq mk --location=US tpch
Crea un service account
Inoltre, nell'ambito della procedura di configurazione di Google Cloud, devi creare un account di servizio Identity and Access Management (IAM). Questo account di servizio viene utilizzato per scrivere dati in BigQuery e archiviare dati temporanei in Cloud Storage.
# Set the PROJECT variable
export PROJECT=$(gcloud config get-value project)
# Create a service account
gcloud iam service-accounts create tpch-transfer
Concedi all'account di servizio le autorizzazioni per amministrare i set di dati BigQuery e l'area temporanea in Cloud Storage:
# Set TPCH_SVC_ACCOUNT = service account email
export TPCH_SVC_ACCOUNT=tpch-transfer@${PROJECT}.iam.gserviceaccount.com
# Bind the service account to the BigQuery Admin role
gcloud projects add-iam-policy-binding ${PROJECT} \
--member serviceAccount:${TPCH_SVC_ACCOUNT} \
--role roles/bigquery.admin
# Bind the service account to the Storage Admin role
gcloud projects add-iam-policy-binding ${PROJECT} \
--member serviceAccount:${TPCH_SVC_ACCOUNT} \
--role roles/storage.admin
Crea il bucket Cloud Storage gestione temporanea
Un'attività aggiuntiva nella configurazione di Google Cloud è la creazione di un bucket Cloud Storage. Il bucket viene utilizzato da BigQuery Data Transfer Service come area temporanea per l'importazione dei file di dati in BigQuery.
# Use gsutil to create the bucket
gsutil mb -c regional -l us-central1 gs://${PROJECT}-tpch
Specifica i pattern dei nomi delle tabelle
Durante la configurazione di un nuovo trasferimento in BigQuery Data Transfer Service, ti viene chiesto di specificare un'espressione che indichi quali tabelle includere
nel trasferimento. In questa guida rapida includerai tutte le tabelle del database tpch
.
Il formato dell'espressione è database
.table
e il nome della tabella può essere sostituito da un carattere jolly. Poiché i caratteri jolly in Java iniziano con due punti, l'espressione per trasferire tutte le tabelle dal database tpch
è la
seguente:
tpch..*
Nota che ci sono due punti.
Verifica dei risultati della query
A questo punto, hai creato i dati di esempio, li hai caricati in Teradata e li hai migrati a BigQuery utilizzando BigQuery Data Transfer Service, come spiegato in un tutorial separato. In questa sezione, eseguirai due delle query TPC-H standard per verificare che i risultati siano gli stessi in Teradata e in BigQuery.
Eseguire la query del report di riepilogo dei prezzi
La prima query è quella del report di riepilogo dei prezzi (sezione 2.4.1 della specifica TPC-H). Questa query riporta il numero di articoli fatturati, spediti e restituiti in una determinata data.
Nell'elenco seguente viene mostrata la query completa:
SELECT
l_returnflag,
l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
SUM(l_extendedprice*(1-l_discount)) AS sum_disc_price,
SUM(l_extendedprice*(1-l_discount)*(1+l_tax)) AS sum_charge,
AVG(l_quantity) AS avg_qty,
AVG(l_extendedprice) AS avg_price,
AVG(l_discount) AS avg_disc,
COUNT(*) AS count_order
FROM tpch.lineitem
WHERE l_shipdate BETWEEN '1996-01-01' AND '1996-01-10'
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus;
Esegui la query in Teradata:
- Esegui BTEQ e connettiti a Teradata. Per maggiori dettagli, consulta la sezione Creare il database TPC-H precedente in questo documento.
Modifica la larghezza di visualizzazione dell'output su 500 caratteri:
.set width 500
Copia la query e incollala nel prompt di BTEQ.
Il risultato è simile al seguente:
L_RETURNFLAG L_LINESTATUS sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order ------------ ------------ ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------- N O 629900.00 943154565.63 896323924.4600 932337245.114003 25.45 38113.41 .05 24746
Esegui la stessa query in BigQuery:
Vai alla console BigQuery:
Copia la query nell'editor di query.
Assicurati che il nome del set di dati nella riga
FROM
sia corretto.Fai clic su Esegui.
Il risultato è lo stesso di Teradata.
Facoltativamente, puoi scegliere intervalli di tempo più ampi nella query per assicurarti che tutte le righe della tabella vengano analizzate.
Esegui la query sul volume dei fornitori locali
Il secondo esempio di query è il report sulle query sul volume dei fornitori locali (sezione 2.4.5 della specifica TPC-H). Per ogni nazione di una regione, questa query restituisce le entrate generate da ogni elemento pubblicitario in cui il cliente e il fornitore si trovavano in quella nazione. Questi risultati sono utili, ad esempio, per pianificare il posizionamento dei centri di distribuzione.
Nell'elenco seguente viene mostrata la query completa:
SELECT
n_name AS nation,
SUM(l_extendedprice * (1 - l_discount) / 1000) AS revenue
FROM
tpch.customer,
tpch.orders,
tpch.lineitem,
tpch.supplier,
tpch.nation,
tpch.region
WHERE c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
AND o_orderdate >= '1996-01-01'
AND o_orderdate < '1997-01-01'
GROUP BY
n_name
ORDER BY
revenue DESC;
Esegui la query in Teradata BTEQ e nella console BigQuery come descritto nella sezione precedente.
Questo è il risultato restituito da Teradata:
Questo è il risultato restituito da BigQuery:
Sia Teradata che BigQuery restituiscono gli stessi risultati.
Eseguire la query sulla misurazione del profitto del tipo di prodotto
Il test finale per verificare la migrazione è l'ultima query di esempio della query sulla misurazione del profitto per tipo di prodotto (sezione 2.4.9 della specifica TPC-H). Per ogni nazione e ogni anno, questa query trova il profitto per tutti i componenti ordinati in quell'anno. Filtra i risultati in base a una sottostringa nei nomi delle parti e in base a un fornitore specifico.
Nell'elenco seguente viene mostrata la query completa:
SELECT
nation,
o_year,
SUM(amount) AS sum_profit
FROM (
SELECT
n_name AS nation,
EXTRACT(YEAR FROM o_orderdate) AS o_year,
(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)/1e+3 AS amount
FROM
tpch.part,
tpch.supplier,
tpch.lineitem,
tpch.partsupp,
tpch.orders,
tpch.nation
WHERE s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nationkey
AND p_name like '%blue%' ) AS profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year DESC;
Esegui la query in Teradata BTEQ e nella console BigQuery come descritto nella sezione precedente.
Questo è il risultato restituito da Teradata:
Questo è il risultato restituito da BigQuery:
Sia Teradata che BigQuery restituiscono gli stessi risultati, anche se Teradata utilizza una notazione scientifica per la somma.
Query aggiuntive
Facoltativamente, puoi eseguire le altre query TPC-H definite nella sezione 2.4 della specifica TPC-H.
Puoi anche generare query seguendo lo standard TPC-H utilizzando lo strumento QGEN,
che si trova nella stessa directory dello strumento DBGEN. QGEN viene creato utilizzando lo stesso
makefile di DBGEN, quindi quando esegui make per compilare dbgen
, hai prodotto anche
l'eseguibile qgen
.
Per ulteriori informazioni su entrambi gli strumenti e sulle relative opzioni della riga di comando, consulta il
file README
per ogni strumento.
esegui la pulizia
Rimuovile per evitare che al tuo account Google Cloud vengano addebitati costi relativi alle risorse utilizzate in questo tutorial.
Elimina il progetto
Il modo più semplice per interrompere gli addebiti è eliminare il progetto che hai creato per questo tutorial.
- Nella console Google Cloud, vai alla pagina Gestisci risorse.
- Nell'elenco dei progetti, seleziona il progetto che vuoi eliminare, quindi fai clic su Elimina.
- Nella finestra di dialogo, digita l'ID del progetto e fai clic su Chiudi per eliminare il progetto.
Passaggi successivi
- Leggi le istruzioni dettagliate per eseguire la migrazione di Teradata a BigQuery.