自動運転研究用データセットをAWSで分析する #3

はじめに

#2でRedshiftにDWH層を構築してSQL分析クエリを書きました。
今回の#3では、RedashをDockerで起動してRedshiftに接続し、走行データ分析ダッシュボードを構築します。

アーキテクチャ（#3追加分）

ハマりポイント

workerがREDASH_COOKIE_SECRET未設定で起動失敗

docker-compose.ymlのworkerコンテナにREDASH_COOKIE_SECRETを入れ忘れると、workerが起動失敗してクエリが実行できません。server・scheduler・worker全コンテナに設定が必要です。

手順

Step 1：docker-compose.yml作成

mkdir -p ~/redash && cd ~/redash

cat > docker-compose.yml << 'EOF'
version: "3"
services:
  server:
    image: redash/redash:10.1.0.b50633
    depends_on:
      - postgres
      - redis
    ports:
      - "5000:5000"
    environment:
      REDASH_DATABASE_URL: "postgresql://postgres:password@postgres/postgres"
      REDASH_REDIS_URL: "redis://redis:6379/0"
      REDASH_SECRET_KEY: "adas-portfolio-secret"
      REDASH_COOKIE_SECRET: "adas-portfolio-cookie"
    command: server

  scheduler:
    image: redash/redash:10.1.0.b50633
    depends_on:
      - postgres
      - redis
    environment:
      REDASH_DATABASE_URL: "postgresql://postgres:password@postgres/postgres"
      REDASH_REDIS_URL: "redis://redis:6379/0"
      REDASH_SECRET_KEY: "adas-portfolio-secret"
      REDASH_COOKIE_SECRET: "adas-portfolio-cookie"
    command: scheduler

  worker:
    image: redash/redash:10.1.0.b50633
    depends_on:
      - postgres
      - redis
    environment:
      REDASH_DATABASE_URL: "postgresql://postgres:password@postgres/postgres"
      REDASH_REDIS_URL: "redis://redis:6379/0"
      REDASH_SECRET_KEY: "adas-portfolio-secret"
      REDASH_COOKIE_SECRET: "adas-portfolio-cookie"
    command: worker

  postgres:
    image: postgres:14
    environment:
      POSTGRES_PASSWORD: password

  redis:
    image: redis:7
EOF

Step 2：初期化・起動

docker compose run --rm server create_db
docker compose up -d
docker ps

http://localhost:5000 にアクセスして管理者アカウントを作成。

Step 3：RedshiftのセキュリティグループにIPを追加

MY_IP=$(curl -s https://checkip.amazonaws.com)

aws ec2 authorize-security-group-ingress \
  --group-id <sg-id> \
  --protocol tcp \
  --port 5439 \
  --cidr ${MY_IP}/32 \
  --region ap-northeast-1

⚠️ IPアドレスが変わると接続できなくなります。接続できない場合は再度IPを確認して更新してください。

Step 4：データソース登録

Redash UIからデータソースを登録します。

まずAPIキーを取得：Redash → 右上アイコン → Profile → API Key

curl -X POST http://localhost:5000/api/data_sources \
  -H "Content-Type: application/json" \
  -H "Authorization: Key <YOUR_API_KEY>" \
  -d '{
    "name": "a2d2-redshift",
    "type": "redshift",
    "options": {
      "host": "<REDSHIFT_ENDPOINT>",
      "port": 5439,
      "user": "admin",
      "password": "<YOUR_PASSWORD>",
      "dbname": "adasdb",
      "sslmode": "require"
    }
  }'

Step 5：クエリ作成

Redash → Queries → New Query → データソースa2d2-redshiftを選択

クエリ1：速度帯別ステアリング特性

SELECT
    CASE
        WHEN vehicle_speed < 20  THEN '低速（~20km/h）'
        WHEN vehicle_speed < 60  THEN '中速（20~60km/h）'
        ELSE '高速（60km/h~）'
    END AS speed_range,
    COUNT(*) AS cnt,
    AVG(ABS(steering_angle_calculated)) AS avg_steering_deg,
    PERCENTILE_CONT(0.95)
        WITHIN GROUP (ORDER BY ABS(steering_angle_calculated)) AS p95_steering_deg
FROM a2d2.bus_data
WHERE vehicle_speed IS NOT NULL
  AND steering_angle_calculated IS NOT NULL
GROUP BY 1
ORDER BY cnt DESC;

クエリ2：急ブレーキシーン

SELECT
    data_ts, vehicle_speed, brake_pressure,
    latitude_degree, longitude_degree
FROM a2d2.bus_data
WHERE brake_pressure > 10
  AND vehicle_speed > 20
ORDER BY brake_pressure DESC
LIMIT 50;

クエリ3：急操舵検出

WITH diff AS (
    SELECT
        data_ts, vehicle_speed, steering_angle_calculated,
        ABS(steering_angle_calculated - LAG(steering_angle_calculated)
            OVER (ORDER BY data_ts)) AS steering_delta
    FROM a2d2.bus_data
)
SELECT * FROM diff
WHERE steering_delta > 3
ORDER BY steering_delta DESC
LIMIT 50;

各クエリを保存後、Publishボタンを押す（Publishしないとダッシュボードに追加できない）。

Step 6：ダッシュボード作成

1. Dashboards → New Dashboard → 名前：「A2D2 走行データ分析」
2. Edit → Add Widget → 各クエリとビジュアライゼーションを選択して追加
3. 速度帯別ステアリングはBar Chartで可視化（X: speed_range, Y: avg_steering_deg）
4. Publish

完成ダッシュボード

#3 まとめ

項目	内容
Redashバージョン	10.1.0.b50633
構成	Docker Compose（server / worker / scheduler / postgres / redis）
登録クエリ	3本（速度帯別ステアリング・急ブレーキ・急操舵）
ダッシュボード	A2D2 走行データ分析

次の#4ではCDKでインフラをコード化し、GitHub ActionsでCI/CDを構築します。