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

2If this is the first time using Temboo, you'll need to set up your ARTIK board to find the Temboo libraries. To do so, you'll need to create the file temboo.conf in /etc/ld.so.conf.d. Inside of temboo.conf, you'll want to copy the line below and paste it into the file:

/opt/iothub/artik/temboo/temboo_artik_library/lib

After saving temboo.conf, run ldconfig from the command line to add the Temboo library directory to the system library search path.

You’ll also need to get the IP address of your ARTIK board using ifconfig. The example below shows the results when the ARTIK board is connected to the internet through an ethernet cable. The IP address you'll want to use is listed next to inet.

[root@localhost ~]$ ifconfig
eth0: flags=4163  mtu 1500
        inet 10.11.6.200  netmask 255.255.255.0  broadcast 10.11.6.255

3Since this sketch 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

4As 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

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

BigQuery create dataset

6Next, 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

7Now, 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.

8Next, 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

9Once 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

10At this point, Temboo has auto-generated the code that you need to send sensor values to BigQuery, so the next step is to grab the code and try it out. Scroll down to the CODE section then download the auto-generated C code.

11The auto-generated code references the TembooAccount.h header file, which contains your Temboo account information and network interface details. You'll find the code for this file beneath your generated sketch. Make sure that you keep this file alongside your main C code on your ARTIK device.

12Copy the downloaded zip file to your ARTIK board using scp. From your computer, go to the directory where the zip file is located and type the command below. Make sure you use the IP address for your board you found using ifconfig.

[root@localhost ~]$ sip [yourtemboozipfile].zip root@xxx.xxx.xxx.xxx:/home

13Now go to the directory containing the code and compile your code and start sending data to BigQuery from your ARTIK. Run the first command below to compile your code, making sure to substitute in the name of the c file that you're compiling, and the name of the file your are outputting. After compiling has finished and no errors have been reported, run your program using the second command, subbing in the name of the file that you want to run. Your data will start uploading to BigQuery from your ARTIK.

[root@localhost ~]$ gcc -L/opt/iothub/artik/temboo/temboo_artik_library/lib -ltemboo -I/opt/iothub/artik/temboo/temboo_artik_library/include -DUSE_SSL [yourtemboocode].c -o [filename]
[root@localhost ~]$ ./[filename]

Note: If you get an error about cdefs.h not being found, this could mean your ARTIK board was not shipped with glibc-headers. This can be solved by reinstalling glibc-headers using the command below. After reinstalling glibc-headers, run the two commands above to compile and run your code.

[root@localhost ~]$ yum reinstall glibc-headers

Extending The Sketch

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

14We outline how to extend any Temboo sketch so that it's sending dynamic sensor data in the "Extending the sketch" section of our Google Spreadsheets tutorial, so take a look at that one to see how you can extend your sketch to send real sensor values to BigQuery. Take care when substituting dynamic variables into the JSON string that you're sending to BigQuery.

15Once you've extended your sketch, you can run it again and you'll be sending live sensor values to BigQuery from your ARTIK. Run your SELECT * ... query again to see the live sensor values in your BigQuery datastore.

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