BigQuery / 信号データ仕様
本製品が読む唯一の本番 BigQuery (sic-poc1-verify) の構造、XHRO デバイスの 4 階層被験者 ID、信号の物理特性、xhro.eeg の partition filter 罠、既存ビューの再利用方針を定義します。
接続先
本リポジトリは sic-poc1-verify プロジェクト (US) を唯一の本番接続先として扱います。詳細な鍵配置とローカルセットアップは getting-started を参照してください。
- Cloud
- Google Cloud Platform
- プロジェクト ID
sic-poc1-verify(XHRO PoC1 検証用プロジェクト)- Location
US(データセットも全て US)- サービスアカウント
suntory-analytics-bq@sic-poc1-verify.iam.gserviceaccount.com(JSON 鍵で配布)- ロール
- BigQuery Data Viewer + Job User
- 鍵の保管
- 本プロジェクトでは
.keys/suntory-analytics-bq.jsonに 600 権限で保管 (旧suntory-nedoリポは外部パス参照、v2 ではリポ配下に閉じ込めた)
データセット一覧 (実測)
bq ls sic-poc1-verify: の結果。Studio 設計に関係する 14 個のデータセット。allowlist 既定は xhro_01.* / xhro_02.* / xhro_03.* / xhro_04.* / xhro_view.* / xhro_04_modeling_v2.* です。
| データセット | 役割 | Studio から見た位置付け |
|---|---|---|
| xhro | 全期間・全 expt の生入庫 (60 億行+) | 読まない (partition filter 罠) |
| xhro_01〜xhro_04 | 実験単位 (XHRO-01 〜 XHRO-04) に切り出したコピー。ms スケール range partition + (uid, timestamp) クラスタ | Studio はこれを正とする。Quick/Advanced とも xhro_NN を読む |
| xhro_04_modeling, xhro_04_modeling_v2 | XHRO-04 派生 (PPG 前処理、HRV-睡眠タグ、HRV-LF/HF など) | AI Copilot のプリセット派生のお手本。読み専。書き戻し禁止 |
| xhro_backup | 過去日付ごとの人手退避 (20231018_*, xhro_01_* 等) | tracking 対象外。Studio 既定では非表示 (blocklist) |
| xhro_view | 運用モニタ用ビュー (missing_interval, sensor_status, wearing_summary, activity_logs) | availability/wear 判定に流用 (内側の時間窓だけ差し替えて使う) |
| xhro_hito | 試験運用テーブル (activity_logs 原本 + info) | xhro_view.activity_logs の裏側。直接は読まない |
| tmp | 開発者の作業領域 | 触らない (blocklist) |
| dataflow_dev | Dataflow パイプライン検証用 (acc, vital のみ) | 触らない (blocklist) |
| clns (test_nishimura) | 個人 sandbox | 触らない |
| edf | EDF (European Data Format) インポート用 (recordings/signal_data/annotations 等 9 テーブル) | Studio スコープ外 |
| main_poc1 | 1 ユーザー = 1 テーブル + 3 ダウンサンプル view (5ms/50ms/1000ms) の旧 PoC スキーマ | レガシー。新規 Studio では使わない |
| switchbot (hub2, hub2_view) | SwitchBot Hub2 環境センサ | スコープ外 |
| firestore_export | Firestore からの変更ログ | Studio スコープ外 |
| firebase_crashlytics | Crashlytics export (2 テーブル, DAY partition) | スコープ外 |
危険なテーブル: 生 xhro.*
xhro.{eeg,opt,acc,vital,meta} は 生入庫の全期間版 で、Studio からは 触ってはいけません。理由を再現可能な形で残します。
partition filter 罠の正体:
xhro.eeg行数: 6,279,358,750 (約 6.3 G 行)。物理サイズ約 609 GB。requirePartitionFilter=TruerangePartitioning.field=timestamprangePartitioning.range: start=1672531200, end=2208988800, interval=86400 (秒スケール)- 実データの
timestamp: ミリ秒 epoch (例: 1696591935725 ≒ 2023-10-06 14:32 JST) INFORMATION_SCHEMA.PARTITIONS: 全 6.3 G 行がpartition_id = __UNPARTITIONED__
つまり: パーティション設定はあるが境界が秒スケール、実値は ms スケールなので どんな timestamp フィルタを入れてもパーティション削減が効かず、requirePartitionFilter のために空結果が返るかエラーになる。結果として xhro.eeg 等は事実上クエリ不能です。
Studio が読むときは必ず xhro_NN 系を選択します。xhro_NN.eeg は range が ms スケール (start=1672531200000, interval=86400000) で正しく動作し、(uid, timestamp) クラスタも乗っています。
唯一の例外: xhro_view.* のビューが from xhro.meta を含むため、これらは事前定義された 12h / 24h 窓限定で使う前提なら問題ありません。Studio が任意窓で同じ集計をしたい場合は、定義を写して xhro_NN.meta を参照するよう書き換えます。
被験者 ID 体系 (4 階層)
実 BQ では被験者は (expt_id, hid, uid, email) の 4 階層で表されます。各 xhro_NN.xhro_id_map がこの SoT です。
| カラム | 型 | 役割 | 例 |
|---|---|---|---|
expt_id | STRING (REQUIRED) | 実験識別子 | XHRO-01 〜 XHRO-04 |
hid | STRING (REQUIRED) | "人 ID" (同一人物が複数 expt に登場する) | XH002, XH015 |
uid | STRING (REQUIRED) | Firebase Auth UID (BLE 経由のデータ行の uid と一致) | wz1B6e... (28 文字) |
email | STRING | uXXX@xhro.org 形式の運用メール | u004@xhro.org |
remarks | STRING | 備考 | XH004→XH052へ結合 |
注意: 同じ hid が異なる expt_id で別の uid を持つ運用があり、また remarks に「結合」等の手当てが入ります。Studio Step 2 では (expt_id, hid) を一意キーとして扱い、uid は時間窓と一緒に渡します。
Studio / Workspace で使う ID
public_id := stable_hash(uid, tenant_salt)で算出。rawuidは Studio 内 (KMS 暗号化ストア) のみで保持し、Workspace API レスポンスには 絶対に出さない。- Studio UI の表示は
(expt_id, hid)を優先。エンドユーザーが認識しやすい (例: 「XHRO-04 / XH015」)。 - BQ クエリには
uidを渡す (xhro_NN.{eeg,opt,acc,vital,meta}の clustering キー)。 - Bundle manifest の
subject.public_idは上記 hash 値のみ。subject.canonical_keyはHMAC-SHA256(tenant_secret, uid)。
実観測カバレッジ
各実験ごとの行数と観測窓 (2026-05-25 時点)。Studio の Recording Index 構築時の参考値です。
| expt | 行数 (`eeg`) | 被験者数 (id_map) | 観測窓 (`eeg` の min/max, JST) |
|---|---|---|---|
| XHRO-01 | 476.2 M | 13 人 | 2023-10-06 〜 2023-10-09 |
| XHRO-02 | 435.4 M | 同形 | 2023-10-28 周辺 |
| XHRO-03 | 525.0 M | 同形 | — |
| XHRO-04 | 482.1 M | 12 人 (XH002/005/008/015/017/023/028/042/044/057/058/059) | 2023-11-10 03:00 〜 2023-11-13 01:44 |
xhro_NN.{opt,acc,vital,meta} は eeg に対し OPT≒1/5、ACC≒1/10、VITAL/META≒1/250 の比率 (250Hz / 50Hz / 25Hz / 1Hz / 1Hz の標本化と整合)。
信号の物理特性
本製品が扱う信号の一覧。Bundle に含めるかどうかは Studio Step 3 でユーザーが選択します。既定 ON は ECG と ACC のみ。
| 信号 | ソース | 頻度 | 型 | 扱い |
|---|---|---|---|---|
| ECG (主信号) | xhro_NN.eeg の ch1 - ch2 差動 | 250 Hz | FLOAT (μV) | Phase 1 の主信号。ch3 - ch4 は EEG 用なので既定では含めない。 |
| ACC | xhro_NN.acc の x/y/z | 25 Hz | FLOAT (G) | 体動アーチファクト判定と付帯解析に使う。millis は 40ms 刻み。 |
| PPG | xhro_NN.opt の ch1/2/3/4 (IR/R/G/B) | 50 Hz | INTEGER (raw counts) | SpO2 や脈波派生の原信号。millis は 20ms 刻み。 |
| BioZ | xhro_NN.vital の mag/phase | 1 Hz | FLOAT (Ω/rad) | 呼吸などの派生候補。 |
| 温度 | xhro_NN.vital.temp | 1 Hz | FLOAT (℃) | 装着判定の補助。 |
| 電池 | xhro_NN.vital.fg | 1 Hz | INTEGER (%) | charge BOOLEAN と併用。 |
| Lead-off | xhro_NN.vital.loff_ch1..4 | 1 Hz | BOOLEAN | 装着判定 (true=非接触)。Bundle の quality.leadoff_ratio 計算の元。 |
| 派生 SpO2 / 呼吸 / HR | PPG / BioZ / ECG から算出 | 派生 | — | BQ に直接テーブルがある前提にしない。Bundle build または Workspace feature で作る。 |
EEG/ECG の扱い (signal_family)
XHRO デバイスの eeg テーブルは命名上は EEG ですが、4 ch は 用途が分かれています。
ECG 用 (Phase 1 の主対象)
ch1 (2 極左) / ch2 (2 極右) → ch1 - ch2 差動。胸部チェストパッチ由来の心電。XHRO は 2 電極装着で 12 誘導 Holter ではない点に注意。
EEG 用 (Phase 1 では Bundle に含めない)
ch3 (1 極左) / ch4 (1 極右) → ch3 - ch4 差動。Phase 1 では manifest の signal.channels に含めない既定。
Studio 側は 「signal_family = ecg」を明示選択させる UI にし、EEG として誤解釈されるパスを塞ぎます。
ECG 既定パイプライン (Bundle build 時)
Bundle ビルド時に以下を必ず実行し、結果も Bundle に同梱します (Workspace 側で再計算可能)。
- ベースライン除去: high-pass 0.5 Hz (既定
ecg.highpass_hz) - 50/60 Hz notch: 地域別 (
ecg.notch_hz)。manifest にどちらを使ったか記録 - 体動アーチファクト検出: ACC RMS しきい値 (
ecg.invalid_threshold.acc_g) →invalid_intervals[] - R ピーク検出:
pan_tompkins_v1またはhamilton(ecg.r_peak_detector) →r_peaks_ms[] - RR / HRV サマリ: Window 単位の中央値 + 1 分 rolling (
ecg.hrv_window_sec)
preset の選び方
xhro-multiday (既定)
XHRO 連続多日装着用。ecg.preset_default の既定値。
default
汎用設定。短時間 ECG 向け。
holter-overnight (使わない)
12 誘導 Holter 向け。XHRO は 2 電極装着なので合わない。UI でも非推奨表示。
主要テーブル スキーマ (xhro_NN.*)
bq show --schema で実観測した型を正本とします。旧リポの記述と整合しています。
eeg (250 Hz, ECG/EEG 4 ch)
| カラム | 型 | mode | 説明 |
|---|---|---|---|
expt_id | STRING | NULLABLE | Experiment ID (UUID 形式の expt 識別子) |
uid | STRING | REQUIRED | Firebase Auth UID (PII。Studio は内部解決後に直接利用) |
device_id | STRING | REQUIRED | BLE MAC (例: 19:43:a4:79:b8:e9) |
timestamp | INTEGER | REQUIRED | Epoch **ms** (range partition の境界も ms スケール) |
millis | INTEGER | REQUIRED | 同 timestamp 内のサブ ms オフセット (4 ms 刻み) |
ch1 | FLOAT | nullable | 2 極 (左) → ECG 用に ch1 - ch2 |
ch2 | FLOAT | nullable | 2 極 (右) |
ch3 | FLOAT | nullable | 1 極 (左) → EEG 用に ch3 - ch4 |
ch4 | FLOAT | nullable | 1 極 (右) |
実行ヒント: 任意の被験者・時間窓を抜くなら必ず WHERE uid = ? AND timestamp BETWEEN ? AND ?。expt_id は冗長 (id_map と JOIN すれば一意に決まる)。
opt (50 Hz, PPG 4 ch)
eeg と同形で ch1=赤外, ch2=赤, ch3=緑, ch4=青 (光学センサー値, 単位なし, INTEGER)。millis は 20 ms 刻み。
acc (25 Hz, 加速度 3 軸)
x / y / z (FLOAT, G 単位, 左右 / 上下 / 前後)。millis は 40 ms 刻み。
vital (1 Hz, BioZ / 装着判定 / バッテリ / 温度)
| カラム | 型 | 説明 |
|---|---|---|
| expt_id / uid / device_id / timestamp | STRING / STRING / STRING / INTEGER (ms) | 共通キー |
| eeg_valid / opt_valid / bz_valid / acc_valid / fg_valid / temp_valid | BOOLEAN | センサーごとの有効判定 |
| mag | FLOAT | BioZ 値の大きさ (Ω) |
| phase | FLOAT | BioZ 値の位相 (rad) |
| fg | INTEGER | バッテリー残量 (%) |
| charge | BOOLEAN | 充電中フラグ |
| temp | FLOAT | 皮膚温度 (℃) |
| loff_ch1〜loff_ch4 | BOOLEAN | 装着判定 (true=非接触, false=接触) |
meta (1 Hz, 各種メタ)
XHRO デバイス名 / UUID / firmware、クライアント (受信端末) プラットフォーム / モデル / OS / アプリ、measured_at / client_received_at / client_sent_at / server_received_at / created_at (全て epoch ms) を持つ。時刻ラグ分析 (xhro_view.missing_interval[_summary]) はこの meta のみで計算しており、Studio の availability スパークを作る一次入力としても利用可能。
xhro.ecg_annot (拍ラベル正解 / 1 データセット限定)
唯一 xhro 直下にあってもクエリ可能 (DAY partition on timestamp TIMESTAMP)。Phase 1 (ECG-first) の精度評価における唯一のゴールドです。
| カラム | 型 | 説明 |
|---|---|---|
subject_id | STRING (REQUIRED) | 現状観測値: "0" のみ (匿名化された 1 被験者) |
segment_id | STRING (REQUIRED) | セグメント識別子 (クラスタキー) |
timestamp | TIMESTAMP (REQUIRED) | UTC タイムスタンプ |
ecg_xhro | FLOAT | XHRO で測った ECG 値 |
ecg_vital | FLOAT | リファレンス機器の ECG 値 |
annotator_1 / _2 / _3 | INTEGER | 3 人のアノテータが付けた拍ラベル |
created_at | TIMESTAMP (REQUIRED) | 挿入時刻 |
観測: 行数 1,350,000 / 期間 2025-06-01 19:32 〜 2025-06-02 16:32 (UTC) / subject_id は "0" のみ。規模が小さい点に注意 (n=1 被験者・21 時間)。受け入れ基準 (workspace) と Workspace の "Evidence" タブ説明で明示します。
既存ビュー (xhro_view) — 流用してよい資産
Studio 側で再実装せず、内側の時間窓パラメータだけ差し替えて使う想定です。Studio の availability スパークは wearing_summary の式を xhro_NN.meta を読むように書き直したテンプレで作るのが筋です。
| ビュー | 目的 | 内側で参照 | 既定窓 |
|---|---|---|---|
activity_logs | 試験中の自己申告ログ + 食事画像有無 + 血圧 | xhro_hito.activity_logs[_images] / info | A.timestamp >= 2023-11-10T06:00:00+0900 (XHRO-04 起点) |
missing_interval | xhro_name × uid 単位の欠損ギャップ列挙 | xhro.meta | 試験初日以降 (2023-10-06T14:00:00+0900) |
missing_interval_summary | 上記の集計 (期間内合計欠損 sec / カウント) | xhro.meta | 直近 12 時間 |
sensor_status | センサー毎の Error Rate と OK/NG カウント | xhro.vital, xhro.meta | 直近 12 時間 |
wearing_summary | 10 秒 bucket の装着 0〜10 ヒートマップ | xhro.meta (10 sec quantize) | 直近 24 時間 |
再現性の注意: xhro_view の各ビューは current_timestamp() を内側に持つので、ビューを単純に SELECT すると「最後の 12h / 24h」限定になります。再現性を求めるなら view 定義を写して時間窓パラメータを外出ししたものを Studio 側のクエリテンプレに持ちます。
派生・モデルデータ (xhro_04_modeling_v2)
XHRO-04 を題材にしたモデリング派生。AI Copilot のプリセット派生のお手本として 読み専で参照します。Workspace 側からは 読みません (Bundle 境界を越えるため)。あくまで built-in 特徴量を設計する際の型と粒度の参考です。
| テーブル | 内容 |
|---|---|
eegecg_raw / _interpolate / _preprocess | EEG/ECG の 3 段階前処理。eegecg_preprocess は (ts_seq INT64, hid STRING, ch1..ch4 FLOAT, ts_jst_clns FLOAT) |
ppg_raw / _preprocess / _interpolate / _interpolate_v2 / _sqi / _stat | PPG の前処理 → 補間 → SQI → 統計の段階パイプ |
acc_raw / _interpolate | 加速度の補間 |
bio_raw / _interpolate | BioZ の補間 |
xh015_hrv_attached / _v2 | 1 被験者 (XH015) について HRV-LF/HF/LFHF を貼り付けた最終出力 (dt_jst, ch12_diff, quality, HRV_LF, HRV_HF, HRV_LFHF) |
xhro_04 側の睡眠ステージ × HRV 集約
hrv_sleep_stage_features: 1 行 = 1 タイムポイント。hrv_rmssd,sleep_stage,sleep_stage_name,hrv_rolling_15min_mean, ...hrv_deep_sleep_features: 1 行 = 1 夜。deep_sleep_duration_sec,sleep_quality_score,hrv_during_deep_sleep_mean,sleep_efficiency, ...
Studio の既定値 (実観測から導出)
Quick Export 1 枚で「被験者 → 時間帯 → エクスポート」を成立させるための既定値です。
| 既定 | 推奨値 (実観測から) |
|---|---|
| 既定データセット | xhro_04 (最新の本番試験) |
| 既定信号 | eeg (250 Hz, ch1-ch2 = ECG) |
| 既定 channel pair | ECG: ch1 - ch2 / EEG: ch3 - ch4 |
| 被験者カタログのキー | (expt_id, hid) 表示、uid を実クエリに渡す |
| 既定時間窓 | XHRO-04 の eeg 最大窓 2023-11-10 03:00 〜 2023-11-13 02:00 (JST) を 3 日窓のひな型として表示 |
| 既定 availability ソース | xhro_NN.meta を 10 秒 bucket でカウント (= wearing_summary 式) |
| 既定 ECG ラベル参照 | xhro.ecg_annot (subject_id="0" 限定なので Phase-1 では「比較タブで開く」程度) |
セキュリティ・運用メモ
- 鍵は絶対にコミットしない。
.gitignoreに*-bq.jsonとsuntory-analytics-bq.jsonの二重防衛がある。.envも同様。 .envには ANTHROPIC API キー等の他種シークレットを混ぜない。Settings 画面 + KMS 経由で扱う。.env.exampleに LLM キーをコメントとして残しているのは「ここには置かないでね」の意図的なリマインダ。- 本番 GCP プロジェクト変更時はこのページの §接続先テーブルだけ書き換える。リポ内に他の場所でハードコードしない (Postgres
settingsへ移すまでの暫定)。 - 鍵ローテーション: SA 鍵を発行し直したら同じ path に上書きするだけで OK。古い鍵は
gcloud iam service-accounts keys deleteで取り消し。 - 監査: Studio が発火するすべての BQ ジョブは
dry-run → confirm → submitの 3 段で、各ステップをaudit_logに積む方針 (詳細は operations)。