ScyllaDB: High Performance NoSQL Database

# Metode 1: Docker (recommended untuk testing)
docker run -d --name scylla-node1 \
    -p 9042:9042 \
    -p 10000:10000 \
    -v ~/scylla-data:/var/lib/scylla \
    scylladb/scylla:5.4 \
    --smp 2 --memory 4G --overprovisioned 1

# Tunggu beberapa detik sampai node ready
sleep 10

# Koneksi ke ScyllaDB
docker exec -it scylla-node1 cqlsh

# Metode 2: Ubuntu/Debian
sudo apt-get install -y apt-transport-https gnupg2 curl
curl -fsSL https://downloads.scylladb.com/deb/debian/scylla-5.4-x86_64.asc | \
    sudo gpg --dearmor -o /usr/share/keyrings/scylladb.gpg

echo "deb [signed-by=/usr/share/keyrings/scylladb.gpg] https://downloads.scylladb.com/deb/debian stable main" | \
    sudo tee /etc/apt/sources.list.d/scylla.list

sudo apt-get update
sudo apt-get install -y scylla
sudo systemctl start scylla-server
sudo systemctl enable scylla-server

# Verifikasi
nodetool status
cqlsh -e "SELECT release_version FROM system.local;"

# Node 1 (seed node)
docker run -d --name scylla1 --hostname scylla1 \
    -p 9042:9042 \
    scylladb/scylla:5.4 \
    --smp 1 --memory 2G \
    --overprovisioned 1 \
    --seeds scylla1

# Tunggu node 1 ready
sleep 15

# Node 2
docker run -d --name scylla2 --hostname scylla2 \
    scylladb/scylla:5.4 \
    --smp 1 --memory 2G \
    --overprovisioned 1 \
    --seeds scylla1

# Node 3
docker run -d --name scylla3 --hostname scylla3 \
    scylladb/scylla:5.4 \
    --smp 1 --memory 2G \
    --overprovisioned 1 \
    --seeds scylla1

# Cek cluster status dari node 1
docker exec -it scylla1 nodetool status

# Output:
# Datacenter: datacenter1
# =======================
# Status=Up/Down
# |/ State=Normal/Leaving/Joining/Moving
# --  Address    Load       Tokens  Owns    Host ID   Rack
# UN  172.17.0.2  256 KB    256     33.3%   xxx       rack1
# UN  172.17.0.3  256 KB    256     33.3%   xxx       rack1
# UN  172.17.0.4  256 KB    256     33.3%   xxx       rack1

-- Membuat keyspace (analog dengan database)
CREATE KEYSPACE IF NOT EXISTS myapp
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'datacenter1': 3
}
AND durable_writes = true;

-- Gunakan keyspace
USE myapp;

-- Membuat tabel
CREATE TABLE IF NOT EXISTS users (
    user_id UUID PRIMARY KEY,
    username TEXT,
    email TEXT,
    created_at TIMESTAMP,
    profile MAP
);

