Tutorial memigrasikan Teradata ke BigQuery

Dokumen ini menjelaskan cara bermigrasi dari Teradata ke BigQuery menggunakan data sampel. Dokumen ini memberikan bukti konsep yang memandu Anda melakukan proses transfer skema dan data dari data warehouse Teradata ke BigQuery.

Tujuan

• Membuat data sintetis dan menguploadnya ke Teradata.
• Memigrasikan skema dan data ke BigQuery, menggunakan BigQuery Data Transfer Service (BQDT).
• Memastikan kueri menampilkan hasil yang sama di Teradata dan BigQuery.

Biaya

Panduan memulai ini menggunakan komponen Google Cloud yang dapat ditagih berikut:

• BigQuery: Tutorial ini menyimpan hampir 1 GB data di BigQuery dan memproses kurang dari 2 GB saat menjalankan kueri satu kali. Sebagai bagian dari Paket Gratis Google Cloud, BigQuery menawarkan beberapa resource gratis hingga batas tertentu. Batas penggunaan gratis ini tersedia selama dan setelah periode uji coba gratis. Jika Anda melewati batas penggunaan ini dan tidak lagi berada dalam periode uji coba gratis, Anda akan dikenai biaya sesuai dengan harga di halaman harga BigQuery.

Anda dapat menggunakan kalkulator harga untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan.

Prasyarat

• Pastikan Anda memiliki izin tulis dan eksekusi di mesin yang memiliki akses ke internet, sehingga Anda dapat mendownload alat pembuat data dan menjalankannya.
• Pastikan Anda dapat terhubung ke database Teradata.

• Pastikan mesin telah menginstal alat klien Teradata BTEQ dan FastLoad. Anda bisa mendapatkan alat klien Teradata dari situs Teradata. Jika memerlukan bantuan untuk menginstal alat tersebut, hubungi administrator sistem Anda untuk mengetahui detail cara menginstal, mengonfigurasi, dan menjalankannya. Sebagai alternatif, atau selain BTEQ, Anda dapat melakukan hal berikut:

  • Instal alat dengan antarmuka grafis seperti DBeaver.
  • Instal Teradata SQL Driver untuk Python untuk interaksi pembuatan skrip dengan Teradata Database.

• Pastikan mesin memiliki konektivitas jaringan dengan Google Cloud agar agen BigQuery Data Transfer Service dapat berkomunikasi dengan BigQuery serta mentransfer skema dan data.

Catatan: Petunjuk untuk memigrasikan skema dan data dibahas dalam tutorial terpisah yang ditautkan dari tutorial ini, seperti yang akan dijelaskan nanti.

Pengantar

Panduan memulai ini memandu Anda melalui bukti konsep migrasi. Selama panduan memulai, Anda membuat data sintetis dan memuatnya ke Teradata. Selanjutnya, Anda menggunakan BigQuery Data Transfer Service untuk memindahkan skema dan data ke BigQuery. Terakhir, Anda menjalankan kueri di kedua sisi untuk membandingkan hasilnya. Status akhirnya adalah skema dan data dari Teradata dipetakan satu per satu ke BigQuery.

Panduan memulai ini ditujukan untuk administrator data warehouse, developer, dan praktisi data secara umum yang tertarik untuk mendapatkan pengalaman langsung terkait migrasi skema dan data menggunakan BigQuery Data Transfer Service.

Menghasilkan data

Transaction Processing Performance Council (TPC) adalah organisasi nonprofit yang memublikasikan spesifikasi benchmark. Spesifikasi ini telah menjadi standar industri de facto untuk menjalankan benchmark terkait data.

Spesifikasi TPC-H adalah benchmark yang berfokus pada dukungan keputusan. Dalam panduan memulai ini, Anda akan menggunakan bagian-bagian dari spesifikasi ini untuk membuat tabel dan menghasilkan data sintetis sebagai model data warehouse sungguhan. Meskipun spesifikasi dibuat untuk benchmark, dalam panduan memulai ini Anda menggunakan model ini sebagai bagian dari bukti migrasi konsep, bukan untuk tugas benchmark.

1. Di komputer yang akan terhubung ke Teradata, gunakan browser web untuk mendownload versi terbaru alat TPC-H yang tersedia dari situs TPC.
2. Buka terminal perintah dan ubah ke direktori tempat Anda mendownload alat.

3. Ekstrak file zip yang didownload. Ganti file-name dengan nama file yang Anda download:

   unzip file-name.zip
   

   Direktori yang namanya mencakup nomor versi alat diekstrak. Direktori ini mencakup kode sumber TPC untuk alat pembuatan data DBGEN dan spesifikasi TPC-H itu sendiri.

4. Buka subdirektori dbgen. Gunakan nama direktori induk yang sesuai dengan versi Anda, seperti pada contoh berikut:

   cd 2.18.0_rc2/dbgen
   

5. Buat makefile menggunakan template yang disediakan:

   cp makefile.suite makefile
   

6. Edit makefile dengan editor teks. Misalnya, gunakan vi untuk mengedit file:

   vi makefile
   

7. Di makefile, ubah nilai untuk variabel berikut:

   CC       = gcc
   # TDAT -> TERADATA
   DATABASE = TDAT
   MACHINE  = LINUX
   WORKLOAD = TPCH
   

   Bergantung pada lingkungan Anda, nilai compiler C (CC) atau MACHINE mungkin berbeda. jika perlu, hubungi administrator sistem Anda.

8. Simpan perubahan dan tutup file tersebut.

9. Proses makefile:

   make
   

10. Buat data TPC-H menggunakan alat dbgen:

    dbgen -v
    

    Pembuatan data memerlukan waktu beberapa menit. Flag -v (panjang) menyebabkan perintah melaporkan progres. Setelah pembuatan data selesai, Anda akan menemukan 8 file ASCII dengan ekstensi .tbl di folder saat ini. File itu berisi data sintetis pipe-delimited yang akan dimuat di setiap tabel TPC-H.

Mengupload data sampel ke Teradata

Di bagian ini, Anda akan mengupload data yang dihasilkan ke database Teradata.

Membuat database TPC-H

Klien Teradata, yang disebut Basic Teradata Query (BTEQ), digunakan untuk berkomunikasi dengan satu atau beberapa server database Teradata dan menjalankan kueri SQL pada sistem tersebut. Di bagian ini, Anda akan menggunakan BTEQ untuk membuat database baru untuk tabel TPC-H.

1. Buka klien Teradata BTEQ:

   bteq
   

2. Login ke Teradata. Ganti teradata-ip dan teradata-user dengan nilai yang sesuai untuk lingkungan Anda.

   .LOGON teradata-ip/teradata-user
   

3. Buat database bernama tpch dengan ruang yang dialokasikan sebesar 2 GB:

   CREATE DATABASE tpch
   AS PERM=2e+09;
   

4. Keluar dari BTEQ:

   .QUIT
   

Memuat data yang dihasilkan

Di bagian ini, Anda akan membuat skrip FastLoad untuk membuat dan memuat tabel contoh. Definisi tabel dijelaskan di bagian 1.4 spesifikasi TPC-H. Bagian 1.2 berisi diagram hubungan entity dari seluruh skema database.

Prosedur berikut menunjukkan cara membuat tabel lineitem, yang merupakan tabel terbesar dan paling kompleks dari tabel TPC-H. Setelah selesai dengan tabel lineitem, ulangi prosedur ini untuk tabel yang tersisa.

1. Dengan menggunakan editor teks, buat file baru bernama fastload_lineitem.fl:

   vi fastload_lineitem.fl
   

2. Salin skrip berikut ke dalam file, yang terhubung ke database Teradata dan membuat tabel bernama lineitem.

   Dalam perintah logon, ganti teradata-ip, teradata-user, dan teradata-pwd dengan detail koneksi Anda.

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

   Skrip ini terlebih dahulu memastikan bahwa tabel lineitem dan tabel error sementara tidak ada, lalu melanjutkan pembuatan tabel lineitem.

3. Dalam file yang sama, tambahkan kode berikut, yang memuat data ke dalam tabel yang baru dibuat. Lengkapi semua kolom tabel di tiga blok (define, insert, dan values), dengan memastikan Anda menggunakan varchar sebagai jenis data pemuatan.

   begin loading tpch.lineitem
   errorfiles tpch.error_1, tpch.error_2;
    set record vartext;
   define
    in_ORDERKEY(varchar(50)),
    in_PARTKEY(varchar(50)),
    in_SUPPKEY(varchar(50)),
    in_LINENUMBER(varchar(50)),
    in_QUANTITY(varchar(50)),
    in_EXTENDEDPRICE(varchar(50)),
    in_DISCOUNT(varchar(50)),
    in_TAX(varchar(50)),
    in_RETURNFLAG(varchar(50)),
    in_LINESTATUS(varchar(50)),
    in_SHIPDATE(varchar(50)),
    in_COMMITDATE(varchar(50)),
    in_RECEIPTDATE(varchar(50)),
    in_SHIPINSTRUCT(varchar(50)),
    in_SHIPMODE(varchar(50)),
    in_COMMENT(varchar(50))
    file = lineitem.tbl;
   insert into tpch.lineitem (
     L_ORDERKEY,
     L_PARTKEY,
     L_SUPPKEY,
     L_LINENUMBER,
     L_QUANTITY,
     L_EXTENDEDPRICE,
     L_DISCOUNT,
     L_TAX,
     L_RETURNFLAG,
     L_LINESTATUS,
     L_SHIPDATE,
     L_COMMITDATE,
     L_RECEIPTDATE,
     L_SHIPINSTRUCT,
     L_SHIPMODE,
     L_COMMENT
   ) values (
     :in_ORDERKEY,
     :in_PARTKEY,
     :in_SUPPKEY,
     :in_LINENUMBER,
     :in_QUANTITY,
     :in_EXTENDEDPRICE,
     :in_DISCOUNT,
     :in_TAX,
     :in_RETURNFLAG,
     :in_LINESTATUS,
     :in_SHIPDATE,
     :in_COMMITDATE,
     :in_RECEIPTDATE,
     :in_SHIPINSTRUCT,
     :in_SHIPMODE,
     :in_COMMENT
   );
   end loading;
   logoff;
   

   Skrip FastLoad memuat data dari file dalam direktori yang sama yang bernama lineitem.tbl, yang Anda buat di bagian sebelumnya.

4. Simpan perubahan dan tutup file tersebut.

5. Jalankan skrip FastLoad:

   fastload < fastload_lineitem.fl
   

6. Ulangi prosedur ini untuk tabel TPC-H lainnya yang tercantum di bagian 1.4 spesifikasi TPC-H. Pastikan Anda menyesuaikan langkah-langkah untuk setiap tabel.

Memigrasikan skema dan data ke BigQuery

Petunjuk tentang cara memigrasikan skema dan data ke BigQuery tercantum dalam tutorial terpisah: Memigrasikan data dari Teradata. Di bagian ini, kami telah menyertakan detail tentang cara melanjutkan langkah-langkah tertentu dalam tutorial tersebut. Setelah menyelesaikan langkah-langkah di tutorial lainnya, kembali ke dokumen ini dan lanjutkan ke bagian berikutnya, Memverifikasi hasil kueri.

Catatan: Anda menjalankan semua perintah di bagian ini di Cloud Shell.

Membuat set data BigQuery

Selama langkah-langkah awal konfigurasi Google Cloud, Anda diminta membuat set data di BigQuery untuk menyimpan tabel setelah tabel dimigrasikan. Berikan nama tpch pada set data tersebut. Kueri di akhir panduan memulai ini mengasumsikan nama ini, dan tidak memerlukan perubahan apa pun.

# Use the bq utility to create the dataset
bq mk --location=US tpch

Membuat akun layanan

Selain itu, sebagai bagian dari langkah-langkah konfigurasi Google Cloud, Anda harus membuat akun layanan Identity and Access Management (IAM). Akun layanan ini digunakan untuk menulis data ke BigQuery dan menyimpan data sementara di Cloud Storage.

# Set the PROJECT variable
export PROJECT=$(gcloud config get-value project)

# Create a service account
gcloud iam service-accounts create tpch-transfer

Berikan izin ke akun layanan yang dapat mengelola set data BigQuery dan area staging di Cloud Storage:

# Set TPCH_SVC_ACCOUNT = service account email
export TPCH_SVC_ACCOUNT=tpch-transfer@${PROJECT}.

# 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

Membuat bucket Cloud Storage staging

Satu tugas tambahan dalam konfigurasi Google Cloud adalah membuat bucket Cloud Storage. Bucket ini digunakan oleh BigQuery Data Transfer Service sebagai area staging untuk file data yang akan diserap ke BigQuery.

# Use gcloud storage to create the bucket
gcloud storage buckets create gs://${PROJECT}-tpch --location=us-central1

Menentukan pola nama tabel

Selama konfigurasi transfer baru di BigQuery Data Transfer Service, Anda diminta untuk menentukan ekspresi yang menunjukkan tabel mana yang akan disertakan dalam transfer. Dalam panduan memulai ini, Anda menyertakan semua tabel dari database tpch.

Format ekspresi adalah database.table, dan nama tabel dapat diganti dengan karakter pengganti. Karena karakter pengganti di Java dimulai dengan dua titik, ekspresi untuk mentransfer semua tabel dari database tpch adalah sebagai berikut:

tpch..*

Perhatikan bahwa ada dua titik.

Memverifikasi hasil kueri

Pada tahap ini, Anda telah membuat data sampel, mengupload data ke Teradata, lalu memigrasikannya ke BigQuery menggunakan BigQuery Data Transfer Service, seperti yang dijelaskan dalam tutorial terpisah. Di bagian ini, Anda akan menjalankan dua kueri standar TPC-H untuk memverifikasi bahwa hasilnya sama di Teradata dan di BigQuery.

Menjalankan kueri laporan ringkasan harga

