Raspi zeroからダウンロードした気象データCSVをDockerコンテナ内のPostgreSQLにインポートするシェルスクリプトの作成方法を紹介します。

前回は下記記事にて、SQLite3データベースからCSVファイル保存するシェルスクリプトを作成する方法を紹介いたしました。
(Qiita) SQLite3データベースのレコードをCSVファイル保存するシェルスクリプトを作る

今回の記事は、下記システム概要中のCSV(ピンク色)をDockerコンテナ内のPostgreSQLにインポートするシェルスクリプトになります。

想定するスクリプトの実行環境

OS: Debian GNU/Linux (Raspberry Pi 4 Model B) または LinuxPC機
docker-composeがインストール済み
気象データベース(PostgreSQL)
- Raspberry Pi 4 Model B
  ※1 本番環境の気象データベースがDockerコンテナ内で稼働していること
  ※2 UDPパケットモニターサービスが稼働し常に気象データは最新状態
  　但しアプリインストール、定期メンテナンス等の理由でSDカードをバックアップするため一時的に稼働を停止することがあり、その時にCSVのインポートが必要になる。
- LinuxPC機
  ※開発用の気象データベースがDockerコンテナ内で稼働していること
  ※手作業でCSVをインポートしない限り気象データは古い状態のまま

スクリプトを作成した環境

OS: Ubuntu 22.04.3 LTS
上記実行環境の開発PC機の条件
※Raspberry Pi 4 (本番機) もDockerコンテナ等の環境が開発PCと同一構成にしているため、作成したシェルスクリプトは修正無しでそのまま実行可能となる

1. docker-composeファイル構成

1-A. Raspberry pi 4 (本番機)

[格納パス] /home/pi/docker/postgres

pi@raspi-4:~/docker $ tree -a postgres/
postgres/
├── .env
├── Dockerfile
├── docker-compose.yml
└── initdb
    ├── 01_create_weather.sh
    ├── 10_createdb_sensors.sql
    └── 11_weather_db.sql

1-B. Dell-T7500 (LinuxPC)

[格納パス] /home/yukio/docker/SynologyNAS/compose/postgresql/12

yukio@Dell-T7500:~/docker/SynologyNAS/compose/postgresql$ tree -a 12
12
├── .env
├── Dockerfile
├── docker-compose.yml
└── initdb
    ├── 01_create_weather.sh
    ├── 10_createdb_sensors.sql
    └── 11_weather_db.sql

1-2.Docker関連リソース

※Raspberry pi 4 (本番機)とDell-T7500 (Linux)で .envファイルを除き同一内容です

1-2-1 Dockerfile

FROM postgres:12-alpine

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

1-2-2. docker-compose.yml

実行環境により異なるボリューム名・ホストホームの値は .env ファイルで変数として定義します
下記 HOST_PG_VOLUME, HOST_HOME, CONTAINER_HOME

version: '3'
services:
  postgres:
    build: .
    container_name: postgres-12
    env_file: ./.env
    ports:
      - "5432:5432"
    volumes:
      - "${HOST_PG_VOLUME}/postgresql-12/data:/var/lib/postgresql/data"
      - "${HOST_HOME}/data/sql:${CONTAINER_HOME}/data/sql"
    environment:
      - POSTGRES_USER=${PG_USER}
      - POSTGRES_PASSWORD=${PG_PASSWD}

1-2-3. 環境固有ファイル (.env)

(A) Raspberry pi 4 (本番機)

ボリューム (volumes)
[ホスト] /home/pi/db/postgresql-12/data [コンテナ] /var/lib/postgresql/data

HOST_PG_VOLUME=/home/pi/db
PG_USER=postgres
PG_PASSWD=yourpassword

# display locale
LANG=C

# locale
LANGUAGE=ja_JP:ja
LC_ALL=ja_JP.UTF-8
# Time zone
TZ=Asia/Tokyo

# volume mount
HOST_HOME=/home/pi
CONTAINER_HOME=/home/pi

(B) Dell-T7500 (Linux)
※同一部分は割愛し差分のみ掲載します

ボリューム (volumes)
[ホスト] /mnt/nas_databases/postgresql-12/data [コンテナ] /var/lib/postgresql/data

# SynologyNAS: /volume1/Databases/postgres-12
# Host Nfs: /mnt/nas_databases <- SynologyNAS:/volume1/Databases
HOST_PG_VOLUME=/mnt/nas_databases
# ...省略...
# volume mount
HOST_HOME=/home/yukio
CONTAINER_HOME=/home/yukio

1-3.initdbデイレクトリ配下のリソース

Raspberry pi 4 (本番機)とDell-T7500 (Linux)で同一内容です

(1) データベース作成スクリプト (10_createdb_sensors.sql)

CREATE ROLE developer WITH LOGIN PASSWORD 'yourpasswd';
--install pgcrypto that is required superuser. 
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;

(2) 気象データテーブル定義スクリプト (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) UNIQUE 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. SQLスクリプトファイル構成

ファイル構成は Rasberry Pi 4もLinuxPCの同一構成になっています ※$HOMEのみが異なる

(A) Rasberry Pi 4 (~/data/sql/weather)

pi@raspi-4:~ $ tree data
data
└── sql
    └── weather
        ├── csv
        │   └── t_weather.csv
        └── import_weather_csv.sh

(B) 開発PC (~/data/sql/weather)

yukio@Dell-T7500:~/data/sql$ tree weather/
weather/
├── csv
│   └── t_weather.csv
└── import_t_weather_csv.sh

3. CSVインポートシェルスクリプト作成

日本語訳されたPostgreSQLのドキュメント

インポートSQLはCSVへの絶対パスを指定します
(A) Raspberry pi 4の場合: /home/pi

-- インポートSQL
COPY weather.t_weather FROM '/home/pi/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;

(B) 開発PCの場合: /home/yukio

-- インポートSQL
COPY weather.t_weather FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;

3-1. Dockerコンテナ内でコマンドラインで実行

dockerコマンドの詳細については日本語訳のDocker-docs-ja コマンドリファレンスを参照

Docs >> コマンドラインリファレンス >> Docker CLI(docker) >> docker exec

3-1-1. docker-compose で Dockerコンテナを作成する

$ docker-compose up -d
Starting postgres-12 ... done

3-1-2. Dockerコンテナ環境に入る

$ docker exec -it postgres-12 bin/bash
19154fac2b2f:/#

上記のインポートSQLをパイプライン経由で対話型インターフェースpsqlに実行させます

19154fac2b2f:/# cd /home/yukio/data/sql/weather/
19154fac2b2f:/home/yukio/data/sql/weather# cat csv/t_weather.csv 
"did","measurement_time","temp_out","temp_in","humid","pressure"
1,"2023-11-10 13:29:53",9.0,17.5,67.2,1006.1
1,"2023-11-10 13:39:37",9.1,17.5,66.9,1005.8
19154fac2b2f:/home/yukio/data/sql/weather#
19154fac2b2f:/home/yukio/data/sql/weather# echo "COPY weather.t_weather FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;" | psql -Udeveloper sensors_pgdb
COPY 2
19154fac2b2f:/home/yukio/data/sql/weather# echo "SELECT max(measurement_time) FROM weather.t_weather WHERE did=(SELECT id FROM weather.t_device WHERE name='esp8266_1');" | psql -Udeveloper sensors_pgdb --tuples-only
 2023-11-10 13:39:37

コマンドラインから実行するのは大変です。
以下ではコンテナ内で実行可能なシェルスクリプトを作成していきます。

3-2. Dockerコンテナ内で実行可能なシェルスクリプト

予め下記のような最終レコードを確認するシェルスクリプトを作成します。

#!/bin/bash
# ./last_weather.sh esp8266_1

cat<<-EOF | psql -Udeveloper sensors_pgdb --tuples-only
SELECT
   max(measurement_time)
FROM 
   weather.t_weather
