# Advanced Setup (Individual Containers)

This setup method gives you complete control over each service configuration. Choose this approach when you need custom networking, specific versions, or advanced configuration options.

## Why Choose Individual Containers?

**✅ Advantages:**

* **Complete Control**: Configure each service exactly as needed
* **Custom Networking**: Set up advanced network topologies
* **Resource Management**: Allocate specific CPU/memory to each service
* **Version Control**: Use different versions of each component
* **Production-Like**: More similar to production deployment patterns

**❌ Considerations:**

* More complex setup and management
* Manual network configuration required
* Requires deeper understanding of each component
* More time-consuming for initial setup

## Step 1: Create Docker Network

Create a custom network for service communication:

```bash
# Create dedicated network for DataSuite services
docker network create datasuite-network

# Verify network creation
docker network ls | grep datasuite
```

**Expected Output:**

```bash
NETWORK ID     NAME                DRIVER    SCOPE
a1b2c3d4e5f6   datasuite-network   bridge    local
```

## Step 2: Set Up MySQL Database

Install and configure MySQL with AdventureWorks data:

```bash
# Run MySQL with persistent storage and custom configuration
docker run --name mysql \
  --network datasuite-network \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=adventureworks \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  -v $(pwd)/mysql-config:/etc/mysql/conf.d \
  --restart unless-stopped \
  -d mysql:8.0
```

**Advanced MySQL Configuration** (optional): Create `mysql-config/custom.cnf`:

```ini
[mysqld]
# Performance tuning
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
max_connections = 200

# Binary logging for replication
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
```

**Load Sample Data:**

```bash
# Wait for MySQL to be ready
until docker exec mysql mysqladmin ping -h localhost -u root -ppassword --silent; do
  echo "Waiting for MySQL to be ready..."
  sleep 2
done

# Load Adventure Works data
docker exec -i mysql mysql -uroot -ppassword adventureworks < docs/data/AdventureWorks2019.sql

# Verify data loading
docker exec mysql mysql -uroot -ppassword adventureworks -e "SELECT COUNT(*) FROM Sales.SalesOrderHeader;"
```

## Step 3: Install ClickHouse Data Warehouse

Set up ClickHouse with custom configuration:

```bash
# Run ClickHouse with authentication and custom settings
docker run --name clickhouse \
  --network datasuite-network \
  -p 8123:8123 \
  -p 9000:9000 \
  -e CLICKHOUSE_USER=admin \
  -e CLICKHOUSE_PASSWORD=clickhouse123 \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  -v clickhouse-data:/var/lib/clickhouse \
  -v $(pwd)/clickhouse-config:/etc/clickhouse-server/config.d \
  --restart unless-stopped \
  -d clickhouse/clickhouse-server:latest
```

**Advanced ClickHouse Configuration** (optional): Create `clickhouse-config/custom.xml`:

```xml
<yandex>
    <!-- Memory settings -->
    <max_memory_usage>8000000000</max_memory_usage>
    <max_bytes_before_external_group_by>4000000000</max_bytes_before_external_group_by>
    
    <!-- Network settings -->
    <max_connections>1000</max_connections>
    <keep_alive_timeout>10</keep_alive_timeout>
    
    <!-- Logging -->
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>3</count>
    </logger>
</yandex>
```

**Initialize ClickHouse Databases:**

```bash
# Wait for ClickHouse to be ready
until curl -s -u admin:clickhouse123 http://localhost:8123/ping; do
  echo "Waiting for ClickHouse to be ready..."
  sleep 2
done

# Create databases
curl -u admin:clickhouse123 http://localhost:8123/ -d "CREATE DATABASE IF NOT EXISTS bronze_layer"
curl -u admin:clickhouse123 http://localhost:8123/ -d "CREATE DATABASE IF NOT EXISTS gold_layer"

# Create bronze layer table for sales orders
curl -u admin:clickhouse123 http://localhost:8123/ -d "
CREATE TABLE IF NOT EXISTS bronze_layer.sales_orders (
    salesorderid Int32,
    customerid Int32,
    territoryid Nullable(Int32),
    orderdate DateTime,
    duedate DateTime,
    shipdate Nullable(DateTime),
    status Int8,
    subtotal Decimal64(4),
    taxamt Decimal64(4),
    freight Decimal64(4),
    totaldue Decimal64(4),
    modifieddate DateTime,
    _ingested_at DateTime DEFAULT now()
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(orderdate)
ORDER BY (orderdate, salesorderid)
SETTINGS index_granularity = 8192
"
```

## Step 4: Install LogStash with Custom Pipeline

Set up LogStash with MySQL connector and custom configuration:

```bash
# Create directories for LogStash configuration
mkdir -p logstash-drivers logstash-config logstash-patterns

# Download MySQL JDBC driver
docker run --rm -v $(pwd)/logstash-drivers:/drivers alpine:latest \
  sh -c "apk add --no-cache wget && \
         wget -O /drivers/mysql-connector-java.jar \
         https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.9-rc/mysql-connector-java-8.0.9-rc.jar"

# Download ClickHouse JDBC driver (optional for alternative output)
docker run --rm -v $(pwd)/logstash-drivers:/drivers alpine:latest \
  sh -c "apk add --no-cache wget && \
         wget -O /drivers/clickhouse-jdbc.jar \
         https://repo1.maven.org/maven2/ru/yandex/clickhouse/clickhouse-jdbc/0.3.2/clickhouse-jdbc-0.3.2.jar"
```

**Create Advanced LogStash Configuration:** Create `logstash-config/pipelines.yml`:

```yaml
- pipeline.id: sales-orders
  path.config: "/usr/share/logstash/pipeline/sales-orders.conf"
  pipeline.workers: 2
  
- pipeline.id: customers  
  path.config: "/usr/share/logstash/pipeline/customers.conf"
  pipeline.workers: 1
  
- pipeline.id: products
  path.config: "/usr/share/logstash/pipeline/products.conf" 
  pipeline.workers: 1
```

**Create Pipeline-Specific Configurations:** Create `logstash-config/sales-orders.conf`:

```ruby
input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/drivers/mysql-connector-java.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://mysql:3306/adventureworks?useSSL=false"
    jdbc_user => "root"
    jdbc_password => "password"
    statement_filepath => "/usr/share/logstash/sql-queries/sales_orders.sql"
    use_column_value => true
    tracking_column => "ModifiedDate"
    tracking_column_type => "timestamp"
    last_run_metadata_path => "/usr/share/logstash/.logstash_jdbc_last_run_sales"
    clean_run => false
    schedule => "*/30 * * * * *"
    type => "sales_order"
  }
}

filter {
  if [type] == "sales_order" {
    # Convert timestamps to ISO format
    date {
      match => [ "orderdate", "yyyy-MM-dd HH:mm:ss" ]
      target => "orderdate_iso"
    }
    
    # Add metadata
    mutate {
      add_field => { "[@metadata][table]" => "sales_orders" }
      add_field => { "[@metadata][database]" => "bronze_layer" }
      remove_field => [ "@version", "@timestamp", "type" ]
    }
    
    # Data quality checks
    if [salesorderid] {
      mutate { add_tag => [ "valid_record" ] }
    } else {
      mutate { add_tag => [ "invalid_record" ] }
    }
  }
}

output {
  if "valid_record" in [tags] {
    http {
      url => "http://clickhouse:8123/"
      http_method => "post"
      format => "message"
      message => "INSERT INTO %{[@metadata][database]}.%{[@metadata][table]} FORMAT JSONEachRow %{message}"
      headers => {
        "Authorization" => "Basic YWRtaW46Y2xpY2tob3VzZTEyMw=="
        "Content-Type" => "application/json"
      }
      # Retry configuration
      retry_failed => true
      retries => 3
      retry_delay => 5
    }
  }
  
  if "invalid_record" in [tags] {
    file {
      path => "/usr/share/logstash/logs/invalid_sales_orders.log"
      codec => json_lines
    }
  }
  
  # Optional: Send to multiple outputs
  # elasticsearch {
  #   hosts => ["elasticsearch:9200"]
  #   index => "sales-orders-%{+YYYY.MM.dd}"
  # }
}
```

**Create SQL Query Files:** Create `sql-queries/sales_orders.sql`:

```sql
SELECT 
    salesorderid,
    customerid,
    territoryid,
    orderdate,
    duedate,
    shipdate,
    status,
    subtotal,
    taxamt,
    freight,
    totaldue,
    modifieddate
FROM Sales.SalesOrderHeader 
WHERE ModifiedDate > :sql_last_value 
ORDER BY ModifiedDate ASC
LIMIT 1000
```

**Run LogStash Container:**

```bash
# Run LogStash with multiple pipeline support
docker run --name logstash \
  --network datasuite-network \
  -p 5044:5044 \
  -p 9600:9600 \
  -v $(pwd)/logstash-config:/usr/share/logstash/pipeline \
  -v $(pwd)/logstash-drivers:/usr/share/logstash/drivers \
  -v $(pwd)/sql-queries:/usr/share/logstash/sql-queries \
  -v logstash-data:/usr/share/logstash/data \
  -e "LS_JAVA_OPTS=-Xmx2g -Xms2g" \
  --restart unless-stopped \
  -d docker.elastic.co/logstash/logstash:8.11.0
```

## Step 5: Install Apache Airflow (Optional)

Set up Airflow for workflow orchestration:

```bash
# Create Airflow directories
mkdir -p airflow-dags airflow-logs airflow-plugins

# Initialize Airflow database and create admin user
docker run --rm \
  -v $(pwd)/airflow-dags:/opt/airflow/dags \
  -v $(pwd)/airflow-logs:/opt/airflow/logs \
  -e AIRFLOW__CORE__EXECUTOR=LocalExecutor \
  -e AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=sqlite:////opt/airflow/airflow.db \
  apache/airflow:2.7.0 \
  bash -c "airflow db init && airflow users create --username admin --password admin --firstname Admin --lastname User --role Admin --email admin@example.com"

# Run Airflow webserver
docker run --name airflow-webserver \
  --network datasuite-network \
  -p 8080:8080 \
  -v $(pwd)/airflow-dags:/opt/airflow/dags \
  -v $(pwd)/airflow-logs:/opt/airflow/logs \
  -v $(pwd)/airflow-plugins:/opt/airflow/plugins \
  -e AIRFLOW__CORE__EXECUTOR=LocalExecutor \
  -e AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=sqlite:////opt/airflow/airflow.db \
  --restart unless-stopped \
  -d apache/airflow:2.7.0 \
  webserver

# Run Airflow scheduler (in separate container)
docker run --name airflow-scheduler \
  --network datasuite-network \
  -v $(pwd)/airflow-dags:/opt/airflow/dags \
  -v $(pwd)/airflow-logs:/opt/airflow/logs \
  -v $(pwd)/airflow-plugins:/opt/airflow/plugins \
  -e AIRFLOW__CORE__EXECUTOR=LocalExecutor \
  -e AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=sqlite:////opt/airflow/airflow.db \
  --restart unless-stopped \
  -d apache/airflow:2.7.0 \
  scheduler
```

## Step 6: Configure Service Health Monitoring

Create monitoring scripts for service health:

**Create `scripts/health-check.sh`:**

```bash
#!/bin/bash

echo "=== DataSuite ETL Health Check ==="

# Check MySQL
echo -n "MySQL: "
if docker exec mysql mysqladmin ping -h localhost -u root -ppassword --silent 2>/dev/null; then
    echo "✅ Running"
else
    echo "❌ Down"
fi

# Check ClickHouse
echo -n "ClickHouse: "
if curl -s -u admin:clickhouse123 http://localhost:8123/ping | grep -q "Ok"; then
    echo "✅ Running"
else
    echo "❌ Down"
fi

# Check LogStash
echo -n "LogStash: "
if curl -s http://localhost:9600/_node/stats >/dev/null 2>&1; then
    echo "✅ Running"
else
    echo "❌ Down"
fi

# Check Airflow (if running)
if docker ps | grep -q airflow-webserver; then
    echo -n "Airflow: "
    if curl -s http://localhost:8080/health >/dev/null 2>&1; then
        echo "✅ Running"
    else
        echo "❌ Down"
    fi
fi

echo "=== Network Connectivity ==="
docker exec mysql nc -zv clickhouse 8123 2>/dev/null && echo "MySQL → ClickHouse: ✅" || echo "MySQL → ClickHouse: ❌"
docker exec logstash nc -zv mysql 3306 2>/dev/null && echo "LogStash → MySQL: ✅" || echo "LogStash → MySQL: ❌"
docker exec logstash nc -zv clickhouse 8123 2>/dev/null && echo "LogStash → ClickHouse: ✅" || echo "LogStash → ClickHouse: ❌"
```

