DEV Community: Pavel Tiunov

Time Series Anomaly Detection Algorithms

Pavel Tiunov — Tue, 12 Mar 2019 15:02:37 +0000

This article is an overview of the most popular anomaly detection algorithms for time series and their pros and cons.

This post is dedicated to non-experienced readers who just want to get a sense of the current state of anomaly detection techniques. Not wanting to scare you with mathematical models, we hid all the math under referral links.

Important Types of Anomalies

Anomaly detection problem for time series is usually formulated as finding outlier data points relative to some standard or usual signal. While there are plenty of anomaly types, we'll focus only on the most important ones from a business perspective, such as unexpected spikes, drops, trend changes and level shifts.

Imagine you track users at your website and see an unexpected growth of users in a short period of time that looks like a spike. These types of anomalies are usually called additive outliers.

Another example with the website is when your server goes down and you see zero or a really low number of users for some short period of time. These types of anomalies are usually classified as temporal changes.

In the case that you deal with some conversion funnel, there could be a drop in a conversion rate. If this happens, the target metric usually doesn't change the shape of a signal, but rather its total value for a period. These types of changes are usually called level shifts or seasonal level shifts depending on the character of the change.

Basically, an anomaly detection algorithm should either label each time point with anomaly/not anomaly, or forecast a signal for some point and test if this point value varies from the forecasted enough to deem it as an anomaly.

Using the second approach, you would be able to visualize a confidence interval, which will help a lot in understanding why an anomaly occurs and validate it.

Statsbot 's anomaly report. Actual time series, predicted time series and confidence interval help understand why anomaly occurs.

Let's review both algorithm types from the perspective of appliance to finding various types of outliers.

STL decomposition

STL stands for seasonal-trend decomposition procedure based on Loess. This technique gives you an ability to split your time series signal into three parts: seasonal, trend and residue.

From top to bottom: original time series, seasonal, trend and residue parts retrieved using STL decomposition.

As the name states, it is suitable for seasonal time series, which is the most popular case.

The not obvious part here is that you should use median absolute deviation to get a more robust detection of anomalies. The leading implementation of this approach is Twitter's Anomaly Detection library. It uses Generalized Extreme Student Deviation test to check if a residual point is an outlier.

Pros

Pros of this approach are in its simplicity and how robust it is. It can handle a lot of different situations and all anomalies can still be intuitively interpreted.

It's good mostly for detecting additive outliers. To detect level changes you can analyze some rolling average signal instead of the original one.

Cons

The cons of this approach are in its rigidity regarding tweaking options. All you can tweak is your confidence interval using the significance level.

The typical scenario in which is doesn't work well is when characteristics of your signal have changed dramatically. For example, you're tracking users on your website that was closed to the public, and then was suddenly opened. In this case, you should track anomalies that occur before and after launch periods separately.

Classification and Regression Trees

Classification and regression trees is one of the most robust and most effective machine learning techniques. It may also be applied to anomaly detection problems in several ways.

First, you can use supervised learning to teach trees to classify anomaly and non-anomaly data points. In order to do that you'd need to have labeled anomaly data points.
The second approach is to use unsupervised learning to teach CART to predict the next data point in your series and have some confidence interval or prediction error as in the case of the STL decomposition approach. You can check if your data point lies inside or outside the confidence interval using Generalized ESD test, or Grubbs' test. * Actual time series (Green), predicted time series made using CART model (Blue), and anomalies detected as deviation from forecasted time series.

The most popular implementation to perform learning for trees is the xgboost library.

Pros

The strength of this approach is that it's not bound in any sense to the structure of your signal, and you can introduce many feature parameters to perform the learning and get sophisticated models.

Cons

The weakness is a growing number of features can start to impact your computational performance fairly quickly. In this case, you should select features consciously.

ARIMA

ARIMA is a very simple method by design, but still powerful enough to forecast signals and to find anomalies in it.

It's based on an approach that several points from the past generate a forecast of the next point with the addition of some random variable, which is usually white noise. As you can imagine, forecasted points in the future will generate new points and so on. Its obvious effect on the forecast horizon: the signal gets smoother.

The difficult part in appliance of this method is that you should select the number of differences, number of autoregressions, and forecast error coefficients.

Another obstacle is that your signal should be stationary after differencing. In simple words, it means your signal shouldn't be dependent on time, which is a significant constraint.

Anomaly detection is done by building an adjusted model of a signal by using outlier points and checking if it's a better fit than the original model by utilizing t-statistics.

Two time series built using original ARIMA model and adjusted for outliers ARIMA model.

The favored implementation of this approach is tsoutliers R package. It's suitable to detect all types of anomalies in the case that you can find a suitable ARIMA model for your signal.

Exponential Smoothing

Exponential smoothing techniques are very similar to the ARIMA approach. The basic exponential model is equivalent to the ARIMA (0, 1, 1) model.

The most interesting method from the anomaly detection perspective is Holt-Winters seasonal method. You should define your seasonal period which can equal to a week, month, year, etc.

In the case you need to track several seasonal periods, such as having both week and year dependencies, you should select only one. Usually, it'll be the shortest one: a week in this example.

This is clearly a drawback of this approach, which affects the forecasting horizon a lot.

Anomaly detection can be done using the same statistical tests for an outlier, as in the case of STL or CARTs.

Neural Networks

As in the case of CART, you have two ways to apply neural networks: supervised and unsupervised learning.

As we're working with time series, the most suitable type of neural network is LSTM. This type of Recurrent Neural Network, if properly built, will allow you to model the most sophisticated dependencies in your time series as well as advanced seasonality dependencies.

This approach can also be very helpful if you have multiple time series coupled with each other.

This area is still on-going research, and it requires a lot of work to build the model for your time series. Should you succeed, you may achieve outstanding performance results in terms of accuracy.

💡To Keep in Mind 💡

Try the simplest model and algorithm that fit your problem the best.
Switch to more advanced techniques if it doesn't work out.
Starting with more general solutions that cover all the cases is a tempting option, but it's not always the best.

At Statsbot, to detect anomalies at scale we use different combinations of techniques starting with STL and ending with CART and LSTM models.

Simple Tips for PostgreSQL Query Optimization

Pavel Tiunov — Wed, 06 Mar 2019 14:21:00 +0000

A single query optimization tip can boost your database performance by 100x. At one point, we advised one of our customers that had a 10TB database to use a date-based multi-column index. As a result, their date range query sped up by 112x. In this post, I share five simple but still powerful tips for PostgreSQL query optimization.

I use these techniques a lot to optimize our customers PostgreSQL databases with billions of data points during Cube.js deployments.

To keep it easy, I ran examples for this article on a test dataset. Although it doesn’t show the actual performance improvement, you will see that our tips solve the significant set of optimization problems and work well in real-world case scenarios.

Explain analyze

Postgres has a cool extension to the well-known EXPLAIN command, which is called EXPLAIN ANALYZE. The difference is that EXPLAIN shows you query cost based on collected statistics about your database, and EXPLAIN ANALYZE actually runs it to show the processed time for every stage.

I highly recommend you use EXPLAIN ANALYZE because there are a lot of cases when EXPLAIN shows a higher query cost, while the time to execute is actually less and vice versa. The most important thing is that the EXPLAIN command will help you to understand if a specific index is used and how.

The ability to see indexes is the first step to learning PostgreSQL query optimization.

One index per query

Indexes are materialized copies of your table. They contain only specific columns of the table, so you can quickly find data based on the values in these columns. Indexes in Postgres also store row identifiers or row addresses used to speed up the original table scans.

It’s always a trade-off between storage space and query time, and a lot of indexes can introduce overhead for DML operations. However, when read query performance is a priority, as is the case with business analytics, it’s usually a well-working approach.

I advise to create one index per unique query for better performance. Look further in this post to learn how to create indexes for specific queries.

Using multiple columns in index

Let’s review the explain analyze plan of the following simple query without indexes:

EXPLAIN ANALYZE SELECT line_items.product_id, SUM(line_items.price)
FROM line_items
WHERE product_id > 80
GROUP BY 1

An explain analyze returns:

HashAggregate (cost=13.81..14.52 rows=71 width=12) (actual time=0.137..0.141 rows=20 loops=1)
Group Key: product_id
-> Seq Scan on line_items (cost=0.00..13.25 rows=112 width=8) (actual time=0.017..0.082 rows=112 loops=1)
Filter: (product_id > 80)
Rows Removed by Filter: 388
Planning time: 0.082 ms
Execution time: 0.187 ms

This query scans all of the line items to find a product with an id that is greater than 80, and then sums up all the values grouped by that product id.

Now we’ll add the index to this table:

CREATE INDEX items_product_id ON line_items(product_id)

We created a B-tree index, which contains only one column: product_id. After reading lots of articles about the benefits of using index, one can expect a query boost from such an operation. Sorry, bad news.

As we need to sum up the price column in the query above, we still need to scan the original table. Depending on the table statistics, Postgres will choose to scan the original table instead of the index. The thing is, index lacks a price column.

We can tweak this index by adding a price column as follows:

CREATE INDEX items_product_id_price ON line_items(product_id, price)

If we rerun the explain plan, we’ll see our index is the forth line:

GroupAggregate (cost=0.27..7.50 rows=71 width=12) (actual time=0.034..0.090 rows=20 loops=1)
Group Key: product_id
-> Index Only Scan using items_product_id_price on line_items (cost=0.27..6.23 rows=112 width=8) (actual time=0.024..0.049 rows=112 loops=1)
Index Cond: (product_id > 80)
Heap Fetches: 0
Planning time: 0.271 ms
Execution time: 0.136 ms

But how would putting the price column first affect the PostgreSQL query optimization?

Column order in a multicolumn index

Well, we figured out that a multicolumn index is used in the previous query because we included both columns. The interesting thing is that we can use another order for these columns while defining the index:

CREATE INDEX items_product_id_price_reversed ON line_items(price, product_id)

If we rerun explain analyze, we’ll see that items_product_id_price_reversed is not used. That’s because this index is sorted firstly on price and then on product_id. Using this index will lead to a full index scan, which is nearly equivalent to scanning the whole table. That’s why Postgres opts to use scan for an original table.

It's a good practice to put in the first place columns, which you use in filters with the biggest number of unique values.

Filters + joins

It’s time to figure out what the best set of indexes is for a specific join query, which also has some filter conditions. Usually, you can achieve optimal results by trial and error.

As in the case of simple filtering, choose the most restrictive filtering condition and add an index for it.

Let’s consider an example:

SELECT orders.product_id, SUM(line_items.price)
FROM line_items
LEFT JOIN orders ON line_items.order_id = orders.id
WHERE line_items.created_at BETWEEN '2018-01-01' and '2018-01-02'
GROUP BY 1

Here we have join on order_id and filter on created_at. This way, we can create a multicolumn index that will contain created_at in the first place, order_id in the second and price in the third:

CREATE INDEX line_items_created_at_order_id_price ON line_items(created_at, order_id, price)

We’ll get the following explain plan:

GroupAggregate (cost=12.62..12.64 rows=1 width=12) (actual time=0.029..0.029 rows=1 loops=1)
Group Key: orders.product_id
-> Sort (cost=12.62..12.62 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)
Sort Key: orders.product_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.56..12.61 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1)
-> Index Only Scan using line_items_created_at_order_id_price on line_items (cost=0.27..4.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: ((created_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2018-01-02 00:00:00'::timestamp without time zone))
Heap Fetches: 0
-> Index Scan using orders_pkey on orders (cost=0.29..8.30 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (line_items.order_id = id)
Planning time: 0.303 ms
Execution time: 0.072 ms

As you can see, line_items_created_at_order_id_price is used to reduce scan by date condition. After that, it’s joined with orders using the orders_pkey index scan.

Date columns are usually one of the best candidates for the first column in a multicolumn index as it reduces scanning throughput in a predictable manner.

Conclusion

Above tips for PostgreSQL query optimization will help you to speed up queries 10-100x for multi-GB databases. They can solve most of your performance bottlenecks in an 80/20 manner. It doesn’t mean you shouldn’t double check your queries with EXPLAIN for real-world case scenarios.

Serverless Analytics Benchmark of AWS Aurora Performance

Pavel Tiunov — Thu, 28 Feb 2019 14:16:18 +0000

It’s well known that doing analytics on production RDBMS instances is prohibitive due to lock and performance issues. The introduction of replication by many of the modern RDBMSs made this process a little bit easier, but not ideal. The next evolution step here is Serverless RDBMSs. Splitting processing power from the storage brings our understanding of how to work with a RDBMS to a new level. This gives us an opportunity for infinite DB infrastructure scaling and allows us to have both OLTP and OLAP workload at the same time.

In this article, we perform an analytics performance benchmark of AWS Aurora MySQL using the Cube.js Serverless Analytics Framework.

TL;DR: Unoptimized 100M rows test dataset ran by Serverless MySQL Aurora can be queried in 176 seconds. A query time of less than 200ms is achievable for the same dataset using multi-stage querying approach.

Aurora MySQL Setup

Setup of Aurora MySQL is fairly simple. Just go to your RDS Service in AWS Console and select create database.

Here you should select the Aurora and MySQL compatible version that supports Serverless deployment.
Then choose Serverless and enter your credentials to access your DB. That’s it! Your Aurora instance is configured and ready to launch.

Preparing your dataset

For the purpose of testing, we’ll generate a 100M row dataset out of Sakila Sample Database. In order to do that, we’ll use slightly modified versions of Sakila tables.

First we’ll introduce the customer table:

CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  create_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (customer_id),
  KEY idx_fk_store_id (store_id),
  KEY idx_fk_address_id (address_id),
  KEY idx_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then the payment table:

CREATE TABLE payment (
  payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Quick hint: Aurora can’t be accessed using a public IP and is accessible only within VPC. To connect to it from your machine, you should use either VPN/SSH Tunnel or you can use AWS RDS query editor, which works just inside browser.

To fill the customer and payment tables we’ll use a sample of the first 600 customer rows and the first 500 payment rows from Sakila Sample Database.

We’ll then use the payment table to generate actual records for the 100M payment_big table. Its definition is very similar to payment:

CREATE TABLE payment_big (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_big_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

As MySQL doesn’t have a generator feature, we’ll introduce some helper views for that.

Integer number generator up to 16:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

Integer number generator up to 256:

CREATE OR REPLACE VIEW generator_256 AS SELECT ( hi.n * 16 + lo.n ) AS n FROM generator_16 lo , generator_16 hi;

To generate actual payments, we’ll use the following SQL, which generates 2,048,000 rows at once:

INSERT INTO payment_big (customer_id, staff_id, rental_id, amount, payment_date, last_update) 
SELECT customer_id, staff_id, rental_id, amount * 10 * RAND() as amount, TIMESTAMPADD(MINUTE, generator_256.n * 1000 + 1000 * RAND(), payment_date) as payment_date, last_update
FROM payment, generator_256, generator_16

We’ll call it 50 times to get a table with approximately 100M rows.

Cube.js setup

Cube.js is an open-source analytics framework that works as an intermediate between your analytics users and your database. It provides analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, API for query results fetch, and visualization. We’ll use it mostly for caching and pre-aggregation to get a sense of how fast Aurora MySQL analytics can be and what the delay is between data ingestion and getting insight from it.

To create a Cube.js application, you’ll need to install Cube.js CLI and Serverless CLI for deployment:

$ npm install -g cubejs-cli
$ npm install -g serverless

Then, let’s create an app:

$ cubejs create aurora-benchmark -d mysql -t serverless

In serverless.yml you should define credentials to access your Aurora MySQL and Redis instance as well as provide vpc settings to access your resources. Please learn more about deployment here.

We’ll also introduce Cube.js schema definitions for our benchmark.
schema/Customers.js:

cube(`Customers`, {
 sql: `select * from test.customer`,

 measures: {
   count: {
     type: `count`
   }
 },

 dimensions: {
   id: {
     sql: `customer_id`,
     type: `number`,
     primaryKey: true
   },

   email: {
     sql: `email`,
     type: `string`
   },

   name: {
     sql: `${CUBE}.first_name || ${CUBE}.last_name`,
     type: `string`
   }
 }
});

schema/Payments.js:

cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 }
});

That’s everything we need to deploy it so let’s do it:

$ serverless deploy -v

If everything is set up correctly, you should see something like this in your shell:

Service Information
service: aurora-benchmark
stage: dev
region: us-east-1
stack: aurora-benchmark-dev
resources: 16
api keys:
  None
endpoints:
  GET - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/
  ANY - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/{proxy+}
functions:
  cubejs: aurora-benchmark-dev-cubejs
  cubejsProcess: aurora-benchmark-dev-cubejsProcess
layers:
  None

Go to your main GET endpoint link which should look like this: https://123456789a.execute-api.us-east-1.amazonaws.com/dev/

If everything is done right, you should see a code sandbox with a sample pie chart.

Let’s create our time series chart for the Payments cube. To do this let’s add moment import to index.js:

import moment from 'moment';

You should add it as a dependency as well using the blue Add Dependency button or package.json.

Then replace renderChart and query:

const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};

After edits you should get an index.js similar to this:

import React from "react";
import ReactDOM from "react-dom";

import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import { Chart, Axis, Tooltip, Geom, Coord, Legend } from "bizcharts";
import moment from 'moment';

const API_URL = "https://123456789a.execute-api.us-east-1.amazonaws.com/dev"; // change to your actual endpoint

const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NTExODQ0NDksImV4cCI6MTU1MTI3MDg0OX0.KLkKp2pRnw9ZlrwMGkoBlpdgGy4eol7258aKVwJLPuM",
  { apiUrl: API_URL + "/cubejs-api/v1" }
);

const App = () => (
  <div style={{ textAlign: 'center', fontFamily: 'sans-serif' }}>
   <h1>Payments Count</h1>
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={({ resultSet, error }) =>
        (resultSet && renderChart(resultSet)) ||
          (error && error.toString()) || <span>Loading...</span>
      }
    />
</div>
);

const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);

