Standards-Based Access to Couchbase Data

So you’ve chosen Couchbase to house your data and now you’d like to be able to access that data however and wherever you’d like. If you’re building your own web, desktop, or mobile application, you can use the excellent SDKs from Couchbase. Sometimes, the SDKs won’t work if you’re trying to work with your Couchbase data in a third-party tool, like Microsoft Excel, Tableau, and any number of other BI/reporting/ETL tools. This is where CData Software can help: by giving you standards-based drivers to connect your Couchbase data to the BI, reporting, and ETL tools that you’re already using. We’ve built drivers that adhere to time- and industry-proven standards, like JDBC, ODBC, and ADO.NET, freeing users to quickly connect with their Couchbase data in their favorite tools, saving them the time and hassle of managing the data integration.

At this point, you may be asking yourself, “how do we connect to our Couchbase data (which is decidedly NoSQL) in BI tools that expect relational data?” With CData’s drivers, your SQL queries are processed and translated into N1QL, simultaneously granting you access to your data in tools you already use and leveraging the utility of Couchbase’s data storage and querying technology. We’ll manage the query interpretation, ask Couchbase for your data, and process the results into a table with rows and columns that modern BI tools expect, allowing Couchbase to manage the query processing and return the appropriate results.

For example, you may have a bucket labelled “business” which has some documents similar to the following:

and you want to chart the number of businesses in a given State. Given a bucket with documents like this, you could use any of the CData drivers and submit a query like the following:

CData’s drivers are able to properly interpret this query and translate it to the equivalent N1QL query to drill down into the address object within the document to pull the state field, allowing you to chart, graph, or otherwise creatively visualize exactly the data that you want.

When it comes to many 3rd party applications, though, you aren’t able to control the SQL query that is being created. In these instances, you can rely on the different schema interpretation methods that CData has put in place to help extract your Couchbase data, whether that’s defining the schema yourself, allowing the driver to scan a bucket and determine the schema, or configuring the driver to flatten objects and arrays. No matter how you configure the driver to extract your Couchbase data, you can rest assured that, with CData’s drivers, you will be able to get to the data that you want, when you want, and where you want.

Connecting with CData’s Products

In order to connect to your Couchbase data using a CData driver, you’ll first need to configure the connection, setting the Server, Username, Password, Web Console Port (defaults to the standard 8091), and N1QL Port (defaults to the standard 8093). This configuration will take many forms, from configuring a DSN for the ODBC Driver, using the Connection Wizard for the Excel Add-In (see the screenshot to the left), or creating a connection string for our ADO.NET Provider or JDBC Driver. No matter where you want your Couchbase data, the initial configuration is simple and straightforward. For advanced users or server administrators, there are other settings that can be configured to help work with proxies and firewalls, manage data caching, and many other options. All of the drivers’ connection properties are outlined in their respective help files.

To help Couchbase customers best connect to their data, CData has created an entire suite of drivers. I’ve provided a little more information for each of our Couchbase drivers (along with links for even MORE information) below:

Microsoft Excel

Our Excel Add-In creates a live connection to your Couchbase data in Excel, allowing you to read from and write to your data instead of being stuck with a static data dump. You can utilize the powerful and familiar features of Excel to see your Couchbase data the way that you want.

BI & Reporting Tools

The ODBC Driver for Couchbase allows you to connect your Couchbase data to any number of BI Tools, including, but not limited to: Cognos BI, Crystal Reports, FileMaker Pro, Microsoft Access, QlikView, and Tableau.

Couchbase as OData

The CData Cloud Driver is a lightweight server-side application that proxies live Couchbase data, making it easily accessible across platforms and devices. With the Cloud Driver, you can expose Couchbase data through many standards based interfaces, like OData, SOAP, REST, HTML, RSS, ATOM, JSON, XML, and CSV.

Java-Based Tools & Development

The JDBC Driver will allows you to connect to many BI tools (such as Cisco Information Server, ColdFusion, DbVisualizer, Informatica, and Pentaho) or create your own custom Java applications to work with your Couchbase data.

.NET Tools & Development

The ADO.NET Provider gives those of you who are .NET developers the opportunity to connect their applications to Couchbase data. We do this by granting access to the data as if it were a set of SQL tables, allowing you to simply DataBind to the data, just like using Relational Databases.

Mobile Development

If you are interested in developing mobile applications that consume social media data, then the ADO.NET Providers for Xamarin are just the products for you. With our provider, you connect to real-time data using standard Xamarin data access, meaning you can easily create iOS and Android apps that consume Couchbase data!

Looking Forward & Free Trials

We’re excited to embark on this partnership with Couchbase and look forward to helping Couchbase customers connect with their data. You can rest assured that as Couchbase updates and improves their product and APIs, CData Software will be there, every step of the way, implementing any changes and improvements so that you never have a lapse in production.

You can download a free, 30-day trial of any of CData’s drivers and start working with your Couchbase data in all of your favorite BI, ETL, reporting, and custom applications today!

Author

Posted by Jerod Johnson, Technology Evangelist, CData Software

Jerod Johnson is a Technology Evangelist at Cdata Software. A big thank you for his contribution to the blog post.

One Comment

  1. Hi,

    I have tried to connect couchbase db using CData ODBC driver through Microfocus UFT tool.I’m getting “table not loaded in the schema ” error. My code is,
    Dim dbexample
    Dim sqlQuery

    ‘ Create the conection object.
    Set dbexample = CreateObject(“ADODB.Connection”)
    dbexample.Open(“Driver={CData ODBC Driver for Couchbase};User=’Administrator’;Password=’Trial1′;Server=’http://localhost’;”)
    ‘Your query info will be different
    sqlQuery = “SELECT name FROM travel-sample;”

    ‘ Get the recordset returned from a select query.
    Set recordset = dbexample.Execute(sqlQuery)

    ‘ Display the results of the query.
    print recordset.GetString

    ‘ Close the database connection.
    dbexample.Close
    Set dbexample = Nothing

    Pls help me to fix this.

Leave a reply