Within the first a part of this weblog collection Create insights by contextualizing industrial gear information utilizing AWS IoT SiteWise (Half 1) we targeted on asset modelling and real-time analytics in AWS IoT SiteWise. We created a dashboard in AWS IoT SiteWise Monitor to get a real-time overview of our furnace heating cycles. However we concluded {that a} extra in-depth evaluation was wanted to search out the basis reason behind the irregular heating cycle of Furnace1. On this second a part of the weblog, we are going to present how clients can use the AWS IoT SiteWise chilly tier storage characteristic to export the uncooked, aggregated and meta-data to AWS IoT Analytics for additional evaluation.
Allow AWS IoT SiteWise chilly tier storage and AWS IoT Analytics export
AWS IoT SiteWise chilly tier storage characteristic makes it simple to devour historic information in downstream AWS analytic companies. Moreover, it would additionally decrease your storage price on AWS IoT SiteWise by exporting historic information to Amazon S3. Buyer can freely outline how lengthy the information will probably be saved within the time-series optimized AWS IoT SiteWise information earlier than being exported into S3 by setting an information retention threshold.
Enabling AWS IoT SiteWise chilly tier storage
To allow AWS IoT SiteWise S3 export, open the AWS IoT SiteWise console, select Settings, Storage, Edit within the navigation pane and verify Allow Chilly tier storage,
Enter an current S3 bucket location in the identical AWS area,
Test Allow AWS IoT Analytics information retailer, sort iotsitewise because the Information retailer title and select Save
For this stroll via we are going to use AWS IoT Analytics to question the information and visualize it in Amazon QuickSight.
The AWS IoT SiteWise S3 export characteristic exports data on asset properties from the asset mannequin into the asset-metadata S3 prefix when the mannequin change. As soon as the standing of the S3 export is enabled, you must see a line delimited JSON file per asset in your S3 bucket. The uncooked information is exported each 6 hours and will probably be positioned into the uncooked S3 prefix. For extra particulars on the export format and site, see File paths and schemas of knowledge saved within the chilly tier.
Create an AWS IoT Analytics dataset to investigate the uncooked information
We will now begin to analyze the AWS IoT SiteWise exported information with AWS IoT Analytics. Open the AWS IoT Analytics console, select Datasets within the navigation menu and select Create dataset, Create SQL. It will open a wizard that may information you thru the Dataset creation. On the primary display screen title your information set and select the iotsitewise information retailer that was created by the AWS IoT SiteWise chilly tier export wizard.
Select Subsequent to open the Writer SQL question dialog, copy and previous the pattern question beneath.
The question used some superior Athena SQL options and likewise demonstrated how AWS IoT Analytics Dataset queries can be utilized to hitch information from totally different AWS IoT Analytics information shops. For this evaluation, we wish to question the metric Final Holding Cycle Time for all our belongings within the present month. To perform this, the question begins from the uncooked information retailer filtered by a selected month. It joins the asset_metadata information retailer to retrieve the property metadata just like the asset title. Lastly, it joins the asset_metadata once more, however this time grouped by asset ID. This final JOIN assertion retrieves all static attribute of the corresponding AWS IoT SiteWise asset and provides it to the outcome row. This information is essential for our evaluation, as a result of we are going to use it in our final step as dimensional information.
SELECT
from_unixtime(information.timeinseconds + (information.offsetinnanos / 1000000000)) ts,
metadata.assetname, metadata.assetpropertyname, metadata.assetpropertydatatype,
information.doublevalue,
latesValue['Location'] as Location , latesValue['Manufacturer'] as Producer,
latesValue['YearOfConstruction'] as YearOfConstruction, latesValue['Setpoint'] as Setpoint
FROM iotsitewise.uncooked as information
-- Be part of the meta information desk
INNER JOIN iotsitewise.asset_metadata as metadata
ON information.seriesid = metadata.timeseriesid
-- Be part of sub question that retrieves all asset attributes and newest values
LEFT JOIN (
SELECT assetid, map_agg(assetpropertyname, latestvalue) latesValue from (
SELECT assetid, assetpropertyid, assetpropertyname,
coalesce (
max_by(information.stringvalue, information.timeinseconds),
max_by(solid(information.integervalue as VARCHAR), information.timeinseconds),
max_by(solid(information.doublevalue as VARCHAR), information.timeinseconds)
) latestvalue
FROM iotsitewise.uncooked information
INNER JOIN iotsitewise.asset_metadata metadata
ON information.seriesid = metadata.timeseriesid
GROUP BY assetid, assetpropertyid, assetpropertyname)
GROUP BY assetid) as dim
ON metadata.assetid = dim.assetid
WHERE information.startyear = yr(current_date)
AND information.startmonth = month(current_date)
AND metadata.assetpropertyname="Final Holding Cycle Time"
To check the question, select Check question. If the question comprises no syntax errors, you must see a preview of the information within the Outcome preview part.
Depart the remainder of the steps 3-6 with the default worth by selecting Subsequent and select Create dataset on the final assessment step 7.
To validate if all the things is appropriately setup, navigate to your newly created dataset
Select Run now and wait till the outcome content material seems on the Content material tab with Succeeded. If you select the Outcome hyperlink, the console reveals you a preview of the question outcome:
The outcome reveals the Final Holding Cycle Time metric by time. The question additionally added the AWS IoT SiteWise mannequin data, just like the asset title and mannequin title, and the asset attribute values to every row. Such flattened information rows make it simpler to investigate the information in BI instruments. Within the subsequent step we are going to use Amazon QuickSight to investigate the IoT Analytics dataset.
Analyze the end in Amazon QuickSight
As a ultimate step, we are going to analyze the information in Amazon QuickSight. Amazon QuickSight comes with a built-in connector for AWS IoT Analytics, so it’s simple to visualise the information.
Open the Amazon QuickSight console and selected New Evaluation, when prompted for an information sources, create a brand new one by selecting New Dataset. Select AWS IoT Analytics and choose the SiteWise AWS IoT Analytics dataset holdingcycletimereport we created within the earlier step. To create the information supply select Create Information Supply.
 |
 |
Select Visualize to begin utilizing Amazon QuickSight Visible Varieties to show the information set.
On this particular use case, we wish perceive how the Manufacture and the Development 12 months influences the HOLDING cycle length, the Amazon QuickSight warmth map is an efficient alternative to visualise this information.
And from this view, we will clearly establish that the furnaces manufactured by Furnace Corp in 1999 have the longest cycle time (>=88) and must be prioritized for substitute.
Conclusion
This concludes the 2 half weblog collection on methods to use AWS IoT SiteWise and AWS IoT Analytics to contextualize your industrial gear information. We began by ingesting uncooked time collection information into AWS IoT SiteWise. Subsequent, we used the AWS IoT SiteWise asset mannequin so as to add context concerning the industrial gear that produced the time collection information. Lastly, we demonstrated methods to use dataset queries in AWS IoT Analytics to mix the time collection information factors and context information right into a flattened format that’s simple to devour in BI instruments like Amazon QuickSights.
In regards to the writer
Jan Borch is a Principal Specialist Answer Architect for IoT at Amazon Net Companies (AWS) and spent the final 10 years serving to clients design and construct best-in-class cloud options on AWS. The final 5 years, he targeted on the intersection of Cloud and IoT, main the AWS IoT Prototyping Group to co-develop progressive related IoT options with AWS clients in Europe, Center East and Africa and lately his point of interest shifted to clients with strategic IoT workloads on AWS.
