OHDSI Home | Forums | Wiki | Github

Error when running achilles. relation "#s_tmpach_700" does not exist

Developers
#1

Hi,

I am running achilles against a OMOP dataset in Redshift. I get the below error. Any clue on how to debug this ? Thanks. I am using v5.1 CDM and it works with synthea data set without issues.

Error:
com.amazon.redshift.util.RedshiftException: ERROR: relation “#s_tmpach_700” does not exist

SQL:
–HINT DISTRIBUTE_ON_KEY(analysis_id)
CREATE TABLE omop_clin.achilles_results

DISTKEY(analysis_id)
AS
WITH cte_merged
AS (
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_0
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_1
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_2
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_3
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_4
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_5
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_10
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_12
union all
select cast(analysis_id as int) as analysis_id, cast(stratum_1 as varchar(255)) as stratum_1, cast(stratum_2 as varchar(255)) as stratum_2, cast(stratum_3 as varchar(255)) as stratum_3, cast(stratum_4 as varchar(255)) as stratum_4, cast(stratum_5 as varchar(255)) as stratum_5, cast(count_value as bigint) as count_value from
#s_tmpach_101

Trimmed the remaining query.

R version:
R version 3.5.3 (2019-03-11)

Platform:
x86_64-pc-linux-gnu

Attached base packages:

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

Other attached packages:

  • Achilles (1.6.3)
  • DatabaseConnector (5.0.2)
  • SqlRender (1.6.2)
#2

This error suggests that analysis 700 failed to complete and I suspect when the final insert is attempted the table is missing. You should take a look inside the achilles log file to see the error causing analysis 700 to fail in order to debug further.

1 Like
#3

Thanks @Frank . It does look like it’s failing

Executing SQL took 9.02 secs
Executing multiple queries. This could take a while
Analysis 700 (Number of persons with at least one drug exposure, by drug_concept_id) – START
| | 0%Analysis 700 – ERROR Error in .createErrorReport():
! Error executing SQL:
com.amazon.redshift.util.RedshiftException: ERROR: function to_clob(character varying) does not exist
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
An error report has been created at /home/ohdsi/errorReportSql.

The above failure is due to a missing input table drug_exposure and once we fixed the schema and data it started to work.

Thanks @Frank

1 Like
#4

Great, glad to hear the issue is resolved!

t