CLOUDBERRY DATABASE - 101

Halo semuanyaa, pada kali ini kita akan belajar database cloudberry, jadi ini adalah salah satu database yang based postgresql yang dioptimasi. Kita akan melanjutkan dari bab sebelumnya dimana kita akan masuk ke section 101, pada bab ini akan terdapat 6 Lesson yang akan kita pelajari 

Lesson 0

Databases (Relational Database) adalah software yang digunakan untuk store dan manage/proses data.Database ini mengimplementasikan sebuah server yang mempunyai multiple clients dan bisa read atau update data.

client biasanya menggunakan SQL languange untuk mengakses data. Data database disimpan dalam objek yang disebut table, table memiliki struktur atau kolom yang telah ditentukan sebelumnya dan memiliki nol atau beberapa baris.Table dikelompokkan dalam sebuah scema.

System database beberapa data seperti catalog data. Catalog database berisi informasi tentang object data dan object pendukung serta hal lain yang perlu disimpan di system level (user authentication, etc...)

SQL (Structured Query Language) adalah bahasa deskriptif, bukan bahasa imperatif. Ketika pengguna user mendeskripsikan apa yang diinginkan maka database perlu memutuskan bagaimana mendapatkannya. Proses tersebut disebut query optimization. Hasil akhir dari proses ini adalah query plan yang merupakan step by step intruction untuk mendapatkan hasil akhir.

Lesson 1

Cloudberry database manage database akses menggunakan roles dan disini ada 1 superuser role, role terasosiasi dengan user OS yang menginisialisasi instance database, biasanya gpadmin. User ini memiliki semua file database cloudberry dan OS processs, jadi penting untuk reserve gpadmin role hanya untuk system task.

Role dapat berupa user atau group, user role dapat masuk ke database yang artinya ia memiliki LOGIN atribut, user atau group dapat menjadi anggota group.

Awalnya hanya user gpadmin yang mempunyai akses untuk create roles, kita bisa membuat user dengan permissionnya menggunakan perintah createuser, CREATE ROLE, dan CREATE USER.

hanya perintah CREATE ROLE dan CREATE USER yang secara otomatis menetapkan atribut LOGIN ke user tersebut.

remote container lalu masuk ke user gpadmin, gunakan perintah psql untuk masuk ke database

ceate user dengan nama lily dengan CREATE USER command dengan password apapun contoh disini saya menggunakan password 'secret', lalu kita bisa cek dengan perintah \du maka nanti akan muncul role apa saja yang ada disini ada gpadmin yaitu default role lalu dibawahnya ada lili yang merupakan user yang sudah kita buat, sayangnya disini lili tidak mempunyai permissions/atribut.

lalu kita bisa keluar dari psql dengan \q lalu coba buat user dengan nama lucy dengan createuser command, lalu ada pertanyaan disini kita jaawab y yang artinya lucy adalah superuser. Lalu cek dengan masuk ke psql dan pastikan bahwa user lucy mempunyai permission.

lalu kita belajar membuat group menggunakan perintah CREATE ROLE, lalu masukkan user lily dan lucy kedalam group tersebut dengan perintah GRANT (group) TO (user);

lalu kita coba login menggunakan kedua user yang sudah kita buat sebelumnya maka tidak akan bisa, 

untuk itu kita harus mengedit file pg_hba.conf telebih dahulu

note :

- pg_hba.conf adalah file konfigurasi di cloudberry database untuk control access permissions

- md5 dan trust adalah autentikasi method, md5 berarti user harus memasukkan password untuk login dan trust berarti user bisa login tanpa password.

Jika sudah coba cek dengan login menggunakan 2 user tersebut, jika sudah maka user lily akan dimintai password sedangkan user lucy tidak.

Lesson 2

untuk membuat database baru kita bisa menggunakan createdb di terminal dan

CREATE DATABASE di psql. disini kita akan membuat database bernama tutorial namun sebelum itu kita cek apakah ada database tutorial dengan cara menghapusnya dengan command dropdb jika error does not exist berarti database tidak ada dan kita bisa membuat database dengan nama tersebut. Untuk mengecek apakah database tersebut sudah terbuat atau belum kita bisa menggunakan perintah psql -l

agar user lily bisa masuk ke database tutorial edit terlebih dahulu file pg_hba.conf lalu restart

dengan gpstop -u, kita ingin agar user lily bisa login ke database tutorial,

untuk itu kita bisa menambahkan md5 ke user lily lalu reload dengan perintah gstop -u

lalu coba masuk ke database tutorial dengan user lily

 jika sudah bisa keluar dari psql lalu masuk menggunakan user gpadmin,

disini kita akan memberikan permission ke user lily agar mempunyai akses ke database tutorial

Create schema and set search path

database schema adalah nama wadah untuk sekumpulan database object seperti tables, data types dan functions. Satu database bisa mempunyai multiple schemas. Object di schema direferensikan dengan mengawali nama object dengan nama schema dengan dipisahkan oleh sebuah titik. example, table TKJ1 yang berada di SMAKENGO schema maka akan dituliskan seperti SMAKENGO.TKJ1

masuk ke user lily, lalu kita kita akan membuat chema faa karena kita tidak mengetahui apakah

schema ini sudah ada atau belum kita bisa mencoba untuk menghapusnya terlebih

dahulu lalu membuat schema yang baru.

search path pertama di search path merupakan schema tempat object baru dibuat ketika tidak ada

schema yang ditentukan. default search path adalah user, public, dan setiap object yang kita buat

termasuk dalam schema terkait dengan user login anda secara default.

lalu kita akan membuat search path ke faa, public, pg_catalog. gp_toolkit. Jika sudah kita bisa cek

dengan SHOW search_path

lalu kita associate kan search path ini dengan user lily

Lesson 3

disini sistem sudah menyiapkan beberapa contoh file sql yang nantinya akan menjadi contoh

pembelajaran kita yang sudah disimpan dalam file faa.tar.gz, untuk itu kita bisa extract dengan

tar xzf (nama-file), jika sudah kita ubah workdir kita di directori /tmp/faa. perintah more (nama file) sama dengan cat (nama file), command ini berfungsi

untuk memperlihatkan kita isi sebuah file

jika sudah coba masuk ke database tutorial dengan user lily, run file sebelumnya yang sudah kita cek

dengan perintah \i (nama file)

didalam file yang sudah kita more sebelumnya terdapat beberapa perintah untuk membuat beberapa

table dalam schema faa, untuk itu cek table yang ada didalam schema faa

Lesson 4 

Terdapat 3 metode untuk load data kedalam database, pada bab ini kita loasa data FAA kedalam tabel

Database Cloudberry yang telah  dibuat sebelumnya.

1. Metode 1 : gunakan INSERT, adalah cara termudah untuk load data, kita dapat langsung mengeksekusinya di psql. Tidak disarankan untuk load data dalam jumlah besar karena loading efficiency rendah

2. Metode 2 : menggunakan SQL statement COPY, copy memungkinkan kita untuk menentukan format file teks sehingga dapat diuari menjadi baris dan kolom.

3. Metode 3 : menggunakan gpfdist dan gpload, digunakan untuk load data dalam jumlah besar dalam waktu singkat dan memungkinkan mencapai transfer data paralel yang cepat.

1. INSERT

pada schema faa kita mempunyai table bernama d_cancellation_codes dan disini tablenya masih

kosong hanya berisi 2 colom yaitu cancel_code dan cancel_desc

kita masukan data kedalamnya menggunakan cara yang pertama yaitu INSERT

2. COPY

cara yang kedua yaitu copy, menggunakan file .csv. Cek terlebih dahulu setiap file .csv atau .sql yang

ada dibawah ini

masuk ke database tutorial dengan user lily seperti sebelumnya, lalu run command dibawah ini

kedalam faa table

3. GPFDIST

untuk fact table akan menggunakan proses ETL (Extract, Transform, Load) process untuk load data

dari source gzip files kedalam data table. Untuk best loading speed gunakan gpfdist untuk

mendistribusikan baris dalam segments.

start gpfdist dengan command,  gpfdist -d /tmp/faa -p 8081 > /tmp/gpfdist.log 2>&1 &

In this operation:

• -d /tmp/faa: Specifies the directory where the data files reside. The utility will serve the files from /tmp/faa.

• -p 8081: Sets the port on which gpfdist will listen, in this case, port 8081.

• > /tmp/gpfdist.log: Redirects standard output to a log file at /tmp/gpfdist.log.

• 2>&1: Ensures that both standard output and standard error are redirected to the same log file.

• &: Runs the process in the background.

Once executed, you see 4340, which indicates the background job number [1] and its process ID 4340.

lalu check running process, dan lihat log file di gpfdist.log dengan command, ps -ef | grep gpfdist

jika sudah seperti diatas masuk ke database tutorial, run file create_load_tables.sql 

load data dari file external, jalankan file create_ext_table.sql

jika sudah pindahkan data dari faa.ext_load_otp ke faa.faa_otp_load

jika ada kesalah kita bisa cek dengan \x lalu select DISTINCT relname, errmsg, count(*) from

gp_read_error_log('faa.ext_load_otp') GROUP BY 1,2;

4. GPLOAD

sebelum menggunakan gpload kita harus memastikan bahwa gpfdist process harus mati, untuk itu kita

bisa cek dan mematikan process itu terlebih dahulu

cek isi file gpload.yaml bisa dengan perintah more atau cat

 lalu run gpload command, anda bisa menyertakan -v jika ingin lebih detail lagi

jika terjadi kesalah kita bisa cek file log di gpload.log. Final step dari ELK adalah memindahkan data

dari load table ke fact table.

Lesson 5

query plan adalah serangkaian operations yang digunakan untuk menjawab query, setiap node atau

step dalam plan mewakili database operation seperti table, scan join, aggregation atau sort. plan dibaca

dari bawah ke atas.

command ANALYZE menghasilkan statistik tentang distribusi data dalam sebuah table, secara khusus menyimpan histogram tentang nilai disetiap kolom.

pastikan kita berada di /tmp/faa tempat filee-file ini berada, EXPLAIN menjelaskan metode yang

dipilih optimizer untuk menghasilkan query. Explain menggunakan statistik yang dihasilkan

oleh ANALYZE command, sehingga plan yang dihasilkan sebelum dan sesudah ANALYZE dijalankan

bisa sangat berbeda.

lalu meminta plan explain untuk COUNT () agregat

pertama adalah pemindaian berurutan pada setiap server segmen untuk mengakses baris, Finalize

Aggregate adalah agregasi pada setiap server segment untuk menghasilkan hitungan jumlah baris

dari segment tersebut. Lalu pengumpulan nilai hitungan ke satu lokasi dan yang terakhir hitungan

dari setipa segment dikumpulkan untuk menghasilkan hasil akhir.

command EXPLAIN ANALYZE benar-benar menjalankan query (tanpa mengembalikan result set),

angka biaya mencerminkan realtime dan juga menghasilkan beberapa statistik memori dan I/O

 

secara default instance sandbox menonaktifkan Pivotal Query Optimizer, cek apakah aktif atau tidak

dengan command gpconfig -s optimizer

lalu nonaktifkan legacy query optimizer dengan command gpconfig -c optimizer -v off --masteronly

lalu reload dengan gpsotop -u

Indexes and Performance

gunakan legacy optimizer untuk mengetahui bagaimana indeks dapat meningkatkan kinerja, pertama

kita masuk kedatabase tutorial dengan user gpadmin lalu jalankan pencarian single row di table

