perl写的一个入库脚本

发布时间:2020-08-30编辑:脚本学堂
perl写的一个入库脚本,有需要的朋友可以参考下。

perl写的一个入库脚本,有需要的朋友可以参考下。
 

复制代码 代码如下:

#!/usr/bin/perl
#make by skylway 20121214
#use warnings;
#use strict;
use POSIX qw(strftime);
use Encode;
use DBI;
use Parallel::ForkManager;
setting();
alarm 3600;
local $SIG{ALRM} = sub {print "Timed out.n "; exit(1);};

#---------------日志文件相关变量
my $logday=strftime("%Y%m%d",localtime(time));
my $mydate=strftime("%Y-%m-%d %H:%M:%S",localtime(time));
my $logdate=strftime("%Y-%m-%d %H:%M",localtime(time));
my $myhour=strftime("%H",localtime(time));

my (%hash_suc,%hash_err,%hashtime,%hash_max,%hash_use_number,%hash_use_time);
my $timek;
my $pathfile;
my $svrsize;
my %hashsvrlog;
#my %hasherr;
my $seekpos;
my ($createtime,$ipaddr,$succ_getaddr_c,$fail_getaddr_c,$maxtime,$avgtime);
#mon_sms_file.tag定义记录文件大小
#my $tmpfile="/tmp/.tongxl/tongxl.tmp";
my $maillog="/tmp/.tongxl/tongxl.log";
#---------数据库相关定义
my $orainst = "testdb";
my $orauser = "testuser";
my $orapass = "123456789";

#-----------统计进程数-------------
my $comand=`ps aux |grep qos_txl_mi.pl|grep -v grep|grep -v vim|grep -v /bin/sh|wc -l`;
chomp $comand;
if ($comand>1){
        print "$mydate exit process os qos_txl_mi.pl will exit in 3 secondn";
        sleep 3;
        exit ;
}
my $max_process = 8;

#----------获取路径文件日志-----------
my $path;
my @logfile=glob("/logs/addSvr/addr_172.16.42.*/addrApi/addr_svr_$logday_*.log");
unlink($maillog);

#------------循环处理日志------------
my $pm = new Parallel::ForkManager( $max_process );
  foreach $path (@logfile){

      $pm->start and next;
       &handle($path);
       $pm->finish;

}
$pm->wait_all_children;

#------------入库--------------
my $dbhora = DBI->connect("dbi:Oracle:$orainst",$orauser,$orapass,{AutoCommit => 0,RaiseError => 1,PrintError => 0})  or die $DBI::errstr;
open(FF,$maillog)||die "ERROR:$!";
while(<FF>){
         my($createtimedb,$ipaddrdb,$succ_getaddr_cdb,$fail_getaddr_cdb,$avgtimedb,$maxtimedb)=(split /|/, $_);

        &insert_QOS_TXL_MI($createtimedb,$ipaddrdb,$succ_getaddr_cdb,$fail_getaddr_cdb,$avgtimedb,$maxtimedb);
    }
close(FF);
$dbhora->commit();
$dbhora->disconnect;

