Why integrating BigQuery was a challenge? 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. Thus BigQuery API integration was not possible, and there could be many security issues. Hence, we found a PHP-based solution in which we first submitted the data to AirTable and then integrated AirTable into BigQuery using external PHP code. The PHP script reads from AirTable in a row-wise manner. Then insert the same row into BigQuery, and delete it from AirTable. Furthermore, we scheduled this PHP script to run every few hours using a Cron job.
For this PHP implementation service, an account and API were created in Google Console.
How to Create a Service Account:
- Need to log in to the Google Cloud Console using your email id .https://console.cloud.google.com/
- Select IAm & admin from the navigational panel provided.
- Next, select Service Accounts and click on Create Service Account.
- Create Service Account window pop opens where you can create your account.
- 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.
- The newly created Service Account will be listed in the Service Account menu selected from the IAM & Admin navigation panel.
- 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.
