Automation Issues/Improvements (version 2)

[KELSEYDOWLING7]

Creating a new issue based on the original issue #28

Now ranked by Priority Level

These reports run locally in R with no issues but have various automation issues in GCP.

Issues

• Weekly Biospecimen logs- says success but doesn't upload the file. It also mentions files in the biospecimen repo that have nothing to do with this report (Sanford_Clincal_Locations.csv)

• logs :https://console.cloud.google.com/run/detail/us-central1/ccc-biospecimen-metrics/observability/logs?inv=1&invt=Abw2Ag&project=nih-nci-dceg-connect-prod-6d04

• scheduler: ccc-biospecimen_logs

• R code : https://github.com/Analyticsphere/ccc_biospecimen_metrics_gcp_pipeline/blob/main/Biospecimen_logs.R

• Weekly Module Metrics (GCP job ccc-weekly-module-metrics)

• Jake, Michelle mention you started working on this. It says successful but the report that's posting doesn't match the R script at all. It looks like it's somehow a much, much older version of the report instead of the current code. Unsure how that's possible. (I moved the outputs out of the ALL folder to avoid confusion)

• Cancer Screening History-- fixed

• Weekly Operations CSV - ccc-weekly-operations-csvs

• logs:https://console.cloud.google.com/run/detail/us-central1/ccc-weekly-report-api/observability/logs?inv=1&invt=Abw2Ag&project=nih-nci-dceg-connect-prod-6d04&supportedpurview=project

• last functioning log_info message: log_info("Creating additional variables for the logs")

• Weekly Operations Report - ccc-weekly-operations-report
• Report fails in GCP during data pull at the very beginning. No issues in R
• R code: https://github.com/Analyticsphere/weekly_ccc_report_gcp_pipeline/blob/main/prod/Weekly_Operations_Report_Prod.Rmd
• Logs: https://console.cloud.google.com/run/detail/us-central1/ccc-weekly-report-api/observability/logs?inv=1&project=nih-nci-dceg-connect-prod-6d04

• (NEW) Recruitment Custom QC- scheduled job ccc-recruitment-qc-metrics says successful in GCP but doesn't post to Box. It's also taking only one minute to supposedly render in GCP however the data pull is massive and takes typically about 10 minutes in Rstudio, so I have a hard time believing that that is happening correctly
• R code: https://github.com/Analyticsphere/ccc_module_metrics_gcp_pipeline/blob/main/Rectruitment%20Derived%20Variable%20QC.Rmd
• logs: https://console.cloud.google.com/run/detail/us-central1/ccc-module-metrics-api/observability/logs?inv=1&project=nih-nci-dceg-connect-prod-6d04

• (NEW) Biospecimen Prod Files (the two remaining csvs that do not have their own scheduled queries) -
  ccc-weekly-biospecimen-metrics-csvs

• (NEW) Adhoc refusal and withdrawal report: I've added a scheduled job adhoc_refwd_csv and confirmed that adhoc_refwd_csv matches and was added to the api.R file in that repo (https://github.com/Analyticsphere/weekly_ccc_report_gcp_pipeline/tree/main/prod) . There's no clear error in the logs, no red error or yellow warning so I'm confused on where it's failing. Confirmed nothing was uploaded to Box
• (NEW) Monthly Data Destruction ccc-data-destruction-qc-metrics
• This started in February but there's been no code change. Only added a "Monthly report" comment on 2/3/26
• R code: https://github.com/Analyticsphere/ccc_module_metrics_gcp_pipeline/blob/main/Data%20Destruction%20CSV%20Output.R

