Getting Started with the SIMBA Couchbase ODBC Driver
ODBC has been around since the early 1990’s as a standard API to access various data sources, traditionally against structured data sources like relational databases. SIMBA Technologies recently released an ODBC Driver for Couchbase; this has opened up Couchbase to a number of third party data integration and reporting tools. This is a quick start guide on how to configure ODBC access for Couchbase on a Windows workstation with the SIMBA ODBC Driver for Couchbase.
Installation of the SIMBA ODBC Drivers
Software Requirements for this Guide
Windows Operating System (Windows 7 or higher)
After installation, you will be required to copy the license file supplied by SIMBA to the /lib directory.
Configuring ODBC Access to Couchbase
The installer configures a Sample System DSN called “Simba Couchbase ODBC DSN”. You can review this set up with the ODBC Data Source Administrator utility which is a Windows Control Panel utility.
Server: Specify the hostname or IP address of a Couchbase node running the Couchbase 4.x query service. In this example, the driver is pointing to a local Couchbase node running on Windows 7.
Port: Port number for the Couchbase 4.x query service (default is 8093.)
Advanced Options: This is covered later in this blog.
- Schema Definition: This is covered later in the blog.
In the test environment, verify that the
travel sample buckets are loaded. Confirm this on the Couchbase Admin Console | Data Buckets tab. If the buckets are not loaded, please refer to the Couchbase documentation site for instructions for how to do so.
Schema Definition Quick Setup
A schema serves as a translation layer between Couchbase data and an ODBC compatible format. A schema can be stored in a physical JSON file located on your local Windows environment or in every Couchbase bucket on the Couchbase cluster. This section will walk you though pushing a schema to a Couchbase cluster.
Step 1 – Create primary indexes on Couchbase Buckets
If you have not already, you need a primary index on the documents in order for the SIMBA Couchbase ODBC driver to recognize the various documents and their attributes stored in a bucket. You will need to use the CBQ command line utility located in the bin directory. In this example, a primary index is created for the beer-sample and travel-sample buckets.
Step 2 – Review Advanced Options
From the Couchbase ODBC Driver DSN panel, click on Advanced Options. The following settings should be noted:
Query Mode – SQL (default), issues a SQL query, failed queries will be sent as N1QL.
String Column Length – 1024 (default), specifies column length for string attributes.
Active Schema Location – Database (default), Database or local JSON schema file used by the driver.
Sample Size – 100 (default), specify the number of documents the driver scans to generate schema metadata.
Type Name List – Allows the developer to specify document types. The default DSN created by the installer is configured to work with the three sample buckets: travel-sample, beer-sample and gamesim-sample.
For the purposes of this guide, it is recommended to stay with the default settings in the advanced panel.
Step 3 – Generate a schema and publish it to the database
To set up the the ODBC for the sample buckets, follow the following steps:
Click Browse and point the driver to an empty json file on your local system. In this example, an empty file called NewCB.json was specified, it was located in c:odbc.
Click on Generate Schema, this may take a while. The schema metadata will be written to the file specified in the previous step. A success dialog will pop up.
Click on Edit Schema File to verify the contents of the generated schema file:
- If you want to store the schema in the database. Click on Import Schema to write the schema to each Couchbase bucket – you will then get a success dialog box. This will take the contents of the JSON file specified in step 2 and export it to each bucket on the Couchbase cluster. You now have uploaded the schema file to Couchbase!
With the release of the SIMBA Couchbase ODBC drivers for Couchbase developers now have access to a wide array of ODBC compliant tools for uses like data integration, reporting and business intelligence. Hopefully, this guide helps you set up your SIMBA ODBC driver for your own environment quickly and efficiently. With the recent release of Couchbase 4.1, developers can now perform DML operations on Couchbase via ODBC, please read a guest blog by Mike Howard from Simba Technologies.