If everything works well, you should see a line chart similar to this one:

Benchmark

At this point we have an unoptimized data set of 100M records in our payment_big table. We’ll use serverless logs to see how long it takes to process queries on the Aurora MySQL side as Cube.js writes these processing logs by default. To enable log tail we’ll use:

$ serverless logs -t -f cubejsProcess

If we request one month of data we’ll get a processing delay of 176 seconds:

2019-02-27T12:47:45.384Z    794618d5-5eb3-40ce-88f3-cce6d75786f1    Performing query completed:
{
    "queueSize": 2,
    "duration": 175900,
    "queryKey": [
        "SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
        []
    ]
}

Not too bad for an unoptimized table of 100M data points. Let’s add an index for payment_date to see if it can affect this time:

CREATE INDEX payment_big_date ON payment_big (payment_date)

The same query with index will be processed in 31 seconds which is great but still not fast enough to build a great user experience. What we can do is introduce pre-aggregations for this table. To do this let’s just add:

preAggregations: {
 main: {
   type: `rollup`,
   measureReferences: [count],
   timeDimensionReference: date,
   granularity: `day`,
   partitionGranularity: `day`
 }
}

to schema/Payments.js so we can get:

cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 },

 preAggregations: {
   main: {
     type: `rollup`,
     measureReferences: [count],
     timeDimensionReference: date,
     granularity: `day`,
     partitionGranularity: `day`
   }
 }
});

It’ll create a rollup table for each day of data and will refresh it incrementally. Here partitionGranularity instructs Cube.js to create rollup table per day of data. Rollup table itself will contain count measure and date dimensions with day granularity. In fact every rollup table here will contain just 1 row which contains count for specific day calculated. Let’s deploy it and see how it changes query processing times:

$ serverless deploy -v

If we request the same interval of data, Cube.js will build pre-aggregations first for each day, which takes 1.5 seconds per one day:

2019-02-27T13:33:19.267Z    11477db2-f66e-4278-9103-eefbbc513be3    Performing query completed:
{
    "queueSize": 1,
    "duration": 1578,
    "queryKey": [
        [
            "CREATE TABLE stb_pre_aggregations.payments_main20050928 AS SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1",
            [
                "2005-09-28T00:00:00Z",
                "2005-09-28T23:59:59Z"
            ]
        ],
        [
            [
                {
                    "current_hour": "2019-02-27T13:00:00.000Z"
                }
            ]
        ]
    ]
}

And then it will query the union of all pre-aggregations tables, which takes less than 200ms:

2019-02-27T13:33:23.647Z    a4162f29-570d-495f-8ca4-34600869d8e7    Performing query completed:
{
    "queueSize": 1,
    "duration": 134,
    "queryKey": [
        "SELECT `payments.date_date` `payments.date_date`, sum(`payments.count`) `payments.count` FROM (SELECT * FROM stb_pre_aggregations.payments_main20050901 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050902 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050903 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050904 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050905 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050906 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050907 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050908 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050909 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050910 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050911 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050912 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050913 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050914 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050915 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050916 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050917 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050918 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050919 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050920 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050921 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050922 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050923 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050924 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050925 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050926 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050927 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050928 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050929 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050930) as partition_union  WHERE (`payments.date_date` >= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00') AND `payments.date_date` <= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00')) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
       ...
}

This approach allows us to split the analytics workload into small reusable chunks to avoid DB burst with an incremental update strategy.

Conclusion

We were playing with just a test dataset and the results are very well correlated with what we see in production workloads. MySQL is capable of handling several billions of data points per table with an analytics workload pretty well. In this case, sub second query times are achievable with several seconds time to insight delay using properly organized query orchestration.

Although Serverless Aurora MySQL is capable of handling an enormous workload while stress testing, we discovered that workload routing algorithms aren’t smart enough yet to route queries to the least loaded nodes, which under certain circumstances can lead to partial database outages while analytics querying. For production environments we recommend you use provisioned Aurora MySQL with read replicas or the Parallel Query feature in order to split your OLTP and OLAP workload by design.

Building MongoDB Dashboard using Node.js

Pavel Tiunov — Tue, 05 Feb 2019 15:32:44 +0000

For pretty decent time doing analytics with MongoDB required additional overhead compared to modern SQL RDBMS and Data Warehouses associated with aggregation pipeline and map-reduce practices. While this approach allowed to craft advanced custom tailored aggregation algorithms it required additional knowledge of how to build and maintain it.

To fill this gap MongoDB released MongoDB connector for BI which acts as MySQL server on top of your MongoDB data. Under the hood it bridges existing aggregation mechanisms to MySQL protocol allowing standard MySQL clients to connect and issue SQL queries.

In this short 30-minutes tutorial we’ll setup MongoDB connector for your local MongoDB instance and spin up Node.js application which provides an analytics API, query cache and orchestration using Cube.js analytics framework. Cube.js can be used as a standalone server or can be embedded as part of existing Node.js web application. You can learn more about it here.

Setting up MongoDB connector for BI

To install MongoDB connector for BI locally you can use either quickstart guide or one of platform dependent installation guides.

Please make sure you use MongoDB version that supports MongoDB connector for BI. In this tutorial we use 4.0.5.

If you don’t have local MongoDB instance please download it here.
BI Connector can be downloaded here.

After BI connector has been installed please start mongod instance first. If you use downloaded installation it can be started from its home directory like:

$ bin/mongod

BI connector itself can be started the same way:

$ bin/mongosqld

Please note that mongosqld resides in another bin directory.
If everything works correctly you should see success log message in your shell for mongosqld process:

[initandlisten] waiting for connections at 127.0.0.1:3307

If you’re using MongoDB Atlas you can use this guide to enable BI connector.

Importing test dataset

You can skip this step if you already have data in your DB which can be analyzed. Otherwise you can use zip code test dataset from MongoDB to populate your DB with some test data.

Download zips.json and run mongoimport from MongoDB home directory:

$ bin/mongoimport --db test --collection zips --file <path/to/zips.json>

Please make sure to restart MongoDB BI connector instance in order to generate up-to-date MySQL schema out from just added collection.

Spinning up Cube.js application

We’ll use Cube.js to make analytic queries to our MongoDB instance. To install its CLI run:

$ npm install -g cubejs-cli

To create new Cube.js application with MySQL driver run:

$ cubejs create mongo-tutorial -d mysql

Go to just created mongo-tutorial directory and edit just created .env file: replace placeholders with your MongoDB BI connector credentials.

By default it should be something like:

CUBEJS_DB_HOST=localhost
CUBEJS_DB_NAME=test
CUBEJS_DB_PORT=3307
CUBEJS_DB_TYPE=mysql
CUBEJS_API_SECRET=941ed7ad8a49bec1b3f87a9f48bff2a5e549e946fc22fcc5f18c3a17bf62c64ed3398e99b271cd66d4521a6fd7caa4bfd268dfccea59ffd9c67de660f3967338

Now generate Cube.js schema files for zips collection from test data set or for your own collection:

$ cd mongo-tutorial
$ cubejs generate -t zips

In order to start Cube.js dev server you’ll also need locally running Redis instance which is used for cache and query queue orchestration. You can download it and run using these instructions.

If everything went smoothly you’re able to run Cube.js dev server:

$ npm run dev

If server started successfully you can now open http://localhost:4000 and navigate to Cube.js dev environment. There you should see working example of a Pie chart.

Building Dashboard

Cube.js dev environment example contains all essential client pieces to build analytics dashboard. Let’s modify it a little bit so it looks like more a dashboard and uses zips collection.

Replace contents of index.js in your dev environment code sandbox:

import React from "react";
import ReactDOM from "react-dom";

import "antd/dist/antd.css";
import { Row, Col, Card, Layout, Spin } from "antd";

import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import { Chart, Axis, Tooltip, Geom, Coord, Legend } from "bizcharts";

const renderChart = resultSet => (
  <Chart height={400} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    <Axis name="Zips.count" />
    <Legend position="bottom" name="category" />
    <Tooltip showTitle={false} />
    <Geom type="intervalStack" position="Zips.count" color="x" />
  </Chart>
);

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NDkzMDk3NzMsImV4cCI6MTU0OTM5NjE3M30.eXEdfUa_ek2V9MlGTpBMOd_AFfs8laaZj8ZsuM1wqqo",
  { apiUrl: "http://localhost:4000/cubejs-api/v1" }
);

const { Header, Footer, Sider, Content } = Layout;

const App = () => (
  <Layout>
    <Header>
      <h2 style={{ color: "#fff" }}>MongoDB Dashboard</h2>
    </Header>
    <Content style={{ padding: "25px", margin: "25px" }}>
      <Row type="flex" justify="space-around" align="top" gutter={24}>
        <Col span={24} lg={12}>
          <Card title="Zip count by state" style={{ marginBottom: "24px" }}>
            <QueryRenderer
              query={{ measures: ["Zips.count"], dimensions: ["Zips.state"] }}
              cubejsApi={cubejsApi}
              render={({ resultSet, error }) =>
               (resultSet && renderChart(resultSet)) ||
(error && error.toString()) || <Spin />
              }
              />
          </Card>
        </Col>
      </Row>
    </Content>
  </Layout>
);

