How to integrate Dialogflow with BigQuery

1. Introduction

In this article we will learn how Dialogflow connects with BigQuery and stores information collected during the conversational experience. We will use the same Agent that we created in previous labs " Appointment Scheduler". In the Agent's GCP project we will create a dataset and a table in BigQuery. Then we will edit the original fulfillment with the BigQuery dataset and table IDs. Finally we will test to see if the interactions are getting recorded in BigQuery.

Here is the sequence diagram of the events from user to the fulfillment and BigQuery.

What you'll learn

• How to create a dataset and table in BigQuery
• How to set up BigQuery connection details in Dialogflow fulfillment.
• How to test fulfillment

Prerequisites

• Basic concepts and constructs of Dialogflow. For introductory Dialogflow tutorial videos that covers basic conversational design, check out the following videos:

• Build an Appointment Scheduler Chatbot using Dialogflow.

• Understanding Entities in Dialogflow.

• Fulfillment: Integrate Dialogflow with Google Calendar.

2. Create Dataset and Table in BigQuery

1. Navigate to the Google Cloud Console
2. In the Cloud console, go to the menu icon ☰ > Big Data > BigQuery
3. Under Resources on the left pane, click on the project ID, once selected, you will see CREATE DATASET on the right
4. Click on CREATE DATASET and name it.

5. Once the dataset is created, click on it from the left panel. You will see CREATE TABLE on the right.
6. Click on CREATE TABLE, provide Table name and click Create table on the bottom of the screen.

7. Once the table is created, click on the table from the left panel. You will see "Edit Schema" button on the right side.
8. Click on Edit Schema button and click on Add Field button. Add "date" field and repeat the same for "time" and "type".
9. Take note of the "DatasetID" and "tableID"

3. Add BigQuery connection details to Dialogflow Fulfillment

1. Open Dialogflow Agent and enable Fulfillment inline editor. Refer to the previous lab if you need help with this .

Refer to the previous codelab Understanding Fulfillment by integrating Dialogflow with Google Calendar if you need help with enabling fulfillment.

2. Make sure the "package.json" in the Dialogflow fulfillment inline editor contains a BigQuery dependency. "@google-cloud/bigquery": "0.12.0". Make sure you use the latest version of BigQuery at the time you are following this article.
3. In index.js create "addToBigQuery" function to add the date, time and appointment type in the BigQuery table.
4. Add the projectID, datasetID and tableID in the TODO section of the index.js file to properly connect your BigQuery table and the dataset to your fulfillment.

package.json

{
  "name": "dialogflowFirebaseFulfillment",
  "description": "Dialogflow fulfillment for the bike shop sample",
  "version": "0.0.1",
  "private": true,
  "license": "Apache Version 2.0",
  "author": "Google Inc.",
  "engines": {
    "node": "6"
  },
  "scripts": {
    "lint": "semistandard --fix \"**/*.js\"",
    "start": "firebase deploy --only functions",
    "deploy": "firebase deploy --only functions"
  },
  "dependencies": {
    "firebase-functions": "2.0.2",
    "firebase-admin": "^5.13.1",
    "actions-on-google": "2.2.0", 
    "googleapis": "^27.0.0",
    "dialogflow-fulfillment": "0.5.0",
    "@google-cloud/bigquery": "^0.12.0"
  }
}

Index.js

'use strict';

const functions = require('firebase-functions');
const {google} = require('googleapis');
const {WebhookClient} = require('dialogflow-fulfillment');
const BIGQUERY = require('@google-cloud/bigquery');


// Enter your calendar ID below and service account JSON below
const calendarId = "[e-mail]";
const serviceAccount = {}; // Starts with {"type": "service_account",...

// Set up Google Calendar Service account credentials
const serviceAccountAuth = new google.auth.JWT({
  email: serviceAccount.client_email,
  key: serviceAccount.private_key,
  scopes: 'https://www.googleapis.com/auth/calendar'
});

const calendar = google.calendar('v3');
process.env.DEBUG = 'dialogflow:*'; // enables lib debugging statements

const timeZone = 'America/Los_Angeles';
const timeZoneOffset = '-07:00';

