SAS Access to MongoDB using a DataDirect ODBC Driver

Introduction

In this tutorial we will show you how to connect from SAS to MongoDB using our DataDirect MongoDB ODBC driver. While this tutorial demonstrates connecting to MongoDB, the same steps can easily be applied to any of the other ODBC data sources supported by DataDirect.

Installing and Configuring DataDirect MongoDB ODBC Driver

1.  Install the Driver
Install DataDirect MongoDB for ODBC 32 or 64bit driver on your Linux client. Ensure that the bits of your driver match the bits of your SAS application. You can watch this video to install the DataDirect ODBC for MongoDB Driver.

2.Set your environment variables:
To help you perform this task, DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. You’ll find these scripts in your installation directory. These scripts configure the following environment variables:

LD_LIBRARY_PATH
PATH
ODBCINI
ODBCINST

You will also need to have a Java SE 6 or higher installed on your Linux client and on your LD_LIBRARY_PATH.

3. Test load the driver:
Use the DataDirect-provided ivtestlib (or ddtestlib for 64bit) utility to test load the driver. You'll find this utility in the bin sub-directory of your installation. You'll need to provide it with the path to the driver as follows:

./ddtestlib /opt/Progress/DataDirect/ODBC_80_64bit/lib/ddmongo28.so

If your installation was successful and your environment variables are properly set, you should receive a message similar to the following:

SASMongoDBODBC1

If you receive an error message at this point, the most likely cause is that your environment variables aren't properly set.

4. Configure a connection:
In order to configure a connection to your database, you will want to edit the odbc.ini file (found in your installation directory). Here is an example of my connection information for the DataDirect ODBC for MongoDB database:

SASMongoDBODBC2

The Database connection option can be used to specify the name of the database you wish to connect to. If left blank, the default is the database defined by the system administrator for each user.

The SchemaDefinition connection option can be used to specify the location of the configuration file where the relational map is written. If left blank, by default the file will be created in the following directory:

~/Progress/DataDirect/MongoDB_Schema/<host_name>.config

Note: Ensure that the ODBC Data Sources section of your odbc.ini file lists out the data source you have configured. For example:
SASMongoDBODBC3

5. Test your connection:

After entering your connection details into your odbc.ini file you can test your connection using the provided example tool. The example tool is located in the samples/example folder.

SASMongoDBODBC4

If you’re unable to successfully connect, double check your login settings with your database administrator. You can also search our Knowledge Base for the error message you received: http://knowledgebase.progress.com/





Installing SAS and Running a Query

1. Install the required SAS products:

Now that you have a working connection to your database, it’s time to install the necessary SAS software.  For this tutorial, I installed the SAS Foundation 9.4 along with the SAS/ACCESS interface to ODBC

2. Launch SAS Foundation:
When you install SAS Foundation, you're prompted to provide an installation directory. I chose /SASHome. SAS then created a subdirectory called SASFoundation/9.4, which is where you'll find the SAS command that launches the product. If you're unable to locate the SAS installation, contact your SAS administrator for more details.

Note: ensure that the environment variables previously set are still set when you execute the SAS application.

3. Run a basic SAS query:
Once SAS Foundation is launched, you can access its query capabilities by switching into the SAS Log window and choosing Tools -> Query:

SASMongoDBODBC5

This launches the SAS: Query dialog. Once this is running, choose the Tools -> Switch Access Mode -> ODBC menu option:

SASMongoDBODBC6

SAS now displays a dialog box where you can enter in the data source, username, and password details that you configured earlier.

SASMongoDBODBC7

Once you press OK, SAS will attempt to connect, via the SAS/ACCESS interface to ODBC and the DataDirect driver to your source. If everything is configured correctly, you should see a list of all available tables. From here you can select the table (or tables) from the list. You can then select any columns you want to include in your query and then choose the Tools -> Run Query -> Run Immediate to launch your query. This will return results either to your SAS output window or to a web browser – depending on how you have your SAS Foundation configured.

Next Steps

Installing SAS and running a query will remain the same for other  DataDirect ODBC drivers including SQL Server, Oracle and RedShift.

To learn more about SAS Access and ODBC, please visit our SAS ODBC solutions page.

For any other questions, or for an extension of your trial software, contact us today.
ODBC TUTORIAL

SAS Access to MongoDB using a DataDirect ODBC Driver

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers