Invalid operation: function datefromparts(integer, integer, integer) does not exist

[lhjohn]

When using a package created from the skeleton I get the following error:

An error occurred while generating cohortName = [HJ] Dementia Prediction - 50-79y - v2. Error: Error in `.createErrorReport()`:
! Error executing SQL:
java.sql.SQLException: [Amazon](500310) Invalid operation: function datefromparts(integer, integer, integer) does not exist 
Hint: No function matches the given name and argument types. You may need to add explicit type casts.;

Could redshift not be supporting datefromparts()?

Could this be related?

• translateSql to Redshift, to_date syntax SqlRender#34

[jreps]

Did this occur when generating the cohorts with the latest skeleton? Do you have the correct dbms specified?

[lhjohn]

It is weird, because I use the exact same settings for a package which was built using the branch issue242 from a few weeks ago and it works without problems, but with the latest master of SkeletonPredictionStudy it does not work anymore.

connectionDetails <- DatabaseConnector::createConnectionDetails(
        dbms = "redshift",                                                                    
        server = "ohda-XXX",
        port = 5439,
        user = "XXX",
        password = rstudioapi::askForPassword(),
        extraSettings="ssl=true&sslfactory=XXX",
        pathToDriver = "~/jdbcDrivers")

[jreps]

The latest skeleton is using cohortGenerator - maybe there is a missing sqlRender::translate() somewhere?

[jreps]

try running : debug(CohortGenerator::generateCohortSet) and then do the execute() and see where the error is happening

[jreps]

This error is definitely caused by: https://github.com/OHDSI/CohortGenerator/blob/main/R/CohortConstruction.R based on the error message. It does do the translate using: targetDialect = connection@dbms - I'm not sure what the @ does though.

[jreps]

I tested it and the connection@dbms works for me and returns 'redshift'

[lhjohn]

While debugging it crashes after the following step.

Browse[2]> n
debug: cohortsGenerated <- ParallelLogger::clusterApply(cluster, cohortDefinitionSet$cohortId, 
    generateCohort, cohortDefinitionSet = cohortDefinitionSet, 
    connection = connection, connectionDetails = connectionDetails, 
    cdmDatabaseSchema = cdmDatabaseSchema, tempEmulationSchema = tempEmulationSchema, 
    cohortDatabaseSchema = cohortDatabaseSchema, cohortTableNames = cohortTableNames, 
    stopIfError = stopOnError, incremental = incremental, recordKeepingFile = recordKeepingFile, 
    stopOnError = stopOnError, progressBar = TRUE)
Browse[2]> n
1/2- Generating cohort: [HJ] Dementia Prediction - 50-79y - v2
  |=========                                                                                                      |   8%An error occurred while generating cohortName = [HJ] Dementia Prediction - 50-79y - v2. Error: Error in `.createErrorReport()`:
! Error executing SQL:
java.sql.SQLException: [Amazon](500310) Invalid operation: function datefromparts(integer, integer, integer) does not exist 
Hint: No function matches the given name and argument types. You may need to add explicit type casts.;

 Error in value[[3L]](cond) : 

[anthonysena]

So CohortGenerator is just passing along the SQL to SqlRender to do the translation to RedShift. Perhaps there is something about the cohort definition itself that is problematic on Redshift? Could you share the cohort definition JSON? Not sure if it is in this skeleton or elsewhere.

[lhjohn]

These are the files that end up in the prediction package, you must change back the ending to .json or.sql since I had to change it to txt to upload here.

5920_JSON.txt
5920_SQL.txt

You can also find the cohort on the internal ATLAS under id 5920.

[anthonysena]

Thanks @lhjohn! So since this cohort was one that I had access to directly from ATLAS, I was able to execute the following code successfully:

library(CohortGenerator)
baseUrl <- keyring::key_get("WEBAPI_URL")
ROhdsiWebApi::authorizeWebApi(baseUrl = keyring::key_get("WEBAPI_URL"), authMethod = "windows")
cohortDefinitionSet <- ROhdsiWebApi::exportCohortDefinitionSet(baseUrl = baseUrl,
                                                               cohortIds = c(5920))

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "redshift",
                                                                user = keyring::key_get("OHDA_PROD_1_USERNAME"),
                                                                password = keyring::key_get("OHDA_PROD_1_PASSWORD"),
                                                                server = paste0(keyring::key_get("OHDA_PROD_1_SERVER"), "/truven_mdcd"),
                                                                extraSettings = "ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory")

# First get the cohort table names to use for this generation task
cohortTableNames <- getCohortTableNames(cohortTable = "issue_28")

# Next create the tables on the database
createCohortTables(connectionDetails = connectionDetails,
                   cohortTableNames = cohortTableNames,
                   cohortDatabaseSchema = "scratch_asena5")

# Generate the cohort set
cohortsGenerated <- generateCohortSet(connectionDetails= connectionDetails,
                                      cdmDatabaseSchema = "cdm_truven_mdcd_v1978",
                                      cohortDatabaseSchema = "scratch_asena5",
                                      cohortTableNames = cohortTableNames,
                                      cohortDefinitionSet = cohortDefinitionSet)

Given this, I'd presume this is not an issue in CohortGenerator but rather it may be in the mechanism you are using to either 1) download the JSON or 2) generate the SQL. I was attempting to find this in the skeleton here but was having a little trouble - could you help me to understand how this is happening?

[jreps]

Sena - are you able to try with the sql Henrik sent? That should be what is causing the issue - I had a look at the sql and all I can think is that for some reason the SqlRender is not translating to Redshift - could it be some issue with doing things in parallel and the connection settings?

Henrik - can you try loading in the sql and doing SqlRender manually to see whether the sql is converted correctly for you?

[anthonysena]

If I use the SQL that Henrik supplied, I get the same error he did.

[jreps]

So SqlRender is not translating it for some reason? That is odd.

[anthonysena]

I see in the Skeleton here that you are using SqlRender v1.7.0 but the latest is v1.9.0. I'm trying to see if there a different in translating with the different version of SqlRender for some reason.

[jreps]

I just set sql <- 'cut from sql file Henrik sent' and then did sql2 <- SqlRender::translate(sql, targetDialect = 'redshift') and it converted correctly. So for some reason the translate is not being called with redshift as the target.

Im using SqlRender_1.9.0

[anthonysena]

Just an update based on discussion with @jreps and others - the root problem here appears to be the way in which the .SQL file was saved. From our investigation, the line feeds were ending in "CR" vs "LF" in some places which was causing problems with SqlRender.

@jreps mentioned updating extras/createDevelopmentPackageFunctions.R to make use of CohortGenerator's saveCohortDefinitionSet and getCohortDefinitionSet functions to prevent this problem from happening moving forward.

[lhjohn]

Great! Thank you for looking into this @jreps and @anthonysena.