信息流zt_bin脚本

bin脚本主要是mysql建表语句和上端数据源同步

一 mysql建表的初始化

建表语句如下：

#!/usr/bin/env bash
base=$(dirname $0)
mysqlConnTo=$1
database=$2
echo "1:$1"
echo "2:$2"
mysql $mysqlConnTo -e "create database if not exists $database ;"
mysql $mysqlConnTo -e "create database if not exists $database ;"
mysql $mysqlConnTo -D$database < $base/init_table/init_**_table.sql
mysql $mysqlConnTo -D$database < $base/init_table/init_zt**_table.sql
mysql $mysqlConnTo -D$database < $base/init_table/init_other_table.sql

建的表参考如下：

-- SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for wwwwdata
-- ----------------------------
-- DROP TABLE IF EXISTS wwwwdata;
CREATE TABLE if not exists `wwwwdata` (
  `date` int(11) NOT NULL DEFAULT '0' COMMENT '日期，eg:20160618',
  `pin_id` int(100) NOT NULL DEFAULT '0' COMMENT '广告主PInID',
  `mobile_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0：PC，1：无线',
  `ad_traffic_group` tinyint(4) NOT NULL DEFAULT '0' COMMENT '流量来源',
  `loc` tinyint(4) NOT NULL DEFAULT '0' COMMENT '站内外，1：站内，2：站外',
  `ad_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '广告类型，1：图片',
  `ad_billing_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '计费类型，0：CPC',
  `ad_business_type` int(11) NOT NULL DEFAULT '0' COMMENT '新增字段，产品类型',
  `impressions` bigint(20) NOT NULL DEFAULT '0' COMMENT '展现量',
  `clicks` bigint(20) NOT NULL DEFAULT '0' COMMENT '点击量',
  `cost` bigint(20) NOT NULL DEFAULT '0' COMMENT '消耗',
  PRIMARY KEY (`pin_id`,`date`,`mobile_type`,`ad_traffic_group`,`loc`,`ad_type`,`ad_billing_type`,`ad_business_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE if not exists `city` (
  `province` varchar(50) NOT NULL COMMENT '省名称' ,
  `city` varchar(50) NOT NULL COMMENT '市名称',
  `id` varchar(50) NOT NULL  primary key COMMENT '省市id',
  `c_id` varchar(50) NOT NULL COMMENT '数据平台内部表示，此字段暂时不用'
  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='city表';

二 数据同步

每3分钟同步一次

*/3 * * * * sh wwww/bin/schedule_timeout.sh >> wwww/log/timeout_info.log 2>&1 &

DT=`date  +%Y-%m-%d`
echo $DT
base=$(dirname $0)
sh $base/sync_data.sh "-u -h -P -p" dsp  "-u -h -P -p"  ad_data_zt "-u-h -P" ad_data "-h -P -u -p" dbp_report $DT

sync_data.sh如下：

#!/usr/bin/env bash
base=$(dirname $0)
mysqlConnFrom1=$1
db1=$2

mysqlConnFrom2=$3
db2=$4

mysqlConnFrom3=$5
db3=$6

mysqlConnTo=$7
targetdb=$8

DT=$9
DT_int=`date --date "$9" +%Y%m%d`
#mysqlConnFrom="-h  -P -u -p"
#db1="test"
#mysqlConnTo="-h  -P -u -p"
#db2="dbp"

sh $base/sync_data/sync_zt4_table.sh "$mysqlConnFrom1" $db1 "$mysqlConnTo" $targetdb "(created_time>=CONCAT('$DT', ' 00:00:00') and created_time<=CONCAT(date_sub('$DT',interval -1 day), ' 00:00:00')) or (modified_time>=CONCAT('$DT', ' 00:00:00') and modified_time<=CONCAT(date_sub('$DT',interval -1 day), ' 00:00:00'))"

sh $base/sync_data/sync_zt1_table.sh "$mysqlConnFrom2" $db2 "$mysqlConnTo" $targetdb "date='$DT_int'"

#sh $base/sync_data/sync_zt2_table.sh "$mysqlConnFrom2" $db2 "$mysqlConnTo" $targetdb "order_day='$DT_int'"

sh $base/sync_data/sync_zt3_table.sh "$mysqlConnFrom3" $db3 "$mysqlConnTo" $targetdb "business_type=4" $DT_int

sync_zt2_table.sh的脚本如下：

#!/usr/bin/env bash
base=$(dirname $0)
mysqlConnFrom=$1
db1=$2
mysqlConnTo=$3
db2=$4
where=$5
echo "1: $1"
echo "2: $2"
echo "3: $3"
echo "4: $4"
echo "5: $5"

#db1="test"

#db2="dbp_report"
#where="date='$DT'"


tables=( ztad1data ztad2data <span style="font-family:Arial, Helvetica, sans-serif;">)</span>

for var in ${tables[@] };
do
    running="sh $base/func_sync_table.sh \"$mysqlConnFrom\" $db1 $var \"$mysqlConnTo\" $db2 \"$where\""
    echo $running
    sh $base/func_sync_table.sh "$mysqlConnFrom" $db1 $var "$mysqlConnTo" $db2 $var  "$where"
done

wait