Earlier this week, Couchbase launched the latest version of its flagship NoSQL database, 4.5, with powerful additions to N1QL, faster queries and indexing, role based security and a range of other features.
At Cloud9 Charts, we are pleased to add support for 4.5 to bring native Business Intelligence (BI) integration into Couchbase, leveraging the latest N1QL 4.5 features while maintaining backward compatibility.
The 4.5 release brings forth a number of query related improvements that are particularly useful from a BI & reporting perspective:
- A new INFER command to determine fields and associated meta data
- High performance JOINs with Index-JOIN support
- Array indexing, that boosts the performance of queries involving arrays
- Memory-optimized indexes that improves query performance under write-intensive workloads
The new INFER command provides metadata of the document structure, as well as providing a sneak-peek of values within it. For example, running INFER database in the Couchbase Query Console returns the document structure, fields, type and sample field data:
We utilize the INFER command to determine the fields displayed for dynamic N1QL query generation (for versions prior to 4.5, we fall back to querying the first 100 documents to build the field suggestions).
See this action in few simple steps:
- Go to www.cloud9charts.com/couchbase. This is pre-populated with a live Couchbase database with travel-sample and beer-sample buckets to easily follow along.
- Expand Query Generation section. Choose travel-sample. This triggers the INFER command to determine the fields to populate Metrics, Dimensions, Filters and Sort fields.
- Use the dropdowns to auto-generate N1QL queries using a point and click interface to build a simple count aggregation by the type field as below.
- Click on Show me to instantly visualize and see results.
N1QL, with SQL like familiarity while fully supporting JSON, is arguably the best query syntax that we’ve worked with, inside the NoSQL ecosystem. If you are using Couchbase or planning to use Couchbase, leverage N1QL to it’s fullest using Cloud9 Charts, the only fully native end-to-end BI solution on Couchbase.