The Couchbase Data Platform in Action: Analytics

The front end UI code is in web/client/src/components/views/Analytics.vue. It has some selectors to parametrize the queries, and some visualization components for the results.

We visualize the data two ways. One uses a line graph to show aggregate values. (We use Chart.js for this.) The graph is interactive. Clicking on an aggregate point brings up a table with details. We won’t go into more detail here. Most of the work is done on the server side. The information gets prepared there in a way that’s easily consumed by the client.

The data is retrieved via REST endpoints written in Node.js. As with the other REST endpoints, the Node server-side code mostly wraps queries to the database.

We chose to organize this via four endpoints: analyticsByAge, analyticsByAgeDetails, analyticsSocial, and analyticsSocialDetails.

The code for a grouping (age, social media) is similar. Let’s take a look at analyticsByAge. The code is in web/server/controllers/searchController.js.

The Express setup code (in app.js) takes care of connecting to the cluster. It passes two globally shareable control objects, the couchbaseobject, which provides some general interfaces, and the clusterobject, which is specific to a single cluster.

Past that, the listing above breaks down into two blocks, generating the query, then handling the results.

CBAS uses SQL++. Developed in collaboration with UC San Diego, UC Irvine, and Couchbase, SQL++ is an advanced query language designed for the JSON format, yet is backward-compatible with SQL.

The query pulls from two document types, Conditons, and Patients. What we want to graph is the number of patients with a condition, listed by the month and year the condition started, binned by age. Walking through the query, we see we’re getting just that from the SELECT clause.

The FROM clause might seem a bit surprising. If you look back at the configuration, you’ll see we’ve set up two datasets, condition and patient. These are just analytics buckets comprising the corresponding document types.

We then have a series of selectors that are the real heart of the query. The first condition substring_after(c.subject.reference, "uuid:") /*+ indexnl */ = meta(p).id is especially interesting. This limits the results to only those documents where the subject (i.e. the patient) in the condition record matches the patient id of the patient document. In other words, it’s performing an inner join.

In CBAS, inner joins, by default, use a hash algorithm. The short sequence that looks like a comment (/*+ indexnl */) is actually a hint to the query compiler that it should try to perform an index nested loop join. This can be more efficient than a hash, but requires an index. Here, the index comes for free in the form of the document keys (document id) of the patient records.

The rest of the query does things like filter by condition, limit the data to a 10-year range, groups and orders the output, and splits it into 30-year chunks by patient age. It only deviates from regular SQL in one other place. A patient can have more than one address.

We want to filter by city, if the user chooses. We can do that directly, even though the patient addresses are kept in an array. In this case, with the snippet AND p.address[0].city = '${req.query.city}', you can see we’ve hard-coded picking the first address. Other operators give you more sophisticated ways of handling this. For example, you could generate separate results for each array entry.

Optimizing queries can be critical to application success. With that in mind, I want to dive a little deeper on the join. The Couchbase admin console can display a query plan, essentially a break down of the operations the query planner maps out.

Further, we see at the bottom of the comparison the version with no hint ran extra data scans and projections to pull in the needed information.

I want to call out one other tidbit that helps me with reading these plans. Looking through you see a lot of sections referring to an exchange operation. Loosely speaking, exchange operations indicate where data is partitioned up and processed in parallel. You can read more about them in this blog post. For an even more complete understanding, read section D, part 2 on connectors in the Hyracks Library here.

Once we have the complete SQL++ query as a string, we use the CbasQuery sub-object of the couchbase object to turn that into a query we can hand off to the cluster.

The code that follows manipulates the results into the form used by Chart.js. The code may look a little convoluted. That’s because we want to plot only those months where at least one patient had a diagnosis. We also have to account for results sets where the same month may have patients in different age groups. Here’s small sample of what the data looks like.

As you can see, February of 2009 had no incidents, while January had patients that fell into two different age brackets.

Going back to the code, we see the first loop creates two data structures.

The labels array will give the text along the x-axis of our graph with the year and month of the onset of the condition. The code only adds entries for dates included in the result set. This may not include every month over the range spanned. (In essence, we’re removing months where no patients contracted a condition from the graph.)

The stats variable is effectively a two dimensional array that totals the number of patients for each age group and year/month of onset. This will be the actual data plotted.

Once we have the data in this form, the second loop iterates over the stats and creates the structure Chart.js expects.