This tutorial creates a transform that enables a data engineer to run SQL analytical queries on collected data for machine learning, business intelligence, and analytical reports.
A transform modifies and enriches data collected by a FairCom server. The FairCom server automatically runs a transform when a connector, MQTT, JSON DB, or SQL inserts a record into an integration table assigned to the transform.
This tutorial's transform extracts a "t1" property from JSON data in the source_payload field and loads it into a table's temperature field. The following example shows a record after the transform has run.
- Ensure the FairCom server is installed and running.
- Confirm server access by running the FairCom API Explorer. The typical URL is
https://localhost:8443/. - Familiarize yourself with how to run JSON actions in API Explorer.
Create a transform
This step creates a transform that extracts a t1 property from JSON data in the source_payload field and loads it into a table's temperature field. You can assign this transform to any integration table if the table has a temperature field with a float data type.
{
"api": "hub",
"action": "createTransform",
"params": {
"transformName": "jsonToTableFields source_payload t1 to temperature",
"transformActions": [
{
"inputFields": [
"source_payload"
],
"outputFields": [
"temperature"
],
"transformStepMethod": "jsonToTableFields",
"transformParams": {
"mapOfPropertiesToFields": [
{
"propertyPath": "t1",
"name": "temperature",
"type": "float"
}
]
}
}
]
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}Create the integration table
This step creates an integration table that is compatible with the transform. It is named "temperature1" and has a temperature field with a float data type.
The "transformName" property assigns the previously created transform to the table.
{
"api": "hub",
"action": "createIntegrationTable",
"params": {
"databaseName": "faircom",
"tableName": "temperature1",
"fields": [
{
"name": "temperature",
"type": "float"
}
],
"transformName": "jsonToTableFields source_payload t1 to temperature"
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}Add records to the integration table
This step inserts a record into the "temperature1" table.
The FairCom server automatically runs the transform when a connector, MQTT, JSON DB, or SQL inserts a record into the "temperature1" table.
{
"api": "db",
"action": "insertRecords",
"params": {
"tableName": "temperature1",
"databaseName": "faircom",
"sourceData": [
{
"source_payload":
{
"t1": 20.1
}
}
]
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}View the results in the table records
In the API Explorer navigation window, navigate to faircom->admin->Tables and select test2.
Note
If you do not see temperature1, click the refresh Tables button.
- Click on the inserted record.
- Select the extracted record in the record viewer.
- Click on the
source_payloadfield's edit button
to show its value. - Observe that the value is
{"t1":20.1},
