Zenn
🔥

【PostgreSQL】created_atを期間で分割してCSV出力する

2023/04/13に公開
PostgreSQL
Bash
macOS
CSV
tech

概要

クエリの結果が重い時、created_atなどの期間を絞り、期間ごとにCSVを出力する方法です

環境

M1 Mac

スクリプトの構成

データベース接続情報

まず、データベース接続情報を設定します。以下の変数に適切な値を入力してください。

db_host="db_host"
db_port=5432
db_name="db_name"
db_user="db_user"
db_password="db_password"

SSH接続情報

必要であれば、次に、SSH接続情報を設定します。以下の変数に適切な値を入力してください。

ssh_stepping="ssh_stepping"

# .sshを使用しない場合
ssh_user="your_ssh_user"
ssh_host="your_ssh_host"
private_key_path="path/to/your/private_key"

ポートフォワード開始

ポートフォワードを開始します。

local_port=15432
echo "Starting port forwarding..."

ssh_command="ssh -f -N -L ${local_port}:${db_host}:${db_port} ${ssh_stepping}"

eval "${ssh_command}"
echo "${ssh_command}"

echo "Port forwarding started."

期間指定

処理する期間を指定します。以下の変数に適切な値を入力してください。
例では、2日間にしてます。

num_days=2

1日ずつ処理

指定された期間のデータを1日ずつ処理します。

# 現在の日付を取得
current_date=$(date +"%Y-%m-%d")

# 1日ずつ処理
for ((i = 1; i <= num_days; i++)); do
　# GNU版のdateを使用する
  start_date=$(gdate -d "$current_date - ${i} days" +"%Y-%m-%d")
  end_date=$(gdate -d "$start_date + 1 day" +"%Y-%m-%d")

  echo "Start date: ${start_date}"
  echo "End date: ${end_date}"
  
  # csvのファイル名
  csv_file="${current_date}_${i}.csv"
  
  # パラメータ化されたSQLクエリ
  query="COPY (
    SELECT *
    FROM your_table
    WHERE created_at BETWEEN '${start_date}' AND '${end_date}'
　) TO STDOUT WITH (FORMAT CSV, HEADER, DELIMITER ',' , QUOTE '\"', ESCAPE 　　'\\')"
 
  # クエリを実行し、CSVファイルに出力
  echo "Executing query for ${start_date}..${end_date}"
  export PGPASSWORD="${db_password}"
  psql -h "localhost" -p "${local_port}" -U "${db_user}" -d "${db_name}" -c "${query}" >"${csv_file}"
 
  # 出力ファイルの確認
  echo "Query results for ${start_date} have been saved to ${csv_file}"
done

ポートフォワード停止

ポートフォワードを停止します。
sshしてるプロセスIDをgrepで取得し、killします。

echo "Stopping port forwarding..."
echo "${ssh_command}"
ps aux | grep "${ssh_command}" | grep -v grep
ps aux | grep "${ssh_command}" | grep -v grep | awk '{ print "kill -9", $2 }' | sh

result=$(ps aux | grep "${ssh_command}" | grep -v grep)
if [ -z "$result" ]; then
  echo "Port forwarding stopped."
else
  echo "Failed to terminate ssh port forwarding"
  exit 1
fi

切断忘れの確認

ポートフォワードの切れ忘れを入念にチェックしたい場合があると思います。
その場合は、最後に、SSH接続の切断忘れがないか確認します。

echo "Check for forgetting to disconnect"
echo "excute ps aux | grep ssh"
ps aux | grep ssh

スクリプト

スクリプトの全文です。

Discussion