Joining Couchbase and SQL data and doing multi-datasource analytics – Tutorial

Table of Contents

• Introduction
• Getting Started With Couchbase Analytics
  • Connecting to Couchbase
  • Writing Your First Query
  • Creating Your First Visualization
    • Create a New Dashboard
    • The Analyze Screen
    • Visualization Settings
• Drilldowns
• Joining Across Datasources
  • Joining Coucbhase With MySQL Data
• Search-based Analytics & Self-Service Analytics with Knowi
• Summary

Introduction

Couchbase is a leading Engagement Database built on powerful NoSQL technology enabling enterprises to store and query large amounts of multi-structured data. Couchbase is quickly becoming an integral part of many enterprise data stacks who aim to revolutionize their digital experience.

However, most existing analytics platforms are architected to understand well-structured data. As a result, if you want to do Couchbase analytics, your modern Couchbase data must be transformed into relational form either through custom coding or cumbersome ETL processes to work with most existing analytics platforms. In addition, most platforms don’t offer a seamless way to blend your Couchbase data with other NoSQL or relational databases like MySQL.

Knowi is an analytics platform that natively integrates with Couchbase so you can leverage the query power of Couchbase to visualize large amounts of data rapidly. Knowi allows you to query Couchbase buckets directly using N1QL, or use a drag-and-drop interface to build queries quickly without prior knowledge of the query syntax. Knowi also natively integrates with over 30 data sources, allowing you to blend your Couchbase data with other SQL, NoSQL, or REST-API sources on the fly, then create new datasets used for downstream analytics. From there, you can choose from a host of visualizations options to create custom interactive dashboards, run ad-hoc analysis, use search-based analytics to ask questions from your data, and more.

This post walks you through the steps of setting up connectivity to your Couchbase database and creating interactive visualizations from it using Knowi. In this demo, we’ll be analyzing sample data pertaining to an email marketing campaign. The topics we’ll cover include: 

1. Connecting your Couchbase data source to Knowi
2. Writing your first query
3. Creating your first visualization
4. Adding Drilldowns
5. Joining across multiple data sources (MySQL)
6. Using Knowi’s search-based natural language processing (NLP)

Sign up for a free Knowi account here to get started.

Getting Started With Couchbase Analytics

In this section, we’ll go through the steps of using the Knowi UI to connect to your Couchbase data source to visualize and analyze data from it.

Connecting to Couchbase

Knowi has broad native integration to other NoSQL, SQL, REST-API, and JSON/CSV data sources. Your data stays at the source so there are no ETL processes to build or ODBC drivers to install.

After logging in to Knowi, we’ll start by establishing a connection to your Couchbase data source.

Steps:

1. From the Playground dashboard, select “Datasources” on the left-hand side panel then click ‘New Datasource’
2. Select Couchbase from the list of datasources
3. Once in the ‘New Datasource’ page, start by giving your datasource a name
4. Enter your Couchbase credentials including the Hostname and Bucket Name
5. Optionally, set a value under “Dataverse” to connect to Couchbase Analytics
6. Click ‘Test Connection’ to confirm successful connection to the Couchbase instance
7. Hit ‘Save’

Writing Your First Query

Once connected to your Couchbase instance, Knowi automatically pulls a list of your buckets along with field samples. To start building your queries, Knowi gives you the option to auto-generate your queries using its drag and drop Query Builder via the UI. This is especially useful for users not as familiar with N1QL. For more advanced users, you also have the option to write your queries directly in the smart Query Editor, a versatile text editor specialized for editing code.

In this example, we’ll select the sending-activiy bucket (containing email marketing data) and select the fields we want to analyze from the auto-generated fields from the Query Builder.

Steps:

1. Open the Query Generator by clicking ‘Start Querying’
2. In the ‘Bucket/Dataset drop down menu, choose the sending-activity bucket
3. In the ‘Metrics’ dropdown, select the fields customer, message_type, sent, and opened
4. Notice that in the Query Editor to the right, a native N1QL query is being auto-generated
5. If you already knew the query you needed, you could’ve pasted or written it directly
6. Click ‘Preview’ to to instantly preview the results, returned in tabular format
7. After previewing the results, give your query a name then hit ‘Save & Run Now’

Creating Your First Visualization

Once the query is saved, Knowi creates a “Virtual Dataset” from the query results and stores it in its “Elastic Store” data warehouse that can store and track the results. Unlike traditional warehouses that require complex ETL processes and pre-defined schema, the elastic store is a flexible, scalable, schema-less warehouse. The stored virtual dataset is reusable and will be the foundation for most of what you’ll do in Knowi, like creating visualizations, adding them to dashboards, and much more.

In this example, we’ll create a stacked column bar chart that shows the total sent emails for each customer by message type. First, we’ll create a new dashboard then the chart itself.

Creating a New Dashboard

Steps:

1. On the left-hand side panel, click ‘Dashboards’ 
2. Hit the ‘+’ icon to create a new dashboard and give it a name then click ‘OK’
3. Drag the widget/report you previously created into the dashboard. By default, it will be in grid form

The Analyze Screen

1. On the top-right corner of the widget, click the ‘More Settings’ icon then select ‘Analyze’
2. In the following screen drag customer and message_type to the ‘Groupings/Dimensions’ section
3. Drag sent to the ‘Fields/Metrics section. In the ‘Operation’ dropdown, select ‘Sum’’

Visualization Settings

1. At the top of the screen, click the ‘Visualization’ tab which takes you to the visualization settings screen. We want to create a stacked column chart with customer in the x-axis and sum of sent in the y-axis
2. In the ‘Settings’ section under the ‘Visualization Type’ dropdown, select ‘Stacked Column’
3. In the ‘Options’ section under the ‘Grouping/Legend’ dropdown, select message_type. We can now visualize the total number of emails by message type for each customer
4. Hit the ‘Clone’ icon on the top right, to create a new widget derived from the original. This allows us to keep the original widget as is
5. Give the cloned widget a name, then add it to the dashboard

