Extend Couchbase Analytics with SQL Server using CData

Extend Couchbase Analytics with SQL Server using CData.

This article will guide you through the steps needed to setup the connection from Couchbase Analytics to SQL Server using the CData ODBC driver for Couchbase. More details regarding this driver can be found here.  The download comes with a second driver, CData SQL Gateway and its information can be found here.

**Note – for the CData SQL Gateway, this setup requires the enterprise license.

Prerequisites

Couchbase

You will first need a Couchbase Server Enterprise Edition (EE) 6.x cluster with the Data and Analytic services enabled. I am using a single node local install of Couchbase Server EE but the information in this article applies to any Couchbase Server EE cluster.

If you do not have an existing Couchbase Server EE cluster, the following links will get you up and running quickly:

1. Download Couchbase Server EE
2. Install Couchbase Server EE
   • Mac OS X
   • Linux
   • Windows
3. Provision a single-node cluster (NOTE: use the default values for cluster configuration)

SQL Server

Using a windows based platform with the following details:

Couchbase Setup

In Couchbase click on Settings 

Then Sample Buckets

The select the beer-sample checkbox and select Load Sample Data.  You can then navigate back to your Buckets and see beer-sample.

Once this is complete, we will need to setup Analytics.

Select Analytics, then create the shadow dataset of beers from the bucket of beer-sample.

Create Dataset in Couchbase Analytics

Click Execute, this will crate the shadow dataset definition.

I want to repeat this step by creating a second shadow dataset with the following definition.

Next you will want to initialize it by activating the dataset with the following.

Click Execute.

You can now test this out within the Analytics dashboard by running something like the following.

More about Couchbase Analytics can be found here.

Your setup for Couchbase is complete!

SQL Server Setup

I installed SQL Server Express with all the defaults which can be found here.

Install CData ODBC Driver for Couchbase (Enterprise Edition) to enable SQL Server Linked Server

Setup the ODBC Driver

• • I set the following parameters
    • Data Source Name: CData Couchbase Source
    • User: Administrator
    • Password:password
    • Server: 13.92.234.252
    • Analytics Port: 8095
    • Verbosity: 5
    • Dataverse: Default
    • Flatten Arrays: 4

Setup SQL Gateway ODBC driver to configure the TDS Remoting Service

Select run as a service

Configure the service with the following settings:

Configure the Users tab with the following settings:

Now we will want to create a linked server to Couchbase

Now that we have successfully setup the connection between SQL Server and Couchbase, lets try a simple SQL statement written in SQL Server Management Studio that will execute against Couchbase Analytics

Next Steps

Try creating a table on SQL Server and inserting data from Couchbase.

Download Couchbase, setup Analytics, and start using SQL Server with your data and see what insights you can gleam.  Extend Analytics with other tools using the many Couchbase CData drivers that are at your fingertips.