可変データ構造をRDBに保存する方法について

Question

いつも本当に助かっています。
テーブルの設計についてご意見をお聞かせください。

仕事でデータロガーのシステムというものを実装しました。
そのシステムは外部の機械から何らかのデータを受けとり、保存していくだけの単純なものですが、受け取るデータ構造を可変としています。

---

例：

• 機械Ａは 気温、湿度、気圧
• 機械Ｂは 電圧、電流
• これらのデータが各機械から１０秒毎に（23℃, 30%, 1000hPa というように）送信されるので受信し保存する。

条件：

• 機械毎のデータ定義を画面から設定する。

(何カラムあり、どのカラムが何のデータで、型は何で、有効桁数が何桁で、単位は何か、等）

• 各カラムのデータ型は真偽値、整数値、小数値、文字列がある。
• 機械Ａも機械Ｂも数千台程度接続されている。今後機械Ｃも追加される
• データを元に各機械毎のグラフを描画したり全体で集計したりする。閾値判定も行う。
• データ蓄積後、定義の変更はある程度許容する。（列の追加や単位の変更など）

---

これらを実現するためのテーブル構造の案として、下段に記載した幾つかの案などがあるかと思います。
実はもうこのシステムは「案１」で開発が終了し、問題なく稼働しています。
上記は例ですが、実際に数億レコードのデータが蓄積されています。
ただ、設計実装が私一人で周りに頼れる人もなく、設計に不安があるため、皆様のご意見をお聞かせください。

質問１．情報が少なくて恐縮ですが、「案１」を採用したのは正解だと思いますか？
質問２．現実的な案として下段以外のものはありますか？
質問３．下段の案の中で、上記条件に対して「ありえない」と考えるものはありますか？
質問４．一般的にこういったもののデザインパターンはありますか？
質問５．他、経験談や参考情報などありましたらご教示ください。

実際に問題なく稼働しているのだからいいじゃん、という部分もあるかと思いますが、後学のためよろしくお願いいたします。
※できるだけ広くご意見を頂きたいので少し長めに質問をオープンしておくかもしれません。

CentOS7.2, MariaDB10.0, Java8

---

案

１．ID, 機械ID, キー(VARCHAR), 値(VARCHAR), 日時 のカラムを持つテーブルを用意する。
※一旦値をVARCHARで保存し(小数値1.23ならそのままテキストで)、読み出し時に各定義の型に合わせて解釈します。
※１カラム（例えば気温）だけで１レコード消費します。
※閾値判定時、VARCHARカラムに対して平気で 「値 > 1.23」 のようなことをやります。
※１回の送信データを示す集計基準は機械IDと日時です。
※この案を採用しました。

２．「定義ごとに柔軟にテーブルを作る」システムを作る。
※プログラムが CREATE TABLE 文を発行します。
※なんだか気持ち悪いという理由と、システムが複雑化しそうという理由で却下しました。
※データ量は最も少なくなり運用も楽そうなため、今はこちらの方がよかったかも、と思っています。

３．ID, 機械ID, c1, c2, c3, c4..., 日時 のようにカラムを予めたくさん用意し、カラムとデータの対応テーブルを別途用意する。
※ダサく感じたので却下しました。なしではなかったなと思っています。

４．ID, 機械ID, データ(VARCHAR), 日時のカラムを用意し、データにXML, JSON, ハッシュオブジェクトシリアライズなどで保存する。
※集計と運用が厳しくなるかと思い却下しました。

５．RDBを用いない。
※RDB以外を知らないことと、集計が厳しくなると思い、却下しました。

Answer 1

回答が長くなりますことをご了承下さい。。。

#始めに
質問者様の現在の設計は、
maisumakunさんの回答の通り、
「EAV」アンチパターンとされる設計技法とされています。

DB設計などについては「SQLアンチパターン」という書籍にて、
そのアンチパターンに陥りやすい背景、
及びどのように改善すべきかまでまとめられているため、
当方としては是非ご一読してほしい書籍の一つです。

#要件に対する設計是非について
「EAV」アンチパターンとはされていますが、
今回の要件のような場合では採用されるケースはしばしば見受けられます。

以下のような懸念が払拭できない場合は、
個人的には今回の設計方針が一番妥当とは考えます。

1. 機械情報(機械A、機械Bなど)の登録数は今後も増加していく
2. 機械情報に付随するパラメタ値は増減する恐れが高い
3. 機械情報に付随するパラメタのデータ型も今後増減する恐れがある（論理値、実数、文字以外が加わるなど）

上記の課題がある場合は、
テーブル数の上限の固定が困難なため、
今回のような汎用的テーブルの導入を推す根拠足るとは思えます。

ただしパラメタ値の検索条件の書き方次第で、
データ型のキャストが必要以上に発生することと、
データ集計などには向かないところが玉に瑕とかもしれません。

#案２～案５への見解
案２について。
「機械ごとに定義情報を作っちゃえ！」ってことであれば、
あまりオススメできる手法ではありません。

理由は「要件に対する設計是非」にあげた懸念１番の問題があるためです。
特に機械情報の登録・修正・削除の頻度が高いと破綻しやすい手法です。

案３について。
情報によっては無駄なカラム情報を管理するだけになるので、
質問者様が言うように活かした設計ではないかなという印象ですね。

案４について。
データの実態自体は、XML, JSON, ハッシュオブジェクトシリアライズで保存ということなので、
それに起因して集計・運用時のノウハウは必要となってくる可能性は高いです。

うまいこと扱う自身がない場合は、
避けた方が無難かもしれませんね。

案５について。
当方はNoSQL系の経験が皆無のため、
こちらは有用なアドバイスができません・・・。

ちなみに、sm_ymさんの回答にあがっています、
カンマ区切りで１つのカラムに保持する方法は、
sm_ymさん自身もおっしゃっているよう破綻しやすいです。

「ジェイウォーク」アンチパターンしても有名なので、
まったくオススメは出来ません。

#EAVを用いない設計方針として
「EAV」を回避しRDBらしく課題解決に無理に拘るのであれば、
一例としてあげるなら以下のようなデータモデリングとなる気がします。
下記のような設計とすると、
恐らくですが、新規テーブル定義追加リスクは発生しません。

ただし前提条件としては、
・今後データ型の増加は起こりえない

上記を満たす必要があり、

問題点としては、
・機械数・パラメタ数がわずかの場合必要以上のテーブル数となる
・単体では意味がなさないテーブルが多いため・別途ビューの作成が要求されるかもしれない

を許容する必要は出てきます。

以下は概念データモデル設計のほんの一例を記載します。
機械マスタ（ID, 機械名, ... , 登録日, 変更日)
機械別パラメータ（ID, 機械ID, 連番, パラメタ名, データ区分, 修正日）
機械別文字列パラメータ (ID, パラメタ値)
機械別数値パラメータ (ID, パラメタ値)
機械別論理値パラメータ (ID, パラメタ値)

