MarkLogic ODBC Setup and Quick start for Linux environments
16 November 2017 10:19 AM
There is a lot of useful information in MarkLogic Server's documentation surrounding many of the new features of MarkLogic 9 - including the new SQL implementation, improvements made to the ODBC driver and the new system for generating SQL "view" templates for your data. This article attempts to pull it all together by showing all the measures needed to create a successful connection and to verify that everything is set up correctly and works as expected?
This guide presents a step-by-step walk through covering the installation of all the necessary components, the configuration of the ODBC driver and the loading of data into MarkLogic in order to create a Template View that will allow a SQL query to be rendered.
We're starting with a clean install of Redhat Enterprise Linux 7:
In this example, I'm using yum to manage the additional dependencies (openssl-libs and unixODBC) required for the MarkLogic ODBC driver:
If you want to use the latest version of unixODBC (2.3.4 at the time of writing), you can get it using cURL by running curl -O ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
Please note - as per the documentation, this method will require unixODBC to be compiled so additional dependencies may need to be met for this.
Getting started: installing and configuring MarkLogic 9 with an ODBC Server
We will start by installing and starting MarkLogic 9:
From there, we can point our browser at http://host:8001 and walk through the initial MarkLogic install process:
As soon as the install process has been completed and you have created an Administrator user for MarkLogic Server, we're ready to create an ODBC Application Server.
To do this, go to Configure > Groups > Default > App Servers and select the Create ODBC tab:
Next we're going to make the minimal configuration necessary by entering the required fields - the odbc server name, the Application Server module directory root and the port.
In this example we will configure the Application Server using the following values:
After this is done, confirm that the Application Server has been created by going to Configure > Groups > Default > App Servers and ensure that you can see the ODBC Server listed and configured on port 5432 as per the image below:
Getting started: Setting up the MarkLogic ODBC Driver
Use RPM to install the ODBC driver:
Configure the base template as instructed in the installation guide:
Getting started: ensure unixODBC is configured
To ensure the unixODBC commandline client is configured, you can run isql -h to bring up the help options:
If you're not seeing the above message, it could be possible that there's another application on your system overriding this, for this configuration, the isql command is found at /usr/bin/isql:
Getting started: initial connection test
If you're happy that isql is correctly, installed, we're ready to test the connection using isql -v:
Let's confirm that it's really working by loading some data into MarkLogic and creating an SQL view around that data.
Loading sample data into MarkLogic
To load data, we're going to use Query Console to insert the same sample data that is created in the Quick Start Documentation:
To access Query Console, point your browser at http://host:8000 and make note of the following:
When these are both set correctly, run the code to generate sample data (note that this data is taken from the quick start guide and reproduced here for convenience):
After that has run, you should see a null response back from the query:
To confirm that the data was loaded successfully, you can use the Explore button. You should now see that 22 employee documents (rows) are now in the database:
Create the template view
Now the documents are loaded, a tabular view for that data needs to be created.
Ensure the database is (still) set to Documents (or at least, matches the database specified by your ODBC Application Server) and ensure that the Query Type is now set to XQuery
As soon as this is set, you can run the code below to generate the template view (note that this data is taken from the quick start guide and reproduced here for convenience):
And to confirm this was loaded, Query Console should report an empty sequence was returned.
Test the template using a SQL Query
The database should remain set to Documents and ensure that the Query Type is now set to SQL:
Then you can run the following SQL Query:
If everything has worked correctly, Query Console should render a view of the table in response to your query:
Test the SQL Query via the ODBC Driver
All that remains now is to go back to the shell and test the same connection over ODBC.
To do this, we're going to use the isql command again and run the same request there: