Details
-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Fix Version/s: None
-
Component/s: squash
-
Labels:None
-
Story Points:1.4
-
Epic Link:
-
Team:SQuaRE
Description
I have reviewed the queries used in the existing dashboards for AP and DRP. In order to make them work with the new data model in DM-18103, the following changes were required.
1. Rename ci_dataset to dataset
2. Rename filter_name to filter
3. The template variable for :Dataset: in DRP must be a Custom Meta Query so that we can filter by the execution environment env_name = 'jenkins' and return only the datasets used in CI.
SHOW TAG VALUES ON "squash-demo" FROM validate_drp WITH KEY= "dataset" WHERE env_name = 'jenkins'
|
|
As a result of that, we must single quote the template variable ':Dataset:' in all of the other DRP dashboard queries.
Note: I would recommend to always use Custom Meta Query to consistently single quote the template variables.
4. With the new data model, metadata like RUN ID can be added as a field to the tables.
------
AP catalog metrics from CI
Template variables
:Dataset:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM "ap_pipe" WITH KEY = "dataset"
|
|
:visit:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM "ap_pipe" WITH KEY = "visit" WHERE "dataset" = :Dataset:
|
|
:ccd:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM "ap_association" WITH KEY IN ("ccd", "ccdnum", "detector") WHERE "dataset" = :Dataset: AND "visit" = ':visit:'
|
|
Total Unassociated DiaObjects
SELECT "ap_association.totalUnassociatedDiaObjects" AS " " FROM "squash-demo"."autogen"."ap_association" WHERE time > :dashboardTime: AND "ccdnum"='60' AND "visit"='411371' AND "dataset"=:Dataset:
|
|
Total Unassociated DiaObjects (table)
SELECT "ap_association.totalUnassociatedDiaObjects" AS "Total Unassociated DiaObject" FROM "squash-demo"."autogen"."ap_association" WHERE time > :dashboardTime: AND "ccdnum"='60' AND "visit"='411371'
|
|
New DiaObjects per CCD
SELECT "ap_association.numNewDiaObjects" AS " " FROM "squash-demo"."autogen"."ap_association" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "visit", "ccdnum"
|
|
Fraction of DiaObjects Updated per CCD
SELECT "ap_association.fracUpdatedDiaObjects" FROM "squash-prod"."autogen"."ap_association" WHERE time > :dashboardTime: AND "ci_dataset"=:Dataset: GROUP BY "visit", "ccdnum"
|
|
Ratio of DIASources to Direct Image Sources per CCD
SELECT "ip_diffim.fracDiaSourcesToSciSources" FROM "squash-prod"."autogen"."ip_diffim" WHERE time > :dashboardTime: AND "ci_dataset"=:Dataset: GROUP BY "visit", "ccdnum"
|
|
AP runtime metrics from CI
Template variables
:Dataset:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM "ap_pipe" WITH KEY = "visit" WHERE "dataset" = :Dataset:
|
|
:ccd:
|
|
|
SHOW TAG VALUES ON "squash-prod" FROM "ap_association" WITH KEY IN ("ccd", "ccdnum", "detector") WHERE "ci_dataset" = :Dataset: AND "visit" = ':visit:'
|
|
:visit:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM "ap_pipe" WITH KEY = "visit" WHERE "dataset" = :Dataset:
|
|
|
|
Overall Runtime per CCD
SELECT "ap_pipe.ApPipeTime" FROM "squash-demo"."autogen"."ap_pipe" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "visit", "ccdnum"
|
|
ApPipeTask Breakdown for visit=:visit:, ccd=:ccd:
SELECT "pipe_tasks.ProcessCcdTime", "pipe_tasks.ImageDifferenceTime" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "ccdnum"=':ccd:' AND "visit"=':visit:' AND "dataset"=:Dataset:
|
|
|
SELECT "ap_association.AssociationTime" FROM "squash-demo"."autogen"."ap_association" WHERE time > :dashboardTime: AND "ccdnum"=':ccd:' AND "visit"=':visit:' AND "dataset"=:Dataset:
|
|
ProcessCccd Runtime
SELECT "pipe_tasks.ProcessCcdTime" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "ccdnum", "visit"
|
|
Image Difference Runtime
SELECT "pipe_tasks.ImageDifferenceTime" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "ccdnum", "visit"
|
|
Association Runtime
SELECT ap_association.AssociationTime FROM "squash-demo"."autogen"."ap_association" WHERE time > :dashboardTime: AND "dataset" = :Dataset: GROUP BY "visit", "ccdnum"
|
|
Characterize Image Runtime
SELECT "pipe_tasks.CharacterizeImageTime" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "ccdnum", "visit"
|
|
Calibrate Runtime
SELECT "pipe_tasks.CalibrateTime" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "dataset"=:Dataset: GROUP BY "ccdnum", "visit"
|
|
Relative Contributions for visit=:visit:, ccd=:ccd:
SELECT "pipe_tasks.ProcessCcdTime"/("pipe_tasks.ProcessCcdTime"+"pipe_tasks.ImageDifferenceTime") as "ProcessCcdFraction", "pipe_tasks.ImageDifferenceTime"/("pipe_tasks.ProcessCcdTime"+"pipe_tasks.ImageDifferenceTime") as "ImageDifferenceFraction" FROM "squash-demo"."autogen"."pipe_tasks" WHERE time > :dashboardTime: AND "ccdnum"=':ccd:' AND "visit"=':visit:' AND "dataset"=:Dataset:
|
|
DRP metrics from CI
Template variables
:Dataset:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM validate_drp WITH KEY= "dataset" WHERE env_name = 'jenkins'
|
|
:Filter:
|
|
|
SHOW TAG VALUES ON "squash-demo" FROM validate_drp WITH KEY = "filter" WHERE "dataset" = ':Dataset:'
|
|
Astrometry
SELECT "validate_drp.AM1" AS "AM1" FROM "squash-demo"."autogen"."validate_drp" WHERE time > :dashboardTime: AND "dataset"=':Dataset:' AND "filter"=':Filter:' GROUP BY "filter"
|
|
|
SELECT "validate_drp.AM2" AS "AM2" FROM "squash-demo"."autogen"."validate_drp" WHERE time > :dashboardTime: AND "dataset"=':Dataset:' AND "filter"=':Filter:' GROUP BY "filter"
|
|
Photometry
SELECT "validate_drp.PA1" AS "PA1" FROM "squash-demo"."autogen"."validate_drp" WHERE time > :dashboardTime: AND "filter"=':Filter:' AND "dataset"=':Dataset:' GROUP BY "filter"
|
|
Image Quality
SELECT "validate_drp.TE1" AS "TE1" FROM "squash-demo"."autogen"."validate_drp" WHERE time > :dashboardTime: AND "filter"=':Filter:' AND "dataset"=':Dataset:' GROUP BY "filter"
|
|
Table
SELECT "validate_drp.AM1" AS "AM1", "validate_drp.AM2" AS "AM2", "validate_drp.PA1" AS "PA1", "validate_drp.TE1" AS "TE1", "run_id" FROM "squash-demo"."autogen"."validate_drp" WHERE time > :dashboardTime: AND "dataset"='validation_data_cfht' AND "filter"='r'
|
|
|
|
I have tested these queries against the squash-demo database and conclusion is that we there's no major issues in updating the dashboards onde we recreate the squash-prod database in
DM-18060.