Optional Tasks
Visualize the data with AWS QuickSight
Here's the last missing part of the architecture:
In order to correctly show the data in AWS QuickSight we need to create another data set in IoT Analytics to add a field that will be used as timestamp for the x-axis of the time series visualization. While we could use the autogenerated __dt field, which represents the IoT Analytics processing/ingestion timestamp, it is more accurate to use a client generated time stamp.
QuickSight can parse date strings in different formats and autodetects when a string contains a date or a date and time, but it does not support date time formats with more then 1 sec granularity, which is the case for the workstart strings contained in the aggregated message payload.
Let's then create this new DataSet.
Go to the AWS IoT Analytics console and select Analyze→Data sets. Click on “Create” to create a new data set.
Select “Create SQL” as you did before. In the next window enter a new Data Set ID, such as all_data_quicksight and select the data store you created previously. Click “Next”.
In the following screen enter the below SQL, which is adding a new field called ts which is derived truncating the workstart string field to remove the trailing millisecond information:
SELECT substr(workstart,1,19) as ts, * FROM drill_data_store
Note: the above query assumes that your data store is called drill_data_store. If needed, change it to the name you gave your data store.
On the next screen, Configure data selection filter leave the data selection window to None and click “Next”.
Select a Frequency of 15 minutes and click “Next”
Leave the last screen configuration as-is and click on “Create data set”.
Select the dataset you just created from the list, and preview the data, and you should see the new ts column has been added.
- Open the AWS Console and open the Service “QuickSight”
- If you haven't used QuickSight before you will see a screen asking to Sign Up
- Open QuickSight in the region you have the workshop running and click “New analysis”
- Click “New data set”
- Select “AWS IoT Analytics” (if you don't see this item you need to enable IoT Analytics data sets in the setting)
- Select the AWS IoT Analytics data set that you just created and click “Create data source”
After the data set has been created click “Edit/Preview data”.
NOTE: if you get an error message that QuickSight cannot connect to the data set, go back to AWS IoT Analytics, select the data set and refresh it.
In the next screen, expand the Fields pane on the left side, and deselect all the label fields, such as spindlelow, durationtooshort, spindlehigh, motorlow, pressurehigh, hasanomaly, durationtoolong, motorhigh. This is to reduce clutter in the field selection pane for the visualisation. Notice also that the ts field is of type date while workstart is of type string.
Now click on “Save & visualize” on the top bar.
Time to create a visualisation.
On the left hand side you have a panel with the available fields, and on the bottom the available visual types. The default selection is to let QuickSight to automatically determine the visualisation type based on the selected fields, but we suggest to explicitly pick the Line Chart visualisation for displaying time series data.
Select the ts and avgmotor fields by clicking on them. You should have a graph showing a line. In the field wells section click on the down arrow on the ts field and change the Aggregate value to Minute. Then the avgmotor field change the aggregation to Average.
You should now get a graph similar to the one below:
Add the avgmotor field again twice to the Value field well and select Min and Max aggregate respectively.
Dragging the two handles on the x-axis you can zoom in and out based on time.
