2024.09.30
チートシート（自分用）追記
目的
mosquittoで受信したデータをdb格納
msserver, mysql, sqliteどれを導入するかと迷ったが
今回はpython で手軽に実行できるので、まずsqliteを選定
＊どこまでできるか判断して、プロジェクトに応じ使用する。
現状
データ取り込みはOKだが、複数のデータを各時間あたりで処理するときの
データ競合など諸問題への対応に直面
chatgptと対話
対話詳細:複数の処理を安全に行うために
SQLiteで複数の処理を安全に行うためには、いくつかのベストプラクティスと適切な設定を行うことが重要です。以下に、SQLiteで安全かつ効率的に複数の処理を行うための方法を説明します。- トランザクションを使用する
トランザクションは、複数のSQL操作を1つの不可分な操作として実行するために使用されます。トランザクションを使用することで、データの整合性を確保しながら複数の操作を安全に行うことができます。
方法:
import sqlite3
conn = sqlite3.connect('example.db')
try:
conn.execute('BEGIN TRANSACTION;')
conn.execute('INSERT INTO table_name (column1, column2) VALUES (?, ?);', (value1, value2))
conn.execute('UPDATE table_name SET column1 = ? WHERE column2 = ?;', (new_value, condition))
conn.commit() # すべての操作が成功した場合のみコミット
except sqlite3.Error as e:
conn.rollback() # エラーが発生した場合、すべての変更を元に戻す
print(f"An error occurred: {e}")
finally:
conn.close()
- WAL（Write-Ahead Logging）モードの使用
WALモードは、デフォルトのジャーナルモード（DELETEモード）に代わるもので、並行読み書きが可能になります。これにより、同時に複数のスレッドやプロセスがデータベースを読み書きする場合にパフォーマンスが向上します。
設定方法:
conn = sqlite3.connect('example.db')
conn.execute('PRAGMA journal_mode=WAL;') # WALモードを有効にする
- 適切なロック管理
SQLiteのロック機構は、競合を最小限に抑えるために適切に管理する必要があります。特に、頻繁に書き込みが発生する場合には、長時間ロックを保持しないようにトランザクションを短く保つことが重要です。
推奨方法:
トランザクション内で行う操作はできるだけ少なく、短時間で完了するようにする。
長いクエリや複雑な計算は、トランザクションの外で行う。
4. インデックスの適切な使用
インデックスを使用することで、読み取り操作のパフォーマンスを向上させることができます。ただし、インデックスの作成や更新にはコストがかかるため、必要なインデックスのみを作成することが重要です。
方法:
CREATE INDEX idx_column_name ON table_name(column_name);
- スレッドやプロセスの安全性を確保する
SQLiteは、スレッドセーフではありますが、複数のスレッドが同時に同じコネクションを使用すると競合が発生する可能性があります。各スレッドごとに個別のコネクションを使用することをお勧めします。
推奨方法:
各スレッドまたはプロセスごとに新しいデータベースコネクションを開く。
スレッド間でコネクションを共有しない。
6. 適切なエラーハンドリング
データベース操作中にエラーが発生することは避けられないため、適切なエラーハンドリングを行うことが重要です。
ex)
try:
# データベース操作
except sqlite3.OperationalError as e:
# ロックタイムアウトなどのエラー処理
except sqlite3.IntegrityError as e:
# データ整合性エラーの処理
except sqlite3.Error as e:
# 一般的なエラー処理
- バッチ処理を使用する
多数のINSERTやUPDATE操作を行う場合、個々の操作を一度に実行するよりも、バッチ処理を使用して一括して実行する方が効率的です。これにより、トランザクションのオーバーヘッドを削減できます。
方法:
data = [(value1, value2), (value3, value4), ...]
conn.executemany('INSERT INTO table_name (column1, column2) VALUES (?, ?);', data)
conn.commit()
まとめ
SQLiteで複数の処理を安全に行うためには、トランザクションの使用、WALモードの活用、適切なロック管理、インデックスの最適化、スレッドの安全性確保、エラーハンドリング、バッチ処理の導入などが重要です。これらのベストプラクティスを実施することで、SQLiteのパフォーマンスと安全性を最大限に引き出すことができます。
対話詳細:データベース設計
1.データの可変性: すべての測定項目が常に存在するわけではなく、時々追加される場合。2.ストレージの効率性: ストレージ使用量を最小限に抑える必要があるかどうか。
3.パフォーマンス: データ挿入とクエリ実行の速度。
4.データの正規化: 冗長性を減らし、データの一貫性を保つために、データベースをどの程度正規化するか。
1. 1行にすべてのデータを格納する方法
利点
パフォーマンス: 1行にすべてのデータを格納することで、1回の挿入操作で全てのデータを保存できるため、データの挿入パフォーマンスが高い。
クエリの簡便性: データが1行に集約されているため、データを取得する際にシンプルなクエリが可能。
ストレージ効率: 列が多くても、使用していない列がNULLまたはデフォルト値（例: 0）で保存されるため、追加のストレージ消費は少ない。
欠点
無駄なデータ: 使われない列が多い場合、無駄なストレージが増える。
データの冗長性: 同じデータを複数回保存することがある場合、データの冗長性が増す可能性がある。
2. 測定ごとに複数行を追加する方法
利点
ストレージの効率: 使用するデータのみを格納するため、ストレージの効率が向上する。
データの可変性: 測定値の数やタイプが変更された場合でも、データベース設計を変更する必要がない。各測定は独立した行として保存できる。
欠点
クエリの複雑性: 必要なデータを取得するためのクエリが複雑になる可能性がある。特に、複数の測定を結合する必要がある場合。
パフォーマンス: 多くの測定値がある場合、データ挿入とクエリの実行が遅くなる可能性がある。
結論: 最適なアプローチ
プロフェッショナルなデータベース設計において、最適なアプローチは次のような要因に依存します。
データの一貫性と正確性が重要である場合、各測定値を個別の行として保存することを検討してください。これにより、データの正規化が保たれ、データの一貫性が向上します。
データ挿入パフォーマンスとクエリのシンプルさが重要であり、使用しない列が少ない場合は、1行にすべてのデータを格納する方法が適しています。これにより、データの挿入がシンプルで高速になります。
今回のケースの結論
まずはシンプルにデータを１行で格納してみることにした（シンプルに格納させる）
ここでどこで処理が限界を迎えるかなど、実環境でのテストを実行する。
（振り返り）自分の立ち位置について
Qiitaも少し投稿を続けて、現状を今一度振り返る。
今は社内DXを推進中（機械学習、AI、IoTなど各種開発）
現時点、機械学習とAIの学習コストが高くまだまだできていないことが多いが
これら取り組みを継続して進めていく。
またiotについては、mqtt,mosquittoを用いた各種センサ類のデータ取り込みは実装できているため次はDBへの格納、DAと展開する。
pythonについて知見も増え
生成AIを活用しながら、各種開発を推進できているので継続していく。
＊より効率的な開発になるように改善（ここはやはりプロンプト改善になっていくだろう）
SQLite コマンドチートシート（for myself)
データベースに関する基本コマンド
SQLite シェルを起動
sqlite3 your_database_name.db
データベースを終了
.exit
テーブル一覧を表示
.tables
テーブルのフィールド情報を確認
PRAGMA table_info(sensor_data);
SQLの実行結果を整形して表示
.mode column
データを操作する基本コマンド
テーブルの全データを表示
SELECT * FROM sensor_data;
テーブルの最初の10行を表示
SELECT * FROM sensor_data LIMIT 10;
特定のカラム（timestamp, category）のみを取得
SELECT timestamp, category FROM sensor_data;
特定の条件に一致するデータを取得 (category = 'temperature')
SELECT * FROM sensor_data WHERE category = 'temperature';
日付が特定の日 (timestamp が '2023-10-01') のデータを取得
SELECT * FROM sensor_data WHERE date(timestamp) = '2023-10-01';
最新のデータを取得
SELECT * FROM sensor_data ORDER BY timestamp DESC LIMIT 1;
最新2日間のデータを取得
SELECT * FROM sensor_data WHERE date(timestamp) >= date('now', '-2 days');
特定のIPアドレス（例: '192.168.1.100'）のデータを取得
SELECT * FROM sensor_data WHERE ip_address = '192.168.1.100';
measurement1 が 100.0 を超えるデータを取得
SELECT * FROM sensor_data WHERE measurement1 > 100.0;
category 別にデータをグループ化してカウント
SELECT category, COUNT(*) FROM sensor_data GROUP BY category;
measurement1 の平均値を取得
SELECT AVG(measurement1) FROM sensor_data;
measurement1 の最大値を取得
SELECT MAX(measurement1) FROM sensor_data;
measurement1 の最小値を取得
SELECT MIN(measurement1) FROM sensor_data;
category 別に measurement1 の合計を取得
SELECT category, SUM(measurement1) FROM sensor_data GROUP BY category;
テーブル内の全行数を取得
SELECT COUNT(*) FROM sensor_data;
テーブルやデータを操作するコマンド
テーブルに新しいデータを挿入
INSERT INTO sensor_data (timestamp, ip_address, category, measurement1) VALUES ('2024-10-01 12:00:00', '192.168.1.101', 'temperature', 25.3);
特定のデータを更新 (id = 1 の行を更新)
UPDATE sensor_data SET measurement1 = 30.5 WHERE id = 1;
特定のデータを削除 (id = 1 のデータを削除)
DELETE FROM sensor_data WHERE id = 1;
全てのデータを削除
DELETE FROM sensor_data;
テーブルを作成
CREATE TABLE sensor_data ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, ip_address TEXT NOT NULL, category TEXT NOT NULL, measurement1 REAL );
テーブルを削除
DROP TABLE sensor_data;
インデックスや最適化に関するコマンド
特定のカラムにインデックスを作成 (timestamp カラムにインデックスを作成)
CREATE INDEX idx_timestamp ON sensor_data (timestamp);
テーブルをバックアップ
.backup backup_database.db
データベースを圧縮（VACUUM）
VACUUM;
インデックスの一覧を確認
PRAGMA index_list(sensor_data);