Docker | Dockerコンテナを作成しよう（TimescaleDB）

みなさん、こんにちは！

今回はIoTデータの蓄積で注目されている「TimescaleDB」のコンテナを取り扱ってみます。

Dockerコンテナ生成までの流れ

以下に手順を整理します。

１）Docker HubからDocker ImageをPullする

DockerHubからDocker ImageをPullします。

→　TimescaleDBのページはこちら

※公式ドキュメントでもやり方が公開されています。

docker pull timescale/timescaledb-ha:pg14-latest

２）Docker Imageを起動する

コンテナをまだ作成していない場合は、以下のコマンドです。

※ 今回はルートパスワードを「passw0rd 」としています。

docker run -d --name miseruIT_timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=passw0rd timescale/timescaledb-ha:pg14-latest

３）接続確認

デフォルトユーザは「postgres」です。

試しに使ってみる

以下のサンプルデータを取り込んで、いろいろと触ってみます。

気象庁：羽田（東京都)　2023年1月1日（１０分ごとの値）

www.data.jma.go.jp

気象庁｜過去の気象データ検索

https://www.data.jma.go.jp/obd/stats/etrn/view/10min_a1.php?prec_no=44&block_no=0371&year=2023&month=1&day=1&view=

気象庁｜過去の気象データ検索

１）時間と温度データの作成

以下のようにデータを作成しました。

２）取り込み用のテーブル作成

以下のクエリを実行し、データ取り込み用のテーブルを作成します。

create table temp( time timestamp,temp float )

３）CSVデータの取り込み

以下の記事に基づいてCSVデータを取り込みます。（A5SQLを用いてインポートするのが楽です）

魅せるIT編集部

2023.09.08

データベース | データベースにCSVデータをインポートする（PostgreSQL、SQL Serve...

https://miseruit.com/2023/09/08/post-5600

みなさん、こんにちは。今回は仕事でもよく利用する CSVデータの一括挿入を検証します。テストデータ以下のテストデータを利用します。ChatGPTに作ってもらいました。employeesダウンロード下図のように、社員番号、社員名が入っています。インポート先のテーブルインポート先のテーブルは以下のように定義します。Create table employees( id varchar(5) NOT NULL PRIMARY KEY, name varchar(50))PostgreSQLの場合PostgreSQLの場合は、「COPY」を用います。SQLサーバ内のtmpフォルダの直下に「employees.csv」を置いた場合、以...

４）集計クエリを実行してみる

データが準備できましたので、集計を行ってみます。

① 1時間毎に気温データをまるめる

現在10分毎のデータとなっているので、1時間毎にまるめてみます。

time_bucket関数を用いることで、簡単にまるめ処理が行えます。

SELECT
    time_bucket ('1 hour', time)  AS bucket,
    avg(temp) as avg_temp
FROM temp
Group by bucket
ORDER BY bucket ASC ; 

2時間毎でもまるめることができます。

SELECT
    time_bucket ('2 hour', time)  AS bucket,
    avg(temp) as avg_temp
FROM temp
Group by bucket
ORDER BY bucket ASC ; 

ちなみに、PostgreSQLには date_trunc関数がありますが、こちらは「1時間」や「1日」など固定時間のみ丸めが可能で、2時間とかは設定できません。

TimescaleDBを用いる場合は「time_bucket」を用いることを覚えておくと良いですね。

②値を補完する

例えば、8時～9時30分のデータをDeleteしてみます。

delete from temp where time between '2023/01/01 08:00' and '2023/01/01 09:30'

この状態で1時間ごとにデータをまるめると、以下のように8時のデータが無くなってしまいます。

間が抜けてしまったデータを、NULLで補完するには、「time_bucket_gapfill」関数を用いて以下のように記載します。

SELECT
    time_bucket_gapfill('1 hour', time)  AS bucket,
    avg(temp) as avg_temp
FROM temp

WHERE time >= '2023-01-01 00:00:00' AND time < '2023-01-01 23:59:59'
Group by bucket
ORDER BY bucket ASC ; 

今回は以上です。

ではまた！

エラー: データの取得に失敗しました。