Melakukan Extract & Transform dari .CSV ke dalam Tabel Operasional ERD

AUT: BanditHijo | PUB: 2018/03/20 | UPD: 2018/10/29

TUGAS 1

Mata Kuliah Data Warehouse & Data Mining
Nama Rizqi Nur Assyaufi
NIM 15.01.157
Kelas TI2-B

Soal

Buatlah tutorial melakukan Extract dan Transform dari data .CSV ke dalam tabel operasional dalam bentuk ERD.

Tutorial boleh dalam bentuk Video Youtube atau laporan makalah atau artikel Blog.

Materi tutorial silakan gunakan file yang disertakan.

Jika sudah selesai, segera kumpulkan disini.

File Attachment

  1. pasar_2014.csv
  2. pasar_data.sql

Jawaban

Pada tulisan ini saya menggunakan aplikasi manajemen database berbasis GUI yang bernama MySQL Workbench pada sistem operasi Arch Linux.

Gambar 1

Step 1: Membuat Connection

Apabila belum terdapat connection, kita dapat terlebih dahulu membuatnya dengan menekan icon (+) seperti yang ada pada Gambar di bawah.

Gambar 2


Nanti akan terbuka window baru, seperti gambar di bawah.

Gambar 3

Isi pada bagian text input berlabel Connection Name : dengan nama koneksi yang kalian inginkan. Sebagai contoh, saya mengisikan localhost 3306.


Lakukan tes koneksi dengan mengklik tombol Test Connection seperti yang ada pada gambar di bawah.

Gambar 4


Apabila berhasil, akan memunculkan window seperti gambar di bawah ini.

Gambar 5

Pilih OK, maka akan terbuat satu buah MySQL Connection yang bernama localhost 3306.

Gambar 6

Pada sistem operasi Arch Linux, akan terdapat window yang berisi pemberitahuan bahwa versi server yang sedang digunakan adalah versi 10, sedangkan yang didukung oleh MySQL Workbench adalah veri 5.1, 5.5, 5.6, 5.7. Kita dapat mengabaikan pesan ini dan memilih Continue Anyway.

Gambar 7


Akan terbuka sebuah tab baru dengan tampilan seperti di bawah.

Gambar 8

Step 2: Membuat Schema

Selanjutnya, kita akan mebuat database schema, dengan cara melakukan klik kanan pada area kosong di sisi sidebar sebelah kiri pada tab Schemas.

Gambar 9


Kemudian, pada area tengah, akan terbuka tab baru seperti di bawah.

Gambar 10


Isi text input Name : sesuai yang diinginkan.

Gambar 11


Apabila telah diberikan nama, pilih tombol Apply, maka akan terbuka window baru seperti di bawah.

Gambar 12

Pilih Apply > Close.


Akan terbuat database scheme dengan struktur seperti gambar di bawah.

Gambar 13

Step 3: Membuat Tabel-tabel

Selanjutnya kita akan memasukkan SQL Script untuk membuat tabel-tabel secara otomatis.

Pilih File > Open SQL Script…

Cari file pasar_data.sql yang telah disertakan bersama tugas. Apabila belum ada, bisa mengunduhnya terlebih dahulu di sini.

Apabila sudah, nanti akan terbuka tab baru di area tengah, seperti pada gambar di bawah.

Gambar 14


Selanjutnya, kita akan mengeksekusi SQL Script yang sudah kita masukkan.

Tapi sebelunya, kita perlu untuk membuat database schema yang kita buat sebelumnya, untuk dijadikan default schema.

Caranya, pada sidebar kiri, pada bagian tab Schemas klik kanan pada nama schema, kemudian pilih Set as Default Schema, seperti contoh di bawah.

Gambar 15


Kemudian, eksekusi SQL script dengan melakukan klik pada tombol bergambar petir. Seperti gambar di bawah.

Gambar 16


Perhatikan pada bagian bawah, terdapat Output Panel, akan terdapat pesan seperti di bawah ini.

Gambar 17


