This is a guest blog post by Jay Gopalakrishnan, Founder of Cloud9 Charts, a Business Intelligence platform specifically focused on NoSQL databases. Follow them at @cloud9charts.
We are thrilled to announce support for N1QL within Cloud9 Charts at Couchbase Connect 2015. N1QL is a powerful query language that not only provides SQL familiarity, but also works with multi-value attributes and nested objects within Couchbase Server 4.0. Cloud9 Charts enables data discovery, N1QL query generation, analysis, data blending & multi-datasource joins, along with easily shareable, embeddable dashboards from Couchbase data.
In this blog post, let’s take a hands-on look at how to generate visual insights from Couchbase instantly using N1QL and Cloud9 Charts.
Overview of the Combined Solution:
- Eliminates the need for ETL into relational databases for BI purposes.
- Native integration with N1QL without shoehorning unstructured data to a relational form.
- Data Discovery: Connect and discover buckets & fields.
- Query Generation: Automatically generate and explore N1QL queries and aggregations using a visual interface.
- Advanced Enterprise Analytics: Perform complex analytics, filters and merge data from multiple Couchbase buckets along with other structured and unstructured data sources.
- Instant Visualizations: Generate shareable, embeddable dashboards.
- Connect from the Cloud or on-premise.
Please visit our Couchbase Instant Analytics page to get started. The page is pre-populated with settings to a live demo Couchbase database to make it easier to follow along.
The Report section contains a N1QL query that looks like the following:
select count(name) as
Count of name,
This is a simple aggregation that groups the counts by type from a beer-sample bucket.
Click on the Show Me button. This will execute the N1QL query and visualizations are automatically created:
Now, let’s add a Geo heatmap of brewery locations to the same dashboard. In the following section, we’ll discover geo fields, auto generate N1QL queries and set up a geo heatmap visualization.
1. Click the ‘Sign Up’ button to create and save the account (it’s free to get started).
2. Click on Data sources from the settings menu. Select Couchbase use the default demo database for Couchbase.
Note: Alternatively, you can also connect to your own Couchbase instance. If the database is inside your network, follow the prompts to download the agent. The agent can facilitate secure connectivity to your own database inside your network.
3. Click on Configure Queries. From the Query generator section,
i. set the Report Name to Brewery Locations.
ii. Choose the beer-sample bucket. This will trigger a field discovery process to determine a set of fields associated to the bucket.
iii. Within the metrics dropdown, click to select geo and name as metrics
iv. Click on filters, select type and enter brewery
Note that the queries will be auto-generated as you add/change any of the fields. You can also plug in queries directly. The N1QL looks like this:
type = “brewery”
v. Click on preview for immediate preview of the data.
vi. Save the Report.
4. Click on Dashboards. Drag & drop the newly created report into the dashboard. From the widget settings menu, change the widget to a Geo Heatmap, as shown in the GIF below:
In a few simple steps, we’ve used N1QL on Couchbase to derive insights on the data with easily customizable, shareable and embeddable dashboards.
Meet us at Couchbase Connect!