Automation Issues Presenting in GCP but not in R

[KELSEYDOWLING7]

• Biospecimen Prod Files (ccc-weekly-biospecimen-metrics-csvs)
• Is this a size issue? The code finishes but nothing gets posted, and 3/4 of the csvs to be created come straight from a BQ query

• Recruitment Custom QC (ccc-recruitment-qc-metrics)

• Module 1 Summary Statistics (ccc-module1-statistics)
• Something to do with the Terminal?

• Module 2 Summary Statistics (ccc-module2-statistics)
• Failed at scheduled run but worked in manual run the next day. Cloudbuildyaml file has a concurrency of 1, so it shouldn’t have been a traffic issue
• Added max retry attempt of 1 instead of 0 so hopefully that will work next month
• Cancer Screening History
• Failed at scheduled run but worked in manual run the next day. Cloudbuildyaml file has a concurrency of 1, so it shouldn’t have been a traffic issue
• Added max retry attempt of 1 instead of 0 so hopefully that will work next month
• Data Destruction Updates (ccc-data-destruction-qc-metrics)
• Failed at scheduled run but worked in manual run the next day. Cloudbuildyaml file has a concurrency of 1, so it shouldn’t have been a traffic issue
• Added max retry attempt of 1 instead of 0 so hopefully that will work next month

[cooknaa]

I'm looking through the issue now. I'll have an idea of how to approach this by tomorrow's meeting.

[KELSEYDOWLING7]

Discussion notes from 9/4/25-

• The Biospecimen Prod files contain 3 BQ queries that directly create csv/xlsx tables without the need for R manipulation. These will be moved directly in BQ as scheduled queries. The 4th excel file code in R will be reviewed once that's done

• The Recruitment Custom QC, and likely the Data Destruction Updates, Cancer Screening History, and Module 2 reports, all have a python specific error, Nanci believe's this is likely stemming from the use of Plumbr API and will be testing the use of an alternative package to utilize python in R. This was found in the 'flask.py' error showing in the GCP logs

• Module 1 was a memory issue and this has now been resolved with the use of gc() functions in the RMD file!

• Data Destruction Updates, Cancer Screening History, and Module 2 reports will be retried in October, hoping the retry attempt will help as these will successfully run when a manual Force Run is done.

[KELSEYDOWLING7]

@cooknaa Only the Biospecimen Prod Files and the Recruitment QC remain! the others worked in automation this month.

