zabbixで東京電力の過去分の値を入れる(完了)

これ
これ
これ
これ
これ
これの続きです。

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

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

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

今回は今までの準備で作ったスクリプトやfunction(ストアドプロシージャ)を使って本番データに入れてみたいと思います。
といっても、あっさりしたものですが。


■function(ストアドプロシージャ)の本番用です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
CREATE OR REPLACE FUNCTION insert_past_trends(TEXT,bigint,INTEGER,numeric(20,0))  RETURNS TEXT AS $$
DECLARE
 
 input_table  ALIAS FOR $1;
 input_itemid ALIAS FOR $2;
 input_clock  ALIAS FOR $3;
 input_value  ALIAS FOR $4;
 
 t_num trends.num%TYPE;
 t_value_min trends.value_min%TYPE;
 t_value_avg trends.value_avg%TYPE;
 t_value_max trends.value_max%TYPE;
 
 tu_num trends_uint.num%TYPE;
 tu_value_min trends_uint.value_min%TYPE;
 tu_value_avg trends_uint.value_avg%TYPE;
 tu_value_max trends_uint.value_max%TYPE;
 
 SQL TEXT;
 
 exist_trends      trends%ROWTYPE;
 exist_trends_uint trends_uint%ROWTYPE;
 
 
 
BEGIN
 
-- 既存データ取得。
 
  SQL := 'SELECT * FROM '|| input_table ||' AS t WHERE t.itemid= '|| input_itemid ||' AND t.clock = '|| input_clock;
 
  IF input_table = 'trends' THEN              --test table 'trends_debug'
    EXECUTE SQL INTO exist_trends;
  ELSE
    EXECUTE SQL INTO exist_trends_uint;
  END IF;
 
-- trends UPDATE 処理。
  IF exist_trends IS NOT NULL AND input_table = 'trends' THEN           --test table 'trends_debug'
    IF input_value < exist_trends.value_min THEN
      t_value_min = input_value;
    ELSE
      t_value_min = exist_trends.value_min;
    END IF;
    IF input_value > exist_trends.value_max THEN
      t_value_max = input_value;
    ELSE
      t_value_max = exist_trends.value_max;
    END IF;
    t_value_avg = (exist_trends.num * exist_trends.value_avg + input_value ) / (exist_trends.num +1);
    t_num := exist_trends.num +1;
 
    SQL := 'UPDATE '|| input_table || 
         ' SET num='|| t_num ||' ,value_min='|| t_value_min || ' ,value_avg=' ||t_value_avg ||',value_max='|| t_value_max ||
           'WHERE itemid = '|| input_itemid ||' AND clock = '|| input_clock;
    -- RAISE NOTICE '%',sql;
    EXECUTE SQL ;
    -- RAISE NOTICE ' trends.value_min: %',t_value_min;
    -- RAISE NOTICE ' trends.value_avg: %',t_value_avg;
    -- RAISE NOTICE ' trends.value_max: %',t_value_max;
    RETURN 'OK';
 
 
-- trends_uint UPDATE 処理。
  ELSIF exist_trends_uint IS NOT NULL AND input_table = 'trends_uint' THEN  --test table 'trends_uint_debug'
 
    IF input_value < exist_trends_uint.value_min THEN
      tu_value_min = input_value;
    ELSE
      tu_value_min = exist_trends_uint.value_min;
    END IF;
    IF input_value > exist_trends_uint.value_max THEN
      tu_value_max = input_value;
    ELSE
      tu_value_max = exist_trends_uint.value_max;
    END IF;
    tu_value_avg = (exist_trends_uint.num * exist_trends_uint.value_avg + input_value ) / (exist_trends_uint.num +1);
    tu_num := exist_trends_uint.num +1;
 
    SQL :='UPDATE '||input_table ||
         ' SET num='|| tu_num ||' ,value_min='|| tu_value_min ||' ,value_avg='|| tu_value_avg ||
         ' ,value_max='|| tu_value_max ||
         'WHERE itemid ='|| input_itemid ||' AND clock ='|| input_clock;
    EXECUTE SQL ;
 
    -- RAISE NOTICE ' trends_uint.value_avg: %',tu_value_avg;
    -- RAISE NOTICE ' trends_uint.value_min: %',tu_value_min;
    -- RAISE NOTICE ' trends_uint.value_max: %',tu_value_max;
    RETURN 'UPDATE OK';
 
-- trends trends_uint INSERT 処理。
  ELSE
    SQL := 'INSERT INTO '|| input_table || ' VALUES '||
    ' ('|| input_itemid || ','|| input_clock||',1,'|| input_value|| ',' || input_value ||','|| input_value||')';
    EXECUTE SQL;
    RETURN 'INSERT OK';
 
  END IF;
 
END;
 
$$ LANGUAGE plpgsql;



■次に、wgetしたデータを入れて、functionをコールするスクリプトです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
#!/bin/sh
source /root/.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'
TRENDS_TABLE='trends_uint'
WGET='wget -q -O '
NOW_DATE=`date +%s`
DL_CSV=/tmp/PAST_ELE.${NOW_DATE}.csv
SQL_TEXT=/tmp/PAST_ELE.${NOW_DATE}.sql
 
ITEMID=${1}
END_TIME=${2}
 
 
if [ -z ${1} ];then
  echo $"Usage: $0 {item_numer} please check items table"
  exit 1
fi
 
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`
 
# 関数の呼び出しを作成
SQL="SELECT insert_past_trends('${TRENDS_TABLE}'::text,${ITEMID},${CLOCK},${VALUE});"
 
IFS=' '        #区切り文字を初期化
echo ${SQL} >> ${SQL_TEXT}
IFS=,       #区切り文字を,に。
 
done
IFS=' '        #区切り文字を初期化
 
# 実行
#${SQL_CLI} -f "${SQL_TEXT}"
 
 
rm -f ${DL_CSV}
rm -f ${SQL_TEXT}



今回は、電力量しか過去データがなかったので、率は求められず、trendsの部分は
呼ばれることがありませんが、条件分岐などの点が勉強になると思ったので入れています。
そのうち、役に立つ日が…来ないでしょうけれど、まあ、それはそれです。

■実行前にバックアップを取っておきます。
(作業中に更新されて、直前の状態には戻せないデータではありますが、どうしようもないときにあったほうが良いですね。)

1
pg_dump -U pgsql -t trends_uint zabbix > trends_uint.20110802.dm




■実行スクリプト
7/5から取り始めたので、1日程度は重なるような日時指定にしています。

 cd /usr/local/zabbix/sbin
 ./past_ele_forecast.batch.sh 22497 '2011/7/5,15:00'



■実行前のテーブルの要素数

zabbix=# SELECT COUNT(clock) FROM trends_uint;
  COUNT  
---------
 1564878
(1 ROW)



■実行後の要素数

zabbix=# SELECT COUNT(clock) FROM trends_uint;
  COUNT  
---------
 1569294
(1 ROW)



4500ほど増えましたね。24時間x180日ぐらいなので、予想通りです。



■データを入れる前の電力量のすべてです。(クリックで元画像)
データを入れる前の全体画像

■データを入れた後の電力量の重なっている部分抜粋です。(クリックで元画像)
スクリプト処理後の全体像

■一部幅広い帯みたいになっている箇所がありますが、ここが1日程度のUPDATE部分です。
失敗…!?ってよく考えたら、最初の時に100万かけるの忘れてて値が極端に低かったからだと
気づきました。
UPDATE文を作れば何とかなると思うけれど今はそのままにしておきますw

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
zabbix=# SELECT * FROM trends_uint WHERE value_min < 100000 AND itemid = 22497;
 itemid |   clock    | num | value_min |  value_avg  |  value_max  
--------+------------+-----+-----------+-------------+-------------
  22497 | 1309705200 |   4 |      3124 | 14815001569 | 29630000000
  22497 | 1309708800 |  11 |      2759 |  5032729598 | 27680000000
  22497 | 1309712400 |  13 |      2652 |  4084617670 | 26550000000
  22497 | 1309719600 |  13 |      2598 |  4000002208 | 26000000000
  22497 | 1309723200 |  13 |      2569 |  4058463733 | 26380000000
  22497 | 1309726800 |  13 |      2619 |  4416925372 | 28710000000
  22497 | 1309730400 |  13 |      2853 |  4858464074 | 31580000000
  22497 | 1309734000 |  13 |      3129 |  5506156672 | 35790000000
  22497 | 1309737600 |  13 |      3586 |  6112310896 | 39730000000
  22497 | 1309741200 |  13 |      3971 |  6375388051 | 41440000000
  22497 | 1309744800 |  13 |      4157 |  6560003562 | 42640000000
  22497 | 1309748400 |  13 |      4157 |  6455388201 | 41960000000
  22497 | 1309752000 |  13 |      4167 |  6715388224 | 43650000000
  22497 | 1309755600 |  13 |      4380 |  6750772946 | 43880000000
  22497 | 1309759200 |  13 |      4323 |  6673849848 | 43380000000
  22497 | 1309766400 |  13 |      4166 |  6390772839 | 41540000000
  22497 | 1309770000 |  13 |      4061 |  6260003468 | 40690000000
  22497 | 1309773600 |  13 |      4056 |  6267695766 | 40740000000
  22497 | 1309777200 |  13 |      3982 |  6087695721 | 39570000000
  22497 | 1309780800 |  13 |      3788 |  5813849439 | 37790000000
  22497 | 1309788000 |  13 |      3512 |  5378464620 | 34960000000
  22497 | 1309791600 |   8 |      3365 |  7912502573 | 31650000000
  22497 | 1309795200 |  13 |      2926 |  4501541023 | 29260000000
  22497 | 1309798800 |  13 |      2801 |  4300002419 | 27950000000
  22497 | 1309802400 |  13 |      2719 |  4175386943 | 27140000000
  22497 | 1309806000 |  13 |      2674 |  4104617667 | 26680000000
  22497 | 1309809600 |  13 |      2640 |  4093848402 | 26610000000
  22497 | 1309813200 |  13 |      2663 |  4432310011 | 28810000000
  22497 | 1309816800 |  13 |      2896 |  4893848721 | 31810000000
  22497 | 1309820400 |  13 |      3171 | 10862309974 | 35530000000
  22497 | 1309716000 |  13 |      2610 |  4021540685 | 26140000000
  22497 | 1309762800 |  13 |      4327 |  6669234446 | 43350000000
  22497 | 1309784400 |  13 |      3707 |  5735387797 | 37280000000
(33 rows)