上記について簡単に説明します。
先ずは機械マスタですが、
こちらは機械共通の属性情報を管理します。
今後、機械一覧検索などが必要になった際に一覧表示する情報はここで管理するとよいでしょう。
尚、主キーはIDの想定です。

次に機械別パラメータですが、
こちらは各種パラメータ詳細テーブルの親テーブル、つまり汎化したものに当たります。
（※オブジェクト指向の抽象クラス・派生クラスをイメージするとよいかも）
こちらでは機械別パラメータの一覧だけを管理します。
尚、主キーはIDで、機械ID、連番に対して複合一意キーを付与する想定です。

ポイントは上記テーブルでは、

• パラメタごとのデータ区分を管理する
• パラメタの具体値までは管理させない

上記の２点となります。
パラメタごとの修正・変更日を管理したいなら、
このテーブルにカラムを設けると良いでしょう。

最後に各種パラメータ詳細テーブル（機械別文字列パラメータ）ですが、
こちらで各種パラメタの具体値を管理します。
この時点でデータ型は適切に管理されているため、
集計、条件比較に無駄なキャストが入り込む隙は与えません。
数値パラメータはnumeric型やnumber型などを利用すると、
整数・少数どちらでも管理可能です。

あとグラフ化なども行うとのことで、
要件次第ですが例えば全機械の電圧のみをグラフ表示するとかがあるなら、
数値パラメタテーブルに更に種別などをもたせて・・・、
など集計の切り口は詳細テーブル内でも与えられるのかなとは思われます。

尚、主キーはIDとなり親テーブルの機械別パラメータのIDと同一値が入る想定です。
データ整合性の観点から各種パラメータ詳細テーブル全てのIDには、
機械別パラメータのIDを参照する外部キー制約を定義すると尚良いでしょう。
（※設計段階で親子関係を明示する意図でも有用です）

ちなみに万一パラメータで保管すべきデータ型が増えたら、

• 各種パラメータ詳細テーブルの追加
• データ区分の追加

上記作業が必要となってきます。

#蛇足
上記で紹介したテーブル構成だと、
検索時に何かと不便なので、
上記設計のような場合ではビューを作成することをオススメはします。

ついでなので以下にビューのイメージを記載しておきます。
注意点としてはビューで値を簡易的に一覧する場合は、
データ型を合わせる必要があることです。
(※要するにキャストが必要になるのが面倒です＾＾；)

実際に下記クエリは流してないので、エラーになる可能性大です。
あくまでイメージとして参考にして下さい。

CREATE VIEW "機械別パラメータ一覧ビュー" AS
SELECT
    m.ID AS "機械ID"
,   m."機械名" AS "機械名"
,   p."パラメタ名" AS "パラメタ名"
,   p."データ区分" AS "データ区分"
,   CASE p."データ区分"
      WHEN '1' THEN "文字列"
      WHEN '2' THEN "数値"
      WHEN '3' THEN "論理値"
      ELSE '‐'
    END AS "データ区分名称"
,   CASE p."データ区分"
      WHEN '1' THEN p_char."パラメタ値"
      WHEN '2' THEN CAST(p_num."パラメタ値" AS varchar)
      /* 
        下記の論理値は普通のキャストでTRUE、FALSEの文字列となるか怪しいので、
        多分直す必要があります。。。
       */
      WHEN '3' THEN CAST(p_bln."パラメタ値" AS varchar)
      ELSE ''
    END AS "パラメタ値"
,   p."修正日"
FROM
    "機械マスタ" m
    /* 以下テーブル群はパラメタ未登録の機械も表示要なら結合方法は「LEFT JOIN」へ */
    INNER JOIN "機械別パラメータ" p
      ON  m."ID" = p."機械ID"
    INNER JOIN "機械別文字列パラメータ" p_char
      ON  p.ID = p_char.ID
      AND p."データ区分" = '1'
    INNER JOIN "機械別数値パラメータ" p_num
      ON  p.ID = p_num.ID
      AND p."データ区分" = '2' 
    INNER JOIN "機械別論理値パラメータ" p_bln
      ON  p.ID = p_bln.ID
      AND p."データ区分" = '3'

また、上記だと結局パラメタ値は全て文字となってしまうので、
数値パラメタのみだけに特化したビューなどを検討するのもありかもしれません。

#追記
ただし今回のケースだと上記のようなRDBのルールに厳密に則って、
手間をかけて設計するメリットが薄い気がするので、
「EAV」の設計パターンの対策として上記のような手法がある、
程度に抑えておくだけで良いかと思います。

Comments

[mosa]

詳細にありがとうございます。

> 当方としては是非ご一読してほしい書籍の一つです。
オライリーですよね？買います。

> 「EAV」の設計パターンの対策として上記のような手法がある、 程度に抑えておくだけで良いかと思います。
今回の質問の趣旨をご理解いただきありがとうございます。今回のシステムにはおそらく手をいれることはないのですが、この設計の問題や課題、その対策や手法について広くご意見をいただくことが目的でしたので、理解しやすいよう詳細に記載いただいてありがたいです。

自分のやっている設計が「EAV」である、という認識すらない状態でした。
後付にはなりますが、設計書に「EAV」（アンチ）パターンで設計されている旨を記載しようと思います。（アンチとは書かないと思いますがｗ）

続きを読む ∨

[Panzer_vor]

> mosaさん
ベストアンサーに指名していただきありがとうございます。

当該質問では、
様々な切り口からの回答を拝見することができ、
当方としても大変有益なものとなりました。

今回は「EAV」パターンに対する、RDBらしい王道の解を提示させていただきましたが、
様々な要件・制約が出てくるとかのやり方がベストにならないことも容易に考えられるので、
その辺りのバランスの見極め、そして適切な技術選択をする能力・知見の広さが当方としても課題かもしれません^^;

Answer 2

解決済となっていますがIoTシステム体験談と個人的な見解？でも
相談内容と被るところもありますが。

1.どういう仕様になるのか運用して決定する仕様

データの内容はセンサー(IoTデバイス)に依存するのでいきなりは変わりません。
はじめにA,B,Cのデータを1分ごとに取得するセンサーにしていたが運用してBデータがいらずにDデータが必要になったりします。

データ取得頻度も1分から30秒など変わっても普通のこと

1.1.仕様がきまらないのはなぜ？

• だれも経験したことがない
• 前例がない
• 参考データがない
• どういうデータが取れるかわからない

なので、そういうものだと諦める。

2.最悪を回避するために

• 生データ(センサー)からのデータを素直に保存

何かしらの変更があった場合、センサー側とシステム側で同期が取れずにデータの取りこぼしや、テーブルの汚染の危険が発生する場合がある。素直？というかバカ正直にデータをそのまま保持するテーブルを持っている。
恩恵に有りつけるのは何かしら問題が発生したときなので、普通の感覚では無駄なテーブルだろと思うのが普通

3.正規化が危険？

RDBの基礎中の基礎である正規化。これは下手に使用しないことにしている。センサー情報は一度取得したら二度と書き換えることがない(偽造しない限り)から。

重複データは無用な産物だから正規化しようと言うのが普通。だがデータ数が多すぎるとPK、index、whereの書き方をどんなに頑張っても時間がかかる場合がある。それに正規化して単位などを他テーブルから取りに行こうとしたら、ただのSELECTが応答せず、バッチ処理かこれ？と勘違いしそうになったりする

怖いところが、これを体験するのは膨大なデータが溜まったときというところ。安定してるな～と思ってたら最近重くて使えないんだけどと言う話が出て来る

例えば1000センサー10秒毎なら1年間に52560000行になる。
他にも、センサー数が変わったら？10秒が5秒になったら？運用期間が数年になったらなど要因はいろいろ

数の暴力は怖い。

4.無知なテーブル設計

無知な設計だと思うがsensorA2016テーブルsensorA2017テーブルなど年ごとにバッサリ分けたことがある。
当たり前だが効果は抜群だった。月まで分割してしまおうかと思うぐらいに
ただし、これは年またがりの処理がないからできる

5.かならず壊れる

物は必ず壊れることを念頭に置く。
センサーのID(シリアルNo)をPKとした場合(複合キー有り)
ID：1000番のセンサーが壊れたからID：2000番のセンサーに付け替えることもある。
システム側としては同じIDのセンサーを付けてくれと言いたくなるが、それじゃシリアルNoじゃないではないかと考えたりする。事実センサーIDの変更＝ファームの書き換えが行われる場合が多いので同じIDを用意することは難しい

テーブルのセンサーIDを1000番を2000番にupdateするか、1000番のテーブルをコピー＋2000番に書き換えるなども考慮が必要。

一番厄介なのが1000番が壊れたから一時的に1500番に、そして2000番を付けたとかの事後報告。
さらに1500番が他で使われてたら、時系列の情報がなかったらどうしようもなかったりする。

6.かならず無くなる

センサーのデータが来ないと思ってたらセンサーがなくなっている場合がある。
新しい社員が取ったり電源を切ったり、窃盗？があったり。

まとめ

設計・仕様書通り作業する仕事がしたい。

Comments

[mosa]

ありがとうございます。
将来発生するであろう問題に対しての切り口で大変参考になりました。
こちらのシステムでは今のところ問題は発生していないのですが、データの削除を行っていないため、将来データがあふれることは確定しています。またインデックスの肥大化も避けられない状態です。
データ蓄積の目的にもよりますが、基本的には古いデータを削除していく方向でいきたいとは思っているのですが、なかなか決断が下されることがなく。。。

> 設計・仕様書通り作業する仕事がしたい。
同感です。

[mosapride]

IoTと一括りにするのは簡単ですが、case by caseの力が多いのでITの一般常識に囚われては行けない時がきます。
私の経験が活かせるIoTシステムもあるでしょうし、経験があるからこそ深入りしすぎて無駄な設計・製造をやってしまうこともあるでしょう。
最近でも変更に変更が重なり4割ぐらいがデットロジックのシステムを作ったりしてしまいます。

「やってみないと分からない」と投げやりな言い方になりますが、これが真実だと思います。

お互い頑張りましょう＞＜

Answer 3

現在行われていらっしゃる手法1は、「Entity Attribute Value」という名前が付く程度にはよく行われる（けれども、RDBMSということを考えればそこまで筋が良いとは言い難い）手法です（Qiita）。

とはいえ、「RDBMSだけを使って」「一定の柔軟性を持たせる」場合には必要悪として採用されることもあります（WordPressにもこのような構造のテーブルがあります）。

データセットが数種類しかないのであれば、案2のように「機械ごとにテーブル作成」という手段も実用になるかなとは思います。

Comments

[mosa]

ありがとうございます。
名前あったんですね。私も市販パッケージシステムの中を見たらこういう構造のテーブルがあって、複雑なことやっているな・・・、と思ったことがあります。
Qiitaの情報ありがとうございます。時間をかけて読んでみます。

Answer 4

私なら多分XMLを使うと思います。
以前アンケートシステムを作ったとき、
レコード単位に構造が全く異なるデータを格納するのに
この手法を利用しました。