10/8:
For testing the Biospecimen Prod files, I temporarily took out 3/4 of the output codes (We're testing those three as regular scheduled BQ queries to be directly outputted to Box) and the fourth output gets this error message in the 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

The Recruitment QC is showing this error message in the logs https://console.cloud.google.com/run/detail/us-central1/ccc-module-metrics-api/observability/logs?inv=1&invt=Abw2Ag&project=nih-nci-dceg-connect-prod-6d04 . Specifically it's failing in the chunk that pushes an excel file to Box. I've tried removing the reference to kable tables, confirming the currentDate is not an issue, ect. But I think this repo is just not setup yet to push excel files to Box.

`

46. If re-invitation date set, then re-invitation campaign type data should be sent

\FloatBarrier

reinvite_date_type <- partsbq %>%  filter(!is.na(d_439351436) & is.na(d_280021666)) %>%  
  mutate(Verif_Status = case_when(d_821247024==875007964 ~ 'Not yet verified',
                                  d_821247024==197316935 ~ 'Verified',
                                  d_821247024==219863910 ~ 'Cannot be verified',
                                  d_821247024==922622075 ~ 'Duplicate',
                                  d_821247024==160161595 ~ 'Outreach timed out')) %>% 
  select(Site, Connect_ID, token, d_454445267, d_430551721, Verif_Status, d_914594314, d_280021666, d_439351436) %>% 
  dplyr::rename('Date/Time Consented' = d_454445267,
                'Date/Time Verified' = d_914594314,
                'Re-Invitation Campaign Type' = d_280021666, 
                'Date/Time Re-Invited' = d_439351436,
                'Date/Time UP Submitted' =  d_430551721)

boxfolder = "194196493018"

#### This ifelse statement is failing in GCP. Testing if it runs when removed from the live code
# if (nrow(reinvite_date_type) < 50) {
#   knitr::kable(reinvite_date_type, longtable = TRUE) %>%
#     kable_styling(latex_options = "HOLD_position")
# } else {
  cat(paste0(
    "There are ", nrow(reinvite_date_type), " rows. This has been made into a separate XLXS file called \n",
    "'Biospecimen_QC_Rule46_Errors", currentDate, "_boxfolder_", boxfolder, ".xlsx' \n"
  ))
  
  openxlsx::write.xlsx(reinvite_date_type,glue("Recruitment_Derived_QC_Rule46_Errors{currentDate}_boxfolder_{boxfolder}.xlsx"),row.names = F,na="")
#}

[KELSEYDOWLING7]

The reason I say that abut the Recruitment QC is because that exact code structure works in the Biospecimen QC file in the Biospecimen Repo. For reference, that is this
GCP: ccc-biospecimen-custom-qc.
Code:https://github.com/Analyticsphere/ccc_biospecimen_metrics_gcp_pipeline/blob/main/Biospecimen_Custom_QC_Output.R

It runs and pushes excel files (specifically xlxs) to Box no problem

[cooknaa]

The issue may be that the excel file isn't being sent from GCP because the characters within the data frame are not UTF-8 characters. A simple conversion of the data frame prior to the exportation should cover this. Here is the code for it:

Example to re-encode a single dataframe df
df[] <- lapply(df, function(x) {
if(is.character(x)) iconv(x, to = "UTF-8", sub = "byte") else x
})

The data frame for the reinvite1 would need to be converted to this prior to its exportation out to box. The issue isn't within R since the code does run locally fine. The issue was within GCP itself with it's inability to handle data that is not in the UTF-8 format. If this doesn't work, we'll need to look at the issue within GCP again.

Several of the former libraries that were being used in GCP for the previous functions and workloads are no longer loading into GCP without error in new functions. Specifically, the boxtoken library for the new export function. I was working with Daniel on it prior to the shutdown, and I'm still looking into while he's out.

[KELSEYDOWLING7]

@cooknaa I tried that method but unfortunately it doesn't seem to resolve the problem. We get the same error message in the logs in the same chunk

I noticed that in the Biospecimen Repo, the Biospecimen QC excel file also has characters that aren't UTF-8 but that one still runs fine

[cooknaa]

@KELSEYDOWLING7. Ok. I'll keep digging. Its the fact GCP is the issue that is causing the headache.

[KELSEYDOWLING7]

@cooknaa This one is new. I did make a bunch of changes on Friday, but the code runs in R and this GCP error message isn't clear. Could you help troubleshoot?

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

Job: ccc-weekly-module-metrics

Code: https://github.com/Analyticsphere/ccc_module_metrics_gcp_pipeline/blob/main/CCC%20Weekly%20Module%20Metrics_RMD.Rmd

[cooknaa]

@KELSEYDOWLING7 . I'll look into this now.

[cooknaa]

@KELSEYDOWLING7 . So, there are multiple steps to correct this issue. We'll have to go down each one until it's fixed. Here's the first one. You need to update the tinytext library package. It's already in the code, but there is another way to type it is compared to what you have on Line 43. Here is what it should look like:

update.packages(ask = FALSE, checkBuilt = TRUE)
tinytex::tlmgr_update()

Put this in line 43's place and try to run it again. If the error persists, we'll have to implement the other recommended steps to resolve it.

[KELSEYDOWLING7]

Thanks! We did have some issues with auto updating packages in the past though, so I'm slightly hesitant to set the auto update. Is there a way to tell from the GCP error message when an outdated package is an issue?

Also would we expect to see tinytex here?

[cooknaa]

@KELSEYDOWLING7 . Line 42 is part of the way to identify it. But it's commented out in your code so it isn't checking. If it were paired with this line of code:

-- Check for outdated packages
outdated_packages <- old.packages()

if (!is.null(outdated_packages)) {
-- Log or alert about outdated packages
print("The following R packages are outdated:")
print(outdated_packages)
}

It would check if the list had any other older packages in it. We could update as necessary with whatever it returns.

To answer the question regarding the not showing up in your local R code. That line compares what is installed in your environment to what is offered for that package in the CRAN where the libraries are being pulled from. So, if it isn't showing up in your local environment, I would assume it is because your tinytext in your local machine is updated and doesn't need to be updated. However, the one being used by GCP isn't the most recent version, so it would be expected to populate in GCP when the code is ran.

[KELSEYDOWLING7]

Oh! Ok I didn't realize adding the line in R to update the packages would then update the package in GCP. Good to know! I'll add that now and push it to GitHub to see if that works

[KELSEYDOWLING7]

@cooknaa The report failed but these logs are not something I can follow https://console.cloud.google.com/run/detail/us-central1/ccc-module-metrics-api/observability/logs?inv=1&invt=Abw2Ag&project=nih-nci-dceg-connect-prod-6d04