DEV Community: Timur Minulin

Vue Dashboard Tutorial Using Cube.js

Timur Minulin — Thu, 23 May 2019 14:49:14 +0000

There are a couple of popular front-end frameworks today. We recently covered how to build an analytics dashboard with React and Angular. What both of these guides share is Cube.js—an open-source analytics framework, which powers these dashboards with high-performance data. Being open-source, Cube.js is open to contributions, and you can find a contribution guide in the repository on Github.
One of the latest contributions from the community is a Cube.js client for Vue. It was built by Ricardo Tapia from Qualibrate and he's got a great blog post about that process. In this tutorial, I'll touch on some details and provide a step-by-step guide on building your own analytics dashboard with Vue.

We will be using Vue, Cube.js, and Laue. You can find a final dashboard here and a CodeSandbox with the source code below.

Setting up a Cube.js Backend

We covered this topic in other tutorials, so if you already have your Cube.js backend set up and running, you can skip this section.

You can install Cube.js CLI, which is used for various Cube.js workflows, via NPM or Yarn.

npm install -g cubejs-cli

Let's prepare a Cube.js Backend to serve data for the dashboard we're building. Cube.js supports many databases and deployment options. You can learn more about it in the documentation. For this tutorial, we’ll use a Postgres database and deploy Cube.js to Heroku. Let's create a new Cube.js application using the CLI we just installed.

cubejs new vue-demo -d postgres
cd vue-demo

In case you don't have a database for the dashboard yet, you can download our demo e-commerce dataset for Postgres.

curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
createdb ecom
psql --dbname ecom -f ecom-dump.sql

The next step is to define a data model. In a production application, you most likely will have multiple schema files, but for our demo app we are going to have only one cube. If you're not familiar with Cube.js data schema, there's an in-depth tutorial here.

cube(`Users`, {
  sql: `SELECT * FROM users`,

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

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

    signedUp: {
      sql: `created_at`,
      type: `time`
    },

    companyName: {
      sql: `company_name`,
      type: `string`
    }
  }
});

Cube.js uses data schema to generate and execute SQL in the connected database. We can test it out by sending a sample request to the Cube.js REST API endpoint.

curl \
 -H "Authorization: EXAMPLE-API-TOKEN" \
 -G \
 --data-urlencode 'query={"measures":["Users.count"]}' \
 http://localhost:4000/cubejs-api/v1/load

You can learn more about the Cube.js Query format here.

Finally, let’s deploy our backend to Heroku:

git init
git add -A
git commit -am "Initial commit"
heroku create cubejs-vue-demo
git push heroku master

You can find full deployment guide in the documentation.

Create Vue App

When the backend is up and running, it's time to build the dashboard. Since we're using Vue, the best way to create a new app is by using vue-cli.

First, install vue-cli if you don't have it already:

npm install -g @vue/cli
# or using yarn
yarn global add @vue/cli

To create an app, you can use your terminal or start a tool called Vue UI:

vue ui

This will run a website on your computer, which allows you to create apps, run, and monitor them. It also contains all links to documentation and other community resources.

To create an app using the terminal, all you need is a name:

vue create YOUR-APP-NAME
cd YOUR-APP-NAME

You can configure plugins for your application, but for the demo we'll use the default setup.

If you created the app using Vue UI, you can start it right there. If you're using the console, run the serve task:

npm run serve
# or using yarn
yarn serve

Now your application is running on your computer and is accessible via the browser.

Setting up the dashboard

First, we'll add some basic styles using Bootstrap. We'll install it from the CDN, but you can add it using npm or yarn. Open your public/index.html file and add Bootstrap resources:

<head>
    ...
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
     crossorigin="anonymous">
</head>

Now when bootstrap is installed, we can create some basic structure for the app:

<template>
  <div class="container-fluid">
    <div class="row">
      <div class="col-sm-4">
      </div>
      <div class="col-sm-4">
      </div>
      <div class="col-sm-4">
      </div>
    </div>
    <div class="row">
      <div class="col-sm-6">
      </div>
      <div class="col-sm-6">
      </div>
    </div>
  </div>
</template>

Now we need some data to show. There's a full documentation for the Cube.js Vue client where you can find additional options. First, we need to set up a Cube.js instance with our backend URL and API token:

// App.vue
import cubejs from "@cubejs-client/core";
import { QueryBuilder } from "@cubejs-client/vue";

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg",
  { apiUrl: "https://react-query-builder.herokuapp.com/cubejs-api/v1" }
);

Now let's set up our app to use a QueryBuilder component and create a query counting all users:

// App.vue

export default {
  name: "App",
  components: {
    Chart,
    QueryBuilder
  },
  data() {
    return {
      cubejsApi,
      usersQuery: { measures: ["Users.count"] }
  }
};

The Cube.js Vue Client allows child components to receive a resultSet object for a given query. Let's create a new component called Chart:

# components/Chart.vue
<template>
  <div class="card">
    <div class="card-body">
      <h5 class="card-title">{{ title }}</h5>
      <div class="card-text">
        <div class="d-flex justify-content-center text-dark">
          <div class="spinner-border" role="status" v-if="loading">
            <span class="sr-only">Loading...</span>
          </div>
        </div>
        <h1
          v-if="!loading && type === 'number'"
          height="300"
        >{{ values[0][metrics[0]] }}</h1>
      </div>
    </div>
  </div>
</template>

<script>

export default {
  name: "Chart",
  props: {
    resultSet: Object,
    loading: Boolean,
    title: String,
  },
  computed: {
    values: function() {
      if (this.loading) return [];
      return this.resultSet.chartPivot();
    },
    metrics: function() {
      if (this.loading) return [""];
      return this.resultSet.seriesNames().map(x => x.key);
    }
  };
</script>

What we need here is to display a loading element while data is loading and show a number after. Let's get back to our App.vue component and create a first tile:

<query-builder :cubejs-api="cubejsApi" :query="usersQuery">
  <template v-slot="{ loading, resultSet }">
    <Chart title="Total Users" :loading="loading" :result-set="resultSet"/>
  </template>
</query-builder>

We're using here a QueryBuilder component that passes the data into the Chart component using Vue Scoped Slot Props. Now there's a counter on our dashboard showing total users. Let's add some charts!

Charts

To create a chart, we'll use the Laue library. It's pretty simple and has a great collection of examples online. First, we'll install Laue in our application:

// main.js

import { Laue } from 'laue';

Vue.use(Laue);

This allows us to use all Laue items in any component. There are other installation methods in the documentation. Now let's create a LineChart component:

<template>
  <la-cartesian autoresize :data="values" :padding="[0, 0, 5, 0]">
    <la-line curve :width="2" color="#7DB3FF" :prop="metrics[0]"/>
    <la-y-axis :nbTicks="4"></la-y-axis>
    <la-x-axis prop="x" :format="dateFormatter" :interval="6"></la-x-axis>
    <la-tooltip></la-tooltip>
  </la-cartesian>
</template>

<script>
import moment from "moment";
export default {
  name: "LineChart",
  props: {
    values: Array,
    metrics: Array
  },
  methods: {
    dateFormatter: function(value) {
      return moment(value).format("MMM YY");
    }
  }
};
</script>

To render chart, we'll use a type prop on our Chart component. Let's add a conditional render there:

<line-chart v-if="!loading && type === 'line'" :values="values" :metrics="metrics"/>

Now our LineChart component is done! What we need now is to add a query for the line chart in our App.vue component:

<query-builder :cubejs-api="cubejsApi" :query="lineQuery">
  <template v-slot="{ loading, resultSet }">
    <Chart
      title="New Users Over Time"
      type="line"
      :loading="loading"
      :result-set="resultSet"
    />
  </template>
</query-builder>

<script>
...
data() {
  return {
    cubejsApi,
    usersQuery: { measures: ["Users.count"] },
    lineQuery: {
      measures: ["Users.count"],
      timeDimensions: [
        {
          dimension: "Users.createdAt",
          dateRange: ["2017-01-01", "2018-12-31"],
          granularity: "month"
        }
      ]
    }
  }
...
</script>

That's it for the line chart. The setup for the bar chart is pretty similar.

And the dashboard is complete! You can find the dashboard live here and the source code in this CodeSandbox. We hope you found this guide useful.

Redash Dashboard Tutorial with Cube.js

Timur Minulin — Thu, 16 May 2019 14:59:16 +0000

We recently covered how to build an analytics dashboard using react, vue, or angular. But if you don't want to code and host your own dashboard, you have some open-source alternatives. Today I'll show you how to build an analytics dashboard using Cube.js Backend and Redash.
You can find the resulting dashboard live here.

Why Cube.js?

You can always connect Redash directly to your database. You can then write queries in pure SQL and build the same dashboard. It's easy to manage if you have one dashboard and just a couple of queries. But usually, a small dashboard is just a first step in building a complex analytics solution. As your business grows, your volume of data also grows. And soon, you may find a hundred dashboards with thousands of queries used by multiple departments. Now imagine you changed a column in the database. All of the queries should be checked and updated.

Another problem may be the data volume. Say you have a large dashboard powered by some complex queries that take some time to execute. Once you change a filter value on that dashboard, all queries will get executed on the database. In a simple case, it may take a long time to update the dashboard, but if you're using a per-query billed database like Google Bigquery or AWS Athena, this will also hit your monthly bill.

Cube.js solves both of these problems. The queries used in Redash may be updated independently, so updating a column name or adding a join condition is a simple update in the data schema file. Also, Cube.js Backend manages pre-aggregations, which helps with loading lots of data fast and efficiently. Cube.js pre-aggregates data for huge queries in separate tables and updates them in the background. This is efficient in terms of performance and cost. Here you can find a guide on Cube.js Data Schema to understand core concepts better.

Setting up Cube.js Backend

To launch a Cube.js Backend, you'll need to provide a database where your analytics data is stored and a hosting option. In this demo, we'll use Postgresql database with our demo dataset. If you don't have access to your analytics database right now, you can use this dataset:

curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
createdb ecom
psql --dbname ecom -f ecom-dump.sql

Now when you have your database running, we have to choose a deployment option. For this demo, we'll host our backend on Heroku. There's a guide for other deployment options in the documentation.

Let's install cubejs-cli and create a new Cube.js app:

npm install -g cubejs-cli
cubejs new redash-demo -d postgres
cd redash-demo

Now we have to set a data model. For this demo, we'll define only one cube and skip advanced options, which are covered in the documentation.

cube(`Users`, {
  sql: `SELECT * FROM users`,

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

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

    signedUp: {
      sql: `created_at`,
      type: `time`
    },

    companyName: {
      sql: `company_name`,
      type: `string`
    }
  }
});

Now when we have our data model set we can deploy this app to Heroku:

git init
git add -A
git commit -am "Initial commit"
heroku create cubejs-redash-demo
git push heroku master

Creating a Redash Dashboard

To use Cube.js Backend with Redash, you need to create a JSON Data Source.

Once you've done that, you can create a query. There are a couple of options like URL and Authorization Headers that will be shared across all of the queries, so you can create one query and then fork it to create others. Here's a simple query that will count all Orders in our demo dataset:

url: "YOUR-CUBEJS-URL/cubejs-api/v1/load"
headers: 
    Authorization: YOUR-API-TOKEN
path: data
params:
    query: '{"measures": ["Orders.count"]}'

Here you need to provide your API URL, which depends on the deployment option you chose. Also, you need to create an API Token to authenticate requests.
Now click "Save" and "Execute." You should get a response that looks like this:

Now you can add a visualization. For this query, we'll use a simple "counter" option:

Let's create a more sophisticated query. We'll create a Stacked Bar Chart for the orders. First, let's define the query:

url: "https://react-query-builder.herokuapp.com/cubejs-api/v1/load"
headers: 
    Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg
path: data
params:
    query: '{
      "measures":["Orders.count"],
      "dimensions":["Orders.status"],
      "timeDimensions":[{"dimension":"Orders.createdAt",
        "dateRange":["2017-01-01","2018-12-31"],
        "granularity":"month"
      }]
}'

Save and execute the query and you'll get a table, which is the default visualization. Let's create a Bar Chart:

Now we have enough queries to create a dashboard. Click "Create" -> "Dashboard," choose a name, and go ahead adding your query widgets.

And the dashboard is done!

Setting Filters

There's also a Redash option called Parameters. We can set some query values using it. Let's add a daterange filter to our queries:

url: "https://react-query-builder.herokuapp.com/cubejs-api/v1/load"
headers: 
    Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg
path: data
params:
    query: '{"measures":["Orders.count"],
        "timeDimensions": [{
          "dimension" : "Orders.createdAt",
          "dateRange": ["{{ daterange.start }}","{{ daterange.end }}"]
        }]
    }'

