OHDSI Home | Forums | Wiki | Github

Achilles wit BigQuery

Hi,

I have been doing some testing trying to run Achilles against a BigQuery database. A number of tests have been failing with something along these lines as below. What’s likely to cause this? Is this expected?

DBMS:
bigquery

Error:
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
"domain" : "global",
"location" : "q",
"locationType" : "parameter",
"message" : "Parameterized types are not allowed in CAST expressions.",
"reason" : "invalidQuery"
  } ],
  "message" : "Parameterized types are not allowed in CAST expressions.",
  "status" : "INVALID_ARGUMENT"
}

SQL:
CREATE TABLE omop.tmpach_231
 AS WITH op_outside as (
select 
	COUNT(distinct vo.person_id) as person_count
from 
	omop.visit_occurrence vo
left join 
	omop.observation_period op 
on 
	vo.person_id = op.person_id
and 
	vo.visit_start_date >= op.observation_period_start_date
and 
	vo.visit_start_date <= op.observation_period_end_date
where
	op.person_id is null
), vo_total as (
select
	COUNT(distinct person_id) person_count
from
	omop.visit_occurrence
)
 SELECT 231 as analysis_id,
	case when vo.person_count != 0 then 
		cast(cast(1.0*op.person_count/vo.person_count as numeric(7,6)) as STRING) 
	else
		cast(null as STRING) 
	end as stratum_1, 
	cast(op.person_count as STRING) as stratum_2,
	cast(vo.person_count as STRING) as stratum_3,
	cast(null as STRING) as stratum_4,
	cast(null as STRING) as stratum_5,
	sign(op.person_count) as count_value
 FROM op_outside op
cross join 
	vo_total vo


R version:
R version 4.2.0 (2022-04-22)

Platform:
x86_64-pc-linux-gnu

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- Achilles (1.7)
- DatabaseConnector (5.0.4)

Thanks, Matt

It seems the CAST(...AS NUMERIC(7,6)) is not OK with BigQuery. Here it is in the pre-translated query. I’m not sure why it was written like this in Achilles, but I don’t think such specific parameterized types translate well across platforms. @Frank: I recommend changing the type to FLOAT in the pre-translated SQL in Achilles.

I made the following changes when ran Achilles SQLs with BigQuery: NUMERIC(7,6) ==> NUMERIC;
INTERSECT ==> INTERSECT DISTINCT;

@Matthew_Creasey

Hi Matt,

This has been addressed here: PR 666 and merged into Master. Please pull the latest Achilles. We implemented the fix suggested by @schuemie.
Thanks, Martijn. : )

Thanks Matt, for bringing this to our attention.

-Anthony

t