# Download the latest QuestDB binary
QUESTDB_VERSION="7.4.0"
wget "https://github.com/questdb/questdb/releases/download/${QUESTDB_VERSION}/questdb-${QUESTDB_VERSION}-no-jre-bin.tar.gz"
tar -xzf "questdb-${QUESTDB_VERSION}-no-jre-bin.tar.gz"

# Install Java if not present
sudo apt update && sudo apt install -y openjdk-17-jdk-headless

# Move QuestDB to system directory
sudo mkdir -p /opt/questdb
sudo mv questdb-*/bin/questdb.jar /opt/questdb/

# Create startup script
cat > /usr/local/bin/questdb << 'EOF'
#!/bin/bash
exec java -server -Xms512m -Xmx4g \
  -Djava.io.tmpdir=/tmp \
  -jar /opt/questdb/questdb.jar \
  -d /var/lib/questdb "$@"
EOF
chmod +x /usr/local/bin/questdb

docker run -d \
  --name questdb \
  -p 9000:9000 \   # Web console and REST API
  -p 9009:9009 \   # InfluxDB Line Protocol (ILP) ingestion
  -p 8812:8812 \   # PostgreSQL wire protocol
  -v questdb_data:/var/lib/questdb \
  questdb/questdb:latest

docker logs -f questdb

sudo useradd -r -d /var/lib/questdb -s /bin/false questdb
sudo mkdir -p /var/lib/questdb
sudo chown questdb:questdb /var/lib/questdb

cat > /etc/systemd/system/questdb.service << 'EOF'
[Unit]
Description=QuestDB Time-Series Database
After=network.target

[Service]
Type=simple
User=questdb
ExecStart=/usr/local/bin/questdb start
ExecStop=/usr/local/bin/questdb stop
Restart=on-failure
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now questdb

-- Create a time-series table for server metrics
-- Use the QuestDB web console or psql client

CREATE TABLE server_metrics (
    ts TIMESTAMP,           -- designated timestamp column
    host SYMBOL,            -- SYMBOL is optimized for low-cardinality string columns
    region SYMBOL,
    cpu_usage DOUBLE,
    memory_mb LONG,
    disk_io_mb DOUBLE
) TIMESTAMP(ts)             -- designated timestamp for time-series ordering
PARTITION BY DAY;           -- partition by day for efficient queries

-- Insert sample data
INSERT INTO server_metrics VALUES (
    now(), 'web01', 'us-east', 45.2, 2048, 12.5
);

-- Insert multiple rows
INSERT INTO server_metrics (ts, host, region, cpu_usage, memory_mb)
VALUES
    (dateadd('m', -5, now()), 'web01', 'us-east', 42.1, 2100),
    (dateadd('m', -4, now()), 'web01', 'us-east', 48.3, 2050),
    (dateadd('m', -3, now()), 'web02', 'us-west', 35.7, 3072);

-- Basic time range query
SELECT ts, host, cpu_usage
FROM server_metrics
WHERE ts > dateadd('h', -1, now())
ORDER BY ts DESC;

-- Time-based aggregation with SAMPLE BY
-- Get average CPU per 5-minute bucket
SELECT
    ts,
    host,
    avg(cpu_usage) AS avg_cpu,
    max(cpu_usage) AS max_cpu,
    min(cpu_usage) AS min_cpu
FROM server_metrics
WHERE ts > dateadd('h', -24, now())
SAMPLE BY 5m;              -- QuestDB extension: bucket by time

-- Latest value per host
SELECT *
FROM server_metrics
LATEST ON ts PARTITION BY host;  -- QuestDB extension: last row per partition key

-- Moving average (1-hour window)
SELECT
    ts,
    host,
    cpu_usage,
    avg(cpu_usage) OVER (
        PARTITION BY host
        ORDER BY ts
        RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW
    ) AS moving_avg_cpu
FROM server_metrics
WHERE ts > dateadd('d', -1, now());

-- Calculate rate of change
SELECT
    ts,
    host,
    cpu_usage,
    cpu_usage - prev_cpu AS cpu_delta
FROM (
    SELECT ts, host, cpu_usage,
           lag(cpu_usage) OVER (PARTITION BY host ORDER BY ts) AS prev_cpu
    FROM server_metrics
    WHERE ts > dateadd('h', -1, now())
);

# Install psql client
sudo apt install -y postgresql-client

# Connect to QuestDB using PostgreSQL protocol
psql -h localhost -p 8812 -U admin -d qdb

# Run a query
psql -h localhost -p 8812 -U admin -d qdb -c "
SELECT ts, host, avg(cpu_usage) as avg_cpu
FROM server_metrics
WHERE ts > dateadd('h', -1, now())
SAMPLE BY 5m
ORDER BY ts DESC
LIMIT 20;
"

# Execute SQL via REST
curl -G "http://localhost:9000/exec" \
  --data-urlencode "query=SELECT ts, host, cpu_usage FROM server_metrics LATEST ON ts PARTITION BY host" \
  | python3 -m json.tool

# Write via ILP (TCP, port 9009)
echo "server_metrics,host=web01,region=us-east cpu_usage=45.2,memory_mb=2048i $(date +%s)000000000" \
  | nc -q1 localhost 9009

