PROC HTTP and Cosmos DB

[AlanC]

I am trying to connect to the Azure Cosmos database. I have a url:

https://mydb.azure.com

And an AccountKey: ABC123

Any ideas on connecting? This is a NoSQL database.

[pink_poodle]

The connection works through ODBC (Open Database Connectivity). Here is a post discussing that:

https://communities.sas.com/t5/SAS-Programming/Experience-with-SAS-and-Cosmos-DB/td-p/479398

LIBNAME mydblib ODBC DATAsrc=azuresrvr1 USER=testuser PASSWORD='ABC123';

Reference: LIBNAME Statement Specifics for ODBC

[NZ_Hockey_Mum]

I have managed to get

• Cosmos DB drivers installed (from CDATA) on my SAS comp server
• Got a system DSN ODBC connection set up on my SAS comp server (that tests as working) using the URL and token
• LIBNAME cosmos ODBC DATAsrc=CosmosDBODBCNAME ; (Syntactical works)

But that is as far as I have got.  I do not know what the collections are called on the database.

Has any one found as examples of how to discover what collections are on a Cosmos nosql database?

Not from within SAS but using https://azure.microsoft.com/en-us/features/storage-explorer/

[NZ_Hockey_Mum]

Update whilst the libname is syntactical correct it does not appear to work.

If I use the pass through syntax I get error "schema map entry for "tablename" could not be found. Check query syntax and schema map definition and try again." 

Any ideas?

Got it working ....

* first get the schema and table names using .... ;
proc sql;
connect to odbc(DATAsrc=ODBC_DNS_NAME);
create table work.odbcSchema as
select * from connection to odbc
(ODBC::SQLTables);
quit;

* the output has the schema name labelled as Table_schem and the table_name in my database ;

* I got field name from using Microsoft Azure Storage Explore ;

proc sql;
connect to odbc(DATAsrc=ODBC_DNS_NAME);
create table work.SAS_dataset_name as
select * from connection to odbc
(select fieldName from Table_schem.table_name);
quit;

(note :S is actually a colon followed by a S)