LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pipito-yukio(吉田 幸雄)

psql メタコマンド\copy で複数テーブルを結合した結果をCSV出力する

Posted at

psqlのメタコマンド\copy で複数テーブルを結合した結果をCSV出力する

psql のメタコマンド \copy を使い複数テーブル (時系列テーブル含む) を結合し指定期間のクエリーを実行した結果をCSVに出力する方法を紹介します。

時系列データは Raspberry Pi 4 サーバーがデータベースに記録しているもので、それを定期的に開発用 PCのデータベースにインポートしています。

【データベースに記録しているデータ】

(1) センサーデバイス名テープルの内容
※現時点で id=1 のセンサーのみ。

 id |   name    |  description   
----+-----------+----------------
  1 | esp8266_1 | メインセンサー
  3 | esp8266_3 | 玄関(停止中)
  5 | esp8266_5 | 予備(未稼働)

(2) 気象データテーブルに記録している内容

 did |  measurement_time   | temp_out | temp_in | humid | pressure 
-----+---------------------+----------+---------+-------+----------
   1 | 2024-08-01 00:09:08 |     20.9 |    26.1 |  65.8 |   1002.9
   1 | 2024-08-01 00:18:52 |     20.9 |    26.1 |  65.7 |   1002.8
   1 | 2024-08-01 00:28:36 |     20.9 |    26.1 |  65.6 |   1002.8
   1 | 2024-08-01 00:38:21 |     20.7 |    26.1 |  65.5 |   1002.8
   1 | 2024-08-01 00:48:05 |     20.7 |    26.1 |  65.4 |   1002.9

【 CSV ファイル出力結果】

２つのテーブルを結合して以下のような形式のCSVファイルを出力します。

device_name,measurement_time,temp_out,temp_in,humid,pressure
"esp8266_1","2024-08-01 00:09:08",20.9,26.1,65.8,1002.9
"esp8266_1","2024-08-01 00:18:52",20.9,26.1,65.7,1002.8
"esp8266_1","2024-08-01 00:28:36",20.9,26.1,65.6,1002.8
"esp8266_1","2024-08-01 00:38:21",20.7,26.1,65.5,1002.8
"esp8266_1","2024-08-01 00:48:05",20.7,26.1,65.4,1002.9

この形式なら pandas 等のライブラリでの扱いが容易になると思います。

参考URL

下記ページのメタコマンド \copy の説明を元にスクリプトを作成しました。

実行環境

  • OS
    Ubuntu 22.04 Desktop
  • データベースサーバー
    Dockerコンテナ内で稼働するPostgreSQL 17
    ※軽量な alpine を採用

参考までに開発用 PCの docker リソースを以下に示します。
Alpine docker イメージについては下記日本語のドキュメントをご覧ください。
アルパインドッカー公式イメージの使い方
※記事は自動的に日本語翻訳したような内容になっています。

Dockerfile

FROM postgres:17-alpine

COPY initdb/*.sql /docker-entrypoint-initdb.d/

docker compose 定義

docker-compose.yml
services:
  postgres:
    build: .
    container_name: postgres-17
    ports:
      - "5432:5432"
    volumes:
      - "/home/yukio/database/postgresql-17/data:/var/lib/postgresql/data"
      - "/home/yukio/data/sql:/home/yukio/data/sql"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=xxxxx

1 データベース

参考までにデータベース生成クエリーとテーブル作成クエリーを示します。

1-1 データベース生成

initdb/10_createdb_sensors.sql
CREATE ROLE developer WITH LOGIN PASSWORD 'xxxxxxx';
ALTER ROLE developer WITH SUPERUSER;
CREATE DATABASE sensors_pgdb WITH OWNER=developer ENCODING='UTF-8' LC_COLLATE='ja_JP.UTF-8' LC_CTYPE='ja_JP.UTF-8' TEMPLATE=template0;
GRANT ALL PRIVILEGES ON DATABASE sensors_pgdb TO developer;

1-2 テーブル作成

先頭行 \connect sensors_pgdb が有ると 初回に Docker コンテナを起動したときデータベース(sensors_pgdb)が作成されたあとにデータベースの中にテーブルが作成されます。
※先頭行を忘れると postgres データベースの中にテーブルが作成されてしまいます。

initdb/11_weather_db.sql
\connect sensors_pgdb

CREATE SCHEMA IF NOT EXISTS weather;

-- 観測デバイス名テーブル
CREATE TABLE IF NOT EXISTS weather.t_device(
   id INTEGER NOT NULL,
   name VARCHAR(20) UNIQUE NOT NULL,
   description VARCHAR(128) NOT NULL,
   CONSTRAINT pk_device PRIMARY KEY (id)
);

-- 気象データテーブル
CREATE TABLE IF NOT EXISTS weather.t_weather(
   did INTEGER NOT NULL,
   measurement_time timestamp NOT NULL,
   temp_out REAL,
   temp_in REAL,
   humid REAL,
   pressure REAL
);

ALTER TABLE weather.t_weather ADD CONSTRAINT pk_weather PRIMARY KEY (did, measurement_time);
ALTER TABLE weather.t_weather ADD CONSTRAINT fk_device FOREIGN KEY (did) REFERENCES weather.t_device (id);

ALTER SCHEMA weather OWNER TO developer;
ALTER TABLE weather.t_device OWNER TO developer;
ALTER TABLE weather.t_weather OWNER TO developer;

2 シェルスクリプト作成

2-1 Dockerコンテナ内で実行するシェルスクリプト

２つのテーブルを結合し指定期間のデータを抽出しCSVファイルに出力します。
※このスクリプトはホストOS 側のシェルスクリプトから呼び出されます。

下記メタコマンドのオプション ( query ) の方に検索クエリーを定義します。

\copy { table [ ( column_list ) ] | ( query ) to ...

使用するオブションは以下の通りになります。
※ query を囲むカッコは必須です。

\copy ( query ) to 'filename' with option [, ...] )
2-1-1 クエリーの変数定義
  • ホストOS側から渡される引数
    • 検索開始日: from_date
      ※ISO8601形式 (YYYY-mm-dd)
    • 検索終了日: excule_to_date
      ※ 検索終了日 + 1日
  • 出力する項目
    • デバイス名: device_name
      ※ t_deviceテーブルの "name" 列
    • 測定時刻: measurement_time
    • 外気温: temp_out
    • 室内気温: temp_in
    • 室内湿度: humid
    • 気圧: pressure
query="(
SELECT
  td.name as device_name, 
  to_char(measurement_time,'YYYY-MM-DD HH24:MI:SS') as measurement_time,
  temp_out,temp_in,humid,pressure
FROM
  weather.t_weather tw INNER JOIN weather.t_device td ON tw.did = td.id
WHERE
  measurement_time >= to_timestamp('${from_date}', 'YYYY-MM-DD HH24::MI:SS')
  AND
  measurement_time < to_timestamp('${excule_to_date}', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY td.id, measurement_time)"
2-1-2 出力パス変数の定義
  • ホストOS側から渡される引数
    • ホストOSのホームディレクトリ: host_home
    • CSVファイル名に使う名前: name_suffix
      ※[年 | 年月]

出力ファイル名はシングルクォートで括ります。

export_csv="${host_home}/data/sql/weather/qiita/csv/output/t_weather_${name_suffix}.csv"
to_output="to '${export_csv}'"
2-1-3 オプション変数の定義

with 以降の項目

  • 区切り (カンマ区切り): delimiter ','
  • 出力形式: csv
  • ヘッダー有り: header
  • ダブルクオートで括る項目: force quote デバイス名,測定時刻
csv_option="with delimiter ',' csv header force quote device_name,measurement_time;"
2-1-4 psqlコマンド指定

処理ブロックごとに改行しておくと実行したクエリーが見やすくなります。
[1行でまとめる場合] -c "\copy ${query} ${to_output} ${csv_option}"

コマンドオプションとして最後に --echo-all をつけると実行したクエリーを出力します。
※ エラーがあった場合に問題切り分けがしやすくなります。

psql -Udeveloper -d sensors_pgdb \
 -c "\copy ${query} 
   ${to_output} 
   ${csv_option}" \
 --echo-all

シェルスクリプト全体を以下に示します。

export_query_csv_with_device_name.sh
#!/bin/bash

# dockerコンテナ内で実行されるシェルスクリプト
# 指定されたデバイス名の全ての気象データを指定されたカラムに従って CSV出力する
# [引数] ホストOSのホーム, 開始日, [翌月|翌年]の開始日(含まない), サフィックス(出力CSVに使う)
host_home=$1
from_date=$2
excule_to_date=$3
name_suffix=$4

# t_device: id,name,description
# t_weather: did, measurement_time, temp_out, temp_in, humid, pressure
# (出力) デバイス名(t_device.name), 測定時刻, 外気温, 室内気温, 室内湿度, 室内気圧
query="(
SELECT
  td.name as device_name, 
  to_char(measurement_time,'YYYY-MM-DD HH24:MI:SS') as measurement_time,
  temp_out,temp_in,humid,pressure
FROM
  weather.t_weather tw INNER JOIN weather.t_device td ON tw.did = td.id
WHERE
  measurement_time >= to_timestamp('${from_date}', 'YYYY-MM-DD HH24::MI:SS')
  AND
  measurement_time < to_timestamp('${excule_to_date}', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY td.id, measurement_time)"

export_csv="${host_home}/data/sql/weather/qiita/csv/output/t_weather_${name_suffix}.csv"
to_output="to '${export_csv}'"
csv_option="with delimiter ',' csv header force quote device_name,measurement_time;"

# エクスポート実行 (--echo-all: 実行スクリプトの出力) 
psql -Udeveloper -d sensors_pgdb \
 -c "\copy ${query} 
   ${to_output} 
   ${csv_option}" \
 --echo-all
exit1=$?
if [ $exit1 -ne 0 ]; then
   echo "Fail export to ${export_csv}!!"
   exit $exit1
fi
echo "Exported to ${export_csv}."

2-2 ホストOS内で実行するシェルスクリプト

いちいち Dockerコンテナ環境に入ってシェルスクリプトを実行するのは大変です。ホストOS側のシェルスクリプトからコンテナ側のシェルスクリプト呼び出したほうが簡単です。

ホスト OS 側のシェルスクリプトで検索期間の終了日 (含まない) を計算します。

$ date -d '2024-12-01 1 month' '+%F'
2025-01-01

【理由】Alpine OS の date コマンドでは -d オプションによる日付の加減算ができない。

79ee39c37e02:# date -d '2024-12-01 1 month' '+%F'
date: invalid date '2024-12-01 1 month'

※1 Alpine OS のコマンドは Busybox ベースなので Ubuntu OS (又は Debian OS) で使えたオプションが使えないことも多いことには注意が必要です。
※2 まあ Docker コンテナ内で複雑なコマンドを実行することは少ないと思いますが:sweat:

引数にコンテナ名が必要か?

Raspberry Pi 端末側のコンテナ名と開発用PC側のコンテナ名が異なるため最初の引数として指定することにしました。
この引数の導入でコンテナ側シェルスクリプト、ホストOS側シェルスクリプトともに Raspberry Pi 端末側の OS (RaspberryPi OS bullseye [debian]) でも動作するようなっています。

2-2-1 月間データのCSVを出力するシェルスクリプト
  • スクリプト引数
    • Docker コンテナ名: $1
    • 対象年月: $2
      [形式] YYYY-mm (例) 2024-08
  • 検索期間の計算
    • 開始日 (年月 + 1日)
      start_date="${2}-01"
    • 終了日 (翌月の1日)
      excule_to_date=$(date -d "${start_date} 1 month" +'%F')
  • docker exec コマンド
    実行中のコンテナ内で指定したスクリプトを実行します。
    docker exec -it [コンテナ名] sh -c "[コンテナ側スクリプト名] 引数,,.."
dkr_export_t_weather_csv_with_yearmonth.sh
#!/bin/bash

# 指定された年月の気象観測データをCSV出力する
# 引数: コンテナ名, 対象年月
container_name=$1
start_date="${2}-01"
excule_to_date=$(date -d "${start_date} 1 month" +'%F')
echo "$start_date =< date_range < $excule_to_date"

# ホスト側からホストホーム, 対象年月の開始日, 翌月の開始日をコンテナ内のスクリプトに引き渡す
# コンテナ側で実行するスクリプトパス
script_name="export_query_csv_with_device_name.sh"
scrpit_path="$HOME/data/sql/weather/qiita/${script_name}"
docker exec -it ${container_name} sh \
 -c "${scrpit_path} ${HOME} ${start_date} ${excule_to_date} ${2}"
2-2-2 年間データのCSVを出力するシェルスクリプト
  • スクリプト引数
    • Docker コンテナ名: $1
    • 対象年: $2
      [形式] YYYY (例) 2024
  • 検索期間の計算
    • 開始日 (年 + 1月1日)
      start_date="${2}-01-01"
    • 終了日 (翌年の 1月1日)
      excule_to_date=$(date -d "${start_date} 1 year" +'%F')
  • docker exec コマンド
    項番 [3-2-1] と同じ
dkr_export_t_weather_csv_with_year.sh
#!/bin/bash

# 指定された年の気象観測データをCSV出力する
# 引数: コンテナ名, 対象年
container_name=$1
start_date="${2}-01-01"
excule_to_date=$(date -d "${start_date} 1 year" +'%F')
echo "$start_date =< date_range < $excule_to_date"

# ホスト側からホストホーム, デバイス名, 年の開始日, 次の月の開始日をコンテナ内のスクリプトに引き渡す
script_name="export_query_csv_with_device_name.sh"
scrpit_path="${HOME}/data/sql/weather/${script_name}"
docker exec -it ${container_name} sh \
 -c "${scrpit_path} ${HOME} ${device_name} ${start_date} ${excule_to_date} ${2}"

3 シェルスクリプトの実行

3-1 月間データのCSV出力

(1) ホストOSのターミナルから実行

~/data/sql/weather/qiita$ ./dkr_export_t_weather_csv_with_yearmonth.sh \
 postgres-17 2024-08
2024-08-01 =< date_range < 2024-09-01
copy (
SELECT
  td.name as device_name, 
  to_char(measurement_time,'YYYY-MM-DD HH24:MI:SS') as measurement_time,
  temp_out,temp_in,humid,pressure
FROM
  weather.t_weather tw INNER JOIN weather.t_device td ON tw.did = td.id
WHERE
  measurement_time >= to_timestamp('2024-08-01', 'YYYY-MM-DD HH24::MI:SS')
  AND
  measurement_time < to_timestamp('2024-09-01', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY td.id, measurement_time) 
   to '/home/yukio/data/sql/weather/qiita/csv/output/t_weather_2024-08.csv' 
   with delimiter ',' csv header force quote device_name,measurement_time;
COPY 4583
Exported to /home/yukio/data/sql/weather/qiita/csv/output/t_weather_2024-08.csv.

(2) 出力内容確認

~/data/sql/weather/qiita/csv/output$ head -n 5 t_weather_2024-08.csv 
device_name,measurement_time,temp_out,temp_in,humid,pressure
"esp8266_1","2024-08-01 00:09:08",20.9,26.1,65.8,1002.9
"esp8266_1","2024-08-01 00:18:52",20.9,26.1,65.7,1002.8
"esp8266_1","2024-08-01 00:28:36",20.9,26.1,65.6,1002.8
"esp8266_1","2024-08-01 00:38:21",20.7,26.1,65.5,1002.8
~/data/sql/weather/qiita/csv/output$ tail -n 5 t_weather_2024-08.csv 
"esp8266_1","2024-08-31 23:13:49",21.6,25.9,58.4,999.2
"esp8266_1","2024-08-31 23:23:33",21.4,25.9,57.9,999.4
"esp8266_1","2024-08-31 23:33:19",21.2,26,58.2,999.4
"esp8266_1","2024-08-31 23:43:02",21.2,25.9,58.3,999.6
"esp8266_1","2024-08-31 23:52:46",21,25.9,58.6,999.5

3-2 年間データのCSV出力

(1) ホストOSのターミナルから実行

~/data/sql/weather/qiita$ ./dkr_export_t_weather_csv_with_year.sh \
 postgres-17 2024
2024-01-01 =< date_range < 2025-01-01
copy (
SELECT
  td.name as device_name, 
  to_char(measurement_time,'YYYY-MM-DD HH24:MI:SS') as measurement_time,
  temp_out,temp_in,humid,pressure
FROM
  weather.t_weather tw INNER JOIN weather.t_device td ON tw.did = td.id
WHEREホストOSのターミナルから実行
  measurement_time >= to_timestamp('2024-01-01', 'YYYY-MM-DD HH24::MI:SS')
  AND
  measurement_time < to_timestamp('2025-01-01', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY td.id, measurement_time) 
   to '/home/yukio/data/sql/weather/qiita/csv/output/t_weather_2024.csv' 
   with delimiter ',' csv header force quote device_name,measurement_time;
COPY 54093
Exported to /home/yukio/data/sql/weather/qiita/csv/output/t_weather_2024.csv.

(2) 出力内容確認

~/data/sql/weather/qiita/csv/output$ head -n 5 t_weather_2024.csv 
device_name,measurement_time,temp_out,temp_in,humid,pressure
"esp8266_1","2024-01-01 00:00:51",-2.9,14,51.7,1009.6
"esp8266_1","2024-01-01 00:10:35",-2.7,13.8,51.7,1009.8
"esp8266_1","2024-01-01 00:20:19",-2.8,13.6,51.8,1009.9
"esp8266_1","2024-01-01 00:30:02",-3,13.4,51.4,1010.1
~/data/sql/weather/qiita/csv/output$ tail -n 5 t_weather_2024.csv 
"esp8266_1","2024-12-31 23:11:41",-3.3,12.9,42.9,1001.9
"esp8266_1","2024-12-31 23:21:24",-3.3,12.7,42.9,1002
"esp8266_1","2024-12-31 23:31:08",-3.3,12.4,43,1002.1
"esp8266_1","2024-12-31 23:40:52",-3.4,12.2,42.9,1002.1
"esp8266_1","2024-12-31 23:50:35",-3.5,12,42.7,1002.4

最後に

時系列データを扱う場合、月単位、又は年単位でデータを CSVファイルに出力することは結構有ると思います。

もし 日単位の CSVファイルが必要なら以下のように日付の計算を変更するだけで済みます。

# 指定日の気象観測データをCSV出力する
# 引数: コンテナ名, 対象日
container_name=$1
start_date=$2
excule_to_date=$(date -d "${start_date} 1 day" +'%F')

Qiita 内投稿記事、Google 検索で何件かこの手の記事を探しましたがどれもリファレンス的ものが多く、今回のような実用的な例を扱ったものが見当たらなかったのでこの記事を投稿するとことにしました。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?