0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@soyaoki

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

0
Posted at

はじめに

#1でA2D2データセットをGlue ETLでS3に格納し、Athenaでクエリを動かすところまで完了しました。
今回の#2では、RedshiftにDWH層を構築し、走行データのSQL分析を深掘りしていきます。

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

AthenaとRedshiftの使い分け

Athena Redshift
用途 EDA・アドホッククエリ 繰り返し分析・ダッシュボード向け
課金 スキャンデータ量従量 クラスター起動時間
向いてるケース 一回限りの探索 同じクエリを繰り返す

今回はAthenaでデータを確認してRedshiftで分析基盤を整備する、という役割分担にしています。

ハマりポイント

dc2.largeが ap-northeast-1 で使用不可

東京リージョンで使えるのはra3系のみでした。最小構成はra3.xlplusのsingle-nodeです（約$1.08/時）。

aws redshift describe-orderable-cluster-options \
  --region ap-northeast-1 \
  --query 'OrderableClusterOptions[].NodeType' \
  --output text
# → ra3.16xlarge ra3.4xlarge ra3.large ra3.xlplus

手順

Step 1：Redshiftクラスター起動

aws redshift create-cluster \
  --cluster-identifier adas-portfolio \
  --node-type ra3.xlplus \
  --cluster-type single-node \
  --master-username admin \
  --master-user-password <YOUR_PASSWORD> \
  --db-name adasdb \
  --region ap-northeast-1

起動確認：

aws redshift describe-clusters \
  --cluster-identifier adas-portfolio \
  --region ap-northeast-1 \
  --query 'Clusters[0].ClusterStatus' \
  --output text

Step 2：パブリックアクセス有効化 + セキュリティグループ設定

aws redshift modify-cluster \
  --cluster-identifier adas-portfolio \
  --publicly-accessible \
  --region ap-northeast-1

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アドレスが変わると接続できなくなります。接続できない場合はcurl -s https://checkip.amazonaws.comでIPを確認して再設定してください。

Step 3：IAMロール作成

cat > /tmp/redshift-trust.json << 'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{"Effect": "Allow", "Principal": {"Service": "redshift.amazonaws.com"}, "Action": "sts:AssumeRole"}]
}
EOF

aws iam create-role \
  --role-name RedshiftS3Role \
  --assume-role-policy-document file:///tmp/redshift-trust.json

aws iam attach-role-policy \
  --role-name RedshiftS3Role \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess

aws redshift modify-cluster-iam-roles \
  --cluster-identifier adas-portfolio \
  --add-iam-roles arn:aws:iam::<ACCOUNT_ID>:role/RedshiftS3Role \
  --region ap-northeast-1

Step 4：ローカルから接続

まずpsqlをインストール。

sudo apt install postgresql-client -y

以下で接続します、設定したパスワード入力が必要です。

psql -h adas-portfolio.<ENDPOINT_ID>.ap-northeast-1.redshift.amazonaws.com \
  -U admin \
  -d adasdb \
  -p 5439

Step 5：テーブル作成

ADDSのスキーマを参考に4テーブルを作成します。

CREATE SCHEMA IF NOT EXISTS a2d2;

CREATE TABLE IF NOT EXISTS a2d2.vehicle (
    vehicleid   VARCHAR(255) NOT NULL ENCODE lzo,
    description VARCHAR(255) ENCODE lzo,
    PRIMARY KEY (vehicleid)
) DISTSTYLE ALL;

INSERT INTO a2d2.vehicle VALUES ('a2d2', 'Audi A2D2 dataset vehicle');

CREATE TABLE IF NOT EXISTS a2d2.sensor (
    sensorid    VARCHAR(255) NOT NULL ENCODE lzo,
    description VARCHAR(255) ENCODE lzo,
    PRIMARY KEY (sensorid)
) DISTSTYLE ALL;

INSERT INTO a2d2.sensor VALUES ('camera/front_center', 'Front center camera');
INSERT INTO a2d2.sensor VALUES ('lidar/front_center',  'Front center LiDAR');

CREATE TABLE IF NOT EXISTS a2d2.drive_data (
    vehicle_id VARCHAR(255) NOT NULL ENCODE Text255,
    scene_id   VARCHAR(255) NOT NULL ENCODE Text255,
    sensor_id  VARCHAR(255) NOT NULL ENCODE Text255,
    data_ts    BIGINT       NOT NULL SORTKEY,
    s3_bucket  VARCHAR(255) NOT NULL ENCODE lzo,
    s3_key     VARCHAR(255) NOT NULL ENCODE lzo,
    PRIMARY KEY (vehicle_id, scene_id, sensor_id, data_ts),
    FOREIGN KEY (vehicle_id) REFERENCES a2d2.vehicle(vehicleid),
    FOREIGN KEY (sensor_id)  REFERENCES a2d2.sensor(sensorid)
) DISTSTYLE AUTO;