You can see we've added params using the {{ }} expression. Now let's change the parameter type to daterange:

Click "Save" and "Execute." Now this query has a daterange filter. You can add other parameters to the query in the same manner. Also, you can set multiple queries to use one dashboard-level filter value using the "edit Parameters" menu. Here's an example:

And it's done! Now we have a live dashboard in Redash, which you can publish, query, or even run on a display with auto-update.

Angular Dashboard Tutorial with Cube.js

Timur Minulin — Thu, 09 May 2019 14:58:38 +0000

Almost any website has some embedded analytics. You can find usage charts for every public Github repository or any social network today. Cube.js is designed to help developers build such analytical applications. It solves a plethora of different problems every production-ready analytic application needs to solve: analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, and API for query results fetch.

We recently covered how to build an analytic dashboard with Cube.js and React, but what about Angular? Starting with version 0.8.4, the Cube.js Client ships with an Angular module for easy integration.
Today I will show you how to build an analytical dashboard using Angular, Cube.js, and ng2-charts.

You can find a final dashboard here and a CodeSandbox with the source code below.

Setting up a Cube.js Backend

We covered this topic in other tutorials, so if you already have your Cube.js backend set up and running, you can skip this section.

You can install Cube.js CLI, which is used for various Cube.js workflows, via NPM or Yarn.

npm install -g cubejs-cli

cubejs new ng-demo -d postgres
cd ng-demo

In case you don't have a database for the dashboard yet, you can download our demo e-commerce dataset for Postgres.

curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
createdb ecom
psql --dbname ecom -f ecom-dump.sql

cube(`Users`, {
  sql: `SELECT * FROM users`,

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

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

    signedUp: {
      sql: `created_at`,
      type: `time`
    },

    companyName: {
      sql: `company_name`,
      type: `string`
    }
  }
});

Cube.js uses data schema to generate and execute SQL in the connected database. We can test it out by sending a sample request to the Cube.js REST API endpoint.

curl \
 -H "Authorization: EXAMPLE-API-TOKEN" \
 -G \
 --data-urlencode 'query={"measures":["Users.count"]}' \
 http://localhost:4000/cubejs-api/v1/load

You can learn more about the Cube.js Query format here.

Finally, let’s deploy our backend to Heroku:

git init
git add -A
git commit -am "Initial commit"
heroku create cubejs-ngx-demo
git push heroku master

