通過awk對記錄檔每天統計併入庫到MySQL的過程講解

互連網公司每天都要統計記錄檔，從中挖掘出想要的資訊，比如一個軟體的安裝量、活躍度等。這些資訊都需要入庫，並進行展示。下面，對這個實現過程進行講解。

具體的過程可分為以下幾個步驟：

(1)awk統計記錄檔，並將統計結果拼接成sql語句；

(2)寫shell指令碼，將這些sql語句入庫；

(3)將shell指令碼加入linux中的計劃任務中，讓其每天淩晨定時執行，從而實現每天自動分析日誌併入庫；

下面分步驟進行講解。

一. awk統計記錄檔，並拼接成sql語句

awk是對記錄檔分析的一個有效命令，下面通過統計日誌，計算出活躍使用者數、活躍安裝id、使用者線上時間長度、一日留存率、和兩日留存率。其中一日留存率表示昨天日誌中的使用者仍然出現在今天的日誌中。其中兩日留存率表示兩天前日誌中的使用者仍然出現在今天的日誌中，即兩天前的使用者今天仍然使用了該軟體。

日誌的格式如下：

時間 ip 版本 使用者行為 安裝序號 使用者id 安裝渠道 所安裝在的作業系統 在軟體上操作的地區 在操作地區上的動作

下面給出幾行日誌資料樣本：

23:55:00    211.11.21.81    1.0.0    getActiveUser    00-00-00-00-00-00-01    000000001    web    Windows XP    
23:55:05    221.21.31.91   2.0.1   sendMsgClient    00-00-00-00-00-00-02    100000000    web    Windows XP    
23:55:05    231.31.41.98   2.0.5   uploadPPimlog    00-00-00-00-00-00-03    000000020    web    Windows 7    main    recent

下面是awk命令對上述記錄檔的統計，統計的結果會輸出為sql語句。檔案命名為PP_sql.sh

#!/bin/shyesterday=`date -d "-1 day" +%Y-%m-%d` if [ "$1" != "" ]; thenlogfile=$1elselogfile="/data/PP-log/*PPIm.access.log.$yesterday"figawk -F"\t" '#function getTitle(name){#if(name == "main_user") return "我的使用者";#else return "其他";#}($3 ~ /^[0-9]+\.[0-9]+\.[0-9]+$/){ #過濾髒資料if($4 == "getActiveUser"){#活躍使用者數統計user[$3"_"$6] ++;#活躍安裝id統計install[$3"_"$5] ++;}else if($9 == "onlineTime"){#使用者線上時間長度統計tmp = ($11 - $10)/1000;if(tmp > 0 && tmp < 86400){onlineCount[$3] ++;onlineTime[$3] += tmp;}}}END{for(k in user){split(k, arr, "_");userOnly[arr[1]] ++;}for(k in install){split(k, arr, "_");installOnly[arr[1]] ++;}#活躍數統計:版本,使用者活躍,安裝活躍 for(k in userOnly){if(k != "total")print "insert into danalysis(analysisdate,version,useractive,installactive) values(\047'$yesterday'\047,\047"k"\047,"userOnly[k]","installOnly[k]")";}#線上時間長度統計:版本,平均線上時間長度(s) for(k in onlineTime){print "update danalysis set avgonlinetime="onlineTime[k]/onlineCount[k]" where analysisdate=\047'$yesterday'\047 and version=\047"k"\047";}}' $logfile#一日留存率和兩日留存率統計oneday=`date -d "-2 day" "+%Y-%m-%d"`twoday=`date -d "-3 day" "+%Y-%m-%d"`onelogfile="/data/PP-log/*PPIm.access.log.$oneday"twologfile="/data/PP-log/*PPIm.access.log.$twoday"awk -F"\t" 'BEGIN{d=0;}($3 ~ /^[0-9]+\.[0-9]+\.[0-9]+$/){ #過濾髒資料split(FILENAME, date, "."); #擷取檔案名稱上的日期,其中FILENAME表示當前讀取的檔案名稱len = length(date);if(d == 0 || two[0] == date[len]){ #統計兩日前的日誌(twologfile)two[0] = date[len];d = 1;two[$3"_"$6] ++;}else if(d == 1 || one[0] == date[len]){ #統計一日前的日誌(onelogfile)d = 2;one[0] = date[len];one[$3"_"$6] ++;}else{ #統計當日的日誌(logfile)thisday[$6] ++;}}END{delete two[0];delete one[0];for(k in two){split(k, arr, "_");total[arr[1]] ++;t[arr[1]] ++;if(arr[2] in thisday) remain[arr[1]] ++;}for(k in one){split(k, arr, "_");if(arr[2] in thisday) remain2[arr[1]] ++;total2[arr[1]] ++;t[arr[1]] ++;}#留存率統計:版本,一日留存,兩日留存for(k in t){onerate = "-";tworate = "-"if(k in total2) onerate=remain2[k]/total2[k];if(k in total) tworate=remain[k]/total[k];print "update danalysis set onedayexist="onerate",twodayexist="tworate" where analysisdate=\047'$yesterday'\047 and version=\047"k"\047";} }' $twologfile $onelogfile $logfile

註：\047是單引號的表示。第二個awk讀取的是多個檔案，當讀取多個檔案時會順序讀取每個檔案，對當前檔案處理完所有行後再處理下個檔案。

二. 通過shell指令碼將sql語句入庫

上面我們通過了awk實現了記錄檔的統計結果轉化成sql語句，下面我們只需要將讀取這些sql語句，並將他們執行即可，這樣統計結果自然就錄入到資料庫中了。入庫的shell命令如下所示：

mysql -u$user -p$pass -D $db -e “insert into t_table values(xxx,xxx,xxx);”

具體的shell：PP_insert_db.sh，如下：

#!/bin/sh/bin/sh /home/lincheung/PP_sql.sh | while read linedo   #echo $line;  mysql -h192.168.111.111 -umysqlroot -p123456 -D mysqldb -e "$line" --default-character-set=utf8done

參考連結：http://blog.csdn.net/jiedushi/article/details/6448740

這裡sql語句入庫是從入到主庫中，當執行這些insert語句時，讀取分離代理器會判斷這些sql操作是寫資料庫操作，會將其寫到資料庫的主庫中。但是，當使用者請求讀取資料庫時，也即select操作，讀取分離代理器會判斷出select是讀操作，會將使用者請求轉移到從庫中，所以讀的資料是從從庫中讀取的。即寫是寫到主庫中，讀是從從庫讀。

參考連結：http://www.cnblogs.com/yangligogogo/articles/1939938.html

三. 將shell指令碼加入linux中的計劃任務中

將shel指令碼加入linux中計劃任務，可以通過contrab -e命令實現，通過這個命令可以開啟目前使用者的計劃任務編輯器，使用者只需要在這裡輸入自己想要什麼時間想要執行的命令就可以了。linux會定期地從該檔案中讀取命令並執行。

首先，我們輸入命令：contrab -e

然後會開啟一個編輯器，在其中輸入下面這個命令：

0 5 * * * cd /home/lincheung; sh PP_insert_db.sh

之後儲存退出即可。這樣便加入了linux的計劃任務中，上面的命令會每天淩晨5點執行。

參考連結：http://blog.csdn.net/21aspnet/article/details/6798179