[Linux] MySQL数据库设计及稀释脚本分析

于 2012-12-18 14:52:02 发布
阅读量679 收藏
点赞数
分类专栏： linux 文章标签： linux Linux LINUX mysql MySQL Mysql MYSQL MySql 稀释数据库 稀释脚本
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mango_song/article/details/8314439
版权
一.背景
   最近参与大规模Hadoop集群可视化项目的设计与开发工作。主要思路是：侦听hadoop集群发来的metric信息，按（cluster,metric_key,metric_value)形式存储。并分为十年度表，年度数据、季度数据、月度数据、周度数据..
 
1>我们希望将定时(每1分钟)采集的数据存放到周表里，每15分钟的数据求均值放在月表里，每一个小时的数据求均值放在季度表里，每6个小时的数据求均值放在年表里，每天的数据求均值放在10年表里 ...我们称为一个逐级稀释的过程... 
2>每个级别的表能定期更新(删除掉过期数据）

二.数据库的设计

MySQL数据库设计及稀释脚本分析 
-- phpMyAdmin SQL Dump
-- version 2.11.9.2
-- http://www.phpmyadmin.net
--
-- 主机: 127.0.0.1:3306
-- 生成日期: 2012 年 08 月 10 日 11:44
-- 服务器版本: 5.0.13
-- PHP 版本: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- 数据库: `hadoop`
--

-- --------------------------------------------------------

--
-- 表的结构 `cluster_budget`
--

CREATE TABLE IF NOT EXISTS `cluster_budget` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data`
--

CREATE TABLE IF NOT EXISTS `cluster_data` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `clustername` (`clustername`,`itemkey`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9508968 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_decade_000000`
--

CREATE TABLE IF NOT EXISTS `cluster_data_decade_000000` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_decade_000001`
--

CREATE TABLE IF NOT EXISTS `cluster_data_decade_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=99149 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_month_000003`
--

CREATE TABLE IF NOT EXISTS `cluster_data_month_000003` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5945838 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_month_000004`
--

CREATE TABLE IF NOT EXISTS `cluster_data_month_000004` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7524798 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_quartor_000001`
--

CREATE TABLE IF NOT EXISTS `cluster_data_quartor_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=407413 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_quartor_000002`
--

CREATE TABLE IF NOT EXISTS `cluster_data_quartor_000002` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1999817 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_week_000012`
--

CREATE TABLE IF NOT EXISTS `cluster_data_week_000012` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22019075 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_week_000013`
--

CREATE TABLE IF NOT EXISTS `cluster_data_week_000013` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23310978 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_year_000000`
--

CREATE TABLE IF NOT EXISTS `cluster_data_year_000000` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_data_year_000001`
--

CREATE TABLE IF NOT EXISTS `cluster_data_year_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=350390 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_info`
--

CREATE TABLE IF NOT EXISTS `cluster_info` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) character set utf8 NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) character set utf8 default NULL,
  `path` varchar(100) character set utf8 default NULL,
  `queue` varchar(45) character set utf8 default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `clustername` (`clustername`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=214796 ;

-- --------------------------------------------------------

--
-- 表的结构 `cluster_real`
--

CREATE TABLE IF NOT EXISTS `cluster_real` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `cluster_budgetID` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- 表的结构 `department`
--

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- 表的结构 `dilute_cluster_data`
--

CREATE TABLE IF NOT EXISTS `dilute_cluster_data` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23305664 ;

-- --------------------------------------------------------

--
-- 表的结构 `dilute_user_data`
--

CREATE TABLE IF NOT EXISTS `dilute_user_data` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `interface`
--

CREATE TABLE IF NOT EXISTS `interface` (
  `id` int(11) NOT NULL,
  `mail` varchar(45) NOT NULL,
  `mobile` varchar(45) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- 表的结构 `machinecost`
--

CREATE TABLE IF NOT EXISTS `machinecost` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` varchar(100) NOT NULL,
  `machine` varchar(100) NOT NULL,
  `dfsused` double NOT NULL,
  `dfscapacity` double NOT NULL,
  `dfsurate` float NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2987427 ;

-- --------------------------------------------------------

--
-- 表的结构 `machinecostconsumed`
--

CREATE TABLE IF NOT EXISTS `machinecostconsumed` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` varchar(100) NOT NULL,
  `consumed` longtext NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=462 ;

-- --------------------------------------------------------

--
-- 表的结构 `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `depID` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- 表的结构 `sla_records`
--

CREATE TABLE IF NOT EXISTS `sla_records` (
  `id` int(11) NOT NULL auto_increment,
  `cluster_name` varchar(45) character set utf8 default NULL,
  `cluster_realID` int(11) NOT NULL,
  `clusterlevel` varchar(45) character set utf8 NOT NULL,
  `type` varchar(45) character set utf8 NOT NULL,
  `affect` varchar(45) character set utf8 NOT NULL,
  `begin_datetime` timestamp NULL default NULL,
  `end_datetime` timestamp NULL default NULL,
  `last` int(45) NOT NULL,
  `quarter` int(10) NOT NULL,
  `remarks` varchar(300) character set utf8 NOT NULL,
  `eip` varchar(100) character set utf8 NOT NULL,
  `eipid` varchar(45) character set utf8 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

-- --------------------------------------------------------

--
-- 表的结构 `sla_show`
--

CREATE TABLE IF NOT EXISTS `sla_show` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` varchar(45) NOT NULL,
  `quarter` int(10) NOT NULL,
  `kpi` varchar(45) NOT NULL,
  `mttr` int(100) NOT NULL,
  `mtbf` longtext NOT NULL,
  `kpiStandard` varchar(45) NOT NULL,
  `mttrStandard` int(100) NOT NULL,
  `mtbfStandard` longtext NOT NULL,
  `interruptTimeSum` int(11) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9001 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data`
--

CREATE TABLE IF NOT EXISTS `user_data` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `clustername` (`clustername`,`itemkey`,`account`,`path`,`timestamp`),
  UNIQUE KEY `clustername_2` (`clustername`,`itemkey`,`account`,`queue`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1282142 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_decade_000000`
--

CREATE TABLE IF NOT EXISTS `user_data_decade_000000` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_decade_000001`
--

CREATE TABLE IF NOT EXISTS `user_data_decade_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=649 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_month_000003`
--

CREATE TABLE IF NOT EXISTS `user_data_month_000003` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_month_000004`
--

CREATE TABLE IF NOT EXISTS `user_data_month_000004` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_quartor_000001`
--

CREATE TABLE IF NOT EXISTS `user_data_quartor_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12529 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_quartor_000002`
--

CREATE TABLE IF NOT EXISTS `user_data_quartor_000002` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12529 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_week_000011`
--

CREATE TABLE IF NOT EXISTS `user_data_week_000011` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_week_000012`
--

CREATE TABLE IF NOT EXISTS `user_data_week_000012` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_year_000000`
--

CREATE TABLE IF NOT EXISTS `user_data_year_000000` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_data_year_000001`
--

CREATE TABLE IF NOT EXISTS `user_data_year_000001` (
  `id` int(11) NOT NULL auto_increment,
  `clustername` int(11) NOT NULL,
  `itemkey` varchar(45) NOT NULL,
  `itemvalue` double NOT NULL,
  `account` varchar(45) NOT NULL,
  `path` varchar(100) default NULL,
  `queue` varchar(45) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2161 ;

-- --------------------------------------------------------

--
-- 表的结构 `user_hadoop`
--

CREATE TABLE IF NOT EXISTS `user_hadoop` (
  `id` int(11) NOT NULL,
  `name` varchar(50) character set utf8 NOT NULL,
  `depID` int(11) NOT NULL,
  `productID` int(11) NOT NULL,
  `account` varchar(50) character set utf8 NOT NULL,
  `path` varchar(100) character set utf8 NOT NULL,
  `queue` varchar(50) character set utf8 NOT NULL,
  `interfaceID` int(11) NOT NULL,
  `cluster_budgetID` int(11) NOT NULL,
  `cluster_realID` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;





三、稀释脚本 
#!/bin/sh

export LD_LIBRARY_PATH=/home/visualhadoop/visualhadoop-v1.0/visualhadoop/didute/mysql/lib/mysql

mysqlPath="/home/visualhadoop/visualhadoop-v1.0/visualhadoop/didute/mysql"
g_host="localhost"
g_user="mango"
g_passwd="mango"

#######################
runSql="$mysqlPath/bin/mysql -h $g_host -u$g_user -p$g_passwd"
runImport="$mysqlPath/bin/mysqlimport --local --host=$g_host --user=$g_user --password=$g_passwd"
tableFormat_1="(id int primary key not null auto_increment, clustername int not null, itemkey varchar(45) not null, itemvalue double not null, account varchar(45) not null, path varchar(100), queue varchar(45), timestamp datetime not null)"
tableFormat_2="(id int primary key not null auto_increment, clustername int not null, itemkey varchar(45) not null, itemvalue double not null, timestamp datetime not null)"
db="hadoop"
sql_item_1="clustername,itemkey,account,path,queue"
sql_group_1="group by clustername,itemkey,account,path,queue"
sql_item_2="clustername,itemkey"
sql_group_2="group by clustername,itemkey"

log_out(){
  now_log="`date +%Y-%m-%d` `date +%H:%M:%S`"
  echo $now_log: $1 >&1
}

log_err(){
  now_log="`date +%Y-%m-%d` `date +%H:%M:%S`"
  echo $now_log: $1 >&2
}

getLevel(){
#  date_gl=$1
#  time_gl=$2

  date_gl=${1%% *}
  time_gl=${1#* }

  hour_gl=${time_gl%%:*}
  mi_gl=${time_gl#*:}
  mi_gl=${mi_gl%%:*}
  sec_gl=${time_gl##*:}

  if [ $hour_gl = 00 ] && [ $mi_gl = 00 ]; then
    echo 4
    return
  elif ( [ $hour_gl = 06 ] || [ $hour_gl = 12 ] || [ $hour_gl = 18 ] ) && [ $mi_gl = 00 ] && [ $sec_gl = 00 ]; then
    echo 3
    return
  elif [ $mi_gl = 00 ] && [ $sec_gl = 00 ]; then
    echo 2
    return
  elif ( [ $mi_gl = 15 ] || [ $mi_gl = 30 ] || [ $mi_gl = 45 ] ) && [ $sec_gl = 00 ]; then
    echo 1
    return
  else
    echo 0
    return
  fi
}

getSecs(){

  if [ $1 = 4 ]; then
    echo $((24*60*60))
  elif [ $1 = 3 ]; then
    echo $((6*60*60))
  elif [ $1 = 2 ]; then
    echo $((60*60))
  elif [ $1 = 1 ]; then
    echo $((15*60))
  else
    echo the time is not right! >&2
    exit 1
  fi
}

createTable(){
#创建并实现表的滚动
  log_out "---------createTable----------"
  log_out "table type:$1, level:$2"
  table_ct=$1
  lev_ct=$2
  sql="use $db;show tables;"
  lastTable=`$runSql -e "$sql" | grep "${table_ct}_${lev_ct}" | tail -1`

#获取id最大的auto_increment
  startIdIndex=0
  if [ ${#lastTable} != 0 ]; then
    sql="use $db;select max(id) from $lastTable;"
    res=`$runSql -e "$sql" | tail -1`
    if [ ${#res} != 0 ] && [ ${res} != "NULL" ]; then
      startIdIndex=$(($res+1))
    fi
    log_out "res=$res"
  fi

#根据最后一个表的名字，创建新表
  tableIndex=-1
  if [ ${#lastTable} = 0 ]; then
    tableIndex=0
  else
    curIndex=${lastTable##*_}
    curIndex=`echo ${curIndex} | sed 's/0*//'`
    tableIndex=$(($curIndex+1))
  fi
  tableIndex=`printf "%6.6d" ${tableIndex}`
  log_out create new table: ${table_ct}_${lev_ct}_$tableIndex
  if [ $table_ct = "user_data" ]; then
    sql="use $db;create table ${table_ct}_${lev_ct}_${tableIndex} ${tableFormat_1} auto_increment=${startIdIndex};"
  elif [ $table_ct = "cluster_data" ]; then
    sql="use $db;create table ${table_ct}_${lev_ct}_${tableIndex} ${tableFormat_2} auto_increment=${startIdIndex};"
  else
    log_err "ERROR! create table error."
    exit 1
  fi

  $runSql -e "$sql"
#删掉旧表，此类型的表只留2个
  sql="use $db;show tables;"
  $runSql -e "$sql" | grep "${table_ct}_${lev_ct}" | sed '$d' | sed '$d' |
  while read line
  do
    sql="use $db; drop table $line;"
    $runSql -e "$sql"
  done
}


rollxxx(){
  now_xxx=$1
  date_xxx=${now_xxx% *}
  time_xxx=${now_xxx#* }
  if [ $time_xxx = 00:00:00 ]; then
    year_xxx=${date_xxx%%-*}
    month_xxx=${date_xxx#*-}
    month_xxx=${month_xxx%%-*}
    day_xxx=${date_xxx##*-}
    decade_xxx=$((${year_xxx}))

    log_out "decade:$decade_xxx, year:$year_xxx, month:$month_xxx, day:$day_xxx"

    if [ $decade_xxx = 0 ] && [ $month_xxx = 01 ] && [ $day_xxx = 01 ]; then
      #整10年的1月1日，更新decade表
      echo update decate table!
      createTable "cluster_data" "decade"
      createTable "user_data" "decade"
    fi
    if [ $month_xxx = 01 ] && [ $day_xxx = 01 ]; then
      #每年的1月1日，更新year表
      echo update year table!
      createTable "cluster_data" "year"
      createTable "user_data" "year"
    fi
    if ([ $month_xxx = 01 ] || [ $month_xxx = 04 ] || [ $month_xxx = 07 ] || [ $month_xxx = 10 ]) && [ $day_xxx = 01 ]; then
      #1、4、7、10月的1日，更新quartor表
      echo update quartor table!
      createTable "cluster_data" "quartor"
      createTable "user_data" "quartor"
    fi
    if [ $day_xxx = 01 ]; then
      #每个月1日，更新month表
      echo update month table!
      createTable "cluster_data" "month"
      createTable "user_data" "month"
    fi
    if [ $day_xxx = 01 ] || [ $day_xxx = 08 ] || [ $day_xxx = 15 ] || [ $day_xxx = 22 ]; then
      #每个月1、8、15、22日，更新week表
      echo update week table!
      createTable "cluster_data" "week"
      createTable "user_data" "week"
    fi
  fi
}

getCurTable(){
  table_gct=$1
  lev_gct=$2
  sql="use ${db};show tables;"
  $runSql -e "$sql" | grep "${table_gct}_${lev_gct}" | tail -1
}

getAllTable(){
  table_gct=$1
  lev_gct=$2
  sql="use ${db};show tables;"
  $runSql -e "$sql" | grep "${table_gct}_${lev_gct}"
}

createTmpTable(){
  table_ctt=$1
  if [ $table_ctt = "user_data" ]; then
    sql="use ${db};create table if not exists dilute_$table_ctt ${tableFormat_1};"
  elif [ $table_ctt = "cluster_data" ]; then
    sql="use ${db};create table if not exists dilute_$table_ctt ${tableFormat_2};"
  else
    log_err "ERROR! cheateTmpTable error."
    exit 1
  fi
  $runSql -e "$sql"
}

dropTmpTable(){
  table_dtt=$1
  sql="use ${db};drop table if exists dilute_$table_dtt;"
  $runSql -e "$sql"
}

getStartTime(){
  now_gst=$1
  lev_gst=$2
  secs_gst=`getSecs ${lev_gst}`
  n_end_gst=`date +%s -d "${now_gst}"`
  n_start_gst=$(($n_end_gst-${secs_gst}))
  start_gst=`date -d '1970-01-01 UTC '${n_start_gst}' seconds' +"%Y-%m-%d %T"`
  echo $start_gst
}

getAvg(){
  start_ga=$1
  end_ga=$2
  table_ga=$3
  arr=("week" "month" "quartor" "year" "decade")
  lev_ga=${arr[$4]}
  target_ga=`getCurTable $table_ga $lev_ga`
  log_out "dilute into table:$target_ga"

  if [ $table_ga = "user_data" ]; then  
    sql="use $db;select $sql_item_1 from dilute_$table_ga where timestamp between '$start_ga' and '$end_ga' $sql_group_1;"
  elif [ $table_ga = "cluster_data" ]; then
    sql="use $db;select $sql_item_2 from dilute_$table_ga where timestamp between '$start_ga' and '$end_ga' $sql_group_2;"
  else
    log_err "ERROR, getAvg error 1."
    exit 1
  fi

  len_ga=`$runSql -e "$sql" | sed '1d' | wc -l`
  log_out "dilite length:$len_ga"
  $runSql -e "$sql" | sed '1d' |
  while read line
  do
    if [ $table_ga = "user_data" ]; then
      tmp=$line
      x1=${tmp%% *}
      tmp=${tmp#* }
      x2=${tmp%% *}
      tmp=${tmp#* }
      x3=${tmp%% *}
      tmp=${tmp#* }
      x4=${tmp%% *}
      tmp=${tmp#* }
      x5=$tmp
      sql="use $db;select clustername,itemkey,avg(itemvalue),account,path,queue from dilute_$table_ga where clustername='$x1' and itemkey='$x2' and account='$x3' and path='$x4' and queue='$x5' and timestamp between '$start_ga' and '$end_ga' $sql_group_1";
      $runSql -e "$sql" | sed '1d' |
      while read item
      do
        tmp=$item
        q1=${tmp%% *}
        tmp=${tmp#* }
        q2=${tmp%% *}
        tmp=${tmp#* }
        q3=${tmp%% *}
        tmp=${tmp#* }
        q4=${tmp%% *}
        tmp=${tmp#* }
        q5=${tmp%% *}
        tmp=${tmp#* }
        q6=$tmp
        q7=$end_ga
        sql="use $db;insert into ${target_ga}(clustername,itemkey,itemvalue,account,path,queue,timestamp) values('$q1','$q2','$q3','$q4','$q5','$q6','$q7');"
        $runSql -e "$sql" 
      done
    elif [ $table_ga = "cluster_data" ]; then
      tmp=$line
      x1=${tmp%% *}
      tmp=${tmp#* }
      x2=$tmp
      sql="use $db;select clustername,itemkey,avg(itemvalue) from dilute_$table_ga where clustername='$x1' and itemkey='$x2' and timestamp between '$start_ga' and '$end_ga' $sql_group_2";

#echo $sql
#$runSql -e "$sql" | sed '1d'

      $runSql -e "$sql" | sed '1d' |
      while read item
      do
        tmp=$item
        q1=${tmp%% *}
        tmp=${tmp#* }
        q2=${tmp%% *}
        tmp=${tmp#* }
        q3=$tmp
        q4=$end_ga
        sql="use $db;insert into ${target_ga}(clustername,itemkey,itemvalue,timestamp) values('$q1','$q2','$q3','$q4');"
        $runSql -e "$sql"
      done
    fi
  done
}

processTable(){
  start_pt=$1
  now_pt=$2
  lev_pt=$3
  table_pt=$4
  
  dropTmpTable "$table_pt"
  createTmpTable "$table_pt"
  sql=""
  tbs=`getAllTable $table_pt week`
  for line in $tbs
  do
    if [ ${#sql} != 0 ]; then
      sql="${sql} union all "
    fi
    sql="$sql select* from $line where timestamp between '$start_pt' and '$now_pt'"
  done
  sql="use $db;$sql;"
#此时的sql为查询所有周表的在start_pt和now_pt区间的数

#echo $sql
#执行sql，将输出行畔⑿慈雂雂ilute_$table_pt.txt,并该txt文件导入hadoop?database
  $runSql -e "$sql" | sed '1d' > dilute_$table_pt.txt
  $runImport hadoop dilute_$table_pt.txt
  rm -rf dilute_$table_pt.txt
#从级别1到级别lev_pt，将对对应级别的平均值插入到相应表
  for lev_i in `seq 1 $lev_pt`
  do
    log_out "-------------------------"
    start_i=`getStartTime "$now_pt" ${lev_i}`

    log_out "dilute level:$lev_pt  between:\'$start_i\' and \'$now_pt\'"

    getAvg "$start_i" "$now_pt" $table_pt $lev_i
    log_out "-------------------------"
  done
}


test_time="`date +%Y-%m-%d` `date +%H:%M:%S`"
test_time="2012-05-14 20:00:00"
test_time=$1

now_main="$test_time"

log_out "now:$now_main"
lev_main=`getLevel "$now_main"`
if [ ${lev_main} = 0 ]; then
  log_err "error."
  exit 1
fi
log_out "lev=$lev_main"

start_main=`getStartTime "$now_main" "$lev_main"`

if [ ${#start_main} = 0 ]; then
  exit 1
fi

log_out "startTime:${start_main}"

rollxxx "$now_main"

processTable "$start_main" "$now_main" "$lev_main" "cluster_data"
processTable "$start_main" "$now_main" "$lev_main" "user_data"




mango_song
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值