ohdsi-studies / covid19predictionstudies Goto Github PK

Hi,
While executing these studies I am getting attached SQL syntax error. Error report is also attached.
Database: PostgreSQL 12.2
CDM: 5.3
R Studio: 2022.12.0 Build 353
R: 4.2.2

errorReportSql.txt

DBMS:
impala

Error:
java.sql.SQLException: [Cloudera]ImpalaJDBCDriver ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Possible loss of precision for target table 'rwd_p_omop_truven_medicaid_results2.e0pr395scov_ref'.
Expression 'CAST(concat('drug_era group during day -365 through -1 days relative to index: ', (CASE WHEN (concept.concept_name IS NULL) THEN 'Unknown concept' ELSE concept.concept_name END)) AS STRING)' (type: STRING) would need to be cast to VARCHAR(512) for column 'covariate_name'
), Query: INSERT INTO rwd_p_omop_truven_medicaid_results2.e0pr395scov_ref (covariate_id,covariate_name,analysis_id,concept_id) SELECT t1.covariate_id, CAST( CONCAT('drug_era group during day -365 through -1 days relative to index: ', (CASE WHEN (concept.concept_name IS NULL) THEN 'Unknown concept' ELSE concept.concept_name END)) AS string ) as covariate_name, 410 as analysis_id, CAST( ((t1.covariate_id - 410) / 1000) AS int ) as concept_id FROM (SELECT DISTINCT e0pr395scov_1.covariate_id FROM rwd_p_omop_truven_medicaid_results2.e0pr395scov_1) AS t1 LEFT JOIN rwd_p_omop_truven_medicaid_cdm2.concept ON (concept.concept_id = CAST( ((t1.covariate_id - 410) / 1000) AS int )).

SQL:
INSERT INTO rwd_p_omop_truven_medicaid_results2.e0pr395scov_ref (
covariate_id,
covariate_name,
analysis_id,
concept_id
)
SELECT covariate_id,

CAST(CONCAT('drug_era group during day -365 through -1 days relative to index: ', CASE WHEN concept_name IS NULL THEN 'Unknown concept' ELSE concept_name END) AS VARCHAR(512)) AS covariate_name,


410 AS analysis_id,
CAST((covariate_id - 410) / 1000 AS INT) AS concept_id

FROM (
SELECT DISTINCT covariate_id
FROM rwd_p_omop_truven_medicaid_results2.e0pr395scov_1
) t1
LEFT JOIN rwd_p_omop_truven_medicaid_cdm2.concept
ON concept_id = CAST((covariate_id - 410) / 1000 AS INT)

R version:
R version 3.6.0 (2019-04-26)

Platform:
x86_64-redhat-linux-gnu

Attached base packages:

• stats
• graphics
• grDevices
• utils
• datasets
• methods
• base

Other attached packages:

• HospitalizationInSymptomaticPatientsValidation (0.0.3)
• Covid19CohortEvaluation (0.0.1)
• DatabaseConnector (2.4.3)

**Time it took to run- 9 hours / R packages are adequately tuned?
Can we generate table/column statistics for the respective tables during each ETL?
**

