Versions Compared

Version Old Version 1 New Version Current
Changes made by

Mariela Mihaleva

Mariela Mihaleva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In rare cases when Mercury ETL stalls and no data enters MDWH, it is important for Analytics DWH to catch up after MDWH is up-to-date. The most affected Analytics ETLs are those using events , QC metrics and library data.

...

Refreshing all other ETLs

  • Most of the ETLs need to have their property key moved back to a date / timestamp close to the time of the outage.

...

  • Login to SQL Navigator as COGNOS.

  • Then, run the agent command in Linus shell on Analytics server.

  1. CovidPodSamples

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.CovidPodSamples';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task task=etlObject agentName=analytics.tiger.agents.CovidPodSamples db=cognos  'task=sqlScript(fileName=Covid.conf,fieldName=CovidPodSamples,deltaInjection=text)' 'delta=MillisDelta.loadFromDb(2h,0h)'

  2. CovidQpcr - this ETL is being run manually because one of the queries tends to be very slow with large amounts of data. The query is in process of optimization.

  3. RgAncestry

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.RgAncestry';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task agentName=analytics.tiger.agents.RgAncestry db=cognos 'task=sqlScript(fileName=SqlRepository.conf,fieldName=RgAncestry)' 'delta=MillisDelta.loadFromDb(1h,0h)'

  4. LibraryQpcrEtl

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.LibraryQpcrEtl';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task agentName=analytics.tiger.agents.LibraryQpcrEtl db=cognos 'task=sqlScript(fileName=SqlRepository.conf,fieldName=LibraryQpcr)' 'delta=MillisDelta.loadFromDb(1d,0d)'

  5. LibraryAncestry

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.LibraryAncestry';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task agentName=analytics.tiger.agents.LibraryAncestry db=cognos 'task=sqlScript(fileName=SqlRepository.conf,fieldName=LibraryAncestry)' 'delta=MillisDelta.loadFromDb(1d,0d)'

  6. SampleQc

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.SampleQc';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task agentName=analytics.tiger.agents.SampleQc db=cognos 'task=sqlScript(fileName=SqlRepository.conf,fieldName=SampleQc)' 'delta=MillisDelta.loadFromDb(1d,0d)'

  7. Extractions

    1. Set the property key

      Code Block
      languagesql
      UPDATE etl_property 
SET VALUE = '2022-Jun-3 16:00:00'
WHERE KEY = 'analytics.tiger.agents.Extractions';
COMMIT;

    2. Run the agent

      Code Block
       $RUN3 Task task=etlObject agentName=analytics.tiger.agents.Extractions db=cognos  'task=sqlScript(fileName=SqlRepository.conf,fieldName=Extractions)' 'delta=MillisDelta.loadFromDb(2h,0h)'

  8. BspPlatingRequest - This ETL is being run from BSP for backfilling from Mercury we only need to refresh the Mercury plating part. Run the query bellow when connected to seqprod as cognos.

    Code Block
    languagesql
    MERGE INTO bsp_plated_samples s
USING (
SELECT
    plated_sample_id,
    pdo_sample_id,
    plated_sample_lsid,
    pdo,
    work_request_item_id,
    work_request_id,
    requestor,
    plating_task_completed_on,
    concentration,
    volume,
    aliquot_type,
    plating_issues,
    work_request_item_type,
    position

FROM (
    SELECT
        ef.lcset_sample_name plated_sample_id,
        ef.sample_name pdo_sample_id,
        s.sample_lsid plated_sample_lsid,
        pdo.jira_ticket_key pdo,
        -1 work_request_item_id,
        -1 work_request_id,
        NULL requestor,
        ef.event_date plating_task_completed_on,
        nvl(m.quant_value, s.concentration) concentration,
        NULL volume,
        NULL aliquot_type,
        NULL plating_issues,
     CASE
        WHEN ef.lab_event_type =  'InfiniumAmplification' OR instr(ef.batch_name,  'ARRAY')>0 THEN 'Mercury Array Import'
        WHEN instr(ef.batch_name,  'LCSET')>0 THEN  'Mercury Seq Import'
     ELSE ''
     END  work_request_item_type,
  ef.position,
    dense_rank () over (PARTITION BY ef.lcset_sample_name, ef.sample_name, ef.product_order_id ORDER BY decode(ef.position, NULL, 2, 1), nvl(m.run_date, SYSDATE -1000) DESC, ef.lab_event_id DESC) myrank
    FROM mercurydw.event_fact ef
    JOIN mercurydw.product_order pdo                      ON pdo.product_order_id = ef.product_order_id
    JOIN mercurydw.product p ON p.product_id = pdo.product_id     AND p.part_number NOT LIKE '%CLA%'
    JOIN analytics.bsp_sample s  ON     s.sample_barcode = ef.lcset_sample_name
    LEFT JOIN mercurydw.lab_metric m ON m.lab_vessel_id = ef.lab_vessel_id AND m.quant_type IN ( 'PLATING_PICO', 'SHEARING_PICO')
    WHERE ef.lab_event_type in ( 'SampleImport', 'InfiniumAmplification')  --, 'ShearingTransfer')
        AND ef.etl_date>= '3-jun-2022'
        AND ef.etl_date < '9-jun-2022'
        AND instr(ef.batch_name,  'LCSET') + instr(ef.batch_name, 'ARRAY')>0
            AND ef.sample_name not in ('SM-BZT6J')   -- violates PK as per RPT-3824
        AND ef.batch_name not in ( 'LCSET-12608',   -- PK violation, GPLIM-5346
        'LCSET-10621', 'ARRAY-CO-6991960')  -- PK violation GPLIM-6553
        
        and ef.sample_name not IN ('SM-JFV8J',  'SM-JFV87')  -- PK violation RPT-6104
) a
WHERE myrank =1
) DELTA
ON (s.plated_sample_id = DELTA.plated_sample_id
AND s.pdo_sample_id = DELTA.pdo_sample_id
AND s.pdo = DELTA.pdo )

WHEN NOT MATCHED THEN INSERT VALUES (
  DELTA.plated_sample_id,
  DELTA.pdo_sample_id,
  DELTA.plated_sample_lsid,
  DELTA.pdo,
  DELTA.work_request_item_id,
  DELTA.work_request_id,
  DELTA.requestor,
  DELTA.plating_task_completed_on,
  DELTA.concentration,
  DELTA.volume,
  DELTA.aliquot_type,
  DELTA.plating_issues,
  DELTA.work_request_item_type,
  SYSDATE,
  DELTA.position
)
WHEN MATCHED THEN UPDATE SET 
  s.work_request_item_id    = DELTA.work_request_item_id,
  s.work_request_id         = DELTA.work_request_id,
  s.requestor               = DELTA.requestor,
  s.plating_task_completed_on = DELTA.plating_task_completed_on,
  s.concentration           = DELTA.concentration,
  s.volume                  = DELTA.volume,
  s.aliquot_type            = DELTA.aliquot_type,
  s.plating_issues          = DELTA.plating_issues,
  s.work_request_item_type  = DELTA.work_request_item_type,
  s.TIMESTAMP               = SYSDATE,
  s.position                = DELTA.position

...