BookmarkSubscribeRSS Feed
AlanC
Barite | Level 11

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.

https://github.com/savian-net
3 REPLIES 3
pink_poodle
Barite | Level 11

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';

ReferenceLIBNAME Statement Specifics for ODBC

NZ_Hockey_Mum
Calcite | Level 5

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
Calcite | Level 5

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)

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1095 views
  • 0 likes
  • 3 in conversation