const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);

Please make sure to replace the auth token with your own Cube.js temporary token that’s printed in the console while server start. Read more about auth tokens here.

If everything worked well you should see the following dashboard:

Why Cube.js?

So why using Cube.js is better than hitting SQL queries to MongoDB directly? Cube.js solves plethora of different problems every production ready analytic application need to solve: analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, API for query results fetch and visualization.

These features allows to build production grade analytics applications that able to handle thousands of concurrent users and billions of data points. It also allows you to do analytics on a production MongoDB read replica or even MongoDB main node due to ability to reduce amount of actual queries issued to a MongoDB instance. Cube.js schemas also allows to model everything from simple counts to funnels and cohort retention analysis. You can learn more about it here.

Performance considerations

In order to be able to handle massive amount of data Cube.js heavily relies on pre-aggregations. As of now MongoDB BI Connector doesn’t support Create Table as Statement which is required to materialize query results right in database and create pre-aggregations. If you need to analyze well over 100M of data points in MongoDB please consider using Presto with MongoDB Connector which is also supported by Cube.js.

Building Serverless Google Analytics from Scratch

Pavel Tiunov — Thu, 20 Dec 2018 15:44:04 +0000

From an engineering standpoint, the technology behind Google Analytics was pretty sophisticated when it was created. Custom, tailored-made algorithms were implemented for event collection, sampling, aggregation, and storing output for reporting purposes. Back then it required years of engineering time to ship such a piece of software. Big data landscapes have changed drastically since then. In this tutorial, we’re going to rebuild an entire Google Analytics pipeline. We’ll start from data collection and reporting. By using the most recent big data technology available, we’ll see how simple it is to reproduce such software nowadays.

TL;DR

Here’s an analytics dashboard with an embedded tracking code that collects data about its visitors while visualizing it at the same time.

Check out the source code on GitHub. Give it a star if you like it!

How Google Analytics works

If you’re familiar with Google Analytics, you probably already know that every web page tracked by GA contains a GA tracking code. It loads an async script that assigns a tracking cookie to a user if it isn’t set yet. It also sends an XHR for every user interaction, like a page load. These XHR requests are then processed and raw event data is stored and scheduled for aggregation processing. Depending on the total amount of incoming requests the data will also be sampled.

Even though this is a high level overview of Google Analytics essentials, it’s enough to reproduce most of the functionality. Let me show you how.

Your very own GA architecture overview

There are numerous ways of implementing a backend. We’ll take the serverless route because the most important thing about web analytics is scalability. In this case, your event processing pipeline scales in proportion to the load. Just as Google Analytics does.

We’ll stick with Amazon Web Services for this tutorial. Google Cloud Platform can also be used as they have pretty similar products. Here’s a sample architecture of the web analytics backend we’re going to build.

For the sake of simplicity, we’re only going to collect page view events. The journey of a page view event begins in the visitor’s browser, where an XHR request to an API Gateway is initiated. The request event is then passed to Lambda where event data is processed and written to a Kinesis Data Stream. Kinesis Firehose uses the Kinesis Data Stream as input and writes processed parquet files to S3. Athena is used to query parquet files directly from S3. Cube.js will generate SQL analytics queries and provide an API for viewing the analytics in a browser.

This seems very complex at first, but component decomposition is key. It allows us to build scalable and reliable systems. Let’s start implementing the data collection.

Building event collection using AWS Lambda

To deploy data collection backend, we’ll use the Serverless Application Framework. It lets you develop serverless applications with minimal code dependencies on cloud providers. Before we start, please ensure Node.js is installed on your machine. Also, if you don’t have an AWS account yet you’d need to signup for free and install and configure AWS CLI.

To install the Serverless Framework CLI let’s run:

# Step 1. Install serverless globally
$ npm install serverless -g
# Step 2. Login to your serverless account
$ serverless login

Now create the event-collection service from a Node.js template:

$ serverless create -t aws-nodejs -n event-collection

This will scaffold the entire directory structure. Let’s cd to the created directory and add the aws-sdk dependency:

$ yarn add aws-sdk

Install yarn package manager if you don’t have it:

$ npm i -g yarn

We’ll need to update handler.js with this snippet:

const AWS = require('aws-sdk');
const { promisify } = require('util');

const kinesis = new AWS.Kinesis();

const putRecord = promisify(kinesis.putRecord.bind(kinesis));

const response = (body, status) => {
  return {
    statusCode: status || 200,
    body: body && JSON.stringify(body),
    headers: {
      'Access-Control-Allow-Origin': '*',
      'Access-Control-Allow-Credentials': true,
      'Content-Type': 'application/json'
    }
  }
}

module.exports.collect = async (event, context) => {
  const body = JSON.parse(event.body);
  if (!body.anonymousId || !body.url || !body.eventType) {
    return response({
      error: 'anonymousId, url and eventType required'
    }, 400);
  }

  await putRecord({
    Data: JSON.stringify({
      anonymous_id: body.anonymousId,
      url: body.url,
      event_type: body.eventType,
      referrer: body.referrer,
      timestamp: (new Date()).toISOString(),
      source_ip: event.requestContext.identity.sourceIp,
      user_agent: event.requestContext.identity.userAgent
    }) + '\n',
    PartitionKey: body.anonymousId,
    StreamName: 'event-collection'
  });

  return response();
};

