Create new Google BigQuery Integration

Owned by Brent Meriwether (Unlicensed)
Sept 17, 2021
4 min read

Connect with Google's Cloud platform to have your IoT device and alert event data sent to a BigQuery table.

Connecting to Google BigQuery

Use the following steps to setup the Google BigQuery Integration.

Create your BigQuery dataset

  1. Log into your Cloud BigQuery account and select the project that you will be using.

  2. If needed, setup BigQuery for your project and make sure you have a dataset by clicking on the CREATE DATASET button

You do not need to create the table - the Integration will do this later when setting that up. For now, just locate the Dataset ID as you will need this later.

Setup your Cloud Storage Account Service key

In this step we will setup an account key which will then be used by the Integration so that it can access your dataset to create the table and access data.

  1. In the Google Cloud console and go to IAM & admin and select the Service accounts option.

  2. Click on Create Service Account.

  3. Give the account an appropriate Name and description. The Service account ID will be generated automatically for you during this step - you can leave it as the default.

  4. Click Create to create the service account and continue.

  5. On the Permissions screen that appears, give the account the permission BigQuery Data Owner so that it can access & create objects.

  6. Click Continue and then Done to complete creating the service account.

  7. Locate the Service account you created, click on the 3-dot menu and select Create Key.

  8. In the popup that appears, you can leave it as JSON and click Create. This will download a json file containing the private key information that we need when setting up the integration.

  9. Open the downloaded json file. You will need to copy the entire contents from this file when asked during integration setup.

Create the Integration

  1. Log into your IoT dashboard account where all of your devices are located.

  2. Select the Integrations option and then select the Google BigQuery integration.

  3. Enter the following information to complete the integration:

    • Name: Enter a name for this integration. In case you have multiple integrations, this will help uniquely identify it in the list.

    • Dataset ID: Paste the Table ID string used to locate your table in BigQuery.

    • Table ID: Enter in an appropriate table ID to be used to store your data in this Dataset.
      You do not need to create this table. The Integration will automatically create the appropriate table structure once the integration setup is complete.

    • Service Account Key: Paste the entire contents of the service key file, as obtained above.
      Paste the entire contents of the json private key file here.

After saving the Integration, you will see it shown in your list of integrations.

  • You can then edit the integration to make any changes needed.

  • You can also toggle the Integration on/off at any time from here as well. Disabling the integration will stop new data from being sent to the BigQuery table.

Verifying things are working

You can verify the integration is working by logging into the Google Cloud Console and viewing the records of your table.

You can view the records as columnar table data or switch to view them in JSON format.