Loading...

Follow Stats and Bots - Medium on Feedspot

Continue with Google
Continue with Facebook
or

Valid

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.

Cube.js Vue Demo - CodeSandbox

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.

Vue Dashboard Tutorial Using Cube.js was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

Redash Dashboard Tutorial with Cube.js was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

cubejs-client-ngx-demo - CodeSandbox

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 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

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-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.

Angular Dashboard Tutorial with Cube.js was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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!

React Native Charts with Cube.js and Victory was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

While working on Cube.js, we’re seeing a lot of different visualization libraries. Given that Cube.js provides an API layer for analytics on top of an SQL database and doesn’t play on the visualization field, any charting library can be used to build dashboards with it. That’s why we’re always on a search for a nice and developer-friendly visualization library.

The list below is for React-specific libraries. I’ll try to build almost the same stacked bar chart with axes formatting, legend and tooltip using every one of it. For the data backend, we’ll use Cube.js hosted on Heroku. I’ll also use Cube.js React binding, which uses the render props technique and doesn’t render anything itself, letting us build whatever we want with any charting library.

Along the way, I’ll also use two very handy libraries — moment.js and numeral.js for dates and numbers formatting, respectively.

As a data input, we are going to use the resultSet.chartPivot() method from the Cube.js Javascript client. It returns an array of data, and in our case, it looks like this:

[
{
"x": "2017-01-01T00:00:00.000",
"completed, Orders.count": 208,
"processing, Orders.count": 252,
"shipped, Orders.count": 233
},
{
"x": "2017-02-01T00:00:00.000",
"completed, Orders.count": 188,
"processing, Orders.count": 209,
"shipped, Orders.count": 222
},

]

Now, let’s jump to the list.

If you don’t see your favorite library or just want me to add one more — just ping me in this Public Slack Community. I’m happy to add as many libraries as I can to the list.

Recharts

Recharts provides a set of modular charting components and does a great job by letting you mix those components together to easily build things like a composed line and bar chart.

It is the most popular library to date. It has more than 11k stars on Github, but a huge number (600 to date) of open issues as well.

The documentation is quite extensive but lacks details in some parts. You can find a bunch of examples at Rechart’s website, which could be a good starting point to building your own charts.

Recharts has a bunch of options for customization. Besides that, it allows low-level customization via custom SVG elements.

Here is how to build our stacked bar chart in Recharts:

In this and all the following examples I’m using colors, dateFormatter, and numberFormatter variables. Here is how they are defined:

const numberFormatter = item => numeral(item).format("0,0");
const dateFormatter = item => moment(item).format("MMM YY");
const colors = ["#7DB3FF", "#49457B", "#FF7C78"];
export default ({ resultSet, colors, dateFormatter, numberFormatter }) => (
<ResponsiveContainer width="100%" height={300}>
<BarChart data={resultSet.chartPivot()}>
<XAxis tickFormatter={dateFormatter} dataKey="x" />
<YAxis tickFormatter={numberFormatter} />
{resultSet.seriesNames().map((series, i) => (
<Bar
stackId="a"
dataKey={series}
name={series.split(",")[0]}
fill={colors[i]}
/>
))}
<Legend />
<Tooltip labelFormatter={dateFormatter} formatter={numberFormatter} />
</BarChart>
</ResponsiveContainer>
);

React Visualization Libraries - CodeSandbox

Victory

Victory follows the same composable pattern as Recharts. It is developed by Formidable, which has other solid open-source libraries besides Victory as well. It is the only library on the list that also works with React Native.

It’s the second most popular library after Recharts with more than 7K stars on Github, but has 5 times less open issues than Recharts. Victory has a community chat on Spectrum.

It is well documented and has an examples gallery.

Victory comes with two themes: grayscale and material, and allows you to create customer themes to have a consistent look across all the charts.

Below you can see the code for our stacked bar chart with Victory:

const transformResult = (series, resultSet) =>
resultSet.chartPivot().map(element => ({ x: element.x, y: element[series] }));
export default ({ resultSet, dateFormatter, colors, numberFormatter }) => (
<div height={300}>
<VictoryChart
containerComponent={
<VictoryVoronoiContainer
voronoiDimension="x"
labels={(d, i) => `${resultSet.seriesNames()[i]}: ${d.y}`}
labelComponent={
<VictoryTooltip cornerRadius={0} flyoutStyle={{ fill: "white" }} />
}
/>
}
domainPadding={{ x: 20, y: [0, 20] }}
>
<VictoryLegend
colorScale={colors}
data={resultSet.seriesNames().map(series => ({
name: series.split(",")[0]
}))}
orientation="horizontal"
y={275}
x={130}
/>
<VictoryAxis tickFormat={dateFormatter} tickCount={8} />
<VictoryAxis dependentAxis />
<VictoryStack colorScale={colors}>
{resultSet.seriesNames().map((series, i) => (
<VictoryBar key={i} data={transformResult(series, resultSet)} />
))}
</VictoryStack>
</VictoryChart>
</div>
);

React Visualization Libraries - CodeSandbox

Nivo

As well as Recharts and Victory, Nivo is built on top of D3 and is React-specific. But unlike previous libraries it is not composable. It provides one component per chart type and this component is configured via multiple props. Nivo is distributed as a set of packages for specific chart types, for example, @nivo/bar. So, one needs to install a specific package to use a specific chart type.

The project itself is quite active; it has more than 5k stars on Github and almost 300 members in the community chat.