Apabila, telah berhenti memberikan output, sekarang kita pergi ke sidebar sebelah kiri, dan lakukan refresh agar menampilkan update terbaru dari database scheme.

Caranya dengan klik kanan pada area kosong dan pilih Refresh All.

Gambar 18


Hasilnya akan seperti gambar di bawah.

Gambar 19

Step 4: Ekstrak dan Transform Data

Terdapat 5 buah tabel:

  1. detil_komoditas
  2. komoditas
  3. pasar
  4. satuan
  5. transaksi

Kita tidak dapat memasukkan data hasil ekstrak dan transform secara acak. Kita perlu membaca relationship tables (relasi antar tabel). Untuk itu kita memerlukan ERD (Entity Relationship Diagram).

4.1 Entity Relationship Diagram

Caranya, pergi ke menu Database > Reverse Engineer….

Gambar 20


Kemudian, masukkan MySQL Connection yang telah kita buat pada Step 1, ke dalam Stored connection. Pada contoh saya localhost 3306.

Gambar 21

Selanjutnya pilih Next.


Gambar 22

Pilih Next.


Pilih Database Scheme yang kita buat.

Gambar 23

Pilih Next.


Gambar 24

Pilih Next.


Gambar 25

Pilih Execute > Next > Close.


Nanti akan terbuka tab EER Diagram.

Gambar 26

Gambar 27


Dari pembacaan relasi antar tabel di atas, dapat kita urutkan tabel mana yang akan kita kerjakan terlebih dahulu.

  1. Tabel pasar
  2. Tabel komoditas
  3. Tabel satuan
  4. Tabel detil_komoditas
  5. Tabel transaksi

4.2 Mengekstrak CSV

Kita akan melakukan ekstraksi file pasar_2014.csv ke dalam bentuk struktur tabel.

Caranya, kembali ke tab localhost 3306 (di bagian atas sebelah kiri, disebelah icon Home). Kemudian pada sidebar kiri, pada database schema yang kita buat, klik kanan dan pilih Table Data Import Wizard.

Gambar 28


Nanti akan terbuka window baru seperti di bawah.

Gambar 29

Cari file pasar_2014.csv yang telah disediakan bersama soal. Atau kalian dapat mengunduhnya di sini.

Kalo sudah, pilih Next >.


Berikan nama tabel dari file .csv yang ingin kita ekstraksi.

Gambar 30

Pilih Next >.


Periksa bagian Columns :, apakah sudah sesuai dengan kategori dari masing-masing kolom / fields.

Gambar 31

Pilih Next >.


Gambar 32

Pilih Next >.


Nah, kalo sudah menampilkan seperti di bawah, proses import data artinya telah sukses.

Gambar 33

Pilih Next >.


Selanjutnya periksa banyaknya data yang telah berhasil diimport, harus sesuai dengan banyaknya data yang ada pada file pasar_2014.csv, yaitu sebanyak 18.940 records.

Gambar 34

Pilih Finish.


Kemudian, kembali ke sidebar sebelah kiri. Belum terdapat tabel data yang baru saja kita buat dari hasil ekstrak file pasar_2014.csv. Kita perlu melakukan Refresh All pada database schema. Setelah melakukan refresh, akan muncul tabel data seperti gambar di bawah.

Gambar 35


Kita akan melihat hasil ekstrak dari file .csv yang sudah kita import. Dengan cara melakukan klik pada icon di samping.

Gambar 36


Akan memunculkan hasil seperti di bawah.

Gambar 37

4.3 Transform Hasil Exstrak

Selanjutnya kita akan melakukan transformasi dari tabel data yang seelunya kita ekstrak dari file pasar_2014.csv.

Untuk melakukan transform data, kita berpindah saja ke tab Query 1. Di sini kita akan mengetikkan perintah-perintah SQL untuk melakukan transformasi tabel data.

Gambar 38


Selanjutnya kita akan melakukan transformasi dari tabel data berurut sesuai dengan hasil pembacaan relasi tabel ERD.

4.3.1 Tabel Pasar

