Couchbase Data Platform now supports hybrid transaction/analytical processing (HTAP) workloads. Plus, our massively parallel processing (MPP) query engine lets users run ad-hoc analytical queries that deliver faster insights without impacting performance.
Couchbase Analytics reduces the time to insight on operational data by providing these key features:
Fast ingest: Make data available for analytical processing in milliseconds
NoETL for NoSQL: Process JSON data in its natural form without any transformation or schema design
Workload isolation: Run ad hoc queries without impacting application performance
Ad hoc querying: Business users can explore data and perform complex joins and aggregations
N1QL for analytics: It’s the first commercial implementation of the SQL++ language for querying schemaless semi-structured JSON data
Many of the popular visualization tools don’t natively support JSON integration but do support connectivity through standard technologies like JDBC, ODBC, or ADO.NET. With the CData drivers, any BI tool that supports SQL-based connectivity (like Tableau, Power BI, MicroStrategy, Excel, and more) will be able to visualize data stored in Couchbase Analytics.
As an example, I’ve enclosed a screenshot of a visualization in Tableau of data coming from Couchbase Analytics.
CData Drivers for Couchbase
The CData Drivers for Couchbase provide a SQL interface for NoSQL data sources, like Couchbase Analytics. Through innovative flattening techniques and a powerful, built-in SQL engine, CData drivers seamlessly translate SQL queries into SQL++, leveraging the server-side capabilities of Couchbase Analytics to provide real-time data for BI and analytics. This is especially critical for tools that generate SQL queries internally to request data from the connected data sources. For example, given a sample ‘cars’ document, you could expect Tableau to generate a SQL query. That SQL query will be translated to SQL++ by the CData driver.
Sample cars document
Sample SQL Query
SELECT ModelType, COUNT(*) AS Total FROM cars GROUP BY ModelType;
The SQL query is translated by the driver to SQL++.
Sample SQL++ Query
SELECT `cars`.`ModelType`, COUNT(*) AS `total` FROM `cars` GROUP BY `cars`.`ModelType`;
While the differences between the SQL and SQL++ queries may seem small, they are exaggerated when working with tools that generate SQL queries based on a graphical user interface (like Tableau). Since the user has no control over the query generated, these differences mean that a SQL to SQL++ translation is not only critical, but necessary for performing BI, analytics, and reporting in SQL-based tools. Since the CData drivers translate to pure SQL++, they are able to leverage the powerful server-side processing capabilities of the Analytics service.
SQL to SQL++ Interface
How do the CData drivers create the SQL interface for JSON data? As we explain, we’ll refer to the above sample document.
CData drivers flatten objects using a dot notation to represent the original hierarchical structure. For example, to access the WindshieldWiper field of the Indicator object, you would refer to the column cars.Indicator.WindshieldWiper. Many tools do not allow periods in column names, so where needed, the CData drivers will create temporary schema definitions in memory to enable connectivity from such tools. Below you’ll see an example SQL query, the corresponding SQL++ query, and the query results.
SQL: SELECT Indicators_WindshieldWiper FROM cars;
SQL++: SELECT `cars`.`Indicators`.`WindshieldWiper` FROM `cars`;
CData drivers flatten arrays within JSON documents using 0-based indexing, similar to what you see in other programming languages. For example, to access the 1st and 2nd entries in the TirePressure array, you would refer to the columns cars.TirePressure.0 and cars.TirePressure.1. Below you’ll see an example SQL query, the corresponding SQL++ query, and the query results.
SQL: SELECT TirePressure_0, TirePressure_1 FROM cars;
SQL++: SELECT `cars`.`TirePressure`, `cars`.`TirePressure` FROM `cars`;
As the Analytics service and SQL++ interfaces grow and evolve, so to will the CData drivers, implementing new query features as they are added and continuing to leverage the powerful data processing capabilities of the Analytics service to allow Couchbase users to quickly and easily work with, visualize, and report on their data.
Try Couchbase 6.0 and CData today
Download the new release of Couchbase and the latest CData drivers to start working with Couchbase data in the SQL-enabled tools and applications you already use. We look forward to your feedback on the Couchbase forums.
This blog post is a collaboration between Couchbase and CData. A big thank you to Jerod Johnson, Technology Evangelist at CData software, for his contribution to this blog post.