Skip to main content

Google BigQuery

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.

alt text 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. Only copy the text after the dot is needed.

BigQuery DatasetID

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.

alt text

  1. Click on Create Service Account.

alt text

  1. 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.

alt text

alt text Click Create to create the service account and continue.

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

alt text

alt text Click Continue and then Done to complete creating the service account. 5. Locate the Service account you created, click on the 3-dot menu and select Create Key.

alt text

  1. 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.

alt text

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

alt text

Create the Integration​

  1. Log into your account on the IoT in a Box portal.

  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: Enter the Dataset ID

      note

      DatasetID should not contain the project name. For example, if the ID is operations-352019.gws_all, use gws_all in the Dataset field.

    • Table ID: Enter in an appropriate table ID to be used to store your data in this Dataset.

      info

      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.

      alt text Paste the entire contents of the json private key file here.

alt text

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

alt text

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.

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

alt text