by Jerod Johnson | March 11, 2019

Updated Couchbase Drivers Offer Unparalleled Functionality

Couchbase Logo

Couchbase Server is an open-source, distributed, NoSQL document-oriented engagement database. It exposes a fast key-value store with managed cache for sub-millisecond data operations, purpose-built indexers for fast queries, and a powerful query engine for executing SQL-like queries. CData Software Drivers offer unparalleled performance and functionality for integrating with live Couchbase data in all of the popular BI, reporting, and ETL tools.

Key Features

  • Support for Couchbase 4.0 and above
  • Innovative, fully configurable SQL to NoSQL integration
  • Full read/write SQL-92 support for the Query (N1QL) service
  • Full read SQL-92 support for the Analytics (SQL++) service

Since Couchbase Server 4.0, Couchbase data has been natively accessible for reading and writing through the Couchbase Query service using N1QL, a proprietary SQL dialect. With Couchbase Server 6.0, Couchbase released a way to access Couchbase data using complex ad-hoc JOIN, set, aggregation, and grouping queries: the Analytics service. The latest version of the CData Drivers allows you to connect to both services for an optimized Couchbase experience from any tool, platform, language, or framework that supports standard SQL.

SQL Over Couchbase

When you issue complex SQL queries from your various tools (for building visualizations, reporting, and data migration), the CData Drivers translate those SQL queries into N1QL or SQL++, pushing every supported operation, from filters and aggregations to JOINs and more, directly to the Couchbase server. The drivers leverage the powerful data processing capabilities of the Couchbase Server and free up local resources, while easing the processing burden on your local machine. Thanks to built-in dynamic metadata querying, your tool, platform, language, or framework sees recognizable data types and relational structures, even though the underlying data is NoSQL.

Supporting the Query (N1QL) Service

The Couchbase Query service supports reading from and writing to Couchbase documents. As such, it is the service of choice for users who wish to insert, update, or delete Couchbase documents using CData Drivers. When connected to the Query service, the CData Drivers will translate SQL-92 queries to N1QL, leveraging the Couchbase server for complex data processing. Some examples of SQL-92 queries translated to N1QL follow:

SQL Query N1QL Query
SELECT * FROM Users SELECT META(`USERS`).id AS `id`, ... FROM `Users`
INSERT INTO users
   ([Document.Id], age, status)
VALUES
   ("bcd001", 45, "A")
INSERT INTO `users`
   (KEY, VALUE)
VALUES
   ('bcd001',{"age":45, "status":"A"})
UPDATE users SET
   status = "C"
WHERE
   [Document.Id] = "bcd001"
UPDATE `users`
USE KEYS "bcd001"
SET
   `status`="C"
DELETE FROM users
WHERE
   [Document.Id] = "bcd001"
DELETE FROM `users`
USE KEYS "bcd001"

Supporting the Analytics (SQL++) Service

While the Query service offers write access to Couchbase documents, the Analytics service offers an optimized query experience, handling ad-hoc (i.e. index-free) JOIN, filtering, aggregating, and grouping queries. For Couchbase users who wish to work with real-time Couchbase data, Analytics is the service of choice. As with the Query service, CData Drivers translate SQL-92 queries to SQL++, again leveraging the Couchbase server to handle complex data processing. Sample translations (SQL-92 to SQL++) follow:

SQL Query N1QL Query
SELECT
   type AS t, SUM(price)
FROM
   [Default.beer-sample] beer
GROUP BY t
SELECT
   `beer`.`type` AS `t`,
   SUM(`beer`.`price`) AS `SUM_price`
FROM
   `Default`.`beer-sample` AS `beer`
GROUP BY `beer`.`type`
SELECT
   flight.number,
   airport.id
FROM
   [Default.flight] flight
INNER JOIN [Default.airport] airport
ON
   flight.airport = airport.id
SELECT
   `flight`.`number` AS `flight.number`,
   `airport`.`id` AS `airport.id`
FROM
   `Default`.`flight` AS `flight`
INNER JOIN `Default`.`airport` AS `airport`
ON
   TONUMBER(`flight`.`airport`) =
   TONUMBER(`airport`.`id`)

Unparalleled Access to Couchbase Data

CData Software has worked closely with the Couchbase engineering team to ensure that the CData Drivers are making full use of N1QL and SQL++ wherever possible and are providing the best Couchbase experience available within third-party tools, applications, platforms, and frameworks. Thanks to an ongoing partnership, the CData Drivers will be updated with new features and functionality in step with any updates to the Couchbase services.

Free Trial & More Information

Thanks to the CData Drivers, Couchbase users can connect to live Couchbase data from an overwhelming majority of popular BI, analytics, and reporting tools to drive business. Get started with a free, 30-day trial of any CData Couchbase driver. For more videos, head over to our YouTube channel. As always, let us know if you have any questions during your evaluation. Our world class Support Team is always available to help.