topic PROC HTTP and Cosmos DB in SAS Procedures

PROC HTTP and Cosmos DB

AlanC — Tue, 25 Sep 2018 17:04:30 GMT

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.

Re: PROC HTTP and Cosmos DB

pink_poodle — Tue, 25 Sep 2018 18:34:45 GMT

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

Re: PROC HTTP and Cosmos DB

NZ_Hockey_Mum — Fri, 15 Feb 2019 01:10:27 GMT

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/

Re: PROC HTTP and Cosmos DB

NZ_Hockey_Mum — Fri, 01 Mar 2019 01:29:58 GMT

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)