Drilldowns

Drilldowns allow you to visually navigate and analyze data in powerful ways. They can be set into another widget, another dashboard, or the same dashboard. Drilldowns can be many levels deep with support for combining different drilldown modes. Data from the parent widget can be used as keys into the drilldown widget (or dashboard) to filter the data specifically for the point selected. Drilldowns can be configured using the ‘Drilldowns’ menu option on each widget in the dashboard.

In this example, we’ll set up a “Widget” drilldown from the stacked column chart (Parent) widget into the original data grid chart that filters the results based on a specific customer.

Steps:

1. On the top-right corner of the bar chart widget, click the ‘More Settings’ icon then select ‘Drilldowns’. The drilldown menu box will appear
2. Under the ‘Drilldown type’ dropdown, select ‘Widget’
3. For ‘Drill into’, select the name of the widget you want to drill into
4. For ‘Optional Drilldown Filters’ select ‘customer’ = ‘customer’ 
5. Hit ‘Save’
6. Remove the original grid chart widget from the dashboard
7. In the bar chart, click on any of the bars representing each customer (i.e. Wells Fargo)
8. By clicking on Wells Fargo, we were able to “drill down” in the original grid chart, but this time, only showing details for the customer Wells Fargo

Joining Across Datasources

Couchbase is a powerful engagement database to support Customer 360 and other digital transformation initiatives. Even so, if you’re like most organizations, your data stack also includes other NoSQL, Relational, and API sources. What if you still need to do MySQL analytics or analytics with REST API data? Knowi uniquely enables you to dynamically blend data from multiple sources and eliminate costly ETL processes. All join types are supported, including INNER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT OUTER JOIN, and LOOP JOIN.

In this example, we’ll connect to another data source, a MySQL database that stores customer information. Once connected, we’ll perform a join against the MySQL database to pull customer addresses. We will then preview each section of the join, then do a final preview on the combined dataset.

Joining Couchbase With MySQL Data

Since we’ve already created a query for the sending-activity bucket, let’s go back and edit it to add a join to the customer table in the MySQL database.

Steps:

1. Repeat the steps from the “Connecting to Couchbase” section, this time, select a MySQL database as the data source
2. Accept the MySQL default credentials, test the connection, give it a name, then Save
3. Return to your original Couchbase query and click “Join” near the bottom of the screen
4. Select the MySQL data source you just saved as the data source
5. This will populate the “Join Fields” section and another “Query Builder” and “Query Editor” sections below the first one
6. In the “Tables” drop-down menu, select the customer table
7. In the “Metrics” drop-down menu, select the fields customer, street, and state
8. Optional — Use the Eye icon on the left-hand side of the page near each of the queries to preview the result of each join part separately

So far, we have the query from Couchbase that gives us the customer, the type of email and how many were sent and opened. From the MySQL table, we get address information from the same customers. Now, it’s time to join them together.

1. In the “Join Fields” section click “Join Builder”. Note that you can also type in the join free-hand in the text bar by entering “customer=customer”
2. Once the fields are retrieved, select ‘INNER JOIN’ as the ‘Join Type. Under ‘Left Field’ (Couchbase side), select the key field customer. Under ‘Right Field’ (MySQL side), you’ll also select the key field customer then Save
3. Click “Preview’ to view the new blended dataset results
4. After reviewing the results, hit ‘Save & Run Now’

In the new combined dataset, we have customer, message_type, sent, and opened fields from Couchbase and the street and state fields form MySQL, joined on the key field customer. As you can see, we were able to easily run the queries from each side of the join then combine them to get the results with just a few clicks. We can now use this combined dataset to create new reports and visualizations.

Search-based Analytics & Self-Service Analytics with Knowi

Knowi’s search-based analytics is a powerful way to enable self-service analytics to non-technical users, by asking questions in plain English to drive insights and visualizations quickly. This gives anyone the ability to make better, data-driven decisions, any time. This feature is available for use across all datasets and widgets within the platform. It can be accessed at the text bar at the top of a dashboard or using the Natural Language/Self Serve Analytics icon on a widget.

Below, we’ll use this feature to ask a simple question from the data: What is the total number of emails sent for each customer? We will then create a brand new pie chart widget from the answer.

Steps:

1. Navigate back to the dashboard you created earlier
2. In the NLP text bar at the top of the dashboard, type “total sent by customer”
3. Notice that as you type, Knowi makes auto-suggestions about the question you are asking
4. When the results are returned, you can check to confirm that the “Sum” operation was performed on the sent field
5. At the top of the screen, click the “Visualization” tab. You’ll see that Knowi already selected a pie chart as the default visualization. You may change it as needed
6. Click “Create Widget”, give it a name, then click “+Create” and it to the dashboard

By simply typing in questions, you were able to instantly analyze and create a visualization from your data. You can try asking the questions below on your own to get a better feel for it.

Simple field selection:

"bounced, sent, customer"
"Show me all for Wells Fargo"
"bounced and sent for Wells Fargo"

Aggregations:

"Sum of sent sum of open by customer"
"Total sent by customer by week"

Dates:

“sum delivered weekly”
“average sent monthly”
“conversion rate by date by customer”

Summary

In summary, we used Knowi to connect to your Couchbase data source and query it using a dynamic query builder. We demonstrated how to quickly create visualizations from the dataset, blend data on the fly between Couchbase and a MySQL database, and use search-based analytics to ask questions and gain insights from the data instantly. Try Knowi to learn more about how its analytics capabilities can leverage the strength of your Couchbase implementation.