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?