OUTDATED - Exome/WGS Autopush has been refactored. New automated tests are being developed.

Motivation

The RQC metrics for hybrid selection are not accurate enough for real-time lab troubleshooting so they need the "cloud" results. Exomes are scaling up dramatically and manual review and "push-to-cloud" are not feasible anymore. We need an automated "autopush" script to do this for us.

How it works

As any other ETL, Tiger framework keeps track of how far Autopush has advanced in terms of rapidqc_agg.uploaded_date. So every time Autopush wakes up it checks the samples aggregated by RQC since the last time (so called "delta"). In reality it looks up to 1 day further back to address potential latency. In each run the following steps are performed

Prepare list of "already-pushed" samples by looking up in SAW table, requestType=RequestFinalAggregation
Look into rapidqc_agg for exome samples which
1. have fingerprint_lod>0 AND
2. have not been already-pushed (by checking in the list from step 1)
Submit requestType=RequestFinalAggregation to SampleAnalyticsWorkflow(SAW) for these samples

All business rules are encoded here: SEQPROD database → ANALYTICSETL schema → ETL package → GET_AUTOPUSH stored function

Environments

AutoPushDev ETL partners with sampleAnalyticsWorkflowTest. AutoPushProd ETL partners with sampleAnalyticsWorkflow.

Both versions look into rapidqc_agg table though.

Unix wrapper

It allows Autopush to be called from Unix. Following example calls AutopushDev for samples uploaded between 0 and 2pm on May-11-2019

/home/unix/analytics/TigerETL3/runEtlAgent.sh Task agentName=analytics.tiger.agents.SAW.AutoPushDev db=cognos task=etlObject 'delta=MillisDelta.manual(2019-May-11 00:00:00,2019-May-11 14:00:00)'

Test Plan

Delete all "autopush" records from SAW test table

Delete all PushToCloud-requests from SAW-Test table for samples aggregated between 15-mar-2019@16:30:00 and 15-mar-2019@20:00:00

DELETE FROM analytics.sample_analytics_workflow_test a 
WHERE a.request_type=1 
	AND (a.pdo,a.pdo_sample) IN (
	SELECT m.pdo,m.pdo_sample
	FROM cognos.rapidqc_agg m
	WHERE m.data_type='Exome' AND m.uploaded_date>=TO_DATE('15-mar-2019 16:30:00','dd-MON-yyyy HH24:MI:SS') AND m.uploaded_date<TO_DATE('15-mar-2019 20:00:00','dd-MON-yyyy HH24:MI:SS')
);
COMMIT;

Make sure there are 626 samples ready to be autopushed

SELECT m.fingerprint_lod, m.pdo,m.pdo_sample
FROM cognos.rapidqc_agg m
WHERE m.data_type='Exome' AND m.uploaded_date>=TO_DATE('15-mar-2019 16:30:00','dd-MON-yyyy HH24:MI:SS') AND m.uploaded_date<TO_DATE('15-mar-2019 20:00:00','dd-MON-yyyy HH24:MI:SS')
AND m.pdo IS NOT NULL
ORDER BY 1 desc

0=fingerprint_lod: SM-I3844, SM-I2Q8W

0<fingerprint_lod: SM-I3797, SM-I2P3C, SM-I371P, SM-I2NWY, ...

Manually run AutopushDev
1. ssh to "analytics" server
2. run AutopushDev targeting samples uploaded between 2019-Mar-15@16:30:00 and 2019-Mar-15@20:00:00
```
/home/unix/analytics/TigerETL3/runEtlAgent.sh Task agentName=analytics.tiger.agents.SAW.AutoPushDev db=cognos task=etlObject 'delta=MillisDelta.manual(2019-Mar-15 16:30:00,2019-Mar-15 20:00:00)'
```
  there should be 624 samples in period specified (2 samples excluded due to 0=fingerprint_lod)

Check that 0=fingerprinting_lod samples are properly excluded in SAW test table. (resp. 0<fingerprinting_lod samples are properly included in SAW test table)

SELECT * FROM analytics.sample_analytics_workflow_test a WHERE a.pdo_sample IN (
	'SM-I3844','SM-I2Q8W',	-- 0=fingerprint_lod
	'SM-I3797','SM-I2P3C','SM-I371P','SM-I2NWY'	-- 0<fingerprint_lod
)

Check that appropriate messages are posted to JMS-dev queue.
1. https://picard-jms-dev.broadinstitute.org:8161/admin/browse.jsp?JMSDestination=broad.pushtocloud.analytics
Check that given sample is pushed only once
1. Manually run AutopushDev again using the very same time-period (step 2 above)
2. Check in ETL Dashboard that no samples have been pushed

Browser not supported