Send sensor data to Google BigQuery

In this tutorial we'll show you how to use your microcontroller to send data to Google BigQuery. This will enable you to store and query large amounts of sensor data extremely efficiently.

This code uses our Google > BigQuery > InsertAll Choreo.

Get Set Up

1Make sure you have a Temboo account. If you don't already have one, you can register for a free account here.

2You'll need a free Texas Instruments account to use their online IDE, CCS Cloud. You can sign up here.

3If you haven't already done so, prepare your board for making secure connections by following our Setting up CC3220 for SSL tutorial.

4Since this code uses Google BigQuery, you'll need a BigQuery account and you'll need to go through the OAuth process so that you can programmatically send sensor data to your BigQuery account. Head over to our BigQuery Choreo instructions and get set up. Here's one helpful tip: when you get to the BigQuery OAuth process, it's sufficient to specify the following scope as an input to the InitializeOAuth Choreo:

https://www.googleapis.com/auth/bigquery

5As you're going through the BigQuery setup process, make sure to save the ProjectID, ClientID, ClientSecret, and RefreshToken that you generate while setting up your BigQuery app and running through the OAuth process - you'll need them while generating code in the next section.

Create Your Dataset & Table

6Now, log into the BigQuery console and create a new dataset called TembooSensorReadings.

BigQuery create dataset

7Next, create a table within your dataset called SensorReadings. Here's the details for your table:

In the source data section, set the file format to CSV
In your favorite text editor, create a .csv file containing the string 55,66 and save it as temboo_test.csv. Upload that file as your source data - it's just a simple example of two integer sensor readings so that your table knows what to expect.
Within the destination table section, call your table SensorReadings
For the schema, create one entry called "Temperature" and another called "Humidity". Set the type of both as Integer.

Here's a screenshot showing you how everything should look.

BigQuery create table

Generate your BigQuery Code

8Now, go to the BigQuery > TableData > InsertAll Choreo. Make sure that IoT Mode is turned on and that you've selected the microcontoller device that you're generating code for.

9Next, fill out all the required inputs. You'll need to use the project ID for the BigQuery app you set up earlier, along with its ClientID and ClientSecret. You'll also need your OAuth refresh token, the name of your dataset (TembooSensorReadings) for the DatasetID, and your sensor data table name (SensorReadings) for the TableID. Finally, add some JSON describing the sensor data that you want to send to BigQuery. Here's some you can use that matches the schema that we set up earlier:

[
  {
    "json": {
      "Temperature": 30,
      "Humidity": 35
    }
  }
]

BigQuery Choreo inputs

10Once you've got all of your inputs filled in, you can run the Choreo and add a row of data to BigQuery. This will prove that everything is working correctly and we can move on to trying out our generated code. To check that your data is adding to BigQuery successfully, you can compose a SELECT * ... query in the BigQuery query composer and you should see the two rows of data the we've added so far.

BigQuery results

Run The Code

11Scroll down to the code box. Click the "Send to CCS Cloud" button to the right of your generated code. Name your application and click Send. Click the link that appears to visit the CCS Cloud IDE in your browser.

Sending code from Temboo to CCS Cloud

12Plug your CC3220 board into your computer via USB. Select its USB connection in CCS Cloud via the Target > Connect COM port... menu.

13Now run the code on your CC3220 from CCS Cloud.

Running your code on your CC3220 board from CCS Cloud

Extending The Code

You'll notice that your code always sends the same value to BigQuery - the values we were testing with. Of course, it's far more interesting to send dynamic values i.e., sensor values.

14To insert a sensor reading into a Choreo input dynamically, click the blue button inside a Choreo input field and select the sensor whose reading you wish to use. Take care when substituting dynamic variables into the JSON code that you're sending.

Inserting a sensor reading into a Choreo input

15Once you've generated your extended code, you can run it and you'll be sending live sensor values to BigQuery from your CC3220. Head back over to your BigQuery console and run the SELECT * ... query again to see the live sensor data.

What's Next?

Now that you've mastered working with BigQuery, why not check out the rest of the 2000+ Choreos in our Library and get inspired for your next project.

Need Help?

We're always happy to help. Just email us at support@temboo.com, and we'll answer your questions.

Back

Hardware

platforms

connectivity