Kueri pertama adalah kueri laporan ringkasan harga (bagian 2.4.1 spesifikasi TPC-H). Kueri ini melaporkan jumlah item yang ditagih, dikirim, dan dikembalikan pada tanggal tertentu.

Listingan berikut menampilkan kueri lengkap:

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;

Jalankan kueri di Teradata:

1. Jalankan BTEQ dan hubungkan ke Teradata. Untuk mengetahui detailnya, lihat Membuat database TPC-H di awal dokumen ini.

2. Ubah lebar tampilan output menjadi 500 karakter:

   .set width 500
   

3. Salin kueri dan tempelkan pada prompt BTEQ.

   Hasilnya terlihat mirip dengan berikut ini:

   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
   

Jalankan kueri yang sama di BigQuery:

1. Buka konsol BigQuery:

   Buka BigQuery

2. Salin kueri ke editor kueri.

3. Pastikan nama set data di baris FROM sudah benar.

4. Klik Run.

   Hasilnya sama dengan hasil dari Teradata.

Secara opsional, Anda dapat memilih interval waktu yang lebih lebar dalam kueri untuk memastikan semua baris dalam tabel dipindai.

Menjalankan kueri volume pemasok lokal

Contoh kueri kedua adalah laporan kueri volume pemasok lokal (bagian 2.4.5 spesifikasi TPC-H). Untuk setiap negara di suatu wilayah, kueri ini menampilkan pendapatan yang dihasilkan oleh setiap item baris tempat pelanggan dan pemasok berada di negara tersebut. Hasil ini berguna untuk sesuatu seperti perencanaan lokasi pusat distribusi.

Listingan berikut menunjukkan kueri lengkap:

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;

Jalankan kueri di Teradata BTEQ dan di konsol BigQuery seperti yang dijelaskan di bagian sebelumnya.

Berikut adalah hasil yang ditampilkan oleh Teradata:

Berikut adalah hasil yang ditampilkan oleh BigQuery:

Teradata dan BigQuery menampilkan hasil yang sama.

Menjalankan kueri ukuran laba tipe produk

Pengujian terakhir untuk memverifikasi migrasi adalah kueri ukuran laba jenis produk dari kueri contoh terakhir (bagian 2.4.9 dalam spesifikasi TPC-H). Untuk setiap negara dan setiap tahunnya, kueri ini menemukan laba untuk semua bagian yang dipesan pada tahun tersebut. Hasil filter akan difilter menurut substring di nama bagian dan berdasarkan pemasok tertentu.

Listingan berikut menampilkan kueri lengkap:

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;

Jalankan kueri di Teradata BTEQ dan di konsol BigQuery seperti yang dijelaskan di bagian sebelumnya.

Berikut adalah hasil yang ditampilkan oleh Teradata:

Berikut adalah hasil yang ditampilkan oleh BigQuery:

Teradata dan BigQuery menampilkan hasil yang sama, meskipun Teradata menggunakan notasi ilmiah untuk penjumlahan tersebut.

Kueri tambahan

Secara opsional, Anda dapat menjalankan kueri TPC-H lainnya yang ditetapkan di bagian 2.4 spesifikasi TPC-H.

Anda juga dapat membuat kueri yang mengikuti standar TPC-H menggunakan alat QGEN, yang ada di direktori yang sama dengan alat DBGEN. QGEN dibuat menggunakan makefile yang sama seperti DBGEN. Jadi, saat menjalankan makefile untuk mengompilasi dbgen, Anda juga menghasilkan file qgen yang dapat dieksekusi.

Untuk informasi selengkapnya tentang alat dan opsi command line-nya, lihat file README untuk setiap alat.

Pembersihan

Agar tidak menimbulkan biaya pada akun Google Cloud Anda untuk resource yang digunakan dalam tutorial ini, hapus resource tersebut.

Menghapus project

Cara termudah untuk menghentikan penagihan biaya adalah dengan menghapus project yang Anda buat untuk tutorial ini.

Perhatian: Menghapus project memiliki efek berikut:
• Semua hal dalam project akan dihapus. Jika menggunakan project yang sudah ada untuk tugas dalam dokumen ini, saat Anda menghapusnya, pekerjaan lain yang telah Anda lakukan dalam project tersebut juga akan terhapus.
• Project ID kustom hilang. Saat membuat project ini, Anda mungkin telah membuat project ID kustom yang ingin digunakan di masa mendatang. Untuk mempertahankan URL yang menggunakan project ID, seperti URL appspot.com, hapus resource yang dipilih di dalam project, bukan menghapus seluruh project.
1. Di konsol Google Cloud, buka halaman Manage resource.

   Buka Manage resource

2. Pada daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

Langkah berikutnya

• Dapatkan petunjuk langkah demi langkah untuk Memigrasikan Teradata ke BigQuery.