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

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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