You can find full deployment guide in the documentation.

Dashboard

Now, when we have a functional backend running, we can move to the next part—building a dashboard. Cube.js has an Angular binding, which doesn’t provide any visualization itself, but is designed to work with any charting library. This way it provides great flexibility for developers to build unique and custom user experiences.

First, install ng-cli if you don't have it already:

npm install -g angular/cli

Let's create a new Angular app using SCSS templates:

ng new ng-demo-dashboard -s scss

We'll be using an ng2-charts library, which is an Angular wrapper for Chart.js, to draw charts. The Cube.js Angular Client will be used to load the data from the backend, and finally Bootstrap will provide us with some nice styling. Let’s add these dependencies:

npm install -s ng2-charts @cubejs-client/core @cubejs-client/ngx moment
# or
yarn add ng2-charts @cubejs-client/core @cubejs-client/ngx moment

Next, add the required modules to the app.module.ts file:

const cubejsOptions = {
  token:
    "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.e30.K9PiJkjegbhnw4Ca5pPlkTmZihoOm42w8bja9Qs2qJg",
  options: {
    apiUrl: "https://react-query-builder.herokuapp.com/cubejs-api/v1"
  }
};

@NgModule({
  declarations: [AppComponent],
  imports: [
    BrowserModule,
    ChartsModule,
    CubejsClientModule.forRoot(cubejsOptions)
  ],
  providers: [],
  bootstrap: [AppComponent]
})

Now we're finished with our app setup. Let's create a chart component:

ng generate component chart

Add some style and an element for ng2-charts:

<div class="card">
  <div class="card-body">
    <h5 class="card-title">{{ title }}</h5>
    <div class="card-text">
      <div *ngIf="ready === false" class="d-flex justify-content-center text-dark">
        <div class="spinner-border" role="status">
      <span class="sr-only">Loading...</span>
    </div>
      </div>
      <canvas *ngIf="ready && showChart" baseChart height="300" [datasets]="chartData" [labels]="chartLabels" [options]="chartOptions"
             [colors]="chartColors" [chartType]="chartType"></canvas>
      <h1 *ngIf="ready && !showChart" height="300">{{ chartData }}</h1>
    </div>
  </div>
</div>

Let's get the data for our chart. We need to define the inputs, which we'll pass to the ngx-chart component to allow customization:

@Input() chartType;
@Input() query;
@Input() title;

public chartData;
public chartLabels;
public chartOptions: any = {
  responsive: true
};
public chartColors;

To gather the data, we'll add an input for the query and use the Cube.js Angular watch API:

constructor(private cubejs: CubejsClient) {}

ngOnInit() {
  this.querySubject = new Subject();
  this.resultChanged = this.resultChanged.bind(this);
  this.cubejs
    .watch(this.querySubject)
    .subscribe(this.resultChanged, err => console.log("HTTP Error", err));

  this.querySubject.next(this.query);
}

This will allow us to get and display new data every time the query changes. Now let's create a simple dashboard in our app.component:

<div class="container-fluid">
  <div class="row">
    <div class="col-sm-4">
      <app-chart chartType="singleValue" [query]="usersQuery" title="Total Users"></app-chart>
    </div>
    <div class="col-sm-4">
      <app-chart chartType="singleValue" [query]="ordersQuery" title="Total Orders"></app-chart>
    </div>
    <div class="col-sm-4">
      <app-chart chartType="singleValue" [query]="shippedOrdersQuery" title="Shipped Orders"></app-chart>
    </div>
  </div>
  <div class="row">
    <div class="col-sm-6">
      <app-chart chartType="line" [query]="lineChartQuery" title="New Users Over Time"></app-chart>
    </div>
    <div class="col-sm-6">
      <app-chart chartType="stackedBar" [query]="stackedBarChartQuery" title="Orders by Status Over time"></app-chart>
    </div>
  </div>
</div>

And it's done! You can find the resulting dashboard here and a codesandbox demo here.

React Native Charts with Cube.js and Victory

Timur Minulin — Thu, 25 Apr 2019 14:52:58 +0000

Giving mobile users access to analytical data is always a hard problem to solve. Browsing a heavy website on a small screen usually is not the best user experience. Building native mobile apps is a great solution, but usually requires a lot of effort.

React-Native makes building and maintaining native applications much easier. By coupling it with Cube.js and Victory-Native, I'll show you how to build an analytics dashboard embedded into a native mobile app. The resulting app can run both on iOS and Android; you can try it out by using the Expo app on your own device.

Below is a screenshot of the final app. Here's the app snack on Expo—you can run it online or launch it on your device. Also, there you can find all the source code from this tutorial.

The data

We are going to use the Cube.js backend with sample data from Nginx logs. I’ve covered how to collect Nginx logs and analyze them with AWS Athena and Cube.js in this tutorial.
Let’s recap the data schema we’re going to query:

cube(`Logs`, {
  measures: {
    count: {
      type: `count`,
    },

    errorCount: {
      type: `count`,
      filters: [
        { sql: `${CUBE.isError} = 'Yes'` }
      ]
    },

    errorRate: {
      type: `number`,
      sql: `100.0 * ${errorCount} / ${count}`,
      format: `percent`
    }
  },

  dimensions: {
    status: {
      sql: `status`,
      type: `number`
    },

    isError: {
      type: `string`,
      case: {
        when: [{
          sql: `${CUBE}.status >= 400`, label: `Yes`
        }],
        else: { label: `No` }
      }
    },

    createdAt: {
      sql: `from_unixtime(created_at)`,
      type: `time`
    }
  }
});

Cube.js uses data schemas like the one above to generate SQL and execute it against your database. If you are new to Cube.js, I recommend checking this Cube.js 101 tutorial.

React-Native

To build a react-native app, we'll be using the react-native-cli package.
Go ahead and install it:

npm install -g react-native-cli
# or
yarn global add react-native-cli

Now you can create a new app:

react-native init cubejs-rn-demo
cd cubejs-rn-demo

This will create a barebones react-native app.

Cube.js

Cube.js provides a client package for loading data from the backend:

npm install -s @cubejs-client/core
# or
yarn add @cubejs-client/core

It works for both web and native apps. Also, Cube.js has a React component, which is easier to work with:

npm install -s @cubejs-client/react
# or
yarn add @cubejs-client/react

The Cube.js React client also works great with React-Native.The client itself doesn't provide any visualisations and is designed to work with existing chart libraries. It provides a set of methods to access Cube.js API and to work with query result.

Victory

For charts, we will be using the victory-native library.

npm install -s victory-native
# or
yarn add victory-native

Now we can create a simple pie chart just like on the demo dashboard. Here's the code:

<VictoryChart width={this.state.width}>
  <VictoryPie
    data={data.chartPivot()}
    y="Logs.count"
    labels={item => numberFormatter(item[data.seriesNames()[0].key])}
  />
</VictoryChart>

Building a Dashboard

We are going to build a simple dashboard with just a couple of tiles, so we'll use a ScrollView. If you're going to have many tiles on a dashboard, it would be better to switch to a FlatList because of potential performance issues. So, let's create a simple dashboard. First, we are going to create a Chart component, where we'll define all required data.

const Empty = () => <Text>No component for that yet</Text>;

const chartElement = (type, data) => {
  switch (type) {
    case 'line':
      return <LineChart data={data} />;
    case 'pie':
      return <PieChart data={data} />;
    case 'bar':
      return <BarChart data={data} />;
    default:
      return <Empty />;
  }
};

const Chart = ({ type }) => (
  <QueryRenderer
    query={queries[type]}
    cubejsApi={cubejsApi}
    render={({ resultSet }) => {
      if (!resultSet) {
        return <ActivityIndicator size="large" color="#0000ff" />;
      }

      return chartElement(type, resultSet);
    }}
  />
);

Also, we will be using Victory's zoomContainer to allow users to zoom into the data. We'll also save current device orientation to add more data in landscape mode and change paddings:

const padding = {
    portrait: { left: 55, top: 40, right: 45, bottom: 50 },
    landscape: { left: 100, top: 40, right: 70, bottom: 50 }
};

const tickCount = {
  portrait: 4,
  landscape: 9
};

export const colors = [
  "#7DB3FF",
  "#49457B",
  "#FF7C78",
  "#FED3D0",
  "#6F76D9",
  "#9ADFB4",
  "#2E7987"
];

class ChartWrapper extends React.Component {
  constructor() {
    super();
    this.state = { orientation: 'portrait', ...Dimensions.get('window')};
    this.updateDimensions = this.updateDimensions.bind(this);
  }

  componentDidMount() {
    this.updateDimensions();
  }

  handleZoom(domain) {
    this.setState({ selectedDomain: domain });
  }

  updateDimensions() {
    const windowSize = Dimensions.get('window');
    const orientation = windowSize.width < windowSize.height ? 'portrait' : 'landscape';
    this.setState({ orientation, ...windowSize });
  }

  render() {
    return (
      <View style={vStyles.container} onLayout={this.updateDimensions}>
        <VictoryChart
          width={this.state.width}
          padding={padding[this.state.orientation]}
          domainPadding={{x: 10, y: 25}}
          colorScale={colors}
          tickCount={4}
          containerComponent={
            <VictoryZoomContainer responsive={true}
              zoomDimension="x"
              zoomDomain={this.state.zoomDomain}
              onZoomDomainChange={this.handleZoom.bind(this)}
            />
          }
        >
          {this.props.children}
          {!this.props.hideAxis &&
            <VictoryAxis tickCount={tickCount[this.state.orientation]} />
          }
          {!this.props.hideAxis &&
            <VictoryAxis dependentAxis />
          }
        </VictoryChart>
      </View>
    );
  }
}

Please note that the default app.json config has locked portrait screen orientation. To allow device rotation, set "orientation" to "default"—that will allow all orientations except upside down. This code scales the charts when the device is rotated:

Let's start with a line chart. First, we'll need to define a basic Cube.js query in Chart.js to get the data:

{
  measures: ["Logs.errorRate"],
  timeDimensions: [
    {
      dimension: "Logs.createdAt",
      dateRange: ["2019-04-01", "2019-04-09"],
      granularity: "day"
    }
  ]
}

Now we can create a LineChart component. It's a basic Victory chart with a bit of styling:

const LineChart = ({ data }) => (
  <ChartWrapper>
    <VictoryLine
      data={data.chartPivot()}
      x={dateFormatter}
      labels={null}
      y={data.seriesNames()[0].key}
      style={{
        data: { stroke: "#6a6ee5" },
        parent: { border: "1px solid #ccc"}
      }}
    />
  </ChartWrapper>
);

We can include this component in the Chart.js file and render in the Dashboard.js screen:

const Dashboard = () => {
  return (
    <ScrollView>
      <View style={styles.item}>
        <Text style={styles.text}>Error Rate by Day</Text>
        <Chart type="line" />
      </View>
    </ScrollView>
  );
};

The same applies to Stacked Bar Chart. The only complication is that it consists of multiple series, so we add a bar for each series and make a legend:

const BarChart = ({ data }) => (
  <ChartWrapper>
    <VictoryStack colorScale={colors}>
      {data.seriesNames().map((series, i) => (
        <VictoryBar
          key={i}
          x={dateFormatter}
          y={series.key.replace(":", ", ")}
          data={data.chartPivot()}
          labels={null}
          style={{
            parent: { border: "1px solid #ccc"}
          }}
        />
      ))}
    </VictoryStack>
    <VictoryLegend x={40} y={280}
      orientation="horizontal"
      colorScale={colors}
      data={data.seriesNames().map(({ title }) => ({ name: title.substring(0, 3) }))}
    />
  </ChartWrapper>
);

Now we come to the pie chart. There's a trick to hide the axis—we add an empty VictoryAxis here:

const PieChart = ({ data }) => (
  <ChartWrapper hideAxis>
    <VictoryPie
      data={data.chartPivot()}
      y="Logs.count"
      labels={item => numberFormatter(item[data.seriesNames()[0].key])}
      padAngle={3}
      innerRadius={40}
      labelRadius={70}
      style={{ labels: { fill: "white", fontSize: 14 } }}
      colorScale={colors}
    />
    <VictoryAxis style={{ axis: { stroke: "none" }, tickLabels: { fill: "none" } }} />
    <VictoryLegend x={40} y={260}
      orientation="horizontal"
      colorScale={colors}
      data={data.chartPivot().map(({ x }) => ({ name: x }))}
    />
  </ChartWrapper>
);

Here's a screenshot of the pie chart on the dashboard:

And our dashboard is done! You can find all the code and the app on Expo. You can run this app online or launch it on your device via the Expo app. It will work relatively slow as it's not compiled with platform-specific native code, but you can always download the source code and build a native app for your platform via Xcode or Android Studio.

I hope this tutorial helps you build great apps!

Nginx Log Analytics with AWS Athena and Cube.js

Timur Minulin — Thu, 11 Apr 2019 15:00:02 +0000

Sometimes, existing commercial or out-of-the-box open-source tools like Grafana don’t fit requirements for Nginx log analytics. Whether it is pricing, privacy, or customization issues, it is always good to know how to build such a system internally.

In the following tutorial, I’ll show you how to build your own Nginx log analytics with Fluentd, Kinesis Data Firehose, Glue, Athena, and Cube.js. This stack also makes it easy to add data from other sources, such as Snowplow events, into the same S3 bucket and merge results in Athena. I’ll walk you through the whole pipeline from data collection to the visualization.

Here is the live demo of the final dashboard.

Here’s a sample architecture of the application we’re going to build:

Collecting Nginx Logs

By default, Nginx writes logs in a plain text format like this:

4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-up HTTP/2.0" 200 9168 "https://example.com/sign-in" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-in HTTP/2.0" 200 9168 "https://example.com/sign-up" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"

Although we can parse that, it would be much easier to change Nginx configuration to use the JSON format.

log_format json_combined escape=json '{ "created_at": "$msec", '
            '"remote_addr": "$remote_addr", '
            '"remote_user": "$remote_user", '
            '"request": "$request", '
            '"status": $status, '
            '"bytes_sent": $bytes_sent, '
            '"request_length": $request_length, '
            '"request_time": $request_time, '
            '"http_referrer": "$http_referer", '
            '"http_x_forwarded_for": "$http_x_forwarded_for", '
            '"http_user_agent": "$http_user_agent" }';

access_log  /var/log/nginx/access.log  json_combined;

Create an S3 Bucket

Create a new S3 bucket for the log files. Note that logs should be placed in the root of the bucket and no other data can be stored in that bucket. Also, consider using the same region as your Athena because Athena is not available in some regions.

Define a Table Schema via the Athena Console

Open your Athena console and select the database you'd be using. To create a table, you can use AWS UI or just run create statement in the console.