WHERE
  did=(SELECT id FROM weather.t_device WHERE name='${1}')
EOF

3-2-1. ホスト上でシェルスクリプトを作成する

別の新しいコンソールを開きシェルスクリプトファイルを作成し実行権限を付与
※あとは gedit か vim で編集します。

$ touch import_t_weather_csv.sh
$ chmod +x import_t_weather_csv.sh 
$ ls -l import_t_weather_csv.sh
-rwxrwxr-x 1 yukio yukio   0 11月 11 17:43 import_t_weather_csv.sh

3-2-1 (1) echoコマンドをヒアドキュメントに置き換える

#!/bin/bash

# 気象データCSVを weather.t_weaterテーブルにインポート
# https://www.postgresql.jp/docs/12/app-psql.html
# PostgreSQL 12.4文書 - PostgreSQLクライアントアプリケーション - psql
# --echo-all をつけると実行したSQLをコンソールに出力できます
cat<<-EOF | psql -Udeveloper sensors_pgdb --echo-all
COPY weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
EOF

■ コンテナ環境で実行 ※コンテナ上のホームは /root になります

19154fac2b2f:/home/yukio/data/sql/weather# ./last_t_weather.sh esp8266_1
 2023-11-10 13:39:37

19154fac2b2f:/home/yukio/data/sql/weather# cat csv/t_weather.csv 
"did","measurement_time","temp_out","temp_in","humid","pressure"
1,"2023-11-10 13:49:21",8.6,17.5,66.5,1006.1
1,"2023-11-10 13:59:05",8.7,17.5,66.4,1006.0
19154fac2b2f:/home/yukio/data/sql/weather#
19154fac2b2f:/home/yukio/data/sql/weather# ./import_t_weather_csv.sh 
COPY weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 2

3-2-1 (2) 絶対パスの先頭のユーザーホームをスクリプト引数に置き換える

ユーザーホームが異なる環境でも実行可能になり可搬性が増します
※1 [変更箇所] /home/yukio ⇒ ${1}
※2 SQL部分のみを記載します。

COPY weather.t_weather
 FROM '${1}/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;

■ コンテナ環境で実行 ※${1}が /home/yukioに置き換わっているのがわかります

19154fac2b2f:/home/yukio/data/sql/weather# ./last_t_weather.sh esp8266_1
 2023-11-10 13:59:05

19154fac2b2f:/home/yukio/data/sql/weather# cat csv/t_weather.csv 
"did","measurement_time","temp_out","temp_in","humid","pressure"
1,"2023-11-10 14:08:51",8.7,17.5,66.1,1006.1
1,"2023-11-10 14:18:33",8.6,17.5,65.8,1006.3
19154fac2b2f:/home/yukio/data/sql/weather# ./import_t_weather_csv.sh /home/yukio
COPY weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 2

3-2-2 制約を解除したインポートスクリプトを作成する

CSVの件数が少量の場合 3-2-1 で示したインポートスクリプトで十分ですが、数十万件以上あった場合に相当な処理時間がかかることが予想されます。
※1 大規模なデータ移行では有りうる件数です。
※2 このようなケースでは一旦制約を解除してからインポートするように作成したスクリプトを実際に実行した経験ことがあります。
※3 この方法が適用できるのは一般的に導入時です。制約を解除しているので運用中にインポートするCSVファイルに登録済みレコードが含まれると二重登録になりデータベースの不整合が発生します。

新しくもう一つスクリプトを作成します (import_t_weather_csv_drop_constaint.sh)
※制約に付きましては 1-3 (3) 気象データテーブル定義スクリプトをご覧ください。

#!/bin/bash

# ./import_t_weather_csv_drop_constaint.sh [ユーザーホームディレクトリ]

# 気象データCSVを weather.t_weaterテーブルにインポート
# PK制約をドロップ
cat<<-EOF | psql -Udeveloper sensors_pgdb 
ALTER TABLE weather.t_weather DROP CONSTRAINT pk_weather;
ALTER TABLE weather.t_weather DROP CONSTRAINT fk_device;
EOF

sleep 1

# データインポート
cat<<-EOF | psql -Udeveloper sensors_pgdb --echo-all
copy weather.t_weather
 FROM '${1}/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
EOF

sleep 2

# PK制約を戻す
cat<<-EOF | psql -Udeveloper sensors_pgdb
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);
EOF

■ コンテナ環境で実行 ※ホストのシェルスクリプトから実行した結果を後ほど掲載します

3-3. ホストの環境から実行可能なシェルスクリプトの作成

上記 3-2 で示したDocker環境内で実行する方法は、いちいち環境に入るコマンドを実行しさらにその環境内でインポートスクリプトを実行せねばならず管理者に負担をかけることになります。

ここからはホスト側からDocker環境内のシェルスクリプトを直接実行できるシェルスクリプトを作成します。

ホストのシェルスクリプトからDockerコンテナ内のシェルスクリプトを実行するヒントは、上記 Docker-docs-ja ドキュメントの docker exec の補足説明にあります。

以下ヒントになった部分のみ抜粋します。

補足説明
...省略...
コマンドは実行可能な状態である必要があり、連結するコマンドや、クォートしたコマンドは動作しません。
例えば、 docker exec -ti my_container "echo a && echo b" は動作しませんが、 
docker exec -ti my_container sh -c "echo a && echo b" は動作します。

上記では下記のように実行すれば良いように解釈されますがちょっとサンプルが良くないですね。
※ sh -c "...." 部分が答えのようですが。

$ docker exec -ti my_container sh -c "echo a && echo b"

3-3-1. ホストのコマンドラインから実行する

ホスト上でdockerコマンドが実行できるか確認します。

$ docker --version
Docker version 24.0.7, build afdd53b

上記の "...."部分 を、コンテナ内のシェルスクリプトに置き換えして実行

$ docker exec -it postgres-12 sh -c "/home/yukio/data/sql/weather/import_t_weather_csv.sh /home/yukio"
copy weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 2

うまく実行されました。コマンド実行部分をシェルスクリプトに置き換えします。

3-3-2. ホストから実行できるシェルスクリプトを作成する

(1) ユーザーホームのbinディレクトリにシェルスクリプトファイルを作成します。

$ touch docker_import_weather_csv.sh
$ chmod +x docker_import_weather_csv.sh

(2) シェルスクリプトの内容は以下のとおりです

"${HOME}" 部分がポイントです。
※これで Raspbery Pi 4では /home/pi に、LinuxPCでは /home/yukio に置換されます。

#!/bin/bash

# ./docker_import_weather_csv.sh

docker exec -it postgres-12 sh -c "${HOME}/data/sql/weather/import_t_weather_csv.sh ${HOME}"

■ ホストで実行 ※エラーなく実行できました

$ ./docker_import_weather_cs.sh 
copy weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 55

3-3-3. 初期データ移行も実行可能なシェルスクリプトを作成する

コマンドライン引数の有無により、通常スクリプト実行か制約解除してからのスクリプト実行を切り替えることができます。

新たなシェルスクリプトファイルを作成します(~/bin/dkr_import_weather.sh)

引数を指定しない場合: 通常のスクリプトが実行されます
引数 --drop-constraintを指定した場合: 制約解除スクリプトが実行されます

#!/bin/bash

readonly SCRIPT_NAME=${0##*/}

print_error()
{
   cat << END 1>&2
$SCRIPT_NAME: $1
Try --help option
END
}

print_help()
{
   cat << END
Usage: $SCRIP_NAME OPTIONS
Execute OPTIONS

--drop-constraint Optional: CSV import with DROP CONSTRAINT (*) over 1000 record.  
--help	display this help and exit

Example:
  $SCRIPT_NAME
  $SCRIPT_NAME --drop-constraint
END
}

params=$(getopt -n "$SCRIPT_NAME" \
       -o "" \
       -l drop-constraint -l help \
         -- "$@")

# Check command exit status
if [[ $? -ne 0 ]]; then
  echo 'Try --help option for more information' 1>&2
  exit 1
fi
eval set -- "$params"

# init option value
drop_constraint=

# Positional parameter count: $#
while [[ $# -gt 0 ]]
do
  case "$1" in
    --drop-constraint)
      drop_constraint=true
      shift
      ;;
    --help)
      print_help
      exit 0
      ;;
    --)
      shift
      break
      ;;
  esac