CREATE TABLE IF NOT EXISTS a2d2.bus_data (
    vehicle_id                      VARCHAR(255) NOT NULL ENCODE Text255,
    scene_id                        VARCHAR(255) NOT NULL ENCODE Text255,
    data_ts                         BIGINT       NOT NULL SORTKEY,
    acceleration_x                  FLOAT4,
    acceleration_y                  FLOAT4,
    acceleration_z                  FLOAT4,
    accelerator_pedal               FLOAT4,
    accelerator_pedal_gradient_sign SMALLINT,
    angular_velocity_omega_x        FLOAT4,
    angular_velocity_omega_y        FLOAT4,
    angular_velocity_omega_z        FLOAT4,
    brake_pressure                  FLOAT4,
    distance_pulse_front_left       FLOAT4,
    distance_pulse_front_right      FLOAT4,
    distance_pulse_rear_left        FLOAT4,
    distance_pulse_rear_right       FLOAT4,
    latitude_degree                 FLOAT4,
    latitude_direction              SMALLINT,
    longitude_degree                FLOAT4,
    longitude_direction             SMALLINT,
    pitch_angle                     FLOAT4,
    roll_angle                      FLOAT4,
    steering_angle_calculated       FLOAT4,
    steering_angle_calculated_sign  SMALLINT,
    vehicle_speed                   FLOAT4,
    PRIMARY KEY (vehicle_id, scene_id, data_ts),
    FOREIGN KEY (vehicle_id) REFERENCES a2d2.vehicle(vehicleid)
) DISTSTYLE AUTO;

Step 6：S3からCOPY

COPY a2d2.bus_data
FROM 's3://your-adas-portfolio/processed/bus_data/'
IAM_ROLE 'arn:aws:iam::<ACCOUNT_ID>:role/RedshiftS3Role'
FORMAT AS PARQUET;
-- → 45,984 record(s) loaded successfully.

COPY a2d2.drive_data
FROM 's3://your-adas-portfolio/processed/drive_data/'
IAM_ROLE 'arn:aws:iam::<ACCOUNT_ID>:role/RedshiftS3Role'
FORMAT AS PARQUET;
-- → 54,902 record(s) loaded successfully.

Step 7：SQL分析と考察

① 速度帯別ステアリング特性

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;
speed_range cnt avg_steering_deg p95_steering_deg
低速（~20km/h） 29,076 44.1度 248.7度
中速（20~60km/h） 16,908 17.5度 95.7度

低速時は平均44度・P95で248度と大きなステアリング操作が多く、市街地の交差点・駐車操作等が多いことがわかります。中速時は直線的な走行が多く平均17度に収まっています。

② 急ブレーキシーン分析

まずbrake_pressureの分布を確認します：

SELECT
    MIN(brake_pressure) AS min_bp,
    MAX(brake_pressure) AS max_bp,
    AVG(brake_pressure) AS avg_bp,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY brake_pressure) AS p95_bp
FROM a2d2.bus_data
WHERE vehicle_speed > 20;

結果：min=-1.2, max=26.1, avg=0.23, P95=0.3

走行中のmax brake_pressureはわずか26.1でP95は0.3です。A2D2はAudiのテストドライバーによる走行データであるため、急ブレーキがほぼ発生しません。一般公道データとは異なり、閾値をbrake_pressure > 10程度まで下げることで有意なシーンを抽出できます。

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 10;

最大ブレーキは23.8km/h走行時のbrake_pressure=26.1で、場所は48.1472, 11.5678（ミュンヘン市街地）に集中していました。

③ 急操舵検出（LAG関数）

WITH diff AS (
    SELECT
        data_ts,
        vehicle_speed,
        steering_angle_calculated,
        LAG(steering_angle_calculated) OVER (ORDER BY data_ts) AS prev_steering,
        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 10;

結果：最大steering_delta=7.35度（15.2km/h時）

急操舵も低速時に集中しており、速度帯別ステアリング特性の結果と一致します。テストドライバーデータではmax7.35度と非常に安定しており、異常検知モデルの正常データ（ベースライン）として活用できます。

注意点

Redshiftクラスターは分析が終わったら必ず停止させましょう。

一時停止（課金停止）のコマンド

aws redshift pause-cluster \
  --cluster-identifier adas-portfolio \
  --region ap-northeast-1

完全削除するコマンド

aws redshift delete-cluster \
  --cluster-identifier adas-portfolio \
  --skip-final-cluster-snapshot \
  --region ap-northeast-1

今後も使う予定があればpause、もう不要ならdeleteです。

#2 まとめ

項目 結果
Redshift構成 ra3.xlplus single-node
bus_data 45,984レコード
drive_data 54,902レコード
速度帯別ステアリング 低速44度 / 中速17度
急ブレーキ最大BP 26.1（走行中）
急操舵最大delta 7.35度

次の#3ではRedashをDockerで起動してRedshiftに接続し、ダッシュボードを構築します。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?