NodeJS bigquery.js

bigquery.js

////////////////////////////////////////////////////////////////////
//
// This javascript code queries a Google Cloud BigQuery database
//
// Prerequisites:
// 1. Setup a service account (Bigquery.admin) by following the below link
//    https://cloud.google.com/video-intelligence/docs/common/auth#set_up_a_service_account
// 2. create a folder adminkey on the path of the javascript and save the service account json file
// 3. Execution in NodeJS
//    npm install --save @google-cloud/bigquery
//
// To run this application:
// 1. Update the configurable parameters
// 2. Run FT90x for 1 minute to send data to google cloud to BigQuery
// 3. Run this application "node bigquery.js"
//
////////////////////////////////////////////////////////////////////
const BigQuery = require('@google-cloud/bigquery');
const credential = require('./adminkey/<serviceaccountfile>.json');
const bigquery = new BigQuery({
  projectId: "<projectid>",
  credentials: credential
});
const projectId = "<projectid>";
const dataSetId = "<dataset>";
const tableId   = "<tablename>";
const deviceId = "ft900_2"
const bigQueryId = projectId + "." + dataSetId + "." + tableId;
const maxItems  = 60;

var datumVal = new Date() - 60000; //timestamp that is 1 minutes before the current date and time

var sqlStatement = "SELECT * FROM `" + bigQueryId 
        + "` WHERE deviceId='"+ deviceId 
        + "' AND timestamp >= " + datumVal.toString(); 
        + " LIMIT " + maxItems;

async function handleError(error) 
{
    console.log("\nAn error with code '" + error.code + "' has occurred:");
    console.log("\t" + error.body || error);
}

syncQuery = function(sqlQuery) {
    // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
    const options = {
      query: sqlQuery,
      timeoutMs: 10000, // Time out after 10 seconds.
      useLegacySql: false, // Use standard SQL syntax for queries.
    };
  
    // Runs the query
    return bigquery
      .query(options)
      .then(results => {
        const rows = results[0];
        //console.log(rows);
        updateData(rows);
        return rows;
      })
      .catch(err => {
        console.error('ERROR:', err);
      });
    // [END bigquery_sync_query]
  
  }
  
asyncQuery = function (sqlQuery) {
    // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
    const options = {
      query: sqlQuery,
      useLegacySql: false, // Use standard SQL syntax for queries.
    };
  
    let job;
  
    // Runs the query as a job
    return bigquery
      .createQueryJob(options)
      .then(results => {
        job = results[0];
        console.log(`Job ${job.id} started.`);
        return job.promise();
      })
      .then(() => {
        // Get the job's status
        return job.getMetadata();
      })
      .then(metadata => {
        // Check the job's status for errors
        const errors = metadata[0].status.errors;
        if (errors && errors.length > 0) {
          throw errors;
        }
      })
      .then(() => {
        console.log(`Job ${job.id} completed.`);
        return job.getQueryResults();
      })
      .then(results => {
        const rows = results[0];
        // console.log(rows); 
        updateData(rows);
        return rows;
      })
      .catch(err => {
        console.error('ERROR:', err);
      });
  }

function updateData(rows)
{
      // placeholders for the data arrays
      var temperatureValues = [];
      var humidityValues = [];
      var luxValues = [];
      var labelValues = [];
      // placeholders for the data read
      var temperatureRead = 0.0;
      var humidityRead = 0.0;
      var luxRead = 0.0;
      var timeRead = "";
      for (var i = 0; i < rows.length; i++) 
      {
        // read the values from the dynamodb JSON packet
        temperatureRead = parseFloat(rows[i].temperature);
        humidityRead = parseFloat(rows[i].humidity);
        luxRead = parseInt(rows[i].lux);
        timeRead = new Date(parseInt(rows[i].timestamp));
        // append the read data to the data arrays
        temperatureValues.push(temperatureRead);
        humidityValues.push(humidityRead);
        luxValues.push(luxRead);
        labelValues.push(timeRead);
        if (temperatureValues.length > 24) {
            temperatureValues.shift();
            humidityValues.shift();
            luxValues.shift();
            labelValues.shift();
        }
      }
      // set the chart object data and label arrays
      console.log(temperatureValues);
      console.log(humidityValues);
      console.log(luxValues);
      console.log(labelValues);
}
  
/* Makes a scan of the BigQuery table to set a data object for the chart */
function getData1() {
	  //syncQuery(sqlStatement).catch(handleError);
	  asyncQuery(sqlStatement).catch(handleError);
}

function myReadyFunction() 
{
  getData1();
  setInterval(getData1, 1000); // 1 second feed
}

myReadyFunction();