done

echo "drop_constraint: ${drop_constraint}"
if [[ ${drop_constraint} == true ]]; then
  docker exec -it postgres-12 sh -c "${HOME}/data/sql/weather/import_t_weather_csv_drop_constaint.sh ${HOME}"
else
  docker exec -it postgres-12 sh -c "${HOME}/data/sql/weather/import_t_weather_csv.sh ${HOME}"
fi

■ (1) 通常インポート実行

$ ./dkr_import_weather.sh 
drop_constraint: 
copy weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 148

■ (2) 制約を解除したインポート実行

$ ./dkr_import_weather.sh --drop-constraint
drop_constraint: true
ALTER TABLE
ALTER TABLE
copy weather.t_weather
 FROM '/home/yukio/data/sql/weather/csv/t_weather.csv' DELIMITER ',' CSV HEADER;
COPY 2366
ALTER TABLE
ALTER TABLE

4. 結論

今回は Linux(bash) 環境でDockerコンテナ上のスクリプトを実行できる可搬性のあるシェルスクリプトの作成方法を紹介いたしました。

制約を解除する方法は実際に大きな現場で経験を積まないと作成できないと思いますし、運用中に実行するには危険を伴います。

自分が経験した限りにおいては多くの場合、既存のシステム改良に伴うデータ移行のテーブル生成ではALTER文で制約を付与するケースがほとんどでした。そうしなければ大量のテータの移行は困難なものになります。

今回作成したシェルスクリプトはこの記事用により最適化したものです。
※Raspbrry Pi 4 (本番機) でのシェルスクリプトの実行については、現在運用中ですので次の記事にて実行結果を投稿する予定です。

下記スクリプトの作成で参考にしたサイト
stack overflow >> Script to automat import of CSV into PostgreSQL

Raspberry Pi 4 (本番機)で実際に使用したスクリプトは下記のとおりで、アプリケーションのインストーラーから下記のスクリプトを実行するようにしています。

#!/bin/bash

# https://stackoverflow.com/questions/34736762/script-to-automat-import-of-csv-into-postgresql
#   Script to automat import of CSV into PostgreSQL

psql -Udeveloper -d sensors_pgdb -c "ALTER TABLE weather.t_weather DROP CONSTRAINT pk_weather;"
psql -Udeveloper -d sensors_pgdb -c "ALTER TABLE weather.t_weather DROP CONSTRAINT fk_device;"

# t_device.csv into t_device table
psql -Udeveloper -d sensors_pgdb -c "\copy weather.t_device FROM '/home/pi/data/sql/csv/device.csv' DELIMITER ',' CSV HEADER;"
# t_weather.csv into t_weather table
psql -Udeveloper -d sensors_pgdb -c "\copy weather.t_weather FROM '/home/pi/data/sql/csv/weather.csv' DELIMITER ',' CSV HEADER;"

# Rebuild constraint.
psql -Udeveloper -d sensors_pgdb -c "ALTER TABLE weather.t_weather ADD CONSTRAINT pk_weather PRIMARY KEY (did, measurement_time);"
psql -Udeveloper -d sensors_pgdb -c "ALTER TABLE weather.t_weather ADD CONSTRAINT fk_device FOREIGN KEY (did) REFERENCES weather.t_device (id);"

気象データ表示システムの移行時にインポートした件数は41,095件で285日分のデータでした。

上記システムの概要とソースコードは下記GitHubリポジトリでご覧になれます
GitHub(pipito-yukio) UDP Weather Sensor packet monitor for Raspberry pi 4