# High-throughput ingestion script
for i in {1..1000}; do
  echo "server_metrics,host=web$(( RANDOM % 5 + 1 )) cpu_usage=$(( RANDOM % 100 )).$(( RANDOM % 9 )),memory_mb=$(( RANDOM % 8192 + 512 ))i $(date +%s)000000000"
done | nc localhost 9009

# Write via HTTP (REST) - lower latency for small batches
curl -X POST "http://localhost:9000/write" \
  --data-binary "server_metrics,host=web01,region=us-east cpu_usage=45.2 $(date +%s)000000000"

# pip install questdb
from questdb.ingress import Sender, IngressError
import datetime
import random

with Sender('localhost', 9009) as sender:
    for i in range(10000):
        sender.row(
            'server_metrics',
            symbols={'host': f'web{random.randint(1,10)}', 'region': 'us-east'},
            columns={'cpu_usage': random.uniform(10, 95), 'memory_mb': random.randint(512, 8192)},
            at=datetime.datetime.utcnow()
        )
    sender.flush()
    print("10,000 rows ingested")

-- Partition by MONTH for less frequent data
CREATE TABLE iot_sensors (
    ts TIMESTAMP,
    device_id SYMBOL,
    sensor_type SYMBOL,
    value DOUBLE,
    unit SYMBOL
) TIMESTAMP(ts)
PARTITION BY MONTH;

-- Partition by HOUR for very high-frequency data
CREATE TABLE tick_data (
    ts TIMESTAMP,
    symbol SYMBOL,
    price DOUBLE,
    volume LONG
) TIMESTAMP(ts)
PARTITION BY HOUR;

-- DEDUP: Deduplicate on timestamp + key columns (QuestDB 7.3+)
-- Useful for exactly-once semantics when data sources can replay
CREATE TABLE sensor_readings (
    ts TIMESTAMP,
    sensor_id SYMBOL,
    value DOUBLE
) TIMESTAMP(ts)
PARTITION BY DAY
DEDUP UPSERT KEYS(ts, sensor_id);  -- Deduplicate by ts + sensor_id

-- Drop old partitions to manage disk space
ALTER TABLE server_metrics DROP PARTITION
    WHERE ts < dateadd('d', -90, now());

-- Check partition info
SELECT * FROM table_partitions('server_metrics');

# Install Grafana
sudo apt install -y grafana
sudo systemctl enable --now grafana-server

# Access Grafana at http://your-server:3000

SELECT
    $__timeGroupAlias(ts, $__interval),
    avg(cpu_usage) AS "CPU Usage %",
    host
FROM server_metrics
WHERE $__timeFilter(ts)
  AND host IN ($host)
SAMPLE BY $__interval FILL(LINEAR)
ORDER BY ts

-- IoT sensor table
CREATE TABLE iot_telemetry (
    ts TIMESTAMP,
    device_id SYMBOL,
    location SYMBOL,
    temperature DOUBLE,
    humidity DOUBLE,
    battery_pct SHORT,
    rssi SHORT
) TIMESTAMP(ts)
PARTITION BY DAY;

-- Find devices with low battery
SELECT device_id, location, last(battery_pct) AS battery
FROM iot_telemetry
WHERE ts > dateadd('h', -1, now())
  AND battery_pct < 20
GROUP BY device_id, location
ORDER BY battery ASC;

-- Detect temperature anomalies (2 std deviations above mean)
SELECT ts, device_id, temperature
FROM iot_telemetry
WHERE ts > dateadd('h', -24, now())
  AND temperature > (
      SELECT avg(temperature) + 2 * stddev(temperature)
      FROM iot_telemetry
      WHERE ts > dateadd('h', -24, now())
  )
ORDER BY ts DESC;

-- Downsample hourly for long-term storage
CREATE TABLE iot_telemetry_hourly AS (
    SELECT
        ts,
        device_id,
        avg(temperature) AS avg_temp,
        max(temperature) AS max_temp,
        avg(humidity) AS avg_humidity
    FROM iot_telemetry
    SAMPLE BY 1h
) TIMESTAMP(ts)
PARTITION BY MONTH;

# Check what's using the ports
ss -tlnp | grep -E "9000|9009|8812"

# Change ports in server.conf
nano /var/lib/questdb/conf/server.conf
# http.port=9000
# line.tcp.net.bind.to=0.0.0.0:9009
# pg.port=8812

# Check QuestDB logs
tail -f /var/lib/questdb/log/questdb.log

# ILP creates tables automatically - check if table was created
curl "http://localhost:9000/exec?query=SHOW+TABLES" | python3 -m json.tool

# Verify ILP port is open
nc -zv localhost 9009

# Always filter on the designated timestamp first
-- Bad (full table scan):
SELECT * FROM server_metrics WHERE host = 'web01';

-- Good (time-first, then filter):
SELECT * FROM server_metrics
WHERE ts > dateadd('h', -1, now()) AND host = 'web01';

# Use SYMBOL columns for low-cardinality strings (host, region, etc.)
# Avoid VARCHAR for repeated string values - use SYMBOL instead