# Add MariaDB repository
sudo apt-get install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64] http://downloads.mariadb.com/ColumnStore/ubuntu jammy main'

# Install ColumnStore
sudo apt-get update
sudo apt-get install mariadb-columnstore-server

# Start services
sudo systemctl start mariadb-columnstore
sudo systemctl enable mariadb-columnstore

# Configure ColumnStore
sudo /usr/local/mariadb/columnstore/bin/postConfigure

# Setup storage
# Local storage atau shared storage

-- Gunakan ColumnStore engine
CREATE TABLE sales_data (
    id BIGINT,
    sale_date DATE,
    product_id INT,
    customer_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(12,2),
    region VARCHAR(50),
    sales_rep VARCHAR(100)
) ENGINE=ColumnStore;

-- ColumnStore tables tidak mendukung:
-- - Primary keys
-- - Auto-increment
-- - Foreign keys
-- - Unique indexes

-- Partitioned ColumnStore table
CREATE TABLE large_sales (
    sale_date DATE,
    product_id INT,
    amount DECIMAL(12,2)
) ENGINE=ColumnStore
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

# Prepare data file (CSV)
cat > sales_data.csv << EOF
2024-01-01,101,1,1001,5,99.99,499.95,North,John
2024-01-01,102,1,1002,3,149.99,449.97,South,Jane
EOF

# Import menggunakan cpimport
/usr/local/mariadb/columnstore/bin/cpimport \
    -s ',' \
    -e 1 \
    mydb \
    sales_data \
    sales_data.csv

-- Load dari InnoDB table
INSERT INTO sales_analytics_columnstore
SELECT * FROM sales_transactions_innodb
WHERE sale_date >= '2024-01-01';

# Split file dan load parallel
split -l 1000000 large_file.csv part_

for file in part_*; do
    /usr/local/mariadb/columnstore/bin/cpimport mydb sales_data "$file" &
done
wait

-- ColumnStore optimal untuk aggregations
SELECT 
    region,
    YEAR(sale_date) as year,
    SUM(total_amount) as total_sales,
    AVG(quantity) as avg_quantity,
    COUNT(*) as transaction_count
FROM sales_data
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region, YEAR(sale_date);

-- WHERE clause optimization
SELECT 
    product_id,
    SUM(total_amount) as revenue
FROM sales_data
WHERE region = 'North'
  AND sale_date >= '2024-01-01'
GROUP BY product_id
HAVING revenue > 10000;

-- Join antara ColumnStore dan InnoDB
SELECT 
    cs.product_id,
    p.name,
    SUM(cs.total_amount) as revenue
FROM sales_data cs
INNER JOIN products p ON cs.product_id = p.id
WHERE cs.sale_date >= '2024-01-01'
GROUP BY cs.product_id, p.name;

-- Check table size
SELECT 
    TABLE_NAME,
    ENGINE,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) as data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) as index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

# Check ColumnStore storage usage
df -h /usr/local/mariadb/columnstore/

# Monitor disk space
/usr/local/mariadb/columnstore/bin/mcsadmin getSystemDiskUsage

-- Update statistics
ANALYZE TABLE sales_data;

-- ColumnStore mengupdate statistics automatic

# Backup dengan mysqldump
mysqldump --single-transaction mydb sales_data > backup.sql

-- Restore
mysql mydb < backup.sql

-- Atau gunakan ColumnStore backup tools