Skip to main content

BigQuery Setup and link with Glific

Before you get started

Many NGOs are concerned about the cost of setting up BigQuery. They are worried about running up a bill. In reality BigQuery provides a very generous free tier that you will find will suffice for several months. You need not worry till your storage exceeds 10GB and queries 1TB / month !

Google also sends notifications before billing you and provides you mechanisms like quotas to help control price. Learn more about these here :

View your billing reports and cost trends.

https://cloud.google.com/bigquery/docs/estimate-costs

https://cloud.google.com/bigquery/docs/custom-quotas

Now that we have that out of the way, let's help you set up your account !

Creating your BigQuery Account

The following steps needs to follow for creating your BigQuery account.

1 . Go to https://console.cloud.google.com and sign in with your Gsuite account.

image

2 . After signing in you will be redirected to Google Cloud console. Use link if not redirected: https://console.cloud.google.com/getting-started

3 . Click on TRY FOR FREE and fill in Billing details.

1 TryForFree

4 . Select Country and check to agree to terms and conditions.

2 Country

5 . Fill in the rest of the details to verify.

3 Card1

6 . On the same page, fill in Primary contact details and payment method details and click on the start my free trial

4 Card2

7 . If it does not redirect you go to the google cloud console dashboard you can also use this link. https://console.cloud.google.com/home

8 . Click My First Project in the dropdown in top Bar to create a new project.

5 My First Project

9 . Click on New Project at top right corner.

6 New Project

10 . Fill in your project details and click on the CREATE button.

7 Create

11 . Select Bigquery from the side panel of Google Cloud Console Dashboard or use link to reach Bigquery Dashboard,

8 BigQuery
  1. Select the project.9 Select a project

13 . Now Go to https://console.cloud.google.com/apis/credentials/serviceaccountkey to generate project API keys

14 . From the drop select your project. Next, select a new service account and choose Role as Project -> Owner. And Click on the Create Serive Account button below.

10 Create a Serive account

  1. Enter serivce account details and click on 'Done'

    11 Serive Account details and done

  2. Create serive Key. CLick on Manage Keys from 3 dot dropdown.

    12 Serive Key

  3. Click on 'Add Key'

13 Create new Key

18 . Save the JSON file downloaded and copy its content.

14 Json Create and download

19 . To integrate BigQuery in Glific. Click on the settings button(bottom left corner) in Glific homepage. image

20 . Click on edit button in BigQuery tab. image

21 . Paste the copied JSON content inside Goth Credentials input

image

22 . Click on is active checkmark and click on the SAVE button. Then send a message to the bot. BigQuery sync starts when a message is sent from or to the bot after adding credentials.

23 . Go to BigQuery console or use link https://console.cloud.google.com/bigquery

24 . You can see a new Dataset with two tables is automatically created. Contacts and messages will be populating automatically.

image

You can refer to this small video to know how to setup BigQuery for your organisation

Big Query Schema - https://github.com/glific/glific/blob/master/lib/glific/third_party/bigquery/bigquery_schema.ex