• Module 1 Monthly Metrics (GCP job ccc-module1-statistics) - error messages below ( https://console.cloud.google.com/run/detail/us-central1/ccc-module-metrics-api/observability/logs?project=nih-nci-dceg-connect-prod-6d04 )

• Module 3 Monthly Metrics (GCP job ccc-module3-statistics) -- doesn't show any error messages, only a warning message so I'm unsure of why it's failing ( https://console.cloud.google.com/run/detail/us-central1/ccc-module-metrics-api/observability/logs?project=nih-nci-dceg-connect-prod-6d04 )

• RCA Metrics--fixed

Improvements

• Biospecimen Prod Files (GCP job ccc-weekly-biospecimen-metrics-csvs)
• The first two files generated in this code (flatBoxes and KitAssembly) are just BQ queries. I'd like to take them out of the R code, and have them run as scheduled queries every Monday at 11:30 with the outputs posted to box (with the file name maintained) to this folder below
• Code: https://github.com/Analyticsphere/ccc_biospecimen_metrics_gcp_pipeline/blob/main/Weekly%20Biospecimen%20CSV%20Outputs.R
• Box Folder: https://nih.app.box.com/folder/221280601453
• Output: Formatted_prod_flatBoxes_{currentDate}boxfolder{boxfolder}.xlsx , Connect_prod_KitAssembly_Table_{currentDate}boxfolder{boxfolder}.xlsx ,
  Connect_prod_recr_veriBiospe_Formats_{currentDate}boxfolder{boxfolder}.xlsx ,
  Connect_prod_Biospe_Formats_{currentDate}boxfolder{boxfolder}.xlsx

[jacobmpeters]

@KELSEYDOWLING7 Regarding the Weekly Module Metrics, it appears to have run successfully today, but when I check the Cloud Run logs, it says both "Latex failed to compile..." and "Uploaded file...". Is it just missing some sections or something? I'll check the .tex and .log files..

[KELSEYDOWLING7]

It did run successfully but the output doesn't match R. From a quick glance at the output, the TOC is missing and it's cut off at table 2.1 instead of going through to section 9

[jacobmpeters]

Ok. I think it's just truncating it where the rendering fails. I'll try to figure out why the rendering is failing after table 2.1...

[jacobmpeters]

Hi Kelsey. The problem with the LaTeX document appears to be unescaped % symbols in tables. Let's work on a solution for this together.. We could write a function to automatically escape percent signs in dataframes before passing them into the table fuctions or we could play with the escape settings in the table function..

[KELSEYDOWLING7]

@jacobmpeters A function makes sense! What times work for you next week? Also can you tell where the problem is occurring by chance? I remember this being an issue we resolved/ran into in the past too.

@gloria-trivitt Nanci had started working on the Biospecimen prod file GCP scheduled queries for a few weeks before you left. I'm not sure if she left/transferred any of the notes she took on the bugs she ran into or if she mentioned any to you @jacobmpeters

[KELSEYDOWLING7]

Notes: An extra escape=FALSE was accidentally added to the kable function. Weekly Module Metrics problem resolved!

[jacobmpeters]

@KELSEYDOWLING7

I implemented the "flat boxes" and the "kit assembly" CSV deliveries as scheduled queries. They are both currently disabled. I'd like to test them out with you before enabling them.

Btw, you might want to check this box folder (https://nih.app.box.com/folder/221280601453) for any junk files from 2/8. I did a couple of tests, but I don't have access to these folders to delete any unwanted deliveries.

links to scheduled queries:

• flat boxes
• kit assembly

[KELSEYDOWLING7]

@jacobmpeters Great!! These look perfect to me but I'll have the bio team review to be sure. Is it possible to make them xlxs instead of csv for manipulation purposes on Box? And any time this week from 4-5 works for me to meet!

[jacobmpeters]

@KELSEYDOWLING7 No, BigQuery unfortunately does not allow you to export as xlsx, just csv, json, parquet or avro.

I'll put something on your calendar.

[KELSEYDOWLING7]

@jacobmpeters Confirmed with Erin that the outputs look good and that csv is not a problem! Looks like you double booked for today's call so we can discuss next week. But feel free to schedule these for Monday's at noon if you get the time unless you still want to meet beforehand

[jacobmpeters]

@KELSEYDOWLING7 Just updating the issue. Today we scheduled those two queries for Monday at noon.

My next task is to understand why the remaining CSVs in that report are not delivering and if it's a file size issue, I'll try using gunzip compression before sending them to GCP.