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
- Log into your Cloud BigQuery account and select the project that you will be using.
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.
#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.
- In the Google Cloud console and go to IAM & admin and select the Service accounts option.
- Click on Create Service Account.
- 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.
Click Create to create the service account and continue.
- On the Permissions screen that appears, give the account the permission BigQuery Data Owner so that it can access & create objects.
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.
- 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.
- Open the downloaded json file. You will need to copy the entire contents from this file when asked during integration setup.
#Create the Integration
Log into your account on the IoT in a Box portal.
Select the Integrations option and then select the Google BigQuery integration.
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
DatasetID should not contain the project name. For example, if the ID is
gws_allin the Dataset field.
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.
Note: You can view the records as columnar table data or switch to view them in JSON format.