Why integrating BigQuery was a challenge? How we solved it.

BigQuery requires authentication and authorization of the data submitted using the client libraries supported by BigQuery API, which are C#, GO, Java, Node.js, PHP, Python, and Ruby.  Engaging Networks used in the Acespace project allows us to edit HTML, CSS and  JavaScript only, hence BigQuery API integration was not possible there could be lots of security issues. Hence PHP based solution was found in which we first took the submission to AirTable then using an external PHP code AirTable was integrated into BigQuery. The PHP script will read from AirTable in a row-wise manner and the same row will be inserted into BigQuery while getting deleted from AirTable, this PHP script was scheduled to run every few hours using Cron job.

For this PHP implementation service account and API were created in Google Console

How To create a service account:

  1. Need to log in to the Google Cloud Console using your email id .https://console.cloud.google.com/
  2.  Select IAm & admin from the navigational panel provided.
  3. Next, select Service Accounts and click on Create Service Account.
  4. Create Service Account window pop opens where you can create your account.
  5.  Your service account J-SON will be downloaded to your computer, which contains the details of the service account created and the key generated will also be provided in the downloaded file. Keep the file safe as you will need this later.
  6. The newly created Service Account will be listed in the Service Account menu selected from the IAM & Admin navigation panel.
  7. Select the KEYS tab to add the key received through the J-SON download.

Thus the created service account can be used for the submission of data to BigQuery.