sample tanpa indeks

jika error coba ulangi command \i create_sample_table.sql seperti dibawah ini

run \timing on agar dapat melihat efek dari berbagai ukuran penyesuaian performa

Perhatikan perbedaan waktu antara baris tunggal SELECT dengan dan tanpa indeks. Perbedaannya

akan jauh lebih besar untuk tabel yang lebih besar, karena indeks meningkatkan kinerja kueri

pada kumpulan data yang besar. Perhatikan bahwa meskipun indeks ada, pengoptimal mungkin

memilih untuk tidak menggunakannya jika tersedia rencana lain yang lebih efisien.

Lihat rencana berikut EXPLAINuntuk membandingkan rencana untuk beberapa jenis kueri umum lainnya.

Baris VS Kolom

dapat menyimpan table dalam orientasi barus atau kolom, semua mempunyai keunggulan tergantung

pada karakteristik kompresi data, jenis kueri yg dijalankan, panjang baris, kompleksitas, dan jumlah

kolom gabungan.

buat fact table FAA On Time Performance versi column-oriented dan masukan data dari versi

row-oriented.

CREATE TABLE FAA.OTP_C (LIKE faa.otp_r) WITH (appendonly=true,

orientation=column)DISTRIBUTED BY (UniqueCarrier, FlightNum) PARTITION BY RANGE(FlightDate) ( PARTITION mth START('2009-06-01'::date) END ('2010-10-31'::date) EVERY ('1 mon'::interval)); CREATE TABLE

Perhatikan bahwa versi berorientasi kolom hanya dapat ditambahkan dan dipartisi. Ia memiliki

tujuh belas file anak untuk partisi, satu untuk setiap bulan dari Juni 2009 hingga Oktober 2010.

bandingkan ukuran table menggunakan function pg_relation_size() dan pg_total_relation_size().

Fungsi pg_size_pretty() mengubah ukuran dalam byte menjadi satuan yang dapat dibaca manusia

Check for even data distribution on segments

Tujuan utama distribusi adalah untuk mendapatkan jumlah data yang seimbang di setiap segmen.

Kueri berikutnya menunjukkan hal ini. Mengingat tabel berorientasi kolom dan tabel berorientasi

baris berbagi kolom distribusi yang sama, jumlah keduanya harus sama.

About Partitioning

Lesson 6

gpbackup tidak disediakan secara default untuk itu kita harus mengintsall nya terlebih dahulu,

Install Go Command :

sudo yum update -y

wget https://go.dev/dl/go1.20.5.linux-amd64.tar.gz

sudo tar -C /usr/local -xzf go1.20.5.linux-amd64.tar.gz

echo "export PATH=\$PATH:/usr/local/go/bin" >> ~/.bashrc

echo "export GOPATH=\$HOME/go" >> ~/.bashrc

echo "export PATH=\$PATH:\$GOPATH/bin" >> ~/.bashrc

source ~/.bashrc

go version

Install gpbackup : 

source : https://github.com/cloudberrydb/gpbackup 

disini command gpbackup masih belum bisa berjalan, untuk itu kita bisa cloning githubnya lalu jalankan make command didalam directory gpbackup

lalu jalankan perintah backup nya : gpbackup --dbname tutorial --backup-dir /tmp/backup

format backup : gpbackup --dbname database --backup-dir /path/for/backup

disini kita buat directory baru yang digunakan untuk backup

dan inilah result ketika gpbackup command dijalankan

lalu remove data

lalu restore dengan perintah gprestore, jika dijalankan maka akan seperti dibawah ini

lalu bagaimana cara mengetahui timestamp? kita bisa ke path dimana kita membackup datanya lalu kita ls seperti dibawah ini nah ini bisa kita ketahui sebagai timestamp yang akan kita restore

lalu kita cek dengan masuk ke database tutorial, dan bisa dilihat data dari table faa.otp_r; kembali seperti semula