4.2 スキーマオブジェクトの定義
テーブル、列、インデックス、VIEW の設計時に決定する事項をまとめます。SQL 構文とオプションは 重複掲載せず、SQL 構文リファレンスを正本とします。
テーブルの作成と削除
先にテーブルタイプの選択を完了します。そのタイプに応じて テーブル名、列、キー、制約、インデックス、VIEW を定義します。
行の単位と列の役割
1テーブル内で行の単位を統一します。設備の日次要約と秒単位の元データを同じ意味の行として
混在させると、COUNT や AVG を解釈しにくくなります。元データと集計には、それぞれ明確な
行の単位とクエリ名を定めます。
| 列の役割 | 例 | 設計原則 |
|---|---|---|
| 対象の識別 | sensor_id, equipment_id | 表示名とは別の安定した値 |
| 発生基準 | measured_at, event_time | 発生時刻か受信時刻かを明示 |
| 計測値 | temperature_c, pressure_kpa | 単位、有効範囲、補正方法を定義 |
| 品質 | quality_code | 欠測、計測失敗、有効な0を区別 |
| 参照属性 | 場所、設備タイプ | タグメタデータか別の参照表で管理するか選択 |
外部設備コードなど既存の業務キーは自然キー、別途発行する番号はサロゲートキーです。コード変更や 複数収集元での再利用がある場合は識別範囲を明確にするか、サロゲートキーを検討します。自動増分番号は 生成順序の値であり、発生時刻や重複のない収集を自動保証しません。TAG の名前は計測行ではなくタグを識別します。
テーブル名は英字で始め、英字・数字・アンダースコアを使います。予約語やシステムオブジェクトと 紛らわしい名前は避けてください。削除前に依存する VIEW、インデックス、ROLLUP、保持ポリシーを確認します。
タイプ別の作成例は次を参照してください。
テーブル変更
列の追加・削除・名前変更・型変更の対応は、タイプとデータ有無によって異なります。 運用テーブルを変える前に、次の順で判断します。
- 対象 Edition とタイプが該当
ALTER TABLEに対応するか確認します。 - 既存データ、インデックス、VIEW、アプリケーションの列順序依存を確認します。
- 運用と同じスキーマ・データ量で実行時間とロックの影響を測定します。
- 戻すのが難しい場合、新テーブルを作成し、検証後に切り替えます。
列追加後は、既存行と新規入力行をそれぞれ検索し、NULL・DEFAULT の結果を確認します。 すべてのテーブルで既存行が DEFAULT になるわけではありません。VOLATILE の既存行や TAG メタデータの 自動登録行には別の規則があります。ARRAY の DEFAULT 規則 を含め、対象タイプの DDL 仕様を確認してください。
アプリケーションのデプロイとスキーマ変更の順序も定めます。対応経路では列リストを明示し、 位置に依存する Append・バインドを新スキーマと照合します。移行時は行数だけでなく、キー別件数、 時間範囲、NULL 比率、代表集計も比較し、切り替え中の新規行の欠落・重複への対応を定めます。
正確な対応と構文は ALTER TABLE リファレンスと テーブルタイプ別サポートを参照してください。
列とデータ型の選択
実際の値範囲と演算に基づき、適切な最小の型を選びます。表示形式と保存型を混同しないでください。
| データ | 検討する型 | 注意点 |
|---|---|---|
| 整数の計測値・コード | SHORT・INTEGER・LONG 系 | NULL 予約値と範囲 |
| 浮動小数点の計測値 | FLOAT・DOUBLE | 精度、集計誤差、NULL 予約値 |
| 正確な小数計算が必要な値 | DECIMAL | precision・scale・丸め規則を先に定義 |
| 時刻 | DATETIME | 表現範囲とタイムゾーンをクライアント・セッション方針と設計 |
| 短い文字列 | VARCHAR | 最大長とエンコーディング |
| 長い本文 | TEXT | 対応タイプ、ソート・集計制約、インデックスコスト |
| ネットワークアドレス | IPV4・IPV6 | 文字列ではなくアドレス型を検討 |
| 構造化文書 | JSON | サイズ、列への昇格基準、タイプ別対応 |
| バイナリ | BINARY | タイプによって可変長・固定長が異なる |
| 固定個数の数値 | 数値 ARRAY | 要素の型・長さ、要素 NULL と全体 NULL の区別 |
VARCHAR(n) の長さはバイト数で、韓国語や絵文字では文字数と異なります。整数型は一部の境界値を
NULL 用に予約するため、プログラミング言語の整数範囲をそのまま使えません。FLOAT・DOUBLE も
正の最大値を NULL と認識します。型の小ささより、有効範囲と演算の意味を優先します。
正確な小数が必要な値: DECIMAL
金額、税率、精算値など10進の正確さが必要なら DECIMAL を使います。誤差を許容し広い指数範囲を
必要とする計測には FLOAT・DOUBLE を使います。違いは保存サイズではなく値の意味です。
丸め結果を業務にそのまま使う値には DECIMAL を選びます。
設計時に次を決めます。
| 決定事項 | 内容 |
|---|---|
| precision | 有効数字の総桁数、1～65 |
| scale | 小数桁数、0～30、precision 以下 |
| 省略時 | DECIMAL は DECIMAL(10,0)、DECIMAL(M) は DECIMAL(M,0) |
scale を超える小数は、入力時に最も近い値へ丸め、ちょうど半分なら0から遠ざかる方向へ丸めます。 保存時に確定するため業務規則と一致するか確認します。precision を超える値は切り詰めや浮動小数点変換ではなく エラーになるため、桁数には余裕を持たせます。
SUM、AVG、MIN、MAX、GROUP BY、ORDER BY、DISTINCT は正確な計算経路を使います。
一方、パーセンタイルや高度な統計など、exact DECIMAL 経路がない演算は DOUBLE へ変換し、近似値となります。
正確さが必要な集計と参考用の統計を区別します。
アプリケーション側で浮動小数点を経由すると、保存型に関係なく精度を失います。JDBC は BigDecimal、
Python は decimal.Decimal、ODBC は SQL_NUMERIC など、10進表現または文字列で渡します。
宣言、インデックス、クライアントの対応は
DECIMAL と NUMERIC 固定小数点型を参照してください。
構造化文書: JSON
収集元でキーが異なる、または項目が増える追加属性には JSON が適しています。スキーマ変更なしに
項目を追加できるためです。一方、WHERE や GROUP BY に頻繁に使う値は別の列に昇格します。
JSON は主キーにできず、LOOKUP は JSON パスインデックスをサポートしません。
1文書は最大32,768バイト、JSON パスは最大512バイトです。元のペイロード全体を保存するより、 クエリに必要な属性を格納するために使います。
| テーブルタイプ | JSON 列 | 確認事項 |
|---|---|---|
| TAG・LOG・TRANSACTION | O | JSON 関数とパスクエリに対応 |
| LOOKUP | O | 一般列として対応、JSON パスインデックスは非対応 |
| VOLATILE | X | JSON 列を作成不可 |
クエリには -> と JSON_EXTRACT_* を使います。JSON_SET 系は変更後の文書を返す関数であり、
列へ保存するには対象テーブルの UPDATE 制約に従います。LOG は行の UPDATE に対応しません。
TAG METADATA では既存の JSON 列を参照して更新できますが、Standard Edition の TAG DATA UPDATE
では既存行の列を参照する SET 式を使用できません。入力時に文書を完成させるか、
対象に適した更新方法を選択します。関数別の対応は
JSON のテーブルタイプ別サポートを参照してください。
型の選択前に確認する制約
| 型 | 設計時の確認事項 |
|---|---|
TEXT | LOG と Standard Edition の TRANSACTION だけで対応。LOG の TEXT は ORDER BY・GROUP BY に使えず、MODIFY COLUMN で VARCHAR に変換できない。ソート・集計値は別の VARCHAR・数値列に保存。 |
BINARY | LOG は可変長で最大64MB、TAG は固定長 BINARY(n) で1～32,767バイト。LOOKUP・VOLATILE は非対応。 |
DATETIME | 1970-01-01～2262-04-11をナノ秒精度で保存。期限や無期限を表すため任意の遠い未来時刻を入れない。 |
ARRAY | 固定長の1次元数値配列。要素数は1～1024。全体 NULL と要素 NULL を区別。 |
全範囲とタイプ別対応はデータ型リファレンスを基準に確認します。
制約とデフォルト値
PRIMARY KEY、NOT NULL、DEFAULT の対応はタイプごとに異なります。TAG の PRIMARY KEY は
タグ識別子、LOOKUP・VOLATILE・TRANSACTION の PRIMARY KEY は行識別と更新経路を決定します。
NULL は未知または欠けた値で、0や空の区間とは異なります。計測失敗を DEFAULT 0 に置き換えると
平均や正常判定が歪みます。COUNT(*) は行数、COUNT(value) はその列が非 NULL の行数なので、
サンプル数を報告する際に区別します。
デフォルト値は省略入力への方針であり、妥当性検証の代わりではありません。テーブルにない制約は 収集・業務アプリケーションで検証します。外部キーなど他 DBMS の制約を名前だけで対応と判断せず、 TRANSACTION のサポート範囲を確認します。
_ARRIVAL_TIME や _RID などのシステム列を業務キーに使わないでください。公開されたクエリ上の
意味が必要な場合だけ参照し、保存構造や生成方式には依存しません。
インデックス設計
インデックスはクエリコストを下げますが、入力・保存コストを増やします。
代表クエリを記述し、条件に一致する行の比率（選択性）を確認します。全設備の月平均と、1台の設備の
特定注文検索ではアクセスが異なります。すべてのフィルター・結合列を索引化せず、EXPLAIN と
実測時間を比較して有効なインデックスを残します。
- 時間範囲とタグ識別子で十分な TAG クエリには、最初から追加インデックスを作りません。
- LOG の頻出フィルター列は、計画と選択性を測定してから索引化を検討します。
- LOOKUP・VOLATILE・TRANSACTION はキー検索と結合条件を基準に設計します。
- 長いテキストの単語検索には、そのタイプが対応する
KEYWORDを検討します。
作成・削除構文と対応タイプはインデックス SQL リファレンスを参照してください。
VIEW 設計
VIEW は繰り返すクエリに名前を付けますが、結果自体は保存しません。意図しない時間範囲の固定や 不要な全列の読み出しを避け、元テーブル・列の変更前に依存 VIEW を確認します。
頻出の列リストや単位変換を VIEW で一貫して提供できます。ただし作成だけでデータがコピーされたり、 クエリコストが下がったりするわけではありません。過去イベントと最新参照表を結合した VIEW では、 参照表変更で過去の結果も変わる場合があります。発生時点の属性が必要なら、バージョン別参照情報や 元の行に記録した属性を使います。
VIEW の作成・クエリ・削除と制約はVIEW SQL リファレンスを参照してください。