RDBMSからみるとデータは共通のキー項目を除くと
単一の文字列という設計。JSONを使う案と基本的に
同じです。

Comments

[mosa]

ありがとうございます。
要件としてもこの「スキーマレス」というのでしょうか、こういったものが結構あるのですね。
また、色々な場面でこういった要求があることもあることもわかったため、今回質問させていただいてよかったと思います。

Answer 5

KEYに格納されているデータの種類はどれくらいあるのでしょう？あと機械の種類はどれくらいでしょう？
DWHの世界ではカラム数が数百あるテーブル定義も珍しくありません。
あと扱っているデータは蓄積していくもののようですからテーブル定義の変更はカラムの追加だけのような気がします。

先の種類の数によりますが、今後もずっとデータを記録していくなら、私なら一つのテーブルにするか機械の種類毎のテーブルにしますね。それからパフォーマンスとバックアップ単位を考えてデータベースを拠点毎や一定期間で切り分けます。

更にデータ量が億単位になる点から、RDBでの処理範囲を超えるかもとも考えます。

ビッグデータの分析の世界ではRDBではなくTSVファイル形式でデータを保存しておいて、それをテーブルに見立てて分析をかけることも珍しくありません。Apache Hadoop とかはその典型的なものでしょう。
Hadoopの機能をサービスとして提供してくれるAWSのEMRと呼ばれるプロダクトも便利です。
テラバイト単位のデータも扱ってくれるから良いですよ。

Comments

[mosa]

ありがとうございます。

> KEYに格納されているデータの種類はどれくらいあるのでしょう？あと機械の種類はどれくらいでしょう？
機械Aは200近くのキーがあり、機械Bは10程度です。今のところ２種類しかないですが、今後、制限なく増えていくことになっています（とは言え年２，３程度？なのかもしれません）

外部のデータストアサービスを利用する、ということを全然考えていませんでした。自前でHadoopを立てるほどのものではないな、とは思っていたのですが外部のサービスを利用するという手もありましたね（今回のPJはお金ないので、それも無理ですがｗ）

色々と自分に欠けている情報をいただけて非常に助かります。続きを読む ∨

Answer 6

案１．よく見ますね。可もなく不可もなく、選択肢としては悪くないと思います。
他の人間が保守・運用するとしてもそこまで抵抗なく触れるでしょうし。
RDBでやるのなら、私もこれかな、と。

案２．個人的には無くはない、という感じですが、採用には気が進みません。
システムの製造者=運用・保守担当者なら問題ないのですが、そうでない場合、
自動生成の特性上、どのテーブルが何の機械のテーブルなのか、後年に分かりにくくなりそうです。
破棄された機械のテーブルのケアとかも煩雑な気がしますね。

案３．RDBの必要ないですよね、多分。個人的には無しですかね。

案４．この組み合わせは遭遇したことが無いので、すいませんがノーコメント(汗

案５．現状で意見があまり出ていないこれに言及させてもらいます。
同じく、用途がログ解析の案件経験が有り、
その際はMongoDBというNoSQLのDBとpentahoというツールで導入しました。

問題は、pentahoに関しては日本語の情報は多くないので、使用者・開発者双方から毛嫌いされるケースがあるかもしれません。
（私の時はpentaho kettle solutionsという本で勉強しました）
NoSQLのDB経験者も人口は多くはないのかな...すいません、その辺は分かりません。

ただ、そんな感じできちんと知識が無いと開発しずらいことから、
プロジェクトメンバー全員にRDBに比べればマイナーな知識が要求されます。
そのハードルさえ超えれれば、この手のDWH解析寄りの案件には非常にマッチするかと思います。

Comments

[mosa]

ありがとうございます。
MongoDBなど「KVSストアやNoSQLというものがある」ということは知っていたのですが、触れる機会がなく今回もなんとなく見送ってしまいました。
これを期にMongoDBはじめ幾つか見てみようと思います。

プロジェクトメンバーは実は私ひとりで、まわりのメンバーはRDBすら怪しい状況でして。。
ですので私が抜けたら保守性云々抜きにして誰も保守できなくなるとは思っています。

ですので知識や経験のある方々からこのようなお話を聞けてとても助かっています。

Answer 7

異なるスキーマを持ったログを同じテーブルに入れるのは誰も得をしません。
気温と電圧は比較が出来ないからです。
キーで分ける手間は開発にもDBサーバにもストレージに負荷をかけます。
このことから、現行の案１は今後問題になる可能性が高いです。

機械の種類ごとにテーブルを分けるのがRDBのやり方です。
この方法であれば、集計に問題が起こることはないと思います。

ただし、機械の種類が多すぎたり、非常に似た項目があるが機械毎に項目が少しずつ違う場合、
うまく管理出来ないということも考えられます。
その場合は、RDBは役に立ちません。苦手な事をさせても意味が無いと思います。

”日時・機械ID・明細”あたりのテーブルで、明細をプログラム側で集計しましょう。
データの表現はプログラム言語の方が豊かなので、管理もし易いと思います。

明細はXMLでもCSVでもどちらでも良いと思います。もちろん、案５でも良いです。
どちらにしろ集計は手間なので、DBの構造をいじる手間が減るだけマシにはあります。

Comments

[mosa]

ありがとうございます。
様々なご意見をいただけて非常に助かります。

> 異なるスキーマを持ったログを同じテーブルに入れるのは誰も得をしません。
本当にそうだと思います。ここの部分に抵抗感がありご質問をさせていただきました。
今回は「キー」「値」という意味では同じスキーマです、というかなり強引な方法をとりました。

[iwamoto_takaaki]

キーと値が許されるなら、すべてのデータを１つのテーブルで管理可能です。
でもそれは、RDBの使い方ではないです。
（実際にKeyValuePairを基本としたNoSQLのDBMSは存在します。）

きつい言い方ですみません。

[mosa]

いえ、とんでもないです。ありがとうございます。
広く意見を募集したかったので、とてもありがたいです。

Answer 8

案1は割と良く目にします。
要件次第ではありますが、悪手ということもないと思います。
最善手かといわれれば、何とも言えないのですが。

案3もたまに見かけますが、RDBでやるのはどうかなぁと感じます。
レガシーな環境から移行したシステムでありがちっぽいです。

とあるシステムを案4で実装したことがあります。
JSONでデータを格納しました。
ただ、そのシステムはJSON部分で検索や集計をすることがほぼない前提だったため、案4を採用したのでした。
最近はRDBMSにもかかわらずJSONに対して検索できるものもある(新しめのSQL Serverはできたはず)ようなので、そういうRDBMSでしたら選択肢として案4もアリかもしれません。

Comments

[mosa]

ありがとうございます。
調べてみたら、PostgresSQLやSQL ServerにJSON型というものがあるんですね。
知りませんでした。
この手の話、意外とあるということにびっくりしました。
実績は参考になります。

Answer 9

私がこれを書き込む時点（2/25）で、回答数１４で Panzer_vor さんの回答は+10までいってるのですが、まだベストアンサーがついてないので、とりあえず、参戦。
IoTと機械学習が大流行なので、今後この手の話はますます増えると思います。teratail としても、ぜひベストアンサーが欲しいところだと思います。

まず、一つのデータベースでいろいろなサービスを一気に実装する考え方（データベース中心アプローチ）をやめたほうが良いと思います。SOAあるいはマイクロサービスという考え方でアプリケーションを分解することを検討すべきです。たとえば、

• ログを収集してとりあえず貯めるだけのサービス（極端な話、テキストでも良い。非同期性や完全性を確保するためのもの）
• ログの多様性を吸収して変換し、データ内容に応じて他のサービスに配信（Contents Based Routing）するメッセージルータ(mule や servicemix のようなESB）
• 集計や分析のためのデータウェアハウス（という言葉は古いのか？、Hadoop, RedShiftなど）
• 集計結果をエンドユーザに提示するための、仮想的なデータベースサービス（すぐに例示できないけど、JBoss Data Virtualization とかかな）と JavaScript でリッチなUIを提供するWebクライアント(react, angularなど）

のようにサービスを階層化して分離します。サービスごとに変更のスピードが求められるもの、完全性が求められるもの、技術革新に追従する必要があるものなど要件がことなるので、Docker などでマイクロサービス化して、追加、交換などが容易になるように設計すべきだと思います。

Comments

[mosa]

ありがとうございます。
そういえば疎結合や独立性のことを全く考えていませんでした。
新しい切り口でのご指摘ありがとうございます。

そろそろベストアンサーを決定したいと思います。

Answer 10

質問5. 他、経験談や参考情報などありましたらご教示ください

こちらのみ回答します。

質問と同じようなセンシングデータを複数のデバイスから定期的に収集するシステムの経験から。

質問に記載の

データを元に各機械毎のグラフを描画したり全体で集計したりする。閾値判定も行う。

RDBは時系列データの処理はあまり得意ではありません。
TSDB系も視野にいれておくことを推奨します。
もちろんRDBでもしっかり設計されていて運用に問題が一切でていないのであればそれでも構いません。

用途にあっていないものを選定すると、次の問題が発生し、性能劣化、障害になる可能性があります
⇒インデックスの肥大化、オプティマイズに膨大な時間を要する
⇒ストレージの消費
⇒ディスクIO性能の限界
⇒削除コスト

また、グラフの描画や全体で集計については掘り下げて、具体的なデータの取得方法について整理しておいたほうがいいかもしれません。

Comments

[mosa]

ありがとうございます。
TSDBというキーワードを初めて知りました。
ちなみに、現システムでは、MQTTでデータ受信、JavaでMariaDB(MySQL)に保存、PHP+Amchartsでグラフ表示、データの出力については期間を区切ってのCSV出力・グラフ表示、バッチでの閾値判定のみです。
事前にこちらで質問し、知識をつけておけばだいぶ設計も変わっていただろうな・・・、という印象です。
take88さんの仰る、MongoDB + Elasticsearch + Kibana を採用していたかもしれません。

⇒インデックスの肥大化、オプティマイズに膨大な時間を要する
　→今のところ時間と機械IDでのインデックスのみなので、時間はかかりませんが、おっしゃる通り既に肥大化し始めています。
⇒ストレージの消費
　→「上の判断」がまだなされていないため、今は削除していません。現状のままでも２年後に溢れます。　
⇒ディスクIO性能の限界
　→一応、システムとしての上限を測り、定義しています。
⇒削除コスト
　→かなり大きいです。「上の判断」がなされていない状態です（伝えてはありますが）。
続きを読む ∨

Answer 11

質問５．他、経験談や参考情報などありましたらご教示ください。

データロガーというユースケースでぱっと思いついたのはDBはMongoDB、検索や集計用ににElasticsearch + Kibana です。

Comments

[mosa]

ありがとうございます。
MongoDBというご意見が結構多くでてきました。知らなかったのがお恥ずかしいです。
Elasticsearch、Kibanaというものも初めて知りましたので、調べてみました。
知識がないことによって随分非効率なことをしていたな、というのが実感です。
ちなみに今回、グラフは AmCharts というものを使ってゴリゴリに実装しました。

Answer 12

MariaDBを使ってるならDynamic Columnを検討するのも手かもしれないです (使ったこはないです)。

https://mariadb.com/kb/en/mariadb/dynamic-columns/

Comments

[mosa]

ありがとうございます。
Dynamic Column、こんなものがあるとは知りませんでした。
まさに今回にうってつけな感じですね。
インデックスが無いのだけは残念ですが、非常に参考になる情報をありがとうございます。

Answer 13

とある情報を管理する製品が「GUIから定義を追加する事で各情報に任意のデータも追加できます」と言っていたので買ってみたら案3だった時があります。データは全て文字列型で、その他1、その他2…とたくさん用意されているだけで、

• このデータは真偽値で→その他1で"TRUE"、"FALSE"という文字列で管理します！
• このデータは整数値で→その他2で"0"、"1".. "100"という文字列で管理します！

という感じで、そしてVARCHAR(255)だし、255文字越える文字列データはどうするんだと聞いたら、分割して入れてくれとかふざけたこと抜かしました。二度とあの製品は買いません。

---

愚痴はここまでとして、サーバーの各情報(ディスク容量とか登録ユーザー数とか、メール総受信数とか色々)を集めてDBで管理するものを作った時、案5を採用しました。採用したDBはMongoDBです。1データの読み込みなら、WebからJSON受け取ってブラウザ側で処理するって方法だったので、JSONにしやすいMongoDBにしたという理由もありますが、サーバによってとりたい情報がバラバラだし、今後も増えていきそうだったのでNoSQLであるMongoDBにしたというのがあります。まだ、集めた過去の情報を閲覧できるだけで、本格的な分析とかの部分は作ってないので、集計とかするときはどんな感じと言われても、なんとも言えないのが現状ですが。

MongoDBの何が良かったというと、スキーマレスな所です。JSONっぽいBSONというものなので、階層化されたデータをそのまま扱えますし、一つ一つテーブル定義をしていく必要が無かったのは楽でした。ただ、ちゃんと最初にどういうデータ構造にするかを詰めておかないと、やっぱこれも！とかなってコード部分の方に出戻りがでるので、そこら辺はきちんとしたほうがいいようです。

もし、RDBMSという縛りがあるなら、Panzer_vorさんの方法をオススメします。任意のデータを追加できる系の結構メジャーなOSSではそのような構造を取っているようです(前に何かで見たんですが、何だったかまでは…たしか、uPortalだったかな？)。市販の製品は本当にできが悪いものが多くありますので、参考にしない方が良いと思います。

Comments

[mosa]

ありがとうございます。
出来上がったものが今のところトラブルを起こしているわけではないのですが、設計実装前にここでご相談しておけばよかったと後悔しています。
MongoDB、調べてみます。色々と参考になるお話ありがとうございます。

Answer 14

案１と案３のいいとこ取りみたいな、
値の保持をvarcharだけにせず、
数字で持てるところはintegerやdouble precisionも活用して
入れ物を冗長に持たせる方が、
どうせSQLで合計値や平均値などを算出するでしょうから
文字列→数値変換を省く意味で使うかなと。
インデックスもかけられそうだし。

それに、測定データなら測定できなかったときの値という特殊な保持の仕方もあるので、
単にNULLやゼロではない別の意味をもたせたフラグも必要だったりしますので。

Comments

[mosa]

ありがとうございます。
確かにおっしゃるとおりですね。Variant型のような形式にする方法がありますね。
VARCHARカラムに「値 > 1.23」 はちょっといかがなものか、と思っていました。

Answer 15

データ量や、頻度、どのような集計・分析を行うかも考慮する必要があるかと思います。

個人的には、ログであること、それなりの量や頻度が見込まれることから、NoSQLを検討すると思います。
NoSQLでも、KVSタイプやドキュメント（JSONがそのまま入ったり）などがあり、集計ができないわけではありません。

http://www.slideshare.net/recruitcojp/rdbnosqlnosql

保守の観点でも、バリデーションができない点で１よりは４ですが、サイズが膨大になることを踏まえると、５になるかなといったところです。

Comments

[mosa]

ありがとうございます。
これを期にNoSQLを少し調べてみます。
参考URLもありがとうございます。

Answer 16

お世話になります。あまり経験はありませんが、私も後学のため回答させていただきます。

まず、案に関する私の経験の度合いです。
案1 : 設計、実装、保守経験有
案2 : 未経験
案3 : 保守経験有
案4 : 未経験
案5 : NoSql等の運用経験はないのですが、よほどでない限りRDBでできることはRDBでする派です。
※ 案2に関しては、動的に一意となるテーブルを生成するということでしょうか。そういった経験はありませんが、とても興味深いです。

質問１．情報が少なくて恐縮ですが、「案１」を採用したのは正解だと思いますか？

私も同じ手法ですると思います。トランザクションデータを管理する上ではよく見ます。

質問２．現実的な案として下段以外のものはありますか？

1カラムにデータをカンマ等の区切り記号連結させ、文字列として格納しているテーブルも考えられると思います。(解析経験ありです。)

質問３．下段の案の中で、上記条件に対して「ありえない」と考えるものはありますか？

経験したことを踏まえると、案3でしょうか。予備カラムが無駄に思えて。。。

質問４．一般的にこういったもののデザインパターンはありますか？

質問1の回答と重複しますが、よく見かけます。

質問５．他、経験談や参考情報などありましたらご教示ください。

質問2の回答の補足ですが、テーブルを解析した際、ドキュメントありき・レコードによってカンマ数が違う等、とても難解でした。。。

なので挙げてはいますが、よほどでない限り採用はしません。

Comments

[mosa]

ありがとうございます。非常に参考になります。

> 案2に関しては、動的に一意となるテーブルを生成するということでしょうか。
はい、そうです。定義に合わせてプログラムが以下のようなテーブルを動的に作り、それに合わせてそれぞれの機械が対象のテーブルにデータを放り込んでいく、というイメージなのですが、実際にこういったシステムを私は見たことはありません。
CREATE TABLE `機械Ａ` (`id` INT, `機械ID` INT, `気温` DOUBLE, `湿度` DOUBLE, `気圧` DOUBLE, `日時` DATETIME);
CREATE TABLE `機械Ｂ` (`id` INT, `機械ID` INT, `電圧` DOUBLE, `電流` DOUBLE, `日時` DATETIME);

> テーブルを解析した際、ドキュメントありき・レコードによってカンマ数が違う等、とても難解でした。
運用保守だけをする側としては本当に大変ですね。いつか自分の設計も後任の担当が同じ目にあってしまうんじゃないか・・・、という不安からご質問させていただきました。

なので実績があるというお話を聞くとだいぶホッとしますｗ
続きを読む ∨