2020-04-01 20:31:23 [Main thread] WARN DatabaseConnector getJbcDriverSingleton the condition has length > 1 and only the first element will be used
2020-04-01 20:48:58 [Main thread] WARN DatabaseConnector getJbcDriverSingleton the condition has length > 1 and only the first element will be used
2020-04-01 20:48:58 [Main thread] WARN DatabaseConnector getJbcDriverSingleton only the first element is used as variable name
2020-04-01 20:49:17 [Main thread] INFO HospitalizationInSymptomaticPatientsValidation execute Creating Cohorts
2020-04-01 20:49:17 [Main thread] WARN HospitalizationInSymptomaticPatientsValidation execute Error 'cannot open the connection' when writing log to file '/app/sas/users/kandupl/Lakshmi_Kandukuri/2020/2020-03-23-005_ttt_covid-19_response/Data/MCID_2/log.txt. Removing file appender from logger.
2020-04-01 20:49:17 [Main thread] WARN DatabaseConnector getJbcDriverSingleton the condition has length > 1 and only the first element will be used
2020-04-01 20:49:17 [Main thread] WARN SqlRender translate Table name ' rwd_p_omop_truven_medicaid_results2' is too long. Table names should be shorter than 30 characters to prevent Oracle from crashing.
2020-04-02 01:55:15 [Main thread] INFO HospitalizationInSymptomaticPatientsValidation createCohorts Counting cohorts
2020-04-02 01:55:16 [Main thread] INFO HospitalizationInSymptomaticPatientsValidation execute Validating Models
2020-04-02 01:55:17 [Main thread] INFO PatientLevelPrediction evaluateMultiplePlp Evaluating model in /homes/kandupl/R/x86_64-redhat-linux-gnu-library/3.6/HospitalizationInSymptomaticPatientsValidation/plp_models/Analysis_2
2020-04-02 01:55:17 [Main thread] INFO PatientLevelPrediction evaluateMultiplePlp plpResult found in /homes/kandupl/R/x86_64-redhat-linux-gnu-library/3.6/HospitalizationInSymptomaticPatientsValidation/plp_models/Analysis_2
2020-04-02 01:55:17 [Main thread] WARN DatabaseConnector getJbcDriverSingleton the condition has length > 1 and only the first element will be used
2020-04-02 01:55:18 [Main thread] WARN DatabaseConnector getJbcDriverSingleton the condition has length > 1 and only the first element will be used
2020-04-02 01:55:56 [Main thread] INFO PatientLevelPrediction 3 Error: Error: Error executing SQL: java.sql.SQLException: [Cloudera]ImpalaJDBCDriver ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Possible loss of precision for target table 'rwd_p_omop_truven_medicaid_results2.e0pr395scov_ref'. Expression 'CAST(concat('drug_era group during day -365 through -1 days relative to index: ', (CASE WHEN (concept.concept_name IS NULL) THEN 'Unknown concept' ELSE concept.concept_name END)) AS STRING)' (type: STRING) would need to be cast to VARCHAR(512) for column 'covariate_name' ), Query: INSERT INTO rwd_p_omop_truven_medicaid_results2.e0pr395scov_ref (covariate_id,covariate_name,analysis_id,concept_id) SELECT t1.covariate_id, CAST( CONCAT('drug_era group during day -365 through -1 days relative to index: ', (CASE WHEN (concept.concept_name IS NULL) THEN 'Unknown concept' ELSE concept.concept_name END)) AS string ) as covariate_name, 410 as analysis_id, CAST( ((t1.covariate_id - 410) / 1000) AS int ) as concept_id FROM (SELECT DISTINCT e0pr395scov_1.covariate_id FROM rwd_p_omop_truven_medicaid_results2.e0pr395scov_1) AS t1 LEFT JOIN rwd_p_omop_truven_medicaid_cdm2.concept ON (concept.concept_id = CAST( ((t1.covariate_id - 410) / 1000) AS int )). An error report has been created at /app/sas/users/kandupl/Lakshmi_Kandukuri/2020/2020-03-23-005_ttt_covid-19_response/Code/errorReport.txt

Thanks
Lakshmi

I ran into this problem when I was executing.

2020-06-04 19:24:14 [Main thread] TRACE PatientLevelPrediction Running setting 2
2020-06-04 19:24:15 [Main thread] TRACE PatientLevelPrediction 3 Error with getPlpData:Error: Error executing SQL: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object An error report has been created at /root/errorReport.txt
2020-06-04 19:24:15 [Main thread] TRACE PatientLevelPrediction Done plpData.
2020-06-04 19:24:15 [Main thread] INFO PatientLevelPrediction No plpData - probably empty cohort issue
2020-06-04 19:24:15 [Main thread] TRACE PatientLevelPrediction Setting population settings for setting 2
2020-06-04 19:24:15 [Main thread] WARN PatientLevelPrediction addExposureDaysToStart is depreciated - please use startAnchor instead
2020-06-04 19:24:15 [Main thread] WARN PatientLevelPrediction addExposureDaysToEnd is depreciated - please use endAnchor instead
2020-06-04 19:24:15 [Main thread] TRACE PatientLevelPrediction 3 Error with pop:Error in class(plpData) %in% c("plpData.libsvm", "plpData.coo", "plpData"): argument "plpData" is missing, with no default

I'm pretty sure the study has progressed beyond "started" ;-)

Maybe also add the study end date, link to paper, and link to Shiny app (if appropriate)?