#------主函数-------
sub handle(){
        my $data = shift;
        $data=~//.*/addr_(dd+.d+.d+.d+)/addrApi/(.*)/;
        my $svr=$1;
                my $name=$2;
if($myhour < 1){unlink "/tmp/.tongxl/${svr}.${name}.log";}
##获取文件大小并存在hashsvrlog散列里
if (! -d  "/tmp/.tongxl/") {
    mkdir "/tmp/.tongxl/";
        }
if (-f "/tmp/.tongxl/${svr}.${name}.log") {
    open(TAGF,"/tmp/.tongxl/${svr}.${name}.log")||die "ERROR:cann't open $!";
 
foreach (<TAGF>){
         chomp;
         ($pathfile, $svrsize)=split;
          $hashsvrlog{$pathfile}=$svrsize;
#          print "$hashsvrlog{$pathfile}n";
         }
         close(TAGF);
         }

#print "$hashsvrlog{$pathfile}n";
open(TAGF,">>/tmp/.tongxl/${svr}.${name}.log")||die "ERROR:cann't open $!";
select ((select (TAGF), $|++)[0]);
my $size= -s $data;
print TAGF "$data $sizen";

#--------$maillog作为输出日志文件------------
open(LLL,">>$maillog")||die "ERROR:cann't open $!";   
select ((select (LLL), $|++)[0]);

#-------seek file
if( exists $hashsvrlog{$data} and $hashsvrlog{$pathfile} == $size ){
               $seekpos=$size;
               }elsif(exists $hashsvrlog{$data} and $hashsvrlog{$pathfile} <$size){
               $seekpos= $hashsvrlog{$pathfile} - 1500000;
               }else{
              $seekpos = 0;
               }
print "$seekposn";
open(FFFF,$data)||die "ERROR:cann't open $!";
select ((select (FFFF), $|++)[0]);
seek(FFFF,$seekpos,0);
           
while (<FFFF>){
my $str=$_;
chomp $str;
#$str=decode("utf8",$str);
#$str=encode("gbk",$str);

#------加载日志:
#2012-12-11 14:02:05 775 [1513777472] INFO  [CServiceMgr.cpp 440] 业务处理结束,[用户:8613909510524][业务:GetUserAddrData][线程号:1513777472][错误码:0]sock[313]       
if($str=~/A(dd+-dd-dds+dd:dd):dd.*(业务处理结束).*[业务:(w+)].*[错误码:(d?w+?)].*Z/){
    $timek=$1;   
    my $type1=$3;
    my $errcode=$4;
if($type1=~/GetUserAddrJsonData/ or $type1=~/GetUserAddrData/){                           
if($errcode=~/0/){
    $hashtime{$svr}{$timek}++;
    $hash_suc{$svr}{$timek}++;

        }elsif($errcode=~/cd/){
           $hashtime{$svr}{$timek}++;                   
           $hash_err{$svr}{$timek}++;}
                                  
        }                          
        }
#-----加载用时
#2012-12-11 14:02:16 936 [1692105024] INFO  [CAddressListMsg.cpp1407] [thread:1692105024][socket:313][用户:8613823201195][业务:GetUserAddrData]处理总耗时[670701]微秒.[接收等待:33][业务等待:19][消息解析:354][业务处理:670180][发送:115]                             
if($str=~/A(dd+-dd-dds+dd:dd):dd.*[业务:(w+)].*处理总耗时[(dd+)].*Z/){
   $timek=$1 ;
   my $type2=$2;
  my $consuming=$3;
# $hash_max{$svr}{$timek}[0]=0;
if($type2=~/GetUserAddrJsonData/ or $type2=~/GetUserAddrData/){
  if($hash_max{$svr}{$timek}[0]<$consuming){$hash_max{$svr}{$timek}[0]=$consuming;}  #取大值并保存加上strict就有问题。

   $hashtime{$svr}{$timek}++;
   $hash_use_number{$svr}{$timek}++;  
   $hash_use_time{$svr}{$timek}+=$consuming; }
       }                                                                         
      
 }            
#开始赋值

close(FFFF);

foreach $svr (sort keys %hashtime) {
       foreach $timek (sort keys %{$hashtime{$svr}}) {
        if(not exists $hash_suc{$svr}{$timek}){$hash_suc{$svr}{$timek}=0;}
        if(not exists $hash_err{$svr}{$timek}){$hash_err{$svr}{$timek}=0;}
        if(not exists $hash_use_number{$svr}{$timek}){$hash_use_number{$svr}{$timek}=0;}
        if(not exists $hash_use_time{$svr}{$timek}){$hash_use_time{$svr}{$timek}=0;}
        my $per_hash_use_time=$hash_use_time{$svr}{$timek}/(0.0000000001+$hash_use_number{$svr}{$timek});
        $createtime="$timek:00";
        $ipaddr=$svr;
        $succ_getaddr_c=$hash_suc{$svr}{$timek};
        $fail_getaddr_c=$hash_err{$svr}{$timek};
        $maxtime=sprintf("%.2f",$hash_max{$svr}{$timek}[0]/1000);
        $avgtime=sprintf("%.2f",$per_hash_use_time/1000);
        print LLL "$createtime|$ipaddr|$succ_getaddr_c|$fail_getaddr_c|$avgtime|$maxtimen";
        }
        }   
close(LLL);
}

sub insert_QOS_TXL_MI(){
        my ($createtime,$ipaddr,$succ_getaddr_c,$fail_getaddr_c,$avgtime,$maxtime)=@_;
    my $sql ="merge into QOS_TXL_MI a using dual b
        on ( a.createtime=TO_Date(?,'YYYY-MM-DD HH24:MI:SS') and a.ipaddr=?)
        when not matched then
        insert
        (a.createtime,a.ipaddr,a.succ_getaddr_c,a.fail_getaddr_c,a.avgtime,a.maxtime)
        values
        (TO_Date(?,'YYYY-MM-DD HH24:MI:SS'),?,?,?,?,?)
        when matched then
        update set a.succ_getaddr_c=?,a.fail_getaddr_c=?,a.avgtime=?,a.maxtime=?";
        eval{
            my $sthora = $dbhora->prepare("$sql");
            $sthora->bind_param(':p1',$createtime);
            $sthora->bind_param(':p2',$ipaddr);
            $sthora->bind_param(':p3',$createtime);
            $sthora->bind_param(':p4',$ipaddr);
            $sthora->bind_param(':p5',$succ_getaddr_c);
            $sthora->bind_param(':p6',$fail_getaddr_c);
            $sthora->bind_param(':p7',$avgtime);
                $sthora->bind_param(':p8',$maxtime);
            $sthora->bind_param(':p9',$succ_getaddr_c);
            $sthora->bind_param(':p10',$fail_getaddr_c);
            $sthora->bind_param(':p11',$avgtime);
            $sthora->bind_param(':p12',$maxtime);
       
            $sthora->execute();
            $dbhora->commit();
        };
        if($@){
            print  "errorcode $@n";
        }
}

sub setting {
use lib qw( /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/lib64 /home
/oracle/product/lib /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracl
e/ /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8
/usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64
/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8);
$ENV{NLS_LANG}="SIMPLIFIED CHINESE_CHINA.ZHS16GBK";
$ENV{ORACLE_BASE}="/home/oracle";
$ENV{ORACLE_HOME}="/home/oracle";
$ENV{ORA_NLS33}="/home/oracle/nls/data";
$ENV{TNS_ADMIN}="/home/oracle/network/admin";
$ENV{LANG}="zh_CN.GBK";
$ENV{PATH}="/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/home/oracle/bin";
$ENV{JAVA_HOME}="/home/jdk/jdk1.5.0_15";
$ENV{LD_LIBRARY_PATH}="/home/oracle/lib";
}