SAS Access to MongoDB using a DataDirect ODBC Driver

In this tutorial we explain how to connect from SAS to MongoDB using Progress DataDirect’s ODBC Driver for MongoDB. 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.

1.  Install the Driver
Install Progress DataDirect’s ODBC Driver for MongoDB 32 or 64bit driver on a Linux client. Ensure that the bits of the driver match the bits of the SAS application. Watch this video to install the DataDirect ODBC for MongoDB Driver.

2.Set the environment variables
DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. These scripts are in the 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. Provide it with the path to the driver as follows:

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

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



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

4. Configure a connection:
In order to configure a connection to the database, edit the odbc.ini file found in the installation directory. Here is an example of our connection information for the DataDirect ODBC for MongoDB database:



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 the odbc.ini file lists out the data source you have configured. For example:


5. Test the connection:

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



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/

1. Install the required SAS products
Now that we have a working connection to the database, it’s time to install the necessary SAS software.  For this tutorial, we 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. We chose /SASHome. SAS then created a subdirectory called SASFoundation/9.4, which is where we'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, access its query capabilities by switching into the SAS Log window and choosing Tools then Query:



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

e

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



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

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 questions contact us today.