-- Tabel dengan composite primary key
CREATE TABLE IF NOT EXISTS messages (
    channel_id UUID,
    message_id TIMEUUID,
    author_id UUID,
    content TEXT,
    attachments LIST,
    reactions MAP,
    PRIMARY KEY (channel_id, message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

-- Tabel dengan multiple clustering columns
CREATE TABLE IF NOT EXISTS sensor_readings (
    sensor_id UUID,
    date TEXT,
    reading_time TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    location TEXT,
    PRIMARY KEY ((sensor_id, date), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC)
  AND default_time_to_live = 7776000;  -- TTL 90 hari

-- INSERT
INSERT INTO users (user_id, username, email, created_at)
VALUES (uuid(), 'budi_santoso', 'budi@example.com', toTimestamp(now()));

-- INSERT dengan TTL (data otomatis hilang setelah 86400 detik = 1 hari)
INSERT INTO users (user_id, username, email)
VALUES (550e8400-e29b-41d4-a716-446655440000, 'temp_user', 'temp@example.com')
USING TTL 86400;

-- INSERT dengan IF NOT EXISTS (lightweight transaction)
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'unique_user', 'unique@example.com')
IF NOT EXISTS;

-- SELECT
SELECT * FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- SELECT dengan token range (untuk range scan)
SELECT * FROM users WHERE token(user_id) > 0 AND token(user_id) < 1000000;

-- SELECT dengan clustering key
SELECT * FROM messages
WHERE channel_id = 123e4567-e89b-12d3-a456-426614174000
AND message_id > maxTimeuuid('2026-01-01 00:00:00')
AND message_id < minTimeuuid('2026-12-31 23:59:59')
LIMIT 50;

-- UPDATE
UPDATE users SET email = 'budi@new.com', profile['city'] = 'Jakarta'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- DELETE
DELETE FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- DELETE kolom tertentu
DELETE profile['city'] FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

-- BATCH (multi-table atomic)
BEGIN BATCH
    INSERT INTO users (user_id, username, email)
    VALUES (uuid(), 'new_user', 'new@example.com');
    INSERT INTO user_stats (user_id, login_count, last_login)
    VALUES (uuid(), 1, toTimestamp(now()));
APPLY BATCH;

-- Pattern 1: Cari user by email
CREATE TABLE users_by_email (
    email TEXT PRIMARY KEY,
    user_id UUID,
    username TEXT,
    created_at TIMESTAMP
);

-- Pattern 2: Cari pesan per channel (urut terbaru)
CREATE TABLE messages_by_channel (
    channel_id UUID,
    created_at TIMEUUID,
    message_id UUID,
    author_id UUID,
    content TEXT,
    PRIMARY KEY (channel_id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Pattern 3: Cari user berdasarkan role
CREATE TABLE users_by_role (
    role TEXT,
    user_id UUID,
    username TEXT,
    email TEXT,
    PRIMARY KEY (role, user_id)
);

-- Materialized view (di ScyllaDB lebih mirip auto-sync secondary table)
CREATE MATERIALIZED VIEW users_by_username AS
    SELECT * FROM users
    WHERE username IS NOT NULL AND user_id IS NOT NULL
    PRIMARY KEY (username, user_id);

-- Secondary index (gunakan hati-hati, hanya untuk low cardinality)
CREATE INDEX ON users (email);
-- Alternatif: SSTable Attached Secondary Index (SASI)
CREATE CUSTOM INDEX ON users (username)
USING 'StorageAttachedIndex';

-- Mengubah compaction strategy
ALTER TABLE users WITH compaction = {
    'class': 'LeveledCompactionStrategy',
    'sstable_size_in_mb': 160
};

-- SizeTieredCompactionStrategy dengan parameter
ALTER TABLE sensor_readings WITH compaction = {
    'class': 'SizeTieredCompactionStrategy',
    'min_sstable_size': 50,
    'min_threshold': 4,
    'max_threshold': 32,
    'bucket_high': 1.5,
    'bucket_low': 0.5
};

-- Menggunakan IncrementalCompactionStrategy
ALTER TABLE large_table WITH compaction = {
    'class': 'IncrementalCompactionStrategy',
    'space_amplification_goal': 1.1
};

-- Monitoring compaction
-- nodetool compactionstats
-- nodetool compactionhistory

-- Memaksa compaction (hanya untuk testing/ops)
-- nodetool compact myapp users

-- TTL: data otomatis dihapus setelah N detik
INSERT INTO sensor_readings (sensor_id, date, reading_time, temperature, humidity)
VALUES (uuid(), '2026-06-29', now(), 25.5, 60.0)
USING TTL 2592000;  -- 30 hari

-- Mengubah TTL tabel
ALTER TABLE sensor_readings
WITH default_time_to_live = 7776000;  -- 90 hari

-- gc_grace_seconds: waktu sebelum tombstone di-cleanup
-- Default 10 hari. Turunkan untuk tabel dengan banyak DELETE
ALTER TABLE temp_data WITH gc_grace_seconds = 86400;  -- 1 hari

-- Tips: Hindari tombstone heavy scan
-- Tambahkan tombstone_warn_threshold untuk monitoring
ALTER TABLE messages WITH tombstone_warn_threshold = 1000;

# Full repair: membandingkan semua data
nodetool repair myapp users

# Subrange repair (untuk tabel besar)
nodetool repair -st 0 -et 1000000 myapp users

# Repair dengan range spesifik
nodetool repair -full myapp

# Incremental repair (hanya repair data yang belum di-repair)
nodetool repair -inc myapp

# Parallel repair
nodetool repair -par myapp users

# ScyllaDB Manager: scheduled repair
sctool repair add --cluster my-cluster --keyspace myapp --interval 3d

# Cek repair status
sctool repair list --cluster my-cluster

-- Aktifkan CDC pada tabel
ALTER TABLE users WITH cdc = {
    'enabled': true,
    'preimage': true,    -- Ambil data sebelum perubahan
    'postimage': true,   -- Ambil data setelah perubahan
    'ttl': 86400         -- TTL untuk CDC log (1 hari)
};

-- CDC log tersimpan di tabel terpisah: <tabel>_scylla_cdc_log
-- Contoh: users_scylla_cdc_log

-- Melihat CDC log
SELECT * FROM users_scylla_cdc_log
WHERE cluster_clock > minTimeuuid('2026-06-29 00:00:00')
LIMIT 10;

-- Kolom di CDC log:
-- - cdc$stream_id: ID stream
-- - cdc$time: timestamp perubahan
-- - cdc$batch_seq_no: nomor sequence dalam batch
-- - cdc$operation: tipe operasi (1=insert, 2=update, 3=row_delete, 4=partition_delete)
-- - cdc$ttl: TTL dari data
-- - <kolom>_cdc$pre: nilai sebelum perubahan (jika preimage=true)
-- - <kolom>_cdc$pro: nilai setelah perubahan (jika postimage=true)

-- Menggunakan CDC dengan ScyllaDB CDC Connector (Kafka)
-- Data di-stream ke Kafka topic secara otomatis
-- Konfigurasi di connector:
-- {
--   "name": "scylla-cdc-connector",
--   "config": {
--     "scylla.host": "scylla1",
--     "scylla.port": "9042",
--     "scylla.keyspace": "myapp",
--     "scylla.table": "users",
--     "kafka.bootstrap.servers": "kafka:9092",
--     "kafka.topic": "scylla-cdc-users"
--   }
-- }

# Mengaktifkan Alternator pada ScyllaDB node
# Tambahkan flag berikut saat menjalankan ScyllaDB:
# --alternator-port 8000
# --alternator-write-isolation=only_rmw_uses_lwt

docker run -d --name scylla-alt \
    -p 9042:9042 -p 8000:8000 \
    scylladb/scylla:5.4 \
    --alternator-port 8000 \
    --alternator-write-isolation=only_rmw_uses_lwt

# Menggunakan AWS CLI untuk berinteraksi dengan Alternator
# (mengarahkan endpoint ke ScyllaDB)
aws dynamodb create-table \
    --endpoint-url http://localhost:8000 \
    --table-name Music \
    --attribute-definitions \
        AttributeName=Artist,AttributeType=S \
        AttributeName=SongTitle,AttributeType=S \
    --key-schema \
        AttributeName=Artist,KeyType=HASH \
        AttributeName=SongTitle,KeyType=RANGE \
    --billing-mode PAY_PER_REQUEST

# Insert item
aws dynamodb put-item \
    --endpoint-url http://localhost:8000 \
    --table-name Music \
    --item '{
        "Artist": {"S": "Band Indonesia"},
        "SongTitle": {"S": "Judul Lagu"},
        "Album": {"S": "Album Pertama"},
        "Year": {"N": "2026"}
    }'

# Query
aws dynamodb query \
    --endpoint-url http://localhost:8000 \
    --table-name Music \
    --key-condition-expression "Artist = :a" \
    --expression-attribute-values '{":a": {"S": "Band Indonesia"}}'

# Scan
aws dynamodb scan \
    --endpoint-url http://localhost:8000 \
    --table-name Music

# Cluster status
nodetool status

# Latency statistics
nodetool tablestats myapp users
nodetool tablehistograms myapp users

# Compaction status
nodetool compactionstats

# Thread pool statistics (per-core)
nodetool tpstats

# Read repair statistics
nodetool proxyhistograms

# Grafana monitoring
# ScyllaDB menyediakan Grafana dashboard bawaan
# akses di http://scylla-node:3000
# atau gunakan ScyllaDB Monitoring Stack:
docker run -d --name scylla-monitoring \
    -p 3000:3000 \
    -v /var/lib/scylla-monitoring:/etc/scylla-monitoring \
    scylladb/scylla-monitoring:latest