This morning; with some problems the Data Warehouse twote an image. The image is a graph with operations statistics from the Data Warehouse. If you look closely you can see the tweet is not without faults. The intention is to add the graph the first of the month, giving a picture of the Data Warehouse progression in terms of jobs and SQL queries. I hope to fix the errors and write a post how this automatic tweets work. For now you can study the current ITL workflow with it’s glitches.
<?xml version='1.0' encoding='UTF-8' standalone='yes'?>
<schedule mustcomplete='yes' logmsg='Publish last @hours hours job activity on Twitter @tooljn'>
<variant name='hours' default='24' cmnt='runtime parameter'/>
<tag name='includeGraph' value='(date("j") == 1)'/> <!-- boolean expression used below (1st day in month) -->
<job name='crtSQLMsg' type='sql'>
<tag name='YNOW' value="subtime(now(),'@hours:00:00')" cmnt='The last @hours hours'/>
<sql>
use OPS;
select STAT.queries as 'queries', 'queries run, and'
,sum(UTABS.jobs) as jobs,'jobs started since',@YNOW as YTIME
, sum(UTABS.success) as success,'success,', sum(UTABS.failure) as failure,'failed,'
, jobs -(success + failure) as running, 'executing.'
from (
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM OPS.`job`
WHERE started > @YNOW
union all
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM CPD_OPS.`job`
WHERE started > @YNOW
union all
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM CAKE_OPS.`job`
WHERE started > @YNOW
union all
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM CTO_OPS.`job`
WHERE started > @YNOW
union all
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM FUJI_OPS.`job`
WHERE started > @YNOW
union all
SELECT count(*) jobs, sum(if(result=1,1,0)) as success,sum(if(result=1,0,1)) as failure
FROM BLM_OPS.`job`
WHERE started > @YNOW
) as UTABS join
(select sum(queries) as queries from OPS.mysqlstat01 WHERE time > @YNOW) as STAT
;
</sql>
<sqlconverter name='sqlconverter_CSV.php' target='report0' delim='space' enclosed='' headers='no'/>
</job>
<job name='monthlyGraph' type='dummy'>
<prereq type='boolean'>@includeGraph</prereq>
<job name='crtData01' type='sql'>
<sql name='BarChart_data'>
use test;
select A.Month,A.jobs as ACTA, B.jobs as CPD, C.jobs Cake, D.jobs CTO, E.jobs Fuji, F.jobs BLM
from
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) A left join
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM CPD_OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) B on B.YYMM = A.YYMM left join
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM CAKE_OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) C on C.YYMM = A.YYMM left join
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM CTO_OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) D on D.YYMM = A.YYMM left join
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM FUJI_OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) E on E.YYMM = A.YYMM left join
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM BLM_OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) F on F.YYMM = A.YYMM;
</sql>
<sql name='LineChart_data'>
select B.queries Mqueries
from
(SELECT date_format(started,'%Y%m') YYMM,date_format(started,'%M') Month, count(*) jobs
FROM OPS.`job`
where date(started) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(started,'%Y%m')) A left join
(select date_format(time,'%Y%m') YYMM,date_format(time,'%Y%m'), round(sum(queries) / 1000000) as queries
from OPS.mysqlstat01
where date(time) >date_format(CURRENT_DATE,'%Y%m01') - interval 1 YEAR
group by date_format(time,'%Y%m')) B on B.YYMM = A.YYMM
</sql>
<sqlconverter name='sqlconverter_splitcolumns.php' target='stem'/>
</job>
<job name='crtGraph01' type='script' pgm='pgraph04.php' >
<image name='@S_directory/dwstat.png' lh='1300x800' type='bar01' sdwhight='56' fontsize='13'
footer='Data Warehouse statistics - @MONTH'/>
<stackedbardata columns='@J_crtData01/stem0' axisname='Jobs' series='column.Month' displayvalues='yes'>
<labels array='@J_crtData/report1'/>
</stackedbardata>
<linedata columns='@J_crtData01/stem1'/>
<arrowlabel center='1175,52' color='blue' label='@LASTMONTH' length='40' angle='135'/>
</job>
</job>
<job name='twittaMsg' include='twitter.xml'>
<tag name='MESSAGE' file='@J_crtSQLMsg/report0.CSV'/>
<tag name='TWEET' value='The #DataWarehouse @MESSAGE'/>
<tag name='image'><function>return @includeGraph ? '@J_monthlyGraph/dwstat.png' : '';</function></tag>
</job>
</schedule>
|
No comments:
Post a Comment