Backtick issue for

[eric-fey-hus]

Issue: we are using spar/databricks backend, where we need to specify tables in the form catalog.schema.table. Our catalog names contain colons, meaning we have to escape with backticks for SQL queries and most DatabaseConnector functions to work properly. Issue is the inconsistent use of dealing with backticks in DatabaseConnector, some functions use it, some do not. Here specifically, DatabaseConnector::getTableNames caused the issue. See details and fix below.

#hotfix for DatabaseConnector::getTableNames

getTableNames <- function(connection, databaseSchema = NULL, cast = "lower") {
  errorMessages <- checkmate::makeAssertCollection()
  checkmate::assertTRUE(DBI::dbIsValid(connection))
  checkmate::assertCharacter(databaseSchema, len = 1, null.ok = TRUE, add = errorMessages)
  checkmate::assertChoice(cast, c("upper", "lower", "none"), add = errorMessages)
  checkmate::reportAssertions(collection = errorMessages)

  if (is.null(databaseSchema)) {
    tableNames <- DBI::dbListTables(connection)
  } else if (is(connection, "DatabaseConnectorConnection")) {
    #EF: here I had to fix (fors spak/databricks with jdbc driver
    tableNames <- DBI::dbListTables(conn = connection, gsub("`", "", databaseSchema))
  } else if (is(connection, "PqConnection") || is(connection, "RedshiftConnection") || is(connection, "duckdb_connection")) {
    stopifnot(length(databaseSchemaSplit) == 1)
    sql <- paste0("SELECT table_name FROM information_schema.tables WHERE table_schema = '", databaseSchema, "';")
    tableNames <- DBI::dbGetQuery(connection, sql)[["table_name"]]
  } else if (is(connection, "Microsoft SQL Server")) {
    databaseSchemaSplit <- strsplit(databaseSchema, "\\.")[[1]]
    if (!(length(databaseSchemaSplit) %in% 1:2)) rlang::abort("databaseSchema can contain at most one dot (.)")

    if (length(databaseSchemaSplit) == 1) {
      tableNames <- DBI::dbListTables(connection, schema_name = databaseSchemaSplit)
    } else {
      tableNames <- DBI::dbListTables(connection, catalog_name = databaseSchemaSplit[[1]], schema_name = databaseSchemaSplit[[2]])
    }
  } else if (is(connection, "SQLiteConnection")) {
    if (databaseSchema != "main") rlang::abort("The only schema supported on SQLite is 'main'")
    tableNames <- DBI::dbListTables(connection)
  } else {
    rlang::abort(paste(paste(class(connection), collapse = ", "), "connection not supported"))
  }

  switch(cast,
         "upper" = toupper(tableNames),
         "lower" = tolower(tableNames),
         "none" = tableNames
  )
}

assignInNamespace("getTableNames", getTableNames, ns="DatabaseConnector")
cat("\nEric: loaded runtime databricks connection hotfix for DatabaseConnector::getTableNames")
cat("\thandle backtick symbol")

So Line 13 was the offender, where I used this

tableNames <- DBI::dbListTables(conn = connection, gsub("`", "", databaseSchema))

to deal with the backticks issue.

[schuemie]

Trying to understand: Suppose your catalog is named 'my:catalog', and your schema is called 'my_schema', then I would imagine:

databaseSchema = "`my:catalog`.my_schema"
tables <- getTableNames(connection, databaseSchema)

For DataBricks, this would be routed to this line where we call cleanDatabaseName(), which is where surrounding quotes are removed. I think that is where have to add handling of backticks, not all the way up front.

[eric-fey-hus]

@schuemie Yes, that sounds right!. And yes, this is how it looks like:

databaseSchema = "`my:catalog`.my_schema"
tables <- getTableNames(connection, databaseSchema)

The way odbc handles this (I believe) is by splitting at "." and adding backticks to all parts of the fullly-specified name, meaning the result would be "`my:catalog`.`my_schema`". It might be worth checking if the backticks are there already (in parts of the name), because I have seen generated SQL queries from some packages/functions with double and triple backticks.

[schuemie]

The problem should now be fixed in the develop branch. Could you check?

[eric-fey-hus]

Thanks. Just checked. Behavior is not as expected:

DatabaseConnector::getTableNames(conn, "lakehouse::omop_prod.omop54")

works.

DatabaseConnector::getTableNames(conn, "`lakehouse::omop_prod`.omop54")

does not and returns character(0).

Details:

mycleanDatabaseName("lakehouse::omop_prod.omop54")
mycleanDatabaseName("`lakehouse::omop_prod`.omop54")
mycleanDatabaseName("`lakehouse::omop_prod`.`omop54`")

should all return "lakehouse::omop_prod.omop54".
Instead it returns:

"lakehouse::omop_prod.omop54"
"`lakehouse::omop_prod`.omop54"
"lakehouse::omop_prod`.`omop54"

Issue is that name in spark can be of format "calalogue.schema" and either/both catalogue and schema can be wrapped in backticks.

[schuemie]

Sorry, I'm not quite following what the issue is. The new implementation assumes either or both can have backticks, and should work for your example. Could you point where the logic is wrong?

Given a connection with dbms == 'spark' and a databaseSchema string that contains a period, you should end up here, which calls the functions here. This should strip the backticks if present:

DatabaseConnector:::cleanDatabaseName("`lakehouse::omop_prod`")
# [1] "lakehouse::omop_prod"

DatabaseConnector:::cleanSchemaName("`omop54`")
# [1] "omop54"

I have test code here that uses our testing server to confirm.

Are you sure you've installed the latest version from the develop branch?

[eric-fey-hus]

Thanks, it works! My fault. Installed again, double checked version and it works now. :-)