As you can see, the only thing this simple function does is write a record into a Kinesis Data Stream named event-collection. Please note that we’re writing data in new-line delimited JSON format so Athena and Kinesis Firehose can understand it.

Also, we need to modify the serverless.yml in order to deploy everything. Paste this into your serverless.yml file.

service: event-collection

provider:
  name: aws
  runtime: nodejs8.10

  iamRoleStatements:
    - Effect: "Allow"
      Action:
        - "kinesis:PutRecord"
      Resource:
        - "*"

functions:
  collect:
    handler: handler.collect
    events:
      - http:
          path: collect
          method: post
          cors: true

This config will deploy the collect function and assign an API Gateway event trigger to it. It’ll also assign AWS Kinesis Data Stream permissions to the function.

With that, we’re done with writing all the backend code we need for our homemade GA. It’ll be able to handle thousands of incoming events per second. Too much for 2018, isn’t it? :)

Let’s deploy it to AWS:

$ serverless deploy -v

If everything is okay you’ll get a URL endpoint. Let’s test it with CURL:

curl -d '{}' https://<your_endpoint_url_here>/dev/collect

It should return a 400 status code and an error message that looks like this:

{"error":"anonymousId, url and eventType required"}

If this is the case, let’s proceed with the Kinesis setup.

AWS Kinesis setup

First of all, we need to create a Kinesis Data Stream calledevent-collection. First, sign in to your AWS account at console.aws.amazon.com and select Kinesis service from the menu. By default the Serverless Framework deploys resources to the us-east-1 region, so we’ll assume the AWS Lambda function was created there and switch regions if necessary before creating the stream.

To create the data stream, we need to set the name to event-collection and set the number of shards. It can be set to 1 for now. The number of shards define your event collection throughput. You can find more information about it here.

Once you’re done with the data stream, create a Kinesis Firehose delivery stream.

Step 1

You should select event-collection Kinesis stream as a source.

Step 2

For now, to keep this tutorial simple, we don’t need to process any data. In production you would need to transform it to ORC or Parquet to ensure optimal performance. You can also use this step for event data population like IP to location.

Step 3

We’ll be using S3 as a destination. You need to create a new S3 bucket. Please choose whichever name you like, but add an events suffix as it will contain events.

Step 4

Here you can choose Gzip compression to save some billing. You will also be prompted to create an IAM role for this delivery stream. Just follow the instructions.

That’s it. If you’ve done everything right, try to run your AWS Lambda function again with a real payload.

curl -d '{"anonymousId": "123", "url": "-", "eventType": "pageView"}' https://<your_endpoint_url_here>/dev/collect

Events should start flowing to your S3 bucket within five minutes. At this point, the event collection is done. Let’s set up querying for analytics.

Setting up AWS Athena for querying analytics

As data starts flowing to S3, we need to support it with metadata. Athena uses it to understand where to find the data and what structure it has. This is a cumbersome process, but it can easily be done with AWS Glue. Glue is a metadata manager and ETL by AWS. It also has a crawler concept, which acts as a cron job that analyzes S3 data in order to extract metadata from it.

Navigate to Glue from the services menu and select Databases. Add a new database and name it aws_web_analytics. Then go to crawlers and choose ‘Add crawler’.

Name it events-crawler and choose the S3 bucket that was just created as the data store:

Create an IAM Role according to the instructions and set to run it hourly:

As an output, select the previously created database:

Once it's created, let’s run it manually. If it’s successful, you should see a table in the aws_web_analytics database. Let’s try to query it.

Go to Athena from the services menu. Select the aws_web_analytics database and write some simple query, such as select * from aws_web_analytics_event_collection. You should get a result like this:

If everything looks fine, we can proceed with building the analytics UI.

Setting up Cube.js to provide analytics for end users

AWS Athena is a great analytics backend suitable to query petabytes of data, but as any big data backend, it isn’t suitable for directly querying by end users. To provide an acceptable performance vs cost balance, you should use a caching and pre-aggregation layer on top of it along with an API for querying analytics. This is exactly what Cube.js does!

First of all, we need to create an IAM user to access Athena from Cube.js. Select IAM from the AWS services menu. Select Users and click the Add User button. Set the user name to cubejs and enable Programmatic access:

At step 2, select attach existing policies directly then select AmazonAthenaFullAccess and AmazonS3FullAccess:

Move on and create a user. Once it’s created, copy the Access key ID and Secret access key and save it. Please note that you’ll only see the secret once, so don’t forget to store it somewhere.

If you don’t have a Cube.js account yet, let’s signup for free now. After you’ve signed up, connect Athena as a data source:

You should also create a new, or find an existing, S3 bucket to store the Athena result output within the us-east-1 region. The setup should look like this:

If Athena connected successfully, you’ll be forwarded to the Cube.js schema. Let’s create a new PageViews file and paste this in:

cube(`PageViews`, {
 sql: `select * from aws_web_analytics.aws_web_analytics_event_collection`,

 measures: {
   count: {
     type: `count`
   },

   userCount: {
     sql: `anonymous_id`,
     type: `countDistinct`,
   }
 },

 dimensions: {
   url: {
     sql: `url`,
     type: `string`
   },

   anonymousid: {
     sql: `anonymous_id`,
     type: `string`
   },

   eventType: {
     sql: `event_type`,
     type: `string`
   },

   referrer: {
     sql: `referrer`,
     type: `string`
   },

   timestamp: {
     sql: `from_iso8601_timestamp(timestamp)`,
     type: `time`
   }
 }
});

Please replace the event table name with your own. Save the file and go to Explorer. You can learn more about the Cube.js Schema in the documentation here. If everything works the way it should, you will see the Page Views Count for this week

Once it works, we’re ready to enable Cube.js API access. In order to do that, go to Data Sources and Edit Athena data source. In the Cube.js API tab enable Cube.js API access and copy the Global Token:

We’re now ready to create a React application to visualize our analytics.

Building a React analytics dashboard

Let’s use create-react-app scaffolding to create directory structure for our app:

$ yarn create react-app analytics-dashboard

Then cd into the created directory and add the required dependencies:

$ yarn add @cubejs-client/core @cubejs-client/react antd bizcharts component-cookie uuid whatwg-fetch moment

The @cubejs-client/core and @cubejs-client/react modules are used to access the Cube.js API in a convenient manner. While antd and bizcharts are used to create layouts and visualize results. The last three, component-cookie, uuid, and whatwg-fetch are used to implement a track page function, which collects event data about users.

Let’s start with the tracking function. Create a track.js file in the analytics-dashboard directory and paste this in:

import { fetch } from 'whatwg-fetch';
import cookie from 'component-cookie';
import uuidv4 from 'uuid/v4';

export const trackPageView = () => {
  if (!cookie('aws_web_uid')) {
    cookie('aws_web_uid', uuidv4());
  }
  fetch(
    'https://<your_endpoint_url>/dev/collect',
    {
      method: 'POST',
      body: JSON.stringify({
        url: window.location.href,
        referrer: document.referrer,
        anonymousId: cookie('aws_web_uid'),
        eventType: 'pageView'
      }),
      headers: {
        'Content-Type': 'application/json'
      }
    }
  )
}

Please replace the URL with your own collect function endpoint. This is all the code we need to track user page views on the client side. This code should be called when a page is loaded.

Let’s create the main App page with two simple charts. In order to do that, replace the App.js contents with this snippet:

import React, { Component } from 'react';
import "antd/dist/antd.css";
import "./index.css";
import { Row, Col, Card, Layout } from "antd";
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import { Spin } from 'antd';
import { Chart, Axis, Tooltip, Geom, Coord, Legend } from 'bizcharts';
import moment from 'moment';
import { trackPageView } from './track';

const dateRange = [
  moment().subtract(14,'d').format('YYYY-MM-DD'),
  moment().format('YYYY-MM-DD'),
];

const { Header, Footer, Sider, Content } = Layout;

const renderChart = (resultSet) => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    {resultSet.seriesNames().map(s => (<Axis name={s.key} />))}
    <Tooltip crosshairs={{type : 'y'}} />
    {resultSet.seriesNames().map(s => (<Geom type="line" position={`category*${s.key}`} size={2} />))}
  </Chart>
);

const API_KEY = 'eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpIjo0MDY3OH0.Vd-Qu4dZ95rVy9pKkyzy6Uxc5D-VOdTidCWYUVhKpYU';

class App extends Component {
  componentDidMount() {
    trackPageView();
  }

  render() {
    return (
      <Layout>
          <Header>
            <h2 style={{ color: '#fff' }}>AWS Web Analytics Dashboard</h2>
          </Header>
          <Content style={{ padding: '25px', margin: '25px' }}>
            <Row type="flex" justify="space-around" align="middle" gutter={24}>
              <Col lg={12} md={24}>
                <Card title="Page Views" style={{ marginBottom: '24px' }}>
                  <QueryRenderer
                    query={{
                      "measures": [
                        "PageViews.count"
                      ],
                      "timeDimensions": [
                        {
                          "dimension": "PageViews.timestamp",
                          "dateRange": dateRange,
                          "granularity": "day"
                        }
                      ]
                    }}
                    cubejsApi={cubejs(API_KEY)}
                    render={({ resultSet }) => (
                      resultSet && renderChart(resultSet) || (<Spin />)
                    )}
                  />
                </Card>
              </Col>
              <Col lg={12} md={24}>
                <Card title="Unique Visitors" style={{ marginBottom: '24px' }}>
                  <QueryRenderer
                    query={{
                      "measures": [
                        "PageViews.userCount"
                      ],
                      "timeDimensions": [
                        {
                          "dimension": "PageViews.timestamp",
                          "dateRange": dateRange,
                          "granularity": "day"
                        }
                      ]
                    }}
                    cubejsApi={cubejs(API_KEY)}
                    render={({ resultSet }) => (
                      resultSet && renderChart(resultSet) || (<Spin />)
                    )}
                  />
                </Card>
              </Col>
            </Row>
          </Content>
        </Layout>
    );
  }
}

export default App;

Make sure to replace the API_KEY constant with your own Cube.js Global Token. You should be able to see the dashboard with two charts:

Once again, here’s the deployed version of the dashboard, if you want to check it out.

To deploy your own, create a public S3 bucket with static site serving enabled, build the app, and sync it to the bucket:

$ yarn build
$ aws s3 sync build/ s3://<your_public_s3_bucket_name>

You also can use a service like Netlify to host your website. They make it incredibly simple to deploy and host a website.

Conclusion

This tutorial has shown you how to build a proof of concept compared to the Google Analytics feature set. The architecture is scalable enough to handle thousands of events per second, and can analyze trillions of data points without breaking a sweat. Cube.js is suitable for implementing all metrics you expect to see in GA, such as bounce rate, session time spent, etc. You can read more about it here. Large scale analytics can’t be built without the pre-aggregation of data. GA does this a lot and Cube.js has a built-in solution for it.