ClickHouse Maintenance Guide

-- Check tables with TTL
SELECT
    database,
    table,
    name as ttl_name,
    expression as ttl_expression
FROM system.ttl_expressions
ORDER BY database, table;

-- Apply TTL manually
ALTER TABLE database.table MODIFY TTL event_date + INTERVAL 90 DAY;

-- Optimize table storage
OPTIMIZE TABLE database.table FINAL;

-- Check if table needs optimization
SELECT
    database,
    table,
    formatReadableSize(data_uncompressed_bytes) as uncompressed_size,
    formatReadableSize(data_compressed_bytes) as compressed_size,
    rows_count
FROM system.parts_columns
WHERE (database, table) IN (
    SELECT database, table
    FROM system.tables
    WHERE engine = 'MergeTree'
)
GROUP BY database, table
HAVING count() > 10  -- Tables with many parts
ORDER BY uncompressed_size DESC;

-- Create backup
BACKUP TABLE database.table TO '/path/to/backup';

-- Restore from backup
RESTORE TABLE database.table FROM '/path/to/backup';

-- Check backup status
SELECT *
FROM system.backup_log
ORDER BY start_time DESC
LIMIT 10;

-- Analyze table structure
SHOW CREATE TABLE database.table;

-- Check table dependencies
SELECT
    database,
    table,
    name as dependency_name,
    type as dependency_type
FROM system.table_dependencies
WHERE database = 'your_database'
  AND table = 'your_table';

-- Verify table consistency
CHECK TABLE database.table;

-- Move table to different disk
ALTER TABLE database.table MOVE PARTITION tuple() TO DISK 'new_disk';

-- Attach/detach partition
ALTER TABLE database.table DETACH PARTITION partition_id;
ALTER TABLE database.table ATTACH PARTITION partition_id;

-- Export configuration
SELECT *
FROM system.settings
WHERE changed = 1
FORMAT TSV;

-- Import configuration
SET setting_name = 'value';

-- Create new user
CREATE USER username
IDENTIFIED WITH sha256_password BY 'password'
SETTINGS max_memory_usage = 20000000000;

-- Grant permissions
GRANT SELECT, INSERT ON database.* TO username;

-- Review user permissions
SHOW GRANTS FOR username;

-- Recover from detached partition
ALTER TABLE database.table ATTACH PARTITION partition_id;

-- Recover from backup
RESTORE TABLE database.table FROM '/path/to/backup';

-- Verify recovery
SELECT count() FROM database.table;