OHDSI Home | Forums | Wiki | Github

Achilles SQL query 2004.sql failed in BigQuery

Developers
#1

Our database is BigQuery, when ran Achilles, it failed at SQL 2004.sql, the error massage:

“ Resources exceeded during query execution: Not enough resources for query planning – too many subqueries or query is too complex.”

To solve this problem, I set sqlOnly = TRUE for the following function:
con ← achilles(connectionDetails,
…
sqlOnly = TRUE,
cdmVersion = “5.3.0”)

to generate the full SQL queries. Then modified the 2004.sql (replace one sub-query with a variable) and ran these SQLs directly .

In this case, I have to manually clean up all the tmp tables.

The question is that if any important tables or processes missing without running the function achilles()?

we have some reports missing from Atlas and I’m wondering if it is related to what we did?

Any suggestion how to solve the 2004.sql to run this R function achilles() against BigQuery?

To share some fixes (for BigQuery) to the SQLs under …/RPackages\Achilles\sql\sql_server\analyses
NUMERIC(7,6) → NUMERIC
INTERSECT → INTERSECT DISTINCT

Thanks.
Luo

#2

Hi @Konstantin_Yaroshove,

What do you think is the issue here?

#3

I think the issue is that the 2004.sql has too many sub-queries and I cannot run Archilles R code since it failed. I think you have BigQuery as your database, correct? Have you made any changes to the SQLs? If I didn’t make changes like intersect to intersect distinct, the SQLs would fail.

#4

Our Atlas has some reports missing (person, data density, etc.) I checked the SQLs from WebApi, seems to me the SQLs used for these reports need some modifications, like UNION replaced by UNION ALL, isNull replaced by coalesce, etc., for BigQuery.
But, what I got confused is that the SQLs for some other reports (measurement, death etc. ) also have the same functions, but they do work. In this case, I think it maybe the issue related to data and tried to run the SQLs (for person, …/cdmresults/sql/report/person, modified for BigQuery) within GCP console and it got data back. I’ve been trying to figure out why some reports not working, any advice is really appreciated. Thanks!
Our Atlas version is 2.7.8 WebAPI version 2.7.8.

t