```bash
# Make script executable and run it
chmod +x scripts/health-check.sh
./scripts/health-check.sh
```

## Step 7: Advanced Network Configuration

### Custom Bridge Network

```bash
# Create network with custom CIDR
docker network create datasuite-network \
  --driver bridge \
  --subnet=172.20.0.0/16 \
  --ip-range=172.20.240.0/20

# Assign static IPs to services
docker run --name mysql \
  --network datasuite-network \
  --ip 172.20.240.10 \
  # ... other options
```

### Service Discovery Configuration

Create `/etc/hosts` entries for easier access:

```bash
# Add to /etc/hosts (requires sudo)
echo "127.0.0.1 mysql.datasuite.local" | sudo tee -a /etc/hosts
echo "127.0.0.1 clickhouse.datasuite.local" | sudo tee -a /etc/hosts
echo "127.0.0.1 logstash.datasuite.local" | sudo tee -a /etc/hosts
```

## Management Scripts

**Create `scripts/manage-services.sh`:**

```bash
#!/bin/bash

case "$1" in
  start)
    echo "Starting DataSuite ETL services..."
    docker start mysql clickhouse logstash
    if docker ps -a | grep -q airflow; then
      docker start airflow-webserver airflow-scheduler
    fi
    ;;
  stop)
    echo "Stopping DataSuite ETL services..."
    docker stop mysql clickhouse logstash
    if docker ps | grep -q airflow; then
      docker stop airflow-webserver airflow-scheduler
    fi
    ;;
  restart)
    echo "Restarting DataSuite ETL services..."
    $0 stop
    sleep 5
    $0 start
    ;;
  status)
    ./scripts/health-check.sh
    ;;
  logs)
    docker logs -f ${2:-logstash}
    ;;
  *)
    echo "Usage: $0 {start|stop|restart|status|logs [service]}"
    exit 1
    ;;
esac
```

```bash
chmod +x scripts/manage-services.sh
```

## Resource Management

### CPU and Memory Limits

```bash
# Run containers with resource limits
docker run --name mysql \
  --cpus="2.0" \
  --memory="4g" \
  --memory-swap="4g" \
  # ... other options

docker run --name clickhouse \
  --cpus="4.0" \
  --memory="8g" \
  --memory-swap="8g" \
  # ... other options
```

### Storage Optimization

```bash
# Create volumes with specific drivers
docker volume create mysql-data --driver local \
  --opt type=ext4 \
  --opt device=/dev/sdb1

docker volume create clickhouse-data --driver local \
  --opt type=ext4 \
  --opt device=/dev/sdb2
```

## Backup and Recovery

**Create `scripts/backup.sh`:**

```bash
#!/bin/bash

BACKUP_DIR="./backups/$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"

# Backup MySQL
echo "Backing up MySQL..."
docker exec mysql mysqldump -uroot -ppassword --all-databases > "$BACKUP_DIR/mysql_backup.sql"

# Backup ClickHouse
echo "Backing up ClickHouse..."
curl -u admin:clickhouse123 "http://localhost:8123/?query=SELECT * FROM bronze_layer.sales_orders FORMAT Native" > "$BACKUP_DIR/clickhouse_bronze.native"

echo "Backup completed in $BACKUP_DIR"
```

## Next Steps

Your individual containers setup provides maximum flexibility. Proceed to:

1. [**Service Verification**](/data-platform/index/setup/verification.md) - Confirm everything works correctly
2. [**LogStash Configuration**](/data-platform/index/development/logstash-configuration.md) - Customize your data pipelines
3. [**DBT Getting Started**](/data-platform/index/development/dbt-getting-started.md) - Build dimensional models

## Benefits of Individual Container Setup

* **🎛️ Fine-Grained Control**: Configure each service precisely for your needs
* **🏗️ Production Similarity**: More closely matches production deployment patterns
* **📈 Scalability**: Easy to scale individual services based on load
* **🔧 Customization**: Advanced configuration options for performance tuning
* **🚀 Deployment Flexibility**: Can be adapted for Kubernetes or other orchestrators

This setup method provides the foundation for understanding how to deploy and manage DataSuite ETL in production environments.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datasuite.vn/data-platform/index/setup/individual-containers.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.