ayo-uda
commented
5 years ago I believe I have pinpointed the problem. The query made by the calling of merge_achilles_tables.sql (snipped provided below) within the achilles.R script gives rise to the introduction of an **invalid character(0) column** throughout the rendered SQL query that is causing the error. I have provided examples of the rendered query and the resulting error message below.
Rendered Query Snippet
SQL:
--HINT DISTRIBUTE_ON_KEY(analysis_id)
CREATE TABLE resultsDataset.achilles_results
AS
SELECT
analysis_id, stratum_1, stratum_2, stratum_3, stratum_4, stratum_5, count_value
FROM
(
select cast(analysis_id as int64) as analysis_id, cast(stratum_1 as string) as stratum_1, cast(stratum_2 as string) as stratum_2, cast(stratum_3 as string) as stratum_3, cast(stratum_4 as string) as stratum_4, cast(stratum_5 as string) as stratum_5, cast(count_value as int64) as count_value from
resultsDataset.tmpach_0 union all select 2000000 as analysis_id, '13.638783 secs' as stratum_1, null as stratum_2, null as stratum_3, null as stratum_4, null as stratum_5, character(0)
union all
select cast(analysis_id as int64) as analysis_id, cast(stratum_1 as string) as stratum_1, cast(stratum_2 as string) as stratum_2, cast(stratum_3 as string) as stratum_3, cast(stratum_4 as string) as stratum_4, cast(stratum_5 as string) as stratum_5, cast(count_value as int64) as count_value from
resultsDataset.tmpach_1 union all select 2000001 as analysis_id, '11.222780 secs' as stratum_1, null as stratum_2, null as stratum_3, null as stratum_4, null as stratum_5, character(0) ...Error Message
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
"code" : 400,
"errors" : [ {
"domain" : "global",
"location" : "q",
"locationType" : "parameter",
"message" : "Function not found: character at [13:225]",
"reason" : "invalidQuery"
} ],
"message" : "Function not found: character at [13:225]",
"status" : "INVALID_ARGUMENT"
}achilles.R Code Snippet Associated With Error
if (!sqlOnly & includeRawCost) {
# obtain the runTime for this analysis
benchmarkSqls <- lapply(c(15000, 16000), function(rawCostId) {
runTime <- .getAchillesResultBenchmark(rawCostId, outputFolder)
benchmarkSelects <- lapply(resultsTable$schema$FIELD_NAME, function(c) {
if (tolower(c) == "analysis_id") {
sprintf("%d as analysis_id", .getBenchmarkOffset() + rawCostId)
} else if (tolower(c) == "stratum_1") {
sprintf("'%s' as stratum_1", runTime)
} else if (tolower(c) == "count_value") {
sprintf("%d as count_value", smallCellCount + 1)
} else {
sprintf("NULL as %s", c)
}
})
SqlRender::render(sql = "select @benchmarkSelect", benchmarkSelect = paste(benchmarkSelects, collapse = ", "))
})
benchmarkSql <- paste(benchmarkSqls, collapse = " union all ")
benchmarkSql <- SqlRender::translate(sql = benchmarkSql, targetDialect = connectionDetails$dbms)
detailSqls <- c(detailSqls, benchmarkSql)
}
SqlRender::loadRenderTranslateSql(sqlFilename = "analyses/merge_achilles_tables.sql",
packageName = "Achilles",
dbms = connectionDetails$dbms,
warnOnMissingParameters = FALSE,
createTable = createTable,
resultsDatabaseSchema = resultsDatabaseSchema,
detailType = resultsTable$detailType,
detailSqls = paste(detailSqls, collapse = " \nunion all\n "),
fieldNames = paste(resultsTable$schema$FIELD_NAME, collapse = ", "),
smallCellCount = smallCellCount)
}
t-abdul-basser
djb2188
Andreyyiv
fdefalco
Expected behavior
The generation of summary and results table data for a OMOP v5.3 CDM instance sourced from BigQuery after executing achilles.
Actual behavior
An 'Error getting job status' that pops up while attempting to merge created achilles result tables several minutes into the execution:
The interrupted execution leaves multiple temp tables remaining in the bq dataset designated as the scratch database schema.
Steps to reproduce behavior