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)

SIMBA Couchbase ODBC driver 1.0.0.1000

Couchbase 4.x

Install Couchbase Sample Buckets (beer-sample and travel-sample)

Installation of the SIMBA Drivers is straightforward, download the installer files from the SIMBA Technologies website and run the installer utility compatible (32-bit or 64-bit) with the application you are running.   

odbc1a

After installation, you will be required to copy the license file supplied by SIMBA to the /lib directory.  

odbc2a

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.  

 odbc3
The DSN defines connectivity information to a Couchbase nodes running the Couchbase query service.  Please refer to the SIMBA documentation for details on how to set up the driver.  The following parameters need to be set up to get things up and running quickly:

odbc4

  1. Description: (Optional)

  2. 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.

  3. Port: Port number for the Couchbase 4.x query service (default is 8093.)

  4. Advanced Options: This is covered later in this blog.  

  5. Schema Definition: This is covered later in the blog.

In the test environment, verify that the beer-sample and 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.  

odbc5a

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.  

odbc6

Step 2 – Review Advanced Options

From the Couchbase ODBC Driver DSN panel, click on Advanced Options.  The following settings should be noted:

odbc7

  1. Query Mode – SQL (default), issues a SQL query, failed queries will be sent as N1QL.

  2. String Column Length – 1024 (default), specifies column length for string attributes.

  3. Active Schema Location – Database (default), Database or local JSON schema file used by the driver.

  4. Sample Size – 100 (default), specify the number of documents the driver scans to generate schema metadata.

  5. 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:

imports

  1. 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.  

  2. 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.

  3. Click on Edit Schema File to verify the contents of the generated schema file:

    odbc9

  4. 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!  

Conclusion:

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.

Author

Posted by Tim Wong

Tim is a Principal Solutions Consultant at Couchbase supporting accounts in the San Francisco Bay Area. He has worked with database, enterprise data integration (batch, real time, cloud) and business intelligence technologies for over 20 years with stints at Oracle, TIBCO and Informatica.

All Posts

2 Comments

  1. wirasakc November 28, 2018 at 6:14 am

    I am not sure this instruction still valid for the latest driver of SIMA?

    [Simba][Couchbase] (130) Can’t connect to the Couchbase server.
    Generic ODBC requires additional configuration. The driver and DSN (data source name) must be installed and configured to match the connection.
    Unable to connect using the DSN named “Sample Simba Couchbase ODBC DSN”. Check that the DSN exists and is a valid connection.

    and from creating schema :

    An error occurred while sampling

    Driver={Simba Couchbase ODBC Driver};AllowHostNameCNMismatch={0};AllowSelfSignedServerCert={0};AuthMech={0};CheckCertRevocation={0};CredFile={};Description={Sample Simba Couchbase ODBC DSN};Driver={Simba Couchbase ODBC Driver};IsTest={0};LocalSchemaFile={};LogFileCount={50};LogFileSize={20};Port={8093};QueryMode={0};Redundancy={0};SampleSize={100};ScanConsistency={0};Schema={default};SchemaMapOperation={0};Server={35.243.80.58};SimulateTransactions={0};SSL={0};StringColumnLength={255};TrustedCerts={C:\Program Files (x86)\Simba Couchbase ODBC Driver\lib\cacerts.pem};TypeNameList={beer-sample:type,travel-sample:type,gamesim-sample:jsonType};UseSystemTrustStore={0}

  2. Tim Wong, Solutions Consultant, Couchbase November 28, 2018 at 10:53 am

    @wirasakc – I have not tested with the latest SIMBA drivers, this was tested on a version that was released over three years ago.

    I would refer you to the SIMBA docs – their last release came out in early 2018.
    https://www.simba.com/products/Couchbase/doc/ODBC_InstallGuide/win/content/odbc/intro.htm

    Alternatively, a colleague just wrote a blog about using CDATA’s ODBC driver that is brand new. https://www.couchbase.com/connecting-to-couchbase-via-odbc/

    I would look at this blog and try out the ODBC drivers from CDATA –

    https://www.couchbase.com/connecting-to-couchbase-via-odbc/

Leave a reply