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.
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
Metode 2 : menggunakan SQL statement COPY, copy memungkinkan kita untuk menentukan format file teks sehingga dapat diuari menjadi baris dan kolom.
Metode 3 : menggunakan gpfdist dan gpload, digunakan untuk load data dalam jumlah besar dalam waktu singkat dan memungkinkan mencapai transfer data paralel yang cepat.
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
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
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;
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
