Oresundsbron Sweden

Adding a chart to your Angular Application with Google BigQuery data

I recently worked on an Internet of Things (IoT) project on the Google Cloud Platform, one of the requirements was to create a data portal to show a few charts based on the data collected by the IoT devices. The IoT devices that were part of this project collected basic weather information on temperature and humidity.

Give some more information about the IoT setup, the IoT devices are connected to the Google IoT core that publishes the data to the Google Sub/Pub service, then a Google Firebase Function listens to a pub/sub event change. When the event change happens, the data received by the Google Firebase Function is pushed to a table created in Google BigQuery. The function also stores the IoT device name and date-time when the last data was collected to a table in the Firebase Realtime Database.

With the data stored in the Google BigQuery table, I could have used the Google Data Studio to create the charts for fun, as the use case was to have a portal and a mobile application that used the same Google Firebase backend.

I built a small web portal using Angular and hosted the portal with Google Firebase Hosting. I added another Google Firebase Function, which used the HTTP event to query BigQuery and serve the data to the portal page that shows a chart of the temperature data from Google query,

What I will show in this article is how we can code to display a chart in an Angular app, as shown in the chart below.

Let’s Get Started

First, I will give you some details on how I used Google Cloud, BigQuery and Google Firebase Functions to get the data for the chart.

Google BigQuery

In BigQuery, I created a table like a schema below – it’s a basic schema that is updated when new data measurement is created by the IoT device I used.

Google Firebase Function

To get the data from BigQuery to the chart we are going to create, and we need a Google Firebase Cloud function that queries the Google BigQuery table and returns the data to the Angular application we are going to create for our charts.

When publishing the Google Firebase Function, I do it as an HTTP request, and I can use an URL to invoke the function each time the data portal requests the data.

To use the code snippet below, you need the index,js, and package.json files to start. Here are an example of these files.

index.js file

const functions = require('firebase-functions');
const admin = require('firebase-admin');
const bigquery = require('@google-cloud/bigquery')();
const cors = require('cors')({ origin: true });

admin.initializeApp(functions.config().firebase);

const db = admin.database();

/**
 * Query bigquery with the last 7 days of data
 * HTTPS endpoint to be used by the webapp
 */
exports.getReportData = functions.https.onRequest((req, res) => {
  const projectId = process.env.GCLOUD_PROJECT;
  const datasetName = functions.config().bigquery.datasetname;
  const tableName = functions.config().bigquery.tablename;
  const table = `${projectId}.${datasetName}.${tableName}`;

  const query = `
    SELECT 
      TIMESTAMP_TRUNC(data.timestamp, HOUR, 'Europe/Amsterdam') data_hora,
      avg(data.temp) as avg_temp,
      avg(data.humidity) as avg_hum,
      min(data.temp) as min_temp,
      max(data.temp) as max_temp,
      min(data.humidity) as min_hum,
      max(data.humidity) as max_hum,
      count(*) as data_points      
    FROM \`${table}\` data
    WHERE data.timestamp between timestamp_sub(current_timestamp, INTERVAL 7 DAY) and current_timestamp()
    group by data_hora
    order by data_hora
  `;

  return bigquery
    .query({
      query: query,
      useLegacySql: false
    })
    .then(result => {
      const rows = result[0];

      cors(req, res, () => {
        res.json(rows);
      });
    });
});

, the package.json file

{
  "name": "functions",
  "description": "Cloud Functions for Firebase",
  "dependencies": {
    "@google-cloud/bigquery": "^0.9.6",
    "@google-cloud/firestore": "^0.8.2",
    "cors": "^2.8.4",
    "firebase-admin": "^5.11.0",
    "firebase-functions": "^2.0.1"
  },
  "private": true
}

To learn more about how to create and use the Google Firebase Functions. Here is a good resource – https://firebase.google.com/docs/database/extend-with-functions

And now to the details.

Let’s get into the nitty-gritty of creating the chart from the data. For creating the chart, I used the Chart.js library.

Prerequisites

I assume you already know this, but because we will be using Angular and the Angular CLI, we will need Node.js with the Node Package Manager (NPM). First, check with these commands what you have installed:

$ node -v
$ npm -v

If you do not get any result from the above commands not being recognized, visit Nodejs.org and download the appropriate installer based on your OS. Install it with the default options and reload your console.

Starting the Angular Project

Now, we’re going to use the Angular CLI to generate a new Angular project:

$ ng new charts && cd charts

Next, let’s install the Charts.js library and save it as a dev. dependency:

$ npm install chart.js --save

We will generate a service file, which is standard practice whenever you connect to an API to retrieve data. This way, you can easily access the API from any component:

$ ng generate service weather

Let’s run the serve command and visit http://localhost:4200 in your browser after this finishes running:

$ ng serve

Retrieving the Data

We could have just hardcoded some data to work with the chart, and I’m going to show how you will be charting data retrieved through a service, and I will specifically show how to get the data from a Google Cloud Function.

Open up the /src/app/weather.service.ts file and add the following imports to the top:

import { Injectable } from '@angular/core';
import { HttpClient, HttpHeaders } from '@angular/common/http';
import { map } from 'rxjs/operators';
@Injectable()
export class WeatherService {
   constructor(private _http: HttpClient) { }
   reportData() {
     return this._http.get<any[]>("<your google cloud function url>")
       .pipe(map((result: any) => result));
   }
}

We’re first creating an instance of the HttpClient through dependency injection, and then we’re creating a method called reportData() to return data from Google Firebase Function HTTP requests. More information on it here.

When you publish your function that I described earlier in this article, you will get an URL for that function, that URL you will use to replace <your google cloud function url> in the code snippet above.

Next, we have to import our WeatherService and the HttpModule. In your /src/app/app.module.ts add:

// Other imports removed for brevity

import { HttpClientModule } from '@angular/common/http';
import { WeatherService } from './weather.service';

@NgModule({
 ...
 imports: [
   BrowserModule,
   HttpClientModule             // Add this
 ],
 providers: [WeatherService],   // Add this
 ...
})

Next, open up the */src/app/app.component.ts file and import our WeatherService and Chart.js, then give ourselves access to it through DI in the constructor:

import { Component } from '@angular/core';
import { WeatherService } from './weather.service';
import { Chart } from 'chart.js';

@Component({
     selector: 'app-root',
     templateUrl: './app.component.html',
     styleUrls: ['./app.component.css']
})

export class AppComponent {
     chart = []; // This will hold our chart info
     
constructor(private weather: WeatherService) {}
   
ngOnInit() {
    this.weather.reportData()
    .subscribe(res => {
       console.log(res)
     })
 }
}

I also made the call here to the weather service reportData() method to console.log the data. Now you can check out in your browser by open up the console and you should see the returned data.

Structuring the Data

Remove the console.log(res) line and add the following:

let maxTempData = res.map(res => res.max_temp);
let avgTempData = res.map(res => res.avg_temp);
let minTempData = res.map(res => res.min_temp);

let maxHumData = res.map(res => res.max_hum);
let avgHumData = res.map(res => res.avg_hum);
let minHumData = res.map(res => res.min_hum);

let labels = res.map(res => res.data_hora.value)

this.buildLineChart(
         'tempLineChart',
         'Temperature in C°',
         labels,
         '#E64D3D',
         avgTempData
       );
       this.buildLineChart(
         'humLineChart',
         'Humidity in %',
         labels,
         '#0393FA',
         avgHumData
       );
     })
    }

For our chart to use the data correctly, we have to format it. So, assuming we want the temp_max, and temp_min, then we have to define variables for each. The .map operator allows us to access properties within the returned response directly.

Chart.js Code

Just beneath this.buildLineChart code, add the following:

    buildLineChart(el, label, labels, color, avgData) {
     const elNode = document.getElementById(el);
     new Chart(elNode, {
       type: 'line',
       data: {
         labels: labels,
         datasets: [
           {
             label: label,
             data: avgData,
             borderWidth: 1,
             fill: true,
             spanGaps: true,
             lineTension: 0.2,
             backgroundColor: color,
             borderColor: '#3A4250',
             pointRadius: 2
           }
         ]
       },
       options: {
         responsive: true,
         scales: {
           xAxes: [
             {
               type: 'time',
               distribution: 'series',
               ticks: {
                 source: 'labels'
               }
             }
           ],
           yAxes: [
             {
               scaleLabel: {
                 display: true,
                 labelString: label
               },
               ticks: {
                 stepSize: 0.5
               }
             }
           ]
         }
       }
     })
   }  

There are many types of charts that Chart.js offers, along with many options and properties for configuration. I’m just showing how to make a Line Chart, and if you need another type of chart, you can check out Chart.js documentation to learn more.

We’re defining a line chart, and you can see for the labels property, we’re specifying the date, and then we have 2 datasets, each showing the temp_min and temp_max arrays.

The HTML Template

Open up /src/app/app.component.html and remove everything there, and replace it with

<main class="mdc-toolbar-fixed-adjust">
   <div class="mdc-layout-grid">
     <div class="mdc-layout-grid__inner">
       <div class="mdc-layout-grid__cell mdc-layout-grid__cell--span-12">
         <div class="mdc-card">
           <section class="mdc-card__primary">
             <h1 class="mdc-card__title mdc-card__title--large">Current data collected</h1>
           </section>
           <ul id="devices" class="mdc-list mdc-list--two-line mdc-list--avatar-list two-line-avatar-text-icon-demo">
             <div role="progressbar" class="mdc-linear-progress mdc-linear-progress--indeterminate">
               <div class="mdc-linear-progress__buffering-dots"></div>
               <div class="mdc-linear-progress__buffer"></div>
               <div class="mdc-linear-progress__bar mdc-linear-progress__primary-bar">
                 <span class="mdc-linear-progress__bar-inner"></span>
               </div>
               <div class="mdc-linear-progress__bar mdc-linear-progress__secondary-bar">
                 <span class="mdc-linear-progress__bar-inner"></span>
               </div>
             </div>
           </ul>
         </div>
       </div>
       <div class="mdc-layout-grid__cell mdc-layout-grid__cell--span-6">
         <div class="mdc-card">
           <section class="mdc-card__primary">
             <h1 class="mdc-card__title mdc-card__title--large">Temperature - Last 7 days</h1>
           </section>
           <canvas id="tempLineChart"></canvas>
           <div role="progressbar" id="tempLineChart_progress" class="mdc-linear-progress mdc-linear-progress--indeterminate">
             <div class="mdc-linear-progress__buffering-dots"></div>
             <div class="mdc-linear-progress__buffer"></div>
             <div class="mdc-linear-progress__bar mdc-linear-progress__primary-bar">
               <span class="mdc-linear-progress__bar-inner"></span>
             </div>
             <div class="mdc-linear-progress__bar mdc-linear-progress__secondary-bar">
               <span class="mdc-linear-progress__bar-inner"></span>
             </div>
           </div>
         </div>
       </div>
       <div class="mdc-layout-grid__cell mdc-layout-grid__cell--span-6">
         <div class="mdc-card">
           <section class="mdc-card__primary">
             <h1 class="mdc-card__title mdc-card__title--large">Humidity - Last 7 days</h1>
           </section>
           <canvas id="humLineChart"></canvas>
           <div role="progressbar" id="humLineChart_progress" class="mdc-linear-progress mdc-linear-progress--indeterminate">
             <div class="mdc-linear-progress__buffering-dots"></div>
             <div class="mdc-linear-progress__buffer"></div>
             <div class="mdc-linear-progress__bar mdc-linear-progress__primary-bar">
               <span class="mdc-linear-progress__bar-inner"></span>
             </div>
             <div class="mdc-linear-progress__bar mdc-linear-progress__secondary-bar">
               <span class="mdc-linear-progress__bar-inner"></span>
             </div>
           </div>
         </div>
       </div>
     </div>
   </div>
 </main>

<div *ngIf="chart">
 <canvas id="canvas">{{ chart }}</canvas>
</div>

For styling, you can add Angular material.

ng add @angular/material

Now try it!

You should get two line charts showing the temperature and the humidity.

Conclusion

I hope this has helped you and you have learned something from this example using Angular and Google Cloud Services. Below you can comment and let me know how this article helped you out.


by

Comments

6 responses to “Adding a chart to your Angular Application with Google BigQuery data”

  1. sagar Avatar
    sagar

    we need your help for following scenario in data studio
    we have a clinical provider dashboard created in data studio
    from our web portal we want to show the dashboard of specific provider that has login through our portal which is in angular.
    we are not able to get how to pass the provider identification to data studio url so that we can show the information for that specific provider, any help is highly appreaciable

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      You would need to provide the provider identification in the data connection that you setup in Data Studio, then you can use CASE statement that lets you create new fields that use conditional logic to determine the field values. CASE is most often used to create new categories or groupings of data. here are some examples of how you can use CASE:

      Creating Social Source
      CASE WHEN Source IN ( “facebook” , “m.facebook.com” , “l.facebook.com” , “facebook.com” , “lm.facebook.com” ) THEN “Facebook”
      WHEN Source IN ( “l.instagram.com” , “instagram.com” , “instagram” ) THEN “Instagram”
      WHEN Source IN ( “t.co” , “twitter.com” , “twitter” ) THEN “Twitter”
      when Source IN ( “pinterest.com” , “pinterest” , “pinterest.ca” , “pinterest.co.uk” , “pinterest.fr” , “pinterest.jp” , “b.pinterest.com” , “pl.pinterest.com” , “pinterest.com.mx” , “pinterest.cl” , “id.pinterest.com” , “ru.pinterest.com” , “br.pinterest.com” , “pinterest.com.au” ) THEN “Pinterest”
      WHEN Source IN (“youtube.com”) THEN “Youtube”
      ELSE “Other Source”
      END

      Creating ‘Social Medium’
      CASE WHEN Medium IN (“social-ad”) THEN “Paid Social”
      WHEN Medium IN (“social”) THEN “Organic Social”
      ELSE “Social Referral”
      END

      Creating ‘Engagement’
      CASE WHEN Page Depth in (“1″,”2″,”3”) THEN “Less than 4”
      WHEN Page Depth in (“7″,”4”) THEN “4-7”
      WHEN Page Depth in (“11”) THEN “10 or more”
      ELSE “Other”
      END

      Creating ‘Channel Type’
      CASE WHEN REGEXP_MATCH(medium,”^(cpc|ppc|cpm|paid)$”) THEN “Paid”
      WHEN REGEXP_MATCH(medium,”organic” THEN “Organic”
      ELSE “Other Channels”
      END

      Creating ‘Page Groups’
      CASE WHEN REGEXP_MATCH(Page, “((?i).*^/$|^/\\?.*).*”) THEN “Homepage”
      WHEN REGEXP_MATCH(Page, “((?i).*.*/about.*).*”) THEN “About Us”
      WHEN REGEXP_MATCH(Page, “((?i).*.*/contact$).*”) THEN “Contact Us”
      WHEN REGEXP_MATCH(Page, “((?i).*^/services$).*”) THEN “Services”
      WHEN REGEXP_MATCH(Page, “((?i).*.*/blog/.*).*”) THEN “Blog Posts”
      WHEN REGEXP_MATCH(Page, “((?i).*^/products/.*).*”) THEN “Product Pages”
      ELSE “_Other”
      END

      You can read more about on Google Help Pages

      I hope this helps you?

  2. Avi Avatar
    Avi

    This is very helpful. Thank you. I need to query a private dataset. Can you tell me how to use a service account or private key?

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      Can you elaborate on what your are trying to do?

  3. Avi Avatar
    Avi

    The bigquery dataset I am trying to query is in a different project than the cloud functions project. When I try to query the dataset the cloud functions “user” gets a permission denied error. Anyways… after I asked the question I found a way to solve it by creating a service account for cloud functions and providing that account permissions to query the bigquery dataset. Is there another better way of doing that?

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      I don’t have a better way then the one you discovered, service account is required to be able to authenticate between GCP projects.

Leave a Reply

Your email address will not be published. Required fields are marked *