Pendahuluan

Apakah Anda bingung bagaimana mengatur kerja dari ETL terutama jika faktor koneksi menjadi masalah ?

Misalkan jika sumber data yang sudah terbaca tidak bisa ditransfer dan harus diulangi lagi dari awal atau bisa di-resume dari titik terakhir.

Walau ini merupakan isu yang sangat sophisticated dan melibatkan trik CDC (Change Data Capture) tapi berikut ini saya membuat contoh transformasi Kettle yang mewakili kasus di atas :

• Transfer data satu table dari MySQL ke SQL Server dengan metode batching per 5000 baris record.
• Setiap batch akan dijaga 'state'-nya dengan menggunakan suatu text file var.dump.
• Jika koneksi terputus maka state offset akan terjaga dan akan diambil kembali jika job dijalankan kembali
  

Di dalam contoh ini akan ditunjukkan beberapa konsep penting controlling di Kettle :

• Looping
• Error handling di Job
• Penggunaan environment variable yang dikenali di keseluruhan paket job/transformation
• Penggunaan javascript untuk mengevaluasi nilai variable
  

Menjalankan Contoh

Data Contoh

Data contoh yang digunakan adalah table sales_fact_1997 dari database foodmart MySQL. Apabila Anda tidak memiliki database contoh ini dapat didownload dari halaman ini. Jika Anda melakukan semuanya dengan benar maka data dari table ini berisi 86,837 baris data.

Untuk table tujuan kita namakan juga sales_fact_1997 dengan struktur yang sama di MS SQL Server. Download script sql  sales_fact_1997_sql_server.sql yang dapat didownload di akhir dari artikel. Eksekusi script tersebut di Query Analyzer untuk membentuk table tersebut.

Contoh File Job / Transformation

• Setelah data telah ter-setup dengan baik, maka langkah selanjutnya adalah mendownload semua file-file job dan transformation pada akhir artikel ini :
• main_job.kjb - file job sebagai kontrol utama terhadap proses ETL
• set_initial_var.ktr - file transformation yang berfungsi membuat file var.dump dan inisialisasi nilai counter awal
• get_and_set_initial_var.ktr - file transformation untuk menghitung offset dari data dan set variable
  
• db_to_db.ktr - file transformation utama untuk memindahkan data table sales_fact_1997 dari MySQL ke SQL Server
• var_increment.ktr - file transformation yang digunakan untuk count down nilai yang digunakan untuk offset
  

• Letakkan semua file tersebut dalam satu folder.
  
  
  
  
  
• Jalankan Spoon dan buka file main_job.kjb
  
  
  
• Ubah konfigurasi database untuk semua file job/transformation.Konfigurasi yang digunakan ada dua, yaitu :
  
• MySQL_Conn : koneksi ke data asal (MySQL)
• STAGING : koneksi ke data tujuan (SQL Server)
  
  
  
  
  

Skenario - Kedua Titik Koneksi dalam keadaan Sempurna

• Hapus semua baris data dari table tujuan dengan perintah "delete from sales_fact_2007".
  
• Jalankan job sampai selesai dengan klik Run | Launch.
  
• Perhatikan log dari eksekusi job ini di Spoon dimana terjadi looping / perulangan step.
  
  
  
  
  

  

  
  
  
  
  
• Periksa data pada table tujuan. Untuk contoh ini saya gunakan count untuk memastikan jumlah data yang ditransfer dengan dari sumber asal.
  
  
  
  
  Gambar : Perintah Select Count pada MySQL
  
  
  

  

  Gambar : Perintah Select Count pada SQL Server

Skenario - Koneksi Tujuan Terputus di Tengah Eksekusi

• Hapus semua baris data dari table tujuan dengan perintah "delete from sales_fact_2007".
• Jalankan job dengan klik Run | Launch.
• Pada titik tertentu sebelum job selesai coba stop services dari control panel atau melalui SQL Service Manager
• Perhatikan log dari Spoon pada saat terjadi error sehingga eksekusi diberhentikan. Ada kemungkinan 2 titik yang menjadi failure point yaitu di step [Test Connection] atau [DB to DB Transform]. Pada contoh gambar di bawah ini maka yang menjadi point of failure saya adalah di step [DB to DB Transform]
  
  
  
  
  
• Start kembali service SQL Server Anda dan periksa jumlah row yang telah ditransform. Pada contoh saya, maka row yang telah ditransfer adalah 1,837 baris - jadi masih kurang 85,000 baris.
  
  
  
  
  
• Perhatikan folder file-file contoh Anda, bahwa sekarang terdapat file var.dump yang menyimpan state kita. Dalam hal ini counter offset.
  
  
  
  
  
• Jalankan kembali job kita sampai selesai.
• Periksa kembali jumlah baris data tujuan. Anda akan mendapatkan kembali 86,837 baris data.
  

Skenario - Koneksi Sumber Terputus di Tengah Eksekusi

• Hapus semua baris data dari table tujuan dengan perintah "delete from sales_fact_2007".
• Lakukan juga hal yang sama dengan memutuskan koneksi server MySQL sebagai sumber data
  
• Perhatikan jumlah data di server tujuan pada saat koneksi dari sumber putus
• Jalankan kembali server MySQL dan job Anda
• Perhatikan jumlah data akhir dimana Anda harus mendapatkan kembali 86,837 baris data.
  

Kelemahan Rancangan

Contoh yang dibuat disini jika digunakan dalam production environment tidak akan akurat karena contoh dibuat berdasarkan asumsi berikut :

• bahwa sumber data yang diambil adalah snapshot dari database transaksional yang tidak akan berubah pada saat terjadinya transformasi data.
  
• database asal memiliki fitur penggunaan offset dimana tidak semua sistem database memiliki fitur seperti itu terutama legacy system.
• penggunaan file teks sebagai penyimpan state sangat tidak sophisticated dan riskan terutama di sistem dimana permission terhadap file creation sangat dibatasi.

Pengembangan Contoh

Agar contoh ini dapat digunakan secara ideal memang dibutuhkan perancangan yang lebih kompleks dan penuh kehati-hatian serta pengkombinasian terhadap tool CDC seperti Golden Gate.

Namun kembali saya ingin gariskan bahwa contoh ini adalah demonstrasi penggunaan state yang sangat fleksibel di dalam Kettle dan bisa dikembangkan lebih lanjut sesuai kebutuhan kita. Termasuk di dalamnya adalah setting scheduler dari step Start, penggabungan dengan script batch atau Ant script untuk menghasilkan kontrol yang lebih reliable, dan beberapa workaround lainnya.

Penjelasan Penggunaan Fitur

Untuk penjelasan rincian fitur yang digunakan akan disambung ke bagian 2 dari artikel wiki ini dengan tujuan lebih terfokus dan  artikel tidak menjadi terlalu panjang. Lihat artikel Data Transfer Loop Handling (Bagian 2).

Contributed by : Feris Thia

Info Lebih Lanjut

Jika Anda masih mengalami kesulitan dan ingin informasi lebih lanjut silahkan gabung ke user group kita di http://groups.google.com/group/pentaho-id atau di http://pentaho-id.ning.com.