CREATE EXTERNAL TABLE `kinesis_logs_nginx`(
  `created_at` double, 
  `remote_addr` string, 
  `remote_user` string, 
  `request` string, 
  `status` int, 
  `bytes_sent` int, 
  `request_length` int, 
  `request_time` double, 
  `http_referrer` string, 
  `http_x_forwarded_for` string, 
  `http_user_agent` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://<YOUR-S3-BUCKET>'
TBLPROPERTIES ('has_encrypted_data'='false');

Create a Kinesis Firehose Stream

Open up the Kinesis Firehose console and click "Create delivery stream." Enter a name for the stream and set delivery to direct PUT.

Click next. Select Record format conversion to Enabled, Output format to Apache ORC, and select the database you created as the schema source.

Select S3 as a storage format. Select the S3 bucket you created for log files and leave S3 Prefix empty.

You can change other options depending on your load, but we'll continue with the default ones. S3 compression is not available since the ORC format includes compression.
Now you've created the Kinesis Firehose stream!

Fluentd

There are many different ways to install and use Fluentd. We'll stick with the Docker version.

First, create a fluent.conf file and add a new Fluentd source to collect logs:

<source>
  @type forward
  port 24224
  bind 0.0.0.0
</source>

Now we can run a Fluentd server with this configuration. Here's a basic configuration; you can check out other options on Docker Hub.

$ docker run \
  -d \
  -p 24224:24224 \
  -p 24224:24224/udp \
  -v /data:/fluentd/log \
  -v <PATH-TO-FLUENT-CONF>:/fluentd/etc fluentd \
  -c /fluentd/etc/fluent.conf
  fluent/fluentd:stable

This configuration uses the /fluentd/log path for cache files. You can run Fluentd in Docker without mounting the data directory, but in the case of a restart, you can lose all cached logs. Also, you can change the default 24224 port to any other unused port. Now, as we have a running Fluentd server, we can stream Nginx logs to it.

Since we run Nginx in Docker, we can run it with the built-in docker Fluentd log driver:

$ docker run \
--log-driver=fluentd \
--log-opt fluentd-address=<FLUENTD-SERVER-ADDRESS>\
--log-opt tag=\"{{.Name}}\" \
-v /some/content:/usr/share/nginx/html:ro \
-d \
nginx

We'll use the Amazon Kinesis Output Plugin. It can store logs captured from Nginx as ORC files.

Next, select the Nginx log using tag prefix match and parse JSON:

<filter YOUR-NGINX-TAG.*>
  @type parser
  key_name log
  emit_invalid_record_to_error false
  <parse>
    @type json
  </parse>
</filter>

We’re using the kinesis_firehose output plugin to send parsed logs to Kinesis Firehose:

<match YOUR-NGINX-TAG.*>
    @type kinesis_firehose
    region region
    delivery_stream_name <YOUR-KINESIS-STREAM-NAME>
    aws_key_id <YOUR-AWS-KEY-ID>
    aws_sec_key <YOUR_AWS-SEC_KEY>
</match>

Athena

Now you can query Nginx logs in Athena with SQL. Let's find some recent errors:

SELECT * FROM "db_name"."table_name" WHERE status > 499 ORDER BY created_at DESC limit 10;

Full Scan for Each Request

Now we have logs parsed and delivered to S3 in the ORC format, which is compressed and efficient to query. Also, Kinesis Firehose partitions logs by date and hour, but querying and filtering them requires Athena to scan all files. This is a problem, because the more logs you store, the more data gets scanned per request. It is slow and also pricey, because Athena pricing depends on scanned data volume.

To solve this, we'll use AWS Glue Crawler, which gathers partition data from S3 and writes it to the Glue Metastore. Once data is partitioned, Athena will only scan data in selected partitions. It makes querying much more efficient in terms of time and cost.

Setting an Amazon Glue Crawler

Amazon Glue Crawler can scan the data in the bucket and create a partitioned table for that data.

Create a Glue Crawler and add the bucket you use to store logs from Kinesis. You can add multiple buckets to be scanned on each run, and the crawler will create separate tables for each bucket. Make sure to schedule this crawler to run periodically depending on your needs. We usually run a single crawler for all buckets every hour. Also, it's easier to use a separate database for all crawled tables.

Partitioned Tables

Open your Athena console and choose the database you selected in the crawler configuration. After the first run of the crawler, you should see a table named the same as the S3 bucket where all log files are located. You can query results for some hours and filter the data by partitions.

SELECT * FROM "default"."part_demo_kinesis_bucket"
WHERE(
  partition_0 = '2019' AND
  partition_1 = '04' AND
  partition_2 = '08' AND
  partition_3 = '06'
  );

This query will select all events that came from 6 AM to 7 AM on April 8, 2019.

Did that help? Let's run the same request without a partition filter.

You can see that the query took 3.59 seconds to run and processed 244.34 megabytes of data. Let's use partition helpers:

This query is a bit faster and, more importantly, took only 1.23 megabytes of scanned data. On larger datasets, the difference grows dramatically.

Building a Dashboard with Cube.js

We will use Cube.js to query Athena and build a dashboard. It has plenty of powerful features, but the main reasons we would want to use it, in this case, are automatic handling of partitions and pre-aggregations.

Cube.js can dynamically generate SQL, taking care of partitioning. It uses data schema, which is written in Javascript, to generate SQL. We can put instructions on how to filter partitions in the data schema.

Let’s create a new Cube.js app with a serverless deployment option. Since we’re using Athena, serverless is the easiest way to deploy. If you prefer other deployment environments, such as Docker or Heroku, you can check the documentation on deployment options here.

$ npm install -g cubejs-cli
$ cubejs create nginx-log-analytics -t serverless -d athena

Cube.js uses environment variables for database credentials. On new app creation, the CLI generates the .env file with placeholders in the project directory. Fill it with your Athena credentials.

Now, we can create a data schema file, where we’ll define how Cube.js should query the Athena logs table, as well as measures and dimensions for our dashboard. If you are just getting started with Cube.js, I recommend checking this or that tutorial to learn more about data schemas and how Cube.js generates SQL.

In the schema folder, create the file Logs.js with the following content:

const partitionFilter = (from, to) => `
    date(from_iso8601_timestamp(${from})) <= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d') AND
    date(from_iso8601_timestamp(${to})) >= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d')
    `

cube(`Logs`, {
  sql: `
  select * from part_demo_kinesis_bucket
  WHERE ${FILTER_PARAMS.Logs.createdAt.filter(partitionFilter)}
  `,

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

    errorCount: {
      type: `count`,
      filters: [
        { sql: `${CUBE.isError} = 'Yes'` }
      ]
    },

    errorRate: {
      type: `number`,
      sql: `100.0 * ${errorCount} / ${count}`,
      format: `percent`
    }
  },

  dimensions: {
    status: {
      sql: `status`,
      type: `number`
    },

    isError: {
      type: `string`,
      case: {
        when: [{
          sql: `${CUBE}.status >= 400`, label: `Yes`
        }],
        else: { label: `No` }
      }
    },

    createdAt: {
      sql: `from_unixtime(created_at)`,
      type: `time`
    }
  }
});

In the top level sql expression for the Logs cube, we are using the FILTER_PARAMS feature of Cube.js to dynamically generate SQL based on the passed filter parameters.

We also define measures and dimensions we’ll use in our dashboard. One last thing to do before building a frontend is to set up pre-aggregations. The Cube.js pre-aggregation engine builds a layer of aggregated data in your database during the runtime and keeps it up-to-date. It can significantly speed up the performance and also in the case of Athena reduce billing as well. This guide covers using pre-aggregations in more details.

To add it to our schema, add the following block to the end of the cube definition.

preAggregations: {
  main: {
    type: `rollup`,
    measureReferences: [count, errorCount],
    dimensionReferences: [isError, status],
    timeDimensionReference: createdAt,
    granularity: `day`,
    partitionGranularity: `month`,
    refreshKey: {
      sql: FILTER_PARAMS.Logs.createdAt.filter((from, to) => 
        `select
           CASE WHEN from_iso8601_timestamp(${to}) + interval '3' day > now()
           THEN date_trunc('hour', now()) END`
      )
    }
  }
}

We’re pre-aggregating all the measures and dimensions we’ll use and also making this pre-aggregation to be partitioned by month. Partitioning pre-aggregations can dramatically increase background refresh time.

Now, we are ready to build our frontend dashboard.

Cube.js provides REST API, a Javascript client, and bindings for popular frameworks, such as React and Vue. The clients are visualization agnostic and take care of API calls and data formatting, letting developers use any visualization library.

The Cube.js server accepts a query in a JSON format with measures and dimensions. It then generates and executes SQL against Athena, and sends the result back. For example, to load the count of requests with an error over time by day, we can use the following request:

{
  "measures": ["Logs.errorCount"],
  "timeDimensions": [
    {
      "dimension": "Logs.createdAt",
      "dateRange": ["2019-01-01", "2019-01-07"],
      "granularity": "day"
    }
  ]
}

You can install the Cube.js Javascript Client and React binding with NPM.

$ npm i --save @cubejs-client/core @cubejs-client/react

Then import the cubejs and QueryRenderer components, and use them to fetch the data. In the example below, we use Recharts to visualize data.

import React from 'react';
import { LineChart, Line, XAxis, YAxis } from 'recharts';
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';

const cubejsApi = cubejs(
  'YOUR-CUBEJS-API-TOKEN',
  { apiUrl: 'http://localhost:4000/cubejs-api/v1' },
);

export default () => {
  return (
    <QueryRenderer
      query={{
        measures: ['Logs.errorCount'],
        timeDimensions: [{
            dimension: 'Logs.createdAt',
            dateRange: ['2019-01-01', '2019-01-07'],
            granularity: 'day'
        }]
      }}
      cubejsApi={cubejsApi}
      render={({ resultSet }) => {
        if (!resultSet) {
          return 'Loading...';
        }

        return (
          <LineChart data={resultSet.rawData()}>
            <XAxis dataKey="Logs.createdAt"/>
            <YAxis/>
            <Line type="monotone" dataKey="Logs.errorCount" stroke="#8884d8"/>
          </LineChart>
        );
      }}
    />
  )
}

This tutorial goes into great detail on how to build dashboards with Cube.js.

You can find a CodeSandbox with a demo dashboard built with React and Cube.js below.