Apache Cassandra: NoSQL Distributed Database

# =============================================
# INSTALASI CASSANDRA (Ubuntu/Debian)
# =============================================

# Install Java (wajib)
sudo apt install openjdk-11-jdk -y

# Tambah repository Cassandra
echo "deb https://debian.cassandra.apache.org 41x main" | \
  sudo tee /etc/apt/sources.list.d/cassandra.sources.list

# Import key
curl https://downloads.apache.org/cassandra/KEYS | sudo apt-key add -

# Install
sudo apt update
sudo apt install cassandra -y

# Cek status
sudo systemctl status cassandra
nodetool status

# =============================================
# CQLSH — Command Line Client
# =============================================
# CQLSH sudah terinstal bersama Cassandra
cqlsh
cqlsh -u cassandra -p cassandra  # login

# =============================================
# Docker (alternatif lebih mudah)
# =============================================
docker run -d --name cassandra \
  -p 9042:9042 \
  cassandra:4.1

# Tunggu ~30 detik sampai ready
docker exec -it cassandra cqlsh

-- =============================================
-- KEYSPACE = "Database" di Cassandra
-- =============================================
CREATE KEYSPACE IF NOT EXISTS ecommerce
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'datacenter1': 3,    -- 3 replica di datacenter1
    'datacenter2': 2     -- 2 replica di datacenter2
}
AND durable_writes = true;

-- Gunakan keyspace
USE ecommerce;


-- =============================================
-- TABEL: Data disimpan dalam tabel
-- =============================================
CREATE TABLE IF NOT EXISTS products (
    product_id   UUID,
    category     TEXT,
    product_name TEXT,
    price        DECIMAL,
    stock        INT,
    description  TEXT,
    created_at   TIMESTAMP,
    tags         SET,         -- Set collection
    PRIMARY KEY (product_id)
);

CREATE TABLE IF NOT EXISTS orders (
    customer_id  UUID,
    order_id     TIMEUUID,
    product_id   UUID,
    product_name TEXT,
    quantity     INT,
    total        DECIMAL,
    status       TEXT,
    order_date   TIMESTAMP,
    PRIMARY KEY (customer_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);


-- =============================================
-- TIPE DATA CASSANDRA
-- =============================================
-- Text types:    TEXT, VARCHAR, ASCII
-- Number types:  INT, BIGINT, SMALLINT, TINYINT, VARINT,
--                FLOAT, DOUBLE, DECIMAL
-- Date types:    TIMESTAMP, DATE, TIME, DURATION
-- ID types:      UUID, TIMEUUID (auto timestamp)
-- Collection:    LIST, SET, MAP
-- Other:         BOOLEAN, BLOB, INET, TUPLE<...>, FROZEN<...>


-- =============================================
-- COLLECTION TYPES
-- =============================================
CREATE TABLE user_profiles (
    user_id     UUID PRIMARY KEY,
    name        TEXT,
    emails      SET,           -- Kumpulan email unik
    phone_numbers LIST,        -- Kumpulan telepon (bisa duplikat)
    preferences MAP,     -- Key-value preferences
    address     FROZEN>  -- (street, city, country)
);

-- =============================================
-- INSERT
-- =============================================
INSERT INTO products (product_id, category, product_name, price, stock, created_at)
VALUES (uuid(), 'Elektronik', 'Laptop ASUS ROG', 15000000, 50, toTimestamp(now()));

INSERT INTO products (product_id, category, product_name, price, stock, tags)
VALUES (uuid(), 'Fashion', 'Kaos Polos', 85000, 200, {'cotton', 'unisex', 'casual'});

-- INSERT dengan TTL (data otomatis hilang setelah 86400 detik = 1 hari)
INSERT INTO products (product_id, category, product_name, price)
VALUES (uuid(), 'Promo', 'Flash Sale Item', 50000)
USING TTL 86400;


-- =============================================
-- SELECT
-- =============================================
-- Select semua (HATI-HATI di tabel besar!)
SELECT * FROM products;

-- Select dengan filter pada primary key
SELECT * FROM products WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;

-- Select berdasarkan customer_id (partition key)
SELECT * FROM orders WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11;

-- Select dengan LIMIT
SELECT * FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
LIMIT 10;

-- Select dengan RANGE pada clustering column
SELECT * FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
  AND order_id > maxTimeuuid('2026-01-01')
  AND order_id < minTimeuuid('2026-07-01');

-- Select dengan IN (multiple partition keys)
SELECT * FROM orders
WHERE customer_id IN (
    a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,
    b5eebc99-9c0b-4ef8-bb6d-6bb9bd380a22
);

-- Aggregation
SELECT COUNT(*) FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11;

SELECT SUM(total) FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11;

SELECT AVG(quantity), MIN(total), MAX(total) FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11;


-- =============================================
-- UPDATE
-- =============================================
UPDATE products SET stock = stock - 1
WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;

UPDATE products SET price = 14500000, tags = tags + {'gaming'}
WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;

-- Update dengan TTL
UPDATE products USING TTL 3600 SET stock = 0
WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;


-- =============================================
-- DELETE
-- =============================================
-- Delete satu baris
DELETE FROM products
WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;

-- Delete kolom tertentu
DELETE description FROM products
WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;

-- Delete dengan range (clustering column)
DELETE FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
  AND order_id < minTimeuuid('2025-01-01');


-- =============================================
-- BATCH (multiple write dalam satu operasi)
-- =============================================
BEGIN BATCH
    INSERT INTO orders (customer_id, order_id, product_id, product_name, quantity, total, status)
    VALUES (a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, now(), 550e8400-e29b-41d4-a716-446655440000,
            'Laptop ASUS ROG', 1, 15000000, 'pending');

    UPDATE products SET stock = stock - 1
    WHERE product_id = 550e8400-e29b-41d4-a716-446655440000;
APPLY BATCH;

-- =============================================
-- PATTERN 1: Single Column Primary Key
-- =============================================
-- product_id = partition key (dan satu-satunya key)
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    price DECIMAL
);
-- Query: SELECT * FROM products WHERE product_id = ?


-- =============================================
-- PATTERN 2: Composite Partition Key + Clustering
-- =============================================
-- customer_id = partition key
-- order_date  = clustering column (diurutkan ASC)
CREATE TABLE orders_by_date (
    customer_id UUID,
    order_date  DATE,
    order_id    TIMEUUID,
    total       DECIMAL,
    status      TEXT,
    PRIMARY KEY (customer_id, order_date, order_id)
);
-- Query: SELECT * FROM orders_by_date WHERE customer_id = ? AND order_date = ?


-- =============================================
-- PATTERN 3: Composite Partition Key
-- =============================================
-- (sensor_id, date) = composite partition key
-- time = clustering column
CREATE TABLE sensor_readings (
    sensor_id  UUID,
    date       DATE,
    time       TIMEUUID,
    value      DOUBLE,
    unit       TEXT,
    PRIMARY KEY ((sensor_id, date), time)
) WITH CLUSTERING ORDER BY (time DESC);
-- Query: SELECT * FROM sensor_readings
--        WHERE sensor_id = ? AND date = ? AND time > ?


-- =============================================
-- PATTERN 4: Tabel denormalized per query
-- =============================================
-- Data yang SAMA disimpan di tabel berbeda untuk query berbeda!
-- Ini NORMAL di Cassandra (bukan redundansi)

-- Query 1: "Tampilkan semua order customer X"
CREATE TABLE orders_by_customer (
    customer_id UUID,
    order_id    TIMEUUID,
    product     TEXT,
    total       DECIMAL,
    PRIMARY KEY (customer_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

-- Query 2: "Tampilkan semua order untuk produk Y"
CREATE TABLE orders_by_product (
    product_id  UUID,
    order_id    TIMEUUID,
    customer_id UUID,
    total       DECIMAL,
    PRIMARY KEY (product_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);
-- Data yang sama, dua tabel berbeda, untuk dua query berbeda!

-- =============================================
-- SIMPLE STRATEGY
-- =============================================
-- Cocok untuk single datacenter / development
-- Menyimpan replica pada node berikutnya secara clockwise
CREATE KEYSPACE dev_keyspace
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
};

-- =============================================
-- NETWORK TOPOLOGY STRATEGY (Produksi!)
-- =============================================
-- Cocok untuk multi-datacenter
-- Memperhatikan rack awareness
CREATE KEYSPACE production
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc_jakarta': 3,     -- 3 replica di Jakarta DC
    'dc_singapore': 2    -- 2 replica di Singapore DC
};

-- =============================================
-- Mengubah replication factor
-- =============================================
ALTER KEYSPACE production
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc_jakarta': 3,
    'dc_singapore': 3    -- tambah dari 2 ke 3
};

-- Cek replication setting
DESCRIBE KEYSPACE production;

-- =============================================
-- Repair (setelah ubah replication)
-- =============================================
-- Harus dijalankan setelah ubah replication factor
nodetool repair production

-- =============================================
-- SET CONSISTENCY PER SESSION
-- =============================================
CONSISTENCY QUORUM;
CONSISTENCY LOCAL_QUORUM;
CONSISTENCY ONE;

-- Cek consistency saat ini
CONSISTENCY;


-- =============================================
-- FORMULA: Read + Write Consistency
-- =============================================
-- Strong Consistency: R + W > Replication Factor
--
-- Contoh: RF = 3
--   Write QUORUM (2) + Read QUORUM (2) = 4 > 3 → Strong!
--   Write ONE (1)    + Read ALL (3)    = 4 > 3 → Strong!
--   Write ONE (1)    + Read ONE (1)    = 2 < 3 → Eventual!
--
-- Recommendation produksi (RF=3):
--   Write = LOCAL_QUORUM
--   Read  = LOCAL_QUORUM
--   → Strong consistency, toleransi 1 node down


-- =============================================
-- CONTOH: Write dengan consistency
-- =============================================
INSERT INTO orders (customer_id, order_id, total)
VALUES (a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, now(), 500000)
USING CONSISTENCY LOCAL_QUORUM;

-- CONTOH: Read dengan consistency
SELECT * FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
USING CONSISTENCY LOCAL_QUORUM;

-- =============================================
-- SKENARIO: Toko Online
-- Query yang dibutuhkan:
-- Q1: Tampilkan semua produk di kategori tertentu
-- Q2: Tampilkan semua pesanan customer
-- Q3: Tampilkan pesanan terbaru di semua customer
-- Q4: Cari produk berdasarkan nama
-- =============================================

-- Q1: Produk per kategori
CREATE TABLE products_by_category (
    category    TEXT,
    product_id  UUID,
    name        TEXT,
    price       DECIMAL,
    stock       INT,
    PRIMARY KEY (category, product_id)
);

-- Q2: Pesanan per customer (terbaru duluan)
CREATE TABLE orders_by_customer (
    customer_id UUID,
    order_date  TIMESTAMP,
    order_id    UUID,
    product     TEXT,
    quantity    INT,
    total       DECIMAL,
    status      TEXT,
    PRIMARY KEY (customer_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);

-- Q3: Pesanan terbaru global (gunakan bucket per hari)
CREATE TABLE orders_by_day (
    day_bucket  DATE,        -- partition key: 2026-06-26
    order_time  TIMESTAMP,   -- clustering
    order_id    UUID,
    customer_id UUID,
    total       DECIMAL,
    PRIMARY KEY (day_bucket, order_time, order_id)
) WITH CLUSTERING ORDER BY (order_time DESC, order_id ASC);

-- Q4: Search produk berdasarkan nama (secondary index)
CREATE TABLE products_by_name (
    first_letter TEXT,       -- partition: 'A', 'B', ...
    product_name TEXT,
    product_id   UUID,
    price        DECIMAL,
    PRIMARY KEY (first_letter, product_name, product_id)
);

-- =============================================
-- TTL (Time To Live)
-- =============================================
-- Data otomatis dihapus setelah waktu tertentu

-- Insert dengan TTL 1 jam (3600 detik)
INSERT INTO sensor_readings (sensor_id, date, time, value, unit)
VALUES (uuid(), '2026-06-26', now(), 25.5, 'celsius')
USING TTL 3600;

-- Insert dengan TTL 30 hari
INSERT INTO session_data (session_id, user_id, data)
VALUES (uuid(), uuid(), 'session_info')
USING TTL 2592000;  -- 30 * 24 * 60 * 60

-- Cek TTL yang tersisa
SELECT TTL(unit) FROM sensor_readings
WHERE sensor_id = xxx AND date = '2026-06-26';

-- Update TTL (perpanjang)
UPDATE sensor_readings USING TTL 7200
SET unit = 'celsius'
WHERE sensor_id = xxx AND date = '2026-06-26';

-- Hapus TTL (jadikan permanent)
UPDATE sensor_readings USING TTL 0
SET unit = 'celsius'
WHERE sensor_id = xxx AND date = '2026-06-26';


-- =============================================
-- LIGHTWEIGHT TRANSACTIONS (LWT)
-- =============================================
-- Compare-and-set: insert/update hanya jika kondisi terpenuhi
-- Berguna untuk uniqueness check

-- Insert hanya jika belum ada
INSERT INTO users (username, email, name)
VALUES ('johndoe', 'john@email.com', 'John Doe')
IF NOT EXISTS;

-- Update hanya jika nilai saat ini sesuai
UPDATE accounts SET balance = balance - 100000
WHERE account_id = xxx
IF balance >= 100000;

-- Result: applied = TRUE jika berhasil, FALSE jika gagal
-- [applied] | balance
-- ----------+--------
-- True      | 500000


-- =============================================
-- USER-DEFINED TYPES (UDT)
-- =============================================
CREATE TYPE address_type (
    street TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT
);

CREATE TABLE customers (
    customer_id UUID PRIMARY KEY,
    name TEXT,
    address FROZEN,
    billing_address FROZEN
);

INSERT INTO customers (customer_id, name, address)
VALUES (uuid(), 'Budi', {street: 'Jl. Sudirman 123', city: 'Jakarta',
                          province: 'DKI Jakarta', postal_code: '12190'});

-- =============================================
-- COMPACTION STRATEGY
-- =============================================
-- Compaction = proses merge SSTable yang sudah expired/tidak relevan

-- STCS (SizeTieredCompactionStrategy) — default
-- Cocok untuk write-heavy, jarang update
CREATE TABLE logs (
    id UUID PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP
) WITH compaction = {
    'class': 'SizeTieredCompactionStrategy',
    'min_threshold': 4,
    'max_threshold': 32
};

-- LCS (LeveledCompactionStrategy)
-- Cocok untuk read-heavy, sering update
CREATE TABLE user_profiles (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
) WITH compaction = {
    'class': 'LeveledCompactionStrategy'
};

-- TWCS (TimeWindowCompactionStrategy)
-- Cocok untuk time-series data dengan TTL
CREATE TABLE metrics (
    metric_id UUID,
    timestamp TIMESTAMP,
    value DOUBLE,
    PRIMARY KEY (metric_id, timestamp)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_size': 1,
    'compaction_window_unit': 'DAYS'
};

# =============================================
# NODETOOL — Maintenance Commands
# =============================================

# Cek status cluster
nodetool status

# Cek info node
nodetool info

# Cek ring (token distribution)
nodetool ring

# Flush memtable ke SSTable
nodetool flush

# Cleanup (hapus data yang bukan milik node ini)
nodetool cleanup

# Repair (sinkronisasi data antar replica)
nodetool repair ecommerce

# Compaction stats
nodetool compactionstats

# Cek tablestats
nodetool tablestats ecommerce.orders_by_customer

# Garbage collect tombstones
nodetool garbagecollect ecommerce orders_by_customer

# =============================================
# MONITORING
# =============================================
# Cek ukuran data per tabel
nodetool tablestats ecommerce

# Cek latensi
nodetool tablehistograms ecommerce orders_by_customer

# Snapshot (backup)
nodetool snapshot ecommerce -t my_backup_2026