zabbixで東京電力の過去分の値を入れる準備その２

https://seedslight.com/wp/archives/140　これの続きです。
テーブルにデータを入れるところまでは行きましたが、重複データが入ってしまっているとエラーになってしまうので回避方法を考える・・・手前のところをまとめます。

途中から見られると怖いので、また心構えを書いときます。

注意：この作業を行うにあたって、心構えとしては

・運用中のzabbixが不安定になる覚悟をする。
・バックアップを取ったデータベースがなければ、過去データはすべて破棄する覚悟。
・作業を行った直後は問題がなかったとしても、特殊なデータや状況が発生した時に、データ破損してしまうことを受け入れる覚悟。

これらのことが許容できる環境の持ち主か、どうにもせっぱつまっていて、上記のリスクに比べたら相当に致命的な状況まで追い込まれている人じゃない限り、やらないほうが良い作業だと思っています。※技術力向上のためにやれるなら幸せかもですが。

下記スクリプトテストとデータベースをいじった作業です。

■まず、スクリプトを作りました。まだ適用段階ではないので別テーブルを作って、そこに入れる形で作っています。

# cat past_ele_forecast.batch.sh

#!/bin/sh
source ~/.bashrc
 
PAST_JUYO_URL='http://www.tepco.co.jp/forecast/html/images/juyo-2011.csv'
SQL_CLI='/usr/local/pgsql/bin/psql -U zabbix -d zabbix'
TRENDS_TABLE='trends_uint_temp'
WGET='wget -q -O '
NOW_DATE=`date +%s`
DL_CSV=/tmp/PAST_ELE.${NOW_DATE}.csv
 
ITEMID=${1}
END_TIME=${2}
 
if [ ${1} -lt 0 ];then
  echo $"Usage: $0 {item_numer} please check items table"
  exit 1
fi
 
if [ ${2} != '' ];then
  ${WGET} - ${PAST_JUYO_URL} | grep -B 100000 ${END_TIME} > ${DL_CSV}
 
  if [ ! -s ${DL_CSV} ];then  # 空ファイル判定
  echo $"Usage: $2 is not match any data. ex:'2011/7/3,2:00'"
  rm -f ${DL_CSV}
  exit 1
  fi
 
else
  ${WGET} ${DL_CSV} ${PAST_JUYO_URL}
fi
 
IFS=,     #区切り文字を,に。
tail -n +4 ${DL_CSV} |tr -d '\r' |while read YMD HOUR VALUE;
do
 
# 時間,値を計算
CLOCK=`date -d "${YMD} ${HOUR}" +%s`
VALUE=`expr ${VALUE} \* 10000000`
 
# INSERT文を作成
SQL="INSERT INTO ${TRENDS_TABLE} ( clock,itemid,num,value_min,value_avg,value_max )"
SQL="${SQL} VALUES ( ${CLOCK},${ITEMID},1,${VALUE},${VALUE},${VALUE} )"
 
IFS=' '        #区切り文字を初期化
#echo ${SQL}
${SQL_CLI} -c "${SQL}"
IFS=,       #区切り文字を,に。
 
done
IFS=' '        #区切り文字を初期化
 
rm -f ${DL_CSV}

http://shellscript.sunone.me/filter_etc.html
参考にしたページです。tail のプラス表記とかgrep の-B なんて初めて使いました。
こんなのあるんだなーって思ったところです。

また、IFS の環境変数にははまりました。read で区切り文字をカンマにすれば、CSVのデータだって入るなと
簡単に思ったら、引数の区切り文字が変わるってことに気づくまで何度もリトライしたりして。

IFSの変更を結構やっているのが痛いところですね。不思議と、変数に突っ込んだコマンドはIFSの区切り文字に影響されて、直接実行したものは引数の区切り文字は変わりませんでした。

では、これを試すためのテーブルをtrends_uintの定義をもとに作ってみます。

zabbix=> \d trends_uint
               TABLE "public.trends_uint"
  Column   |     TYPE      |          Modifiers
-----------+---------------+-----------------------------
 itemid    | bigint        | NOT NULL DEFAULT 0::bigint
 clock     | INTEGER       | NOT NULL DEFAULT 0
 num       | INTEGER       | NOT NULL DEFAULT 0
 value_min | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_avg | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_max | numeric(20,0) | NOT NULL DEFAULT 0::numeric
Indexes:
    "trends_uint_pkey" PRIMARY KEY, btree (itemid, clock)
 
zabbix=> CREATE TABLE trends_uint_temp (LIKE trends_uint INCLUDING DEFAULTS);
CREATE TABLE
 
zabbix=> \d trends_uint_temp
             TABLE "public.trends_uint_temp"
  Column   |     TYPE      |          Modifiers