Kita akan mentransformasi fields namapasar dari tabel data, dengan queries seperti di bawah ini.

SELECT @nomor:[email protected]+1 as id, namapasar
FROM data,(SELECT @nomor:=0)r
GROUP BY namapasar;

Kemudian Excute dengan menekan tombol bergambar petir.


Akan menghasilkan tabel hasil transformasi dari tabel data seperti gambar di bawah.

Gambar 39


Kemudian, export ke dalam format .csv, dengan menekan tombol Export.

Gambar 40

berikan nama, misal data_pasar.csv, karena kita akan memasukkan ke dalam tabel pasar.


Buka tabel pasar, atau view tabel pasar. Klik tombol Import untuk mengambil file data_pasar.csv yang tadi sudah kita export.

Gambar 41


Akan memunculkan hasil seperti gambar di bawah.

Gambar 42


Kita perlu menghapus baris pertama karena bukan berisi record yang kita perlukan.

Gambar 43


Dan akan menghasilkan isi tabel seperti gambar di bawah.

Gambar 44

Apabila seperti di atas, artinya records data yang kita perlukan sudah tepat.


Selanjutnya kita perlu melakukan Apply. Klik tombol Apply pada pojok kanan bawah.

Gambar 45


Akan terbuka window seperti di bawah yang berisi queries untuk memasukkan data ke dalam tabel data.

Gambar 46

Pilih Apply.

Apabila berhasil dan tidak ada error, pilih Close. Data yang kita import tadi akan masuk ke dalam tabel pasar.

Apabila gagal, maka kita perlu meninjau kembali data yang kita dapat dari hasil queries yang kita buat saat melakukan transformasi dari tabel data, apakah sudah sesuai dengan relasi dari tabel yang kita tuju.

[ ! ] Perhatian

Untuk tabel selanjutnya, proses Export dan Import sama seperti tabel pasar, sehingga tidak saya tuliskan kembali.

Selanjutnya saya hanya akan menuliskan query syntax nya saja untuk melakukan transformasi tabel data ke bentuk yang diperlukan oleh tabel tujuan.

4.3.2 Tabel Komoditas

Kita akan mentransformasi fields komoditas dari tabel data, dengan queries seperti di bawah ini.

SELECT @nomor:[email protected]+1 as id, komoditas
FROM data,(SELECT @nomor:=0)r
GROUP BY komoditas;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel komoditas.

4.3.3 Tabel Satuan

Kita akan mentransformasi fields satuan dari tabel data, dengan queries seperti di bawah ini.

SELECT @nomor:[email protected]+1 as id, satuan
FROM data,(SELECT @nomor:=0)r
GROUP BY satuan;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel satuan.

4.3.4 Tabel Detil Komoditas

Kita akan mentransformasi fields detilkomoditas dari tabel data yang berelasi dengan tabel komoditas, dan satuan dengan queries seperti di bawah ini.

SELECT
@nomor:[email protected]+1 as id,
data.detilkomoditas,
komoditas.id_komoditas,
satuan.id_satuan

FROM
data,
komoditas,
satuan

WHERE
data.komoditas=komoditas.nama_komoditas
AND
data.satuan=satuan.nama_satuan;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel detil_komoditas.

4.3.5 Tabel Transaksi

Kita akan mentransformasi fields tanggal, harga, jumlahterjual dari tabel data yang berelasi dengan tabel detil_komoditas dan tabel pasar dengan queries seperti di bawah ini.

SELECT
detil_komoditas.id_detil_komoditas,
pasar.id_pasar,
data.tanggal,
data.harga,
data.jumlahterjual

FROM
data,
detil_komoditas,
pasar

WHERE
data.detilkomoditas=detil_komoditas.nama_detil_komoditas
AND
data.namapasar=pasar.nama_pasar;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel transaksi.


Maka proses ekstrak dan transformasi dari file pasar_2014.csv ke dalam tabel operasional ERD telah selesai.

Gambar 27

Lisensi

logo_cc-by-nc-sa