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.

We're using an Arduino Yún for this tutorial. If you're not using a Yún, we have the same tutorial for the following embedded devices:

Get Set Up

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

2Make sure that you have the latest version of the Arduino IDE. You should also be sure that you have the newest version of the Temboo Library by checking the Arduino Library Manager.

3Make sure that your Yún is connected to the Internet. Arduino has a helpful guide if you need assistance.

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

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.

Creating a new BigQuery 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.

Creating a new BigQuery table with the correct settings for this tutorial

Generate your BigQuery Code

8Now, go to the BigQuery > TableData > InsertAll Choreo. Select Arduino from the drop down menu at the top of the Choreo page, then specify whether you're working with the Yún or another Arduino board paired with the Yún Shield.

Selecting your Arduino configuration

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 filled in

10Once you've got all of your inputs filled in, you can click Generate Code to test the Choreo from our site. This will add a row of data to BigQuery and prove that everything is working correctly. Next 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.

Our new data showing up in BigQuery

Run The Code

11Scroll down and copy the Arduino code from the Code section and paste it into your Arduino IDE.

12The auto-generated sketch references the TembooAccount.h header file, which contains your Temboo account information. You'll find the code for this file beneath your generated sketch. Create a new tab in the Arduino IDE called TembooAccount.h and copy in the header file information.

Extending The Sketch

You'll notice that your sketch 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. Here's an example of what you need to do to make that happen.

13Add the following lines of code to your setup() method so that you're reading the value on pins A0 and A1:

pinMode(A0, INPUT);
pinMode(A1, INPUT);

14Next, replace the line of code that currently adds static data to BigQuery with the following code so that your sketch reads the value on pin A0 and A1 and sends that data to BigQuery instead.

String temperature = (String)analogRead(A0);
String humidity = (String)analogRead(A1);
String sensorJSON = "[\n  {\n    \"json\": {\n      \"Temperature\":" + temperature +",\n      \"Humidity\":" + humidity + "\n    }\n  }\n]";
InsertAllChoreo.addInput("Rows", sensorJSON);

15That's it! Now your sketch should be generating dynamic values and adding them to your BigQuery table. Go back and run your SELECT * ... query to see the updated 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.

Once you've got your code up and running, you're ready to move on and do more. From monitoring your running applications, to moving your generated Temboo code to your preferred development environment and sharing it with colleagues, collaborators and friends - we've got you covered.

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