-----------+---------------+-----------------------------
 itemid    | bigint        | NOT NULL DEFAULT 0::bigint
 clock     | INTEGER       | NOT NULL DEFAULT 0
 num       | INTEGER       | NOT NULL DEFAULT 0
 value_min | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_avg | numeric(20,0) | NOT NULL DEFAULT 0::numeric
 value_max | numeric(20,0) | NOT NULL DEFAULT 0::numeric

■テストで3データを入れてみました。

# sh ./past_ele_forecast.batch.sh 3 ‘2011/1/1,2:00’ | head

zabbix=# SELECT * FROM trends_uint_temp ;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max
--------+------------+-----+-------------+-------------+-------------
      3 | 1293807600 |   1 | 30580000000 | 30580000000 | 30580000000
      3 | 1293811200 |   1 | 28590000000 | 28590000000 | 28590000000
      3 | 1293814800 |   1 | 27000000000 | 27000000000 | 27000000000
(3 rows)

■2回実行するとこんな感じ

zabbix=# SELECT * FROM trends_uint_temp ;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max
--------+------------+-----+-------------+-------------+-------------
      3 | 1293807600 |   1 | 30580000000 | 30580000000 | 30580000000
      3 | 1293811200 |   1 | 28590000000 | 28590000000 | 28590000000
      3 | 1293814800 |   1 | 27000000000 | 27000000000 | 27000000000
      3 | 1293807600 |   1 | 30580000000 | 30580000000 | 30580000000
      3 | 1293811200 |   1 | 28590000000 | 28590000000 | 28590000000
      3 | 1293814800 |   1 | 27000000000 | 27000000000 | 27000000000
(6 rows)

重複データが作れてしまうという証明。これは、元テーブルで定義していたPRIMARY KEYが無いため。

■PRIMARY KEY 作成失敗

zabbix=# ALTER TABLE trends_uint_temp ADD PRIMARY KEY (itemid,clock);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will CREATE implicit INDEX "trends_uint_temp_pkey" FOR TABLE "trends_uint_temp"
ERROR:  could NOT CREATE UNIQUE INDEX "trends_uint_temp_pkey"
DETAIL:  TABLE contains duplicated VALUES.

■データ削除してPRIMARY KEY作成

zabbix=# DELETE FROM trends_uint_temp ;
DELETE 6
 
zabbix=# ALTER TABLE trends_uint_temp ADD PRIMARY KEY (itemid,clock);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will CREATE implicit INDEX "trends_uint_temp_pkey" FOR TABLE "trends_uint_temp"
ALTER TABLE

■エラーを確認。
1回目:

[root@redalarm sbin]# sh ./past_ele_forecast.batch.sh 3 '2011/1/1,2:00' | head
INSERT 0 1
INSERT 0 1
INSERT 0 1

2回目:

[root@redalarm sbin]# sh ./past_ele_forecast.batch.sh 3 '2011/1/1,2:00' | head
ERROR:  duplicate key VALUE violates UNIQUE constraint "trends_uint_temp_pkey"
ERROR:  duplicate key VALUE violates UNIQUE constraint "trends_uint_temp_pkey"
ERROR:  duplicate key VALUE violates UNIQUE constraint "trends_uint_temp_pkey"

この時点で時間指定を誤るとデータが入らないというエラーが予想されます。

■7/3 2:00 までを入れたデータの様子の抜粋。

# sh ./past_ele_forecast.batch.sh 3 ‘2011/7/3,2:00’ | head

zabbix=# SELECT * FROM trends_uint_temp;
 itemid |   clock    | num |  value_min  |  value_avg  |  value_max
--------+------------+-----+-------------+-------------+-------------
  22497 | 1293807600 |   1 | 30580000000 | 30580000000 | 30580000000
  22497 | 1293811200 |   1 | 28590000000 | 28590000000 | 28590000000
  22497 | 1293814800 |   1 | 27000000000 | 27000000000 | 27000000000
  22497 | 1293818400 |   1 | 25750000000 | 25750000000 | 25750000000
  22497 | 1293822000 |   1 | 25000000000 | 25000000000 | 25000000000
  22497 | 1293825600 |   1 | 24910000000 | 24910000000 | 24910000000
  22497 | 1293829200 |   1 | 25880000000 | 25880000000 | 25880000000
  22497 | 1293832800 |   1 | 27620000000 | 27620000000 | 27620000000
  22497 | 1293836400 |   1 | 29300000000 | 29300000000 | 29300000000
  22497 | 1293840000 |   1 | 29360000000 | 29360000000 | 29360000000
  22497 | 1293843600 |   1 | 28570000000 | 28570000000 | 28570000000
  22497 | 1293847200 |   1 | 27660000000 | 27660000000 | 27660000000

とりあえず、この時点でもテーブル名の指定さえ変えれば入るはずですが、もうちょっと改良してからテーブルに入れてみたいと思います。

SeedsLight

Server and Development Blog

zabbixで東京電力の過去分の値を入れる準備その２