It has interactive documentation, where you can build config for your chart. Although it is nice, It lacks a good old text API reference and search option. It also has a Storybook with examples. It helped me a lot to shortcut the time to build the first chart. Same as Victory, Nivo lets you create your own theme to have a consistent look across all the charts.

We’re going to use the @nivo/bar package for our stack bar chart; you can find a code sample and CodeSandbox demo below.

//https://github.com/plouc/nivo/issues/138#issuecomment-373015114
const ticksFormmater = (ticksCount, value, data, dateFormatter) => {
const valueIndex = data.map(i => i.x).indexOf(value);
if (valueIndex % Math.floor(data.length / ticksCount) === 0) {
return dateFormatter(value);
}
  return "";
};
export default ({ resultSet, colors, dateFormatter, numberFormatter }) => (
<div style={{ height: 300 }}>
<ResponsiveBar
enableLabel={false}
colors={colors}
data={resultSet.chartPivot()}
keys={resultSet.seriesNames()}
indexBy="x"
enableGridY={false}
padding={0.3}
margin={{ top: 60, right: 80, bottom: 60, left: 40 }}
axisLeft={{
format: numberFormatter
}}
axisBottom={{
format: value =>
ticksFormmater(8, value, resultSet.chartPivot(), dateFormatter)
}}
tooltip={({ id, value, color }) => (
<strong style={{ color }}>
{id.split(",")[0]}: {numberFormatter(value)}
</strong>
)}
legends={[
{
anchor: "bottom",
direction: "row",
translateY: 50,
itemsSpacing: 2,
itemWidth: 150,
itemHeight: 20,
itemDirection: "left-to-right"
}
]}
/>
</div>
);

React Visualization Libraries - CodeSandbox

BizCharts

BizCharts is React-specific visualization library based on G2, a visualization grammar. It is backed by Alibaba and plays nicely with Ant Design Framework.

It has almost 4k stars on Github, but the majority of development is in Chinese, same for most of the documentation. I think the team is doing a great job of translating the docs, but it is still a work in progress.

Though, it lacks documentation in English the API is pretty straightforward. The only hard thing is to re-format the data into the specific format for the stacked chart.

const stackedChartData = resultSet => {
const data = resultSet
.pivot()
.map(({ xValues, yValuesArray }) =>
yValuesArray.map(([yValues, m]) => ({
x: resultSet.axisValuesString(xValues, ", "),
color: resultSet.axisValuesString(yValues, ", "),
measure: m && Number.parseFloat(m)
}))
)
.reduce((a, b) => a.concat(b));
  return data;
};
export default ({ resultSet, dateFormatter, colors, numberFormatter }) => (
<Chart
scale={{ x: { tickCount: 10 } }}
height={400}
data={stackedChartData(resultSet)}
forceFit
>
<Axis name="x" label={{ formatter: dateFormatter }} />
<Axis label={{ formatter: numberFormatter }} name="measure" />
<Tooltip />
<Geom
type="intervalStack"
position={`x*measure`}
color={["color", colors]}
/>
<Legend itemFormatter={item => item.split(",")[0]} />
</Chart>
)}
/>
);

React Visualization Libraries - CodeSandbox

React-vis

React-vis is being developed by Uber and seems quite active with 5.4k Github stars, 150 open issues, and a lot of recent commits. It is modular, like most of the libraries on the list. It comes with some nice default styles, which should be imported separately as CSS files.

Same as Nivo, it has Storybook with examples. The components API is also well documented. On the customization side you can control the look and feel via custom CSS styles. React-vis also provides a specific component for building custom SVG charts — CustomSVGSeries.

The API is similar to Victory’s. You can see I’m using almost the same transformResult from Victory's snippet.

const transformResult = (series, resultSet, dateFormatter) =>
resultSet
.chartPivot()
.map(element => ({ x: Date.parse(element.x), y: element[series] }));
export default ({ resultSet, dateFormatter, colors, numberFormatter }) => (
<XYPlot xType="time" height={300} width={500} stackBy="y">
<XAxis tickFormat={dateFormatter} tickSize={8} />
<YAxis />
{resultSet.seriesNames().map((series, i) => (
<VerticalBarSeries
cluster="stack 1"
key={i}
color={colors[i]}
data={transformResult(series, resultSet, dateFormatter)}
/>
))}
<DiscreteColorLegend
colors={colors}
items={resultSet.seriesNames().map(i => i.split(",")[0])}
orientation="horizontal"
style={{ position: "absolute", left: 130, bottom: -30 }}
/>
</XYPlot>
);

React Visualization Libraries - CodeSandbox

If you don’t see your favorite library or just want me to add one more — just ping me in this Public Slack Community. I’m happy to add as many libraries as I can to the list.

React Visualization Libraries in 2019 was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is an advanced tutorial. If you are just getting started with Cube.js, I recommend checking this tutorial first and then coming back here.

One of the most powerful features of Cube.js is pre-aggregations. Coupled with data schema, it eliminates the need to organize, denormalize, and transform data before using it with Cube.js. The pre-aggregation engine builds a layer of aggregated data in your database during the runtime and maintains it to be up-to-date.

Upon an incoming request, Cube.js will first look for a relevant pre-aggregation. If it cannot find any, it will build a new one. Once the pre-aggregation is built, all the subsequent requests will go to the pre-aggregated layer instead of hitting the raw data. It could speed the response time by hundreds or even thousands of times.

Pre-aggregations are materialized query results persisted as tables. In order to start using pre-aggregations, Cube.js should have write access to the stb_pre_aggregations schema where pre-aggregation tables will be stored.

Cube.js also takes care of keeping the pre-aggregation up-to-date. It performs refresh checks and if it finds that a pre-aggregation is outdated, it schedules a refresh in the background.

Creating a Simple Pre-Aggregation

Let’s take a look at the example of how we can use pre-aggregations to improve query performance.

For testing purposes, we will use a Postgres database and will generate around ten million records using the generate_series function.

$ createdb cubejs_test

The following SQL creates a table, orders, and inserts a sample of generated records into it.

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
amount integer,
created_at timestamp without time zone
);
CREATE INDEX orders_created_at_amount ON orders(created_at, amount);
INSERT INTO orders (created_at, amount)
SELECT
created_at,
floor((1000 + 500*random())*log(row_number() over())) as amount
FROM generate_series
( '1997-01-01'::date
, '2017-12-31'::date
, '1 minutes'::interval) created_at

Next, create a new Cube.js application if you don’t have any.

$ npm install -g cube.js
$ cubejs create test-app -d postgres

Change the content of .env in the project folder to the following.

CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=cubejs_test

Finally, generate a schema for the orders table and start the Cube.js server.

$  cubejs generate -t orders
$ npm run dev

Now, we can send a query to Cube.js with the Orders.count measure and Orders.createdAt time dimension with granularity set to month.

curl \
-H "Authorization: EXAMPLE-API-TOKEN" \
-G \
--data-urlencode 'query={
"measures" : ["Orders.amount"],
"timeDimensions":[{
"dimension": "Orders.createdAt",
"granularity": "month",
"dateRange": ["1997-01-01", "2017-01-01"]
}]
}' \
http://localhost:4000/cubejs-api/v1/load

Cube.js will respond with , because this query takes more than 5 seconds to process. Let’s look at Cube.js logs to see exactly how long it took for our Postgres to execute this query.

Performing query completed:
{
"queueSize":2,
"duration":6514,
"queryKey":[
"
SELECT
date_trunc('month', (orders.created_at::timestamptz at time zone 'UTC')) \"orders.created_at_month\",
sum(orders.amount) \"orders.amount\"
FROM
public.orders AS orders
WHERE (
orders.created_at >= $1::timestamptz
AND orders.created_at <= $2::timestamptz
)
GROUP BY 1
ORDER BY 1 ASC limit 10000
",
[
"2000-01-01T00:00:00Z",
"2017-01-01T23:59:59Z"
],
[]
]
}

It took 6,514 milliseconds (6.5 seconds) for Postgres to execute the above query. Although we have an index on the created_at and amount columns, it doesn't help a lot in this particular case since we're querying almost all the dates we have. The index would help if we query a smaller date range, but still, it would be a matter of seconds, not milliseconds.

We can significantly speed it up by adding a pre-aggregation layer. To do this, add the following preAggregations block to src/Orders.js:

preAggregations: {
amountByCreated: {
type: `rollup`,
measureReferences: [amount],
timeDimensionReference: createdAt,
granularity: `month`
}
}

The block above instructs Cube.js to build and use a rollup type of pre-aggregation when the “Orders.amount” measure and “Orders.createdAt” time dimension (with “month” granularity) are requested together. You can read more about pre-aggregation options in the documentation reference.

Now, once we send the same request, Cube.js will detect the pre-aggregation declaration and will start building it. Once it’s built, it will query it and send the result back. All the subsequent queries will go to the pre-aggregation layer.

Here is how querying pre-aggregation looks in the Cube.js logs:

Performing query completed: 
{
"queueSize":1,
"duration":5,
"queryKey":[
"
SELECT
\"orders.created_at_month\" \"orders.created_at_month\",
sum(\"orders.amount\") \"orders.amount\"
FROM
stb_pre_aggregations.orders_amount_by_created
WHERE (
\"orders.created_at_month\" >= ($1::timestamptz::timestamptz AT TIME ZONE 'UTC')
AND
\"orders.created_at_month\" <= ($2::timestamptz::timestamptz AT TIME ZONE 'UTC')
)
GROUP BY 1 ORDER BY 1 ASC LIMIT 10000
",
[
"1995-01-01T00:00:00Z",
"2017-01-01T23:59:59Z"
],
[
[
"
CREATE TABLE
stb_pre_aggregations.orders_amount_by_created
AS SELECT
date_trunc('month', (orders.created_at::timestamptz AT TIME ZONE 'UTC')) \"orders.created_at_month\",
sum(orders.amount) \"orders.amount\"
FROM
public.orders AS orders
GROUP BY 1
",
[]
]
]
]
}

As you can see, now it takes only 5 milliseconds (1,300 times faster) to get the same data. Also, you can note that SQL has been changed and now it queries data from stb_pre_aggregations.orders_amount_by_created, which is the table generated by Cube.js to store pre-aggregation for this query. The second query is a DDL statement for this pre-aggregation table.

Pre-Aggregations Refresh

Cube.js also takes care of keeping pre-aggregations up to date. By default, every two minutes on a new request Cube.js will initiate the refresh check.

You can set up a custom refresh check strategy by using refreshKey. The default strategy works the following way:

  • Check the max of time dimensions with updated in the name, if none exist…
  • Check the max of any existing time dimension, if none exist…
  • Check the count of rows for this cube.

If the result of the refresh check is different from the last one, Cube.js will initiate the rebuild of the pre-aggregation in the background and then hot swap the old one.

Next Steps

This guide is the first step to learning about pre-aggregations and how to start using them in your project. But there is much more you can do with them. You can find the pre-aggregations documentation reference here.

Also, here are some highlights with useful links to help you along the way.

Pre-aggregate queries across multiple cubes

Pre-aggregations work not only for measures and dimensions inside the single cube, but also across multiple joined cubes as well. If you have joined cubes, you can reference measures and dimensions from any part of the join tree. The example below shows how the Users.country dimension can be used with the Orders.count and Orders.revenue measures.

cube(`Orders`, {
sql: `select * from orders`,
  joins: {
Users: {
relationship: `belongsTo`,
sql: `${CUBE}.user_id = ${Users}.id`
}
},
  // …
  preAggregations: {
categoryAndDate: {
type: `rollup`,
measureReferences: [count, revenue],
dimensionReferences: [Users.country],
timeDimensionReference: createdAt,
granularity: `day`
}
}
});
Generate pre-aggregations dynamically

Since pre-aggregations are part of the data schema, which is basically a Javascript code, you can dynamically create all the required pre-aggregations. This guide covers how you can dynamically generate a Cube.js schema.

Time partitioning

You can instruct Cube.js to partition pre-aggregations by time using the partitionGranularity option. Cube.js will generate not a single table for the whole pre-aggregation, but a set of smaller tables. It can reduce the refresh time and cost in the case of BigQuery for example.

Time partitioning documentation reference.

preAggregations: {
categoryAndDate: {
type: `rollup`,
measureReferences: [count],
timeDimensionReference: createdAt,
granularity: `day`,
partitionGranularity: `month`
}
}
Data Cube Lattices

Cube.js can automatically build rollup pre-aggregations without the need to specify which measures and dimensions to use. It learns from query history and selects an optimal set of measures and dimensions for a given query. Under the hood it uses the Data Cube Lattices approach.

It is very useful if you need a lot of pre-aggregations and you don’t know ahead of time which ones exactly. Using autoRollup will save you from coding manually all the possible aggregations.

You can find documentation for auto rollup here.

cube(`Orders`, {
sql: `select * from orders`,
  preAggregations: {
main: {
type: `autoRollup`
}
}
});

Optimize Cube.js Performance with Pre-Aggregations was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Sometimes, existing commercial or out-of-the-box open-source tools like Grafana doesn’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 thisor 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-aggregationscan 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.

Nginx Logs Dashboard - CodeSandbox

Nginx Log Analytics with AWS Athena and Cube.js was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is the second part of a tutorial series on building an analytical web application with Cube.js. You can find the first part here. It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. The final source code is available here and the live demo is here. The example app is serverless and running on AWS Lambda. It displays data about its own usage.

In this part, we are going to add Funnel Analysis to our application. Funnel Analysis, alongside with Retention Analysis, is vital to analyze behavior across the customer journey. A funnel is a series of events that a user goes through within the app, such as completing an onboarding flow. A user is considered converted through a step in the funnel if she performs the event in the specified order. Calculating how many unique users made each event could show you a conversion rate between each step. It helps you to localize a problem down to a certain stage.

Since our application tracks its own usage, we’ll build funnels to show how well users navigate through the funnels usage. Quite meta, right?

Here’s how it looks. You check the live demo here.

Building SQL for Funnels

Just a quick recap of part I — we are collecting data with the Snowplow tracker, storing it in S3, and querying with Athena and Cube.js. Athena is built on Presto, and supports standard SQL. So to build a funnel, we need to write a SQL code. Real-world funnel SQL could be quite complex and slow from a performance perspective. Since we are using Cube.js to organize data schema and generate SQL, we can solve both of these problems.

Cube.js allows the building of packages, which are a collection of reusable data schemas. Some of them are specific for datasets, such as the Stripe Package. Others provide helpful macros for common data transformations. And one of them we’re going to use — the Funnels package.

If you are new to Cube.js Data Schema, I strongly recommend you check this or that tutorial first and then come back to learn about the funnels package.

The best way to organize funnels is to create a separate cube for each funnel. We’ll use eventFunnel from the Funnel package. All we need to do is to pass an object with the required properties to the eventFunnel function. Check the Funnels package documentation for detailed information about its configuration.

Here is how this config could look. In production applications, you’re most likely going to generate Cubes.js schema dynamically. You can read more about how to do it here.

import Funnels from "Funnels";
import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from "./Events.js";
cube("FunnelsUsageFunnel", {
extends: Funnels.eventFunnel({
userId: {
sql: `user_id`
},
time: {
sql: `time`
},
steps: [
{
name: `viewAnyPage`,
eventsView: {
sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT}`
}
},
{
name: `viewFunnelsPage`,
eventsView: {
sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT} AND page_title = 'Funnels'`
},
timeToConvert: "30 day"
},
{
name: `funnelSelected`,
eventsView: {
sql: `select * from (${eventsSQl}) WHERE event = '${CUSTOM_EVENT} AND se_category = 'Funnels' AND se_action = 'Funnel Selected'`
},
timeToConvert: "30 day"
}
]
})
});

The above, 3-step funnel, describes the user flow from viewing any page, such as the home page, to going to Funnels and then eventually selecting a funnel from the dropdown. We’re setting timeToConvert to 30 days for the 2nd and 3rd steps. This means we give a user a 30 day window to let her complete the target action to make it to the funnel.

In our example app, we generate these configs dynamically. You can check the code on Github here.

Materialize Funnels SQL with Pre-Aggregations

As I mentioned before, there is a built-in way in Cube.js to accelerate queries’ performance. Cube.js can materialize query results in a table. It keeps them up to date and queries them instead of raw data. Pre-Aggregations can be quite complex, including multi-stage and dependency management. But for our case, the simplest originalSql pre-aggregation should be enough. It materializes the base SQL for the cube.

Learn more about pre-aggregations here.

import Funnels from 'Funnels';
import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from './Events.js';
cube('FunnelsUsageFunnel', {
extends: Funnels.eventFunnel({ ... }),
preAggregations: {
main: {
type: `originalSql`
}
}
});
Visualize

There are a lot of way to visualize a funnel. Cube.js is visualization-agnostic, so pick one that works for you and fits well into your app design. In our example app, we use a bar chart from the Recharts library.

The Funnels package generates a cube with conversions and conversionsPercent measures, and steps and time dimensions. To build a bar chart funnel, we need to query the conversions measure grouped by the step dimension. The time dimension should be used in the filter to allow users to select a specific date range of the funnel.

Here is the code (we are using React and the Cube.js React Client):

import React from "react";
import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip } from "recharts";
const cubejsApi = cubejs(
"YOUR-API-KEI",
{ apiUrl: "http://localhost:4000/cubejs-api/v1" }
);
const Funnel = ({ dateRange, funnelId }) => (
<QueryRenderer
cubejsApi={cubejsApi}
query={{
measures: [`${funnelId}.conversions`],
dimensions: [`${funnelId}.step`],
filters: [
{
dimension: `${funnelId}.time`,
operator: `inDateRange`,
values: dateRange
}
]
}}
render={({ resultSet, error }) => {
if (resultSet) {
return (
<BarChart
width={600}
height={300}
margin={{ top: 20 }}
data={resultSet.chartPivot()}
>
<CartesianGrid strokeDasharray="3 3" />
<XAxis dataKey="x" minTickGap={20} />
<YAxis />
<Tooltip />
<Bar dataKey={`${funnelId}.conversions`} fill="#7A77FF"/>
</BarChart>
);
}
      return "Loading...";
}}
/>
);
export default Funnel;

If you run this code in CodeSandbox, you should see something like this.

Cube.js Funnel Example - CodeSandbox

The above example is connected to the Cube.js backend from our event analytics app.

In the next part, we’ll walk through how to build a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 4 will cover the Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

You can check out the full source code of the application here.

And the live demo is available here.

Building an Open Source Mixpanel Alternative. Part 2: Conversion Funnels was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Cube.js is an open source framework for building analytical web applications. It is primarily used to build internal business intelligence tools or to add customer-facing analytics to an existing application. In a majority of cases, the first step of building such an application is an analytics dashboard. It usually starts with — “let’s add an analytics dashboard to our admin panel.” Then, as it always happens in software development, things get more complicated, much more complicated.

When we started working on Cube.js, we wanted to build a tool, which is simple to start but scales easily in features, complexity, and data volume. Cube.js puts down a solid foundation for your future analytical system, whether it is a standalone application or embedded into the existing one.

You can think about this tutorial as “Cube.js 101.” I’ll walk you through the basic steps of designing the first dashboard from the database to visualizations.

The live demo of the final dashboard is available here. The full source code is on Github.

Architecture

The majority of modern web applications are built as a single-page application, where the frontend is separated from the backend. The backend also usually is split into multiple services, following a microservice architecture.

Cube.js embraces this approach. Conventionally you run Cube.js Backend as a service. It manages the connection to your database, including queries queue, caching, pre-aggregation, and more. It also exposes an API for your frontend app to build dashboards and other analytics features.

Backend

Analytics starts with the data and data resides in a database. That is the first thing we need to have in place. You most likely already have a database for your application and usually, it is just fine to use for analytics. Modern popular databases such as Postgres or MySQL are well suited for a simple analytical workload. By simple, I mean a data volume with less than 1 billion rows.

MongoDB is fine as well, the only thing you’ll need to add is MongoDB Connector for BI. It allows executing SQL code on top of your MongoDB data. It is free and can be easily downloaded from the MongoDB website. One more thing to keep in mind is replication. It is considered a bad practice to run analytics queries against your production database mostly because of the performance issues. Cube.js can dramatically reduce the amount of a database’s workload, but still, I’d recommend connecting to the replica.

To summarize — If you use Postgres or MySQL, just create a replica and we’re good to go. If you use MongoDB — download MongoDB Connector for BI and create a replica.

If you don’t have any data for the dashboard, you can load our sample e-commerce Postgres dataset.

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

Now, as we have data in the database, we’re ready to create the Cube.js Backend service. Run the following commands in your terminal:

$ npm install -g cubejs-cli
$ cubejs create dashboard-backend -d postgres

The commands above install Cube.js CLI and create a new service, configured to work with Postgres database.

Cube.js uses environment variables for configuration. It uses environment variables starting with CUBEJS_. To configure the connection to our database, we need to specify the db type and name.

CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=ecom
Cube.js Data Schema

The next step is to create a Cube.js data schema. Cube.js uses the data schema to generate an SQL code, which will be executed in your database. The data schema is not a replacement for SQL. It is designed to make SQL reusable and give it a structure while preserving all of its power. Basic elements of the data schema are measures and dimensions.

Measure is referred to as quantitative data, such as the number of units sold, number of unique visits, profit, and so on.

Dimension is referred to as categorical data, such as state, gender, product name, or units of time (e.g., day, week, month).

Conventionally, schema files are located in the schema folder. Here is an example of the schema, which can be used to describe users’ data.

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, with the above schema in place, we can send queries to the Cube.js backend about users’ data. Cube.js queries are plain javascript objects. Usually it has one or more measures, dimensions, and timeDimensions.

If we want to answer the question “Where are our users based?” we can send the following query to the Cube.js:

{
measures: ['Users.count'],
dimensions: ['Users.city']
}

Cube.js will generate the required SQL based on the schema, execute it, and send the result back.

Let’s create a slightly more complicated query. We can add a timeDimensions to see how the ratio of different cities has been changing every month during the last year. To do this, we will add a signedUp time dimension, group it by monthly, and filter only last year’s signups.

{
measures: ['Users.count'],
dimensions: ['Users.city'],
timeDimensions: [{
dimension: 'Users.signedUp',
granularity: 'month',
dateRange: ['2018-01-31', '2018-12-31']
}]
}

Cube.js can generate simple schemas based on your database’s tables. Let’s generate schemas we need for our dashboard and then start a dev server.

$ cubejs generate -t users, orders
$ npm dev

You can inspect generated schemas and send test queries by opening a development playground at http://localhost:4000.

Frontend

We’ll build our frontend and dashboard with React, using the Cube.js React client. But you can use any framework or just vanilla javascript to build a frontend with Cube.js. This tutorial shows you how to build a dashboard in pure javascript. We’ll set everything up using Create React App, which is officially supported by the React team. It packages all the dependencies for React app and makes it easy to get started with a new project. Run the following commands in your terminal:

$ npx create-react-app dashboard-frontend
$ cd cubejs-dashboard
$ npm start

The last line starts a server on port 3000 and opens your web browser at http://localhost:3000.

We’ll build our UI with Reactstrap, which is a React wrapper for Bootstrap 4. Install Reactstrap and Bootstrap from NPM. Reactstrap does not include Bootstrap CSS, so this needs to be installed separately:

$ npm install reactstrap bootstrap --save

Import Bootstrap CSS in the src/index.js file before importing ./index.css:

import 'bootstrap/dist/css/bootstrap.min.css';

Now we are ready to use the Reactstrap components.

The next step is to install Cube.js client to fetch the data from the server and our visualization library to display it. For this tutorial, we’re going to use Recharts. Cube.js is visualization agnostic, meaning you can use any library you want. We’ll also use moment and numeral to nicely format dates and numbers.

$ npm install --save @cubejs-client/core @cubejs-client/react recharts moment numeral

Finally, we’re done with dependencies, so let’s go ahead and create our first chart. Replace the contents of src/App.js with the following:

import React, { Component } from "react";
import {
BarChart,
Bar,
XAxis,
YAxis,
Tooltip,
ResponsiveContainer
} from "recharts";
import cubejs from "@cubejs-client/core";
import moment from "moment";
import { QueryRenderer } from "@cubejs-client/react";
const cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, {
apiUrl: process.env.REACT_APP_API_URL
});
const dateFormatter = item => moment(item).format("MMM YY");
class App extends Component {
render() {
return (
<QueryRenderer
query={{
measures: ["Orders.count"],
timeDimensions: [
{
dimension: "Orders.createdAt",
dateRange: ["2017-01-01", "2018-12-31"],
granularity: "month"
}
]
}}
cubejsApi={cubejsApi}
render={({ resultSet }) => {
if (!resultSet) {
return "Loading...";
}
         return (
<ResponsiveContainer width="100%" height={300}>
<BarChart data={resultSet.chartPivot()}>
<XAxis dataKey="x" tickFormatter={dateFormatter} />
<YAxis />
<Tooltip labelFormatter={dateFormatter} />
<Bar dataKey="Orders.count" fill="rgba(106, 110, 229)" />
</BarChart>
</ResponsiveContainer>
);
}}
/>
);
}
}
export default App;

You can check out this example in the CodeSandbox below.

Let’s look deeper at how we load data and draw the chart.

First, we’re initializing the Cube.js API client:

const cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, {
apiUrl: process.env.REACT_APP_API_URL
});

Here we are using the REACT_APP_CUBEJS_TOKEN and REACT_APP_API_URLenvironment variables. Create React App automatically loads your env variables from the .env file if they start with REACT_APP_. The Cube.js backend will print the development API token during the startup.

Create a .env file with the correct credentials.

REACT_APP_CUBEJS_TOKEN=COPY-API-TOKEN-FROM-TERMINAL-OUTPUT
REACT_APP_API_URL=http://localhost:4000/cubejs-api/v1

Next, we are using the QueryRenderer Cube.js React Component to load Orders data.

<QueryRenderer
query={{
measures: ["Orders.count"],
timeDimensions: [
{
dimension: "Orders.createdAt",
dateRange: ["2017-01-01", "2018-12-31"],
granularity: "month"
}
]
}}
cubejsApi={cubejsApi}
render={({ resultSet }) => {
// Render result
}}
/>

QueryRenderer performs an API request to the Cube.js backend and uses the render props technique to let you render the result however you want to. We’ve already covered the query format above, but in case you want to refresh — here is the query format full reference.

The render parameter of QueryRenderer is a function of the type ({error, resultSet, isLoading}) => React.Node. The output of this function will be rendered by the QueryRenderer. A resultSet is an object containing data obtained from the query. If this object is not defined, it means that the data is still being fetched.

resultSet provides multiple methods for data manipulation, but in our case, we need just the chartPivot method, which returns data in a format expected by Recharts.

We’ll plot the Orders data as a bar chart inside a responsive container.

if (!resultSet) {
return "Loading...";
}
return (
<ResponsiveContainer width="100%" height={300}>
<BarChart data={resultSet.chartPivot()}>
<XAxis dataKey="x" tickFormatter={dateFormatter} />
<YAxis />
<Tooltip labelFormatter={dateFormatter} />
<Bar dataKey="Orders.count" fill="rgba(106, 110, 229)" />
</BarChart>
</ResponsiveContainer>
);
Building a Dashboard

We learned how to build a single chart with Cube.js and Recharts, and we are now ready to start building the whole dashboard. There are some best practices regarding designing the layout of the dashboard. The common practice is to put the most important and high-level metrics on the top as single value charts, sometimes called KPIs, and then list the relevant breakdowns of those metrics.

Here is the screenshot of our final dashboard with KPIs on top followed by bar and line charts.

First, let’s refactor our chart and extract the common code into a reusable <Chart /> component. Create an src/Chart.js file the following content:

import React from "react";
import { Card, CardTitle, CardBody, CardText } from "reactstrap";
import { QueryRenderer } from "@cubejs-client/react";
const Chart = ({ cubejsApi, title, query, render }) => (
<Card>
<CardBody>
<CardTitle tag="h5">{title}</CardTitle>
<CardText>
<QueryRenderer
query={query}
cubejsApi={cubejsApi}
render={({ resultSet }) => {
if (!resultSet) {
return <div className="loader" />;
}
           return render(resultSet);
}}
/>
</CardText>
</CardBody>
</Card>
);
export default Chart;

Next, let’s use this component to create the dashboard. Replace the content of src/App.js with the following:

import React, { Component } from "react";
import { Container, Row, Col } from "reactstrap";
import {
AreaChart,
Area,
XAxis,
YAxis,
Tooltip,
ResponsiveContainer,
Legend,
BarChart,
Bar
} from "recharts";
import moment from "moment";
import numeral from "numeral";
import cubejs from "@cubejs-client/core";
import Chart from "./Chart.js";
const cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, {
apiUrl: process.env.REACT_APP_API_URL
});
const numberFormatter = item => numeral(item).format("0,0");
const dateFormatter = item => moment(item).format("MMM YY");
const renderSingleValue = (resultSet, key) => (
<h1 height={300}>{numberFormatter(resultSet.chartPivot()[0][key])}</h1>
);
class App extends Component {
render() {
return (
<Container fluid>
<Row>
<Col sm="4">
<Chart
cubejsApi={cubejsApi}
title="Total Users"
query={{ measures: ["Users.count"] }}
render={resultSet => renderSingleValue(resultSet, "Users.count")}
/>
</Col>
<Col sm="4">
<Chart
cubejsApi={cubejsApi}
title="Total Orders"
query={{ measures: ["Orders.count"] }}
render={resultSet => renderSingleValue(resultSet, "Orders.count")}
/>
</Col>
<Col sm="4">
<Chart
cubejsApi={cubejsApi}
title="Shipped Orders"
query={{
measures: ["Orders.count"],
filters: [
{
dimension: "Orders.status",
operator: "equals",
values: ["shipped"]
}
]
}}
render={resultSet => renderSingleValue(resultSet, "Orders.count")}
/>
</Col>
</Row>
<br />
<br />
<Row>
<Col sm="6">
<Chart
cubejsApi={cubejsApi}
title="New Users Over Time"
query={{
measures: ["Users.count"],
timeDimensions: [
{
dimension: "Users.createdAt",
dateRange: ["2017-01-01", "2018-12-31"],
granularity: "month"
}
]
}}
render={resultSet => (
<ResponsiveContainer width="100%" height={300}>
<AreaChart data={resultSet.chartPivot()}>
<XAxis dataKey="category" tickFormatter={dateFormatter} />
<YAxis tickFormatter={numberFormatter} />
<Tooltip labelFormatter={dateFormatter} />
<Area
type="monotone"
dataKey="Users.count"
name="Users"
stroke="rgb(106, 110, 229)"
fill="rgba(106, 110, 229, .16)"
/>
</AreaChart>
</ResponsiveContainer>
)}
/>
</Col>
<Col sm="6">
<Chart
cubejsApi={cubejsApi}
title="Orders by Status Over time"
query={{
measures: ["Orders.count"],
dimensions: ["Orders.status"],
timeDimensions: [
{
dimension: "Orders.createdAt",
dateRange: ["2017-01-01", "2018-12-31"],
granularity: "month"
}
]
}}
render={resultSet => {
return (
<ResponsiveContainer width="100%" height={300}>
<BarChart data={resultSet.chartPivot()}>
<XAxis tickFormatter={dateFormatter} dataKey="x" />
<YAxis tickFormatter={numberFormatter} />
<Bar
stackId="a"
dataKey="shipped, Orders.count"
name="Shipped"
fill="#7DB3FF"
/>
<Bar
stackId="a"
dataKey="processing, Orders.count"
name="Processing"
fill="#49457B"
/>
<Bar
stackId="a"
dataKey="completed, Orders.count"
name="Completed"
fill="#FF7C78"
/>
<Legend />
<Tooltip />
</BarChart>
</ResponsiveContainer>
);
}}
/>
</Col>
</Row>
</Container>
);
}
}
export default App;

That is enough to build our first dashboard. Give it a try in the CodeSanbox below.

Cube.js React 101 - 2 - CodeSandbox

Next Steps

We’ve built a simple proof of concept dashboard with Cube.js. You can check the live demo here. The full source code is available on Github.

To learn more about Cube.js backend deployment, you can refer to the deployment documentation. Also, here you can find more tutorials on a variety of topics.

And join our Slack Community! It is a great to place to get help and stay up to date with new releases.

Cube.js, the Open Source Dashboard Framework: Ultimate Guide was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Starting from version 0.4, the React Cube.js client comes with the <QueryBuilder /> component. It is designed to help developers build interactive analytics query builders. The <QueryBuilder /> abstracts state management and API calls to Cube.js Backend. It uses render prop and doesn’t render anything itself, but calls the render function instead. This way it gives maximum flexibility to building a custom-tailored UI with a minimal API.

The example below shows the <QueryBuilder /> component in action with Ant Design UI framework elements.

The above example is from Cube.js Playground. You can check its source code on Github.

This tutorial walks through building the much simpler version of the query builder. But it covers all the basics you need to build one of your own.

Setup a Demo Backend

If you already have Cube.js Backend up and running you can skip this step

First, let’s install Cube.js CLI and create a new application with a Postgres database.

$ npm install -g cubejs-cli
$ cubejs create -d postgres react-query-builder

We host a dump with sample data for tutorials. It is a simple “E-commerce database” with orders, products, product categories, and users tables.

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

Once you have data in your database, change the content of the .env file inside your Cube.js directory to the following. It sets the credentials to access the database, as well as a secret to generate auth tokens.

CUBEJS_DB_NAME=ecom
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=SECRET

Now that we have everything configured, the last step is to generate a Cube.js schema based on some of our tables and start the dev server.

$ cubejs generate -t line_items
$ yarn dev

If you open http://localhost:4000 in your browser you will access Cube.js Playground. It is a development environment, which generates the Cube.js schema, creates scaffolding for charts, and more. It has its own query builder, which lets you generate charts with different charting libraries.

Now, let’s move on to building our own query building.

Building a Query Builder

The <QueryBuilder /> component uses the render props technique. It acts as a data provider by managing the state and API layer, and calls render props to let developers implement their render logic.

Besides render, the only required prop is cubejsApi. It expects an instance of your cube.js API client returned by the cubejs method.

Here you can find a detailed reference of the <QueryBuilder /> component.

import cubejs from "@cubejs-client/core";
import { QueryBuilder } from "@cubejs-client/react";
const cubejsApi = cubejs("CUBEJS_TOKEN", { apiurl: "CUBEJS_BACKEND_URL" });
export default () => (
<QueryBuilder
cubejsApi={cubejsApi}
render={queryBuilder => {
// Render whatever you want based on the state of queryBuilder
}}
/>
);

The properties of queryBuilder can be split into categories based on what element they are referred to. To render and update measures, you need to use measures, availableMeasures, and updateMeasures.

measures is an array of already selected measures. It is usually empty in the beginning (unless you passed a default query prop). availableMeasures is an array of all measures loaded via API from your Cube.js data schema. Both measures and availableMeasures are arrays of objects with name, title, shortTitle, and type keys. name is used as an ID. title could be used as a human-readable name, and shortTitle is only the measure’s title without the Cube’s title.

// `measures` and `availableMeasures` are arrays with the following structure
[
{ name: "Orders.count", title: "Orders Count", shortTitle: "Count", type: "number" },
{ name: "Orders.number", title: "Orders Number", shortTitle: "Number", type: "number" }
]

updateMeasures is an object with three functions: add, remove, and update. It is used to control the state of the query builder related to measures.

Now, using these properties, we can render a UI to manage measures and render a simple line chart, which will dynamically change the content based on the state of the query builder.

import React from "react";
import ReactDOM from "react-dom";
import { Layout, Divider, Empty, Select } from "antd";
import { QueryBuilder } from "@cubejs-client/react";
import cubejs from "@cubejs-client/core";
import "antd/dist/antd.css";
import ChartRenderer from "./ChartRenderer";
const cubejsApi = cubejs(
"YOUR-CUBEJS-API-TOKEN",
{ apiUrl: "http://localhost:4000/cubejs-api/v1" }
);
const App = () => (
<QueryBuilder
query={{
timeDimensions: [
{
dimension: "LineItems.createdAt",
granularity: "month"
}
]
}}
cubejsApi={cubejsApi}
render={({ resultSet, measures, availableMeasures, updateMeasures }) => (
<Layout.Content style={{ padding: "20px" }}>
<Select
mode="multiple"
style={{ width: "100%" }}
placeholder="Please select"
onSelect={measure => updateMeasures.add(measure)}
onDeselect={measure => updateMeasures.remove(measure)}
>
{availableMeasures.map(measure => (
<Select.Option key={measure.name} value={measure}>
{measure.title}
</Select.Option>
))}
</Select>
<Divider />
{measures.length > 0 ? (
<ChartRenderer resultSet={resultSet} />
) : (
<Empty description="Select measure or dimension to get started" />
)}
</Layout.Content>
)}
/>
);
const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);

The code above is enough to render a simple query builder with a measure select. Here’s how it looks in the CodeSandbox:

Similar to measures, availableMeasures, and updateMeasures, there are properties to render and manage dimensions, segments, time, filters, and chart types. You can find the full list of properties in the documentation.

Also, it is worth checking the source code of a more complicated query builder from Cube.js Playground. You can find it on Github here.

React Query Builder with Cube.js was originally published in Stats and Bots on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview