I'm using ODBC to connect to an Azure Cosmos DB instance. For those who haven't heard of it, Cosmos DB is a partitioned key-value store available in the cloud. Kind of like MS's equivalent to Mongo.

Here is a picture of the databases and containers (tables) in my instance.

I can connect mostly without problems, using DBI and odbc, like so:

conn <- DBI::dbConnect(odbc::odbc(),
    driver="Microsoft Azure DocumentDB ODBC Driver",
    host="https://hongcosmossqlsvl.documents.azure.com:443/",
    authenticationkey="****",
    RESTAPIversion="2018-12-31")

conn
<OdbcConnection> MSUser@https://hongcosmossqlsvl.documents.azure.com:443/
  Database: db1
  DocumentDB Version: 00.00.0000

Notice how it connected to database db1 by default.

My problem is that I can't get a connection to database db2. If I set the database argument, it doesn't seem to have an effect:

conn2 <- DBI::dbConnect(odbc::odbc(),
    driver="Microsoft Azure DocumentDB ODBC Driver",
    host="https://hongcosmossqlsvl.documents.azure.com:443/",
    database="db2",
    authenticationkey="****",
    RESTAPIversion="2018-12-31")

conn2
<OdbcConnection> MSUser@https://hongcosmossqlsvl.documents.azure.com:443/
  Database: db1
  DocumentDB Version: 00.00.0000

And indeed, when I run

DBI::dbGetQuery(conn2, "select * from cont1")

I get the rows from the db1.cont1 table, not db2.cont1. Adding the database to the query results in an error.

DBI::dbGetQuery(conn2, "select * from db2.cont1")
Error in new_result(connection@ptr, statement, immediate) : 
  nanodbc/nanodbc.cpp:1412: HY000: [Microsoft][DocumentDB] (10) Schema map entry for Table cont1 could not be found. Check query syntax and schema map definition and try again

How can I get DBI/odbc to use the db2 database?

Additional info: the MS ODBC driver seems rather weird in how it handles databases. With my conn connection above, which references database db1, I can run

DBI::dbGetQuery(conn, "select * from contdb2")

and still get the results from a table in database db2.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.