exports.dialogflowFirebaseFulfillment = functions.https.onRequest((request, response) => {
  const agent = new WebhookClient({ request, response });
  console.log("Parameters", agent.parameters);
  const appointment_type = agent.parameters.AppointmentType;

// Function to create appointment in calendar  
function makeAppointment (agent) {
    // Calculate appointment start and end datetimes (end = +1hr from start)
    const dateTimeStart = new Date(Date.parse(agent.parameters.date.split('T')[0] + 'T' + agent.parameters.time.split('T')[1].split('-')[0] + timeZoneOffset));
    const dateTimeEnd = new Date(new Date(dateTimeStart).setHours(dateTimeStart.getHours() + 1));
    const appointmentTimeString = dateTimeStart.toLocaleString(
      'en-US',
      { month: 'long', day: 'numeric', hour: 'numeric', timeZone: timeZone }
    );
  
// Check the availability of the time, and make an appointment if there is time on the calendar
    return createCalendarEvent(dateTimeStart, dateTimeEnd, appointment_type).then(() => {
      agent.add(`Ok, let me see if we can fit you in. ${appointmentTimeString} is fine!.`);

// Insert data into a table
      addToBigQuery(agent, appointment_type);
    }).catch(() => {
      agent.add(`I'm sorry, there are no slots available for ${appointmentTimeString}.`);
    });
  }

  let intentMap = new Map();
  intentMap.set('Schedule Appointment', makeAppointment);
  agent.handleRequest(intentMap);
});

//Add data to BigQuery
function addToBigQuery(agent, appointment_type) {
    const date_bq = agent.parameters.date.split('T')[0];
    const time_bq = agent.parameters.time.split('T')[1].split('-')[0];
    /**
    * TODO(developer): Uncomment the following lines before running the sample.
    */
    //const projectId = '<INSERT your own project ID here>'; 
    //const datasetId = "<INSERT your own dataset name here>";
    //const tableId = "<INSERT your own table name here>";
    const bigquery = new BIGQUERY({
      projectId: projectId
    });
   const rows = [{date: date_bq, time: time_bq, type: appointment_type}];
  
   bigquery
  .dataset(datasetId)
  .table(tableId)
  .insert(rows)
  .then(() => {
    console.log(`Inserted ${rows.length} rows`);
  })
  .catch(err => {
    if (err && err.name === 'PartialFailureError') {
      if (err.errors && err.errors.length > 0) {
        console.log('Insert errors:');
        err.errors.forEach(err => console.error(err));
      }
    } else {
      console.error('ERROR:', err);
    }
  });
  agent.add(`Added ${date_bq} and ${time_bq} into the table`);
}

// Function to create appointment in google calendar  
function createCalendarEvent (dateTimeStart, dateTimeEnd, appointment_type) {
  return new Promise((resolve, reject) => {
    calendar.events.list({
      auth: serviceAccountAuth, // List events for time period
      calendarId: calendarId,
      timeMin: dateTimeStart.toISOString(),
      timeMax: dateTimeEnd.toISOString()
    }, (err, calendarResponse) => {
      // Check if there is a event already on the Calendar
      if (err || calendarResponse.data.items.length > 0) {
        reject(err || new Error('Requested time conflicts with another appointment'));
      } else {
        // Create event for the requested time period
        calendar.events.insert({ auth: serviceAccountAuth,
          calendarId: calendarId,
          resource: {summary: appointment_type +' Appointment', description: appointment_type,
            start: {dateTime: dateTimeStart},
            end: {dateTime: dateTimeEnd}}
        }, (err, event) => {
          err ? reject(err) : resolve(event);
        }
        );
      }
    });
  });
}

Understand the sequence of events from the code

1. The intent map calls the "makeAppointment" function to schedule an appointment on Google Calendar
2. Within the same function a call is made to "addToBigQuery" function to send the data to be logged into BigQuery.

4. Test Your Chatbot and the BigQuery Table!

Let's test our chatbot, you can test it in the simulator or use the web or google home integration we have learnt in previous articles.

• User: "Set an appointment for vehicle registration at 2pm tomorrow"
• Chatbot response: "Ok, let me see if we can fit you in. August 6, 2 PM is fine!."

• Check the BigQuery table after the response. Use query "SELECT * FROM projectID.datasetID.tableID"

5. Cleanup

If you are planning on doing the other labs in this series, don't do the cleanup now, do it after you are done with all the labs in the series.

Delete the Dialogflow Agent

• Click on the gear icon next to your existing agent

• In the General tab scroll down to the bottom and click Delete this Agent.
• Type DELETE into the window that appears and click Delete.

6. Congratulations!

You created a chatbot and integrated it with BigQuery to gain insights. You're now a chatbot developer!

Check out these other resources:

Next Steps:

Checkout the following codelabs in this series to learn more about building a chatbot with Dialogflow and build integrations

• Create a custom Dialogflow front-end with Django
• Integrate Google Vision API with Dialogflow

• Check out the code samples at the Dialogflow Github page.