注意：この作業を行うにあたって、心構えとしては
・運用中の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文を作れば何とかなると思うけれど今はそのままにしておきますｗ
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) |