JSON to different table API reference
API reference for the JSON to different table transform method
Request examples
Minimal request
{ "api": "hub", "action": "createTransform", "params": { "transformName": "TestTransform2", "transformActions": [ { "inputFields": [ "In1" ], ], "transformStepMethod": "jsonToDifferentTableFields", "transformParams": { "targetDatabaseName": "faircom", "targetTableName": "test_out2", "mapOfPropertiesToFields": [ { "propertyPath": "out1", "name": "out1", "type": "VARCHAR", "length": 200 } ] } } ] }, "authToken": "replaceWithAuthTokenFromCreateSession" }
{ "api": "transform", "action": "createTransform", "params": { "transformName": "singlePropertyToMultipleFieldsTranform", "transformActions": [ { "inputFields": [ "source_payload" ], "outputFields": [ "temperature_a", "temperature_b", "temperature_c" ], "transformStepMethod": "jsonToTableFields", "transformParams": { "targetTableName": "my_table", "mapOfPropertiesToFields": [ { "propertyPath": "temperature", "name": "temperature_a", "type": "DOUBLE" }, { "propertyPath": "temperature", "name": "temperature_b", "type": "DOUBLE" }, { "propertyPath": "temperature", "name": "temperature_c", "type": "DOUBLE" } ] } } ] }, "requestId": "00000002", "authToken": "replaceWithAuthTokenFromCreateSession" }
Tip
Creating the transform's target table before creating the transform enables you to create fields with more precise types than the default types created by the transform, such as NUMBER or INTEGER rather than DOUBLE.
{ "api": "hub", "apiVersion": "1.0", "requestId": "00000006", "action": "createTransform", "params": { "transformName": "TestTransform2", "transformActions": [ { "inputFields": [ "In1" ], "outputFields": [ "out1", "out2" ], "transformService": null, "transformStepMethod": "jsonToDifferentTableFields", "transformParams": { "targetDatabaseName": "faircom", "targetTableName": "test_out2", "newTargetTableFeatures": { "integrationTable": true, "retentionPolicy": "autoPurge", "retentionPeriod": 30, "retentionUnit": "day" }, "mapOfPropertiesToFields": [ { "propertyPath": "out1", "name": "out1", "type": "VARCHAR", "length": 200 }, { "propertyPath": "out2", "name": "out2", "type": "DOUBLE" } ] }, "metadata": { "myTag": "my data" } } ] }, "responseOptions": { "binaryFormat": "base64", "dataFormat": "arrays", "numberFormat": "number" }, "authToken": "replaceWithAuthTokenFromCreateSession" }
Note
The jsonToDifferentTableFields
transform will automatically add the following fields to the target table if they do not already exist. When the transform inserts a new row into the target table, it auto-populates these fields:
change_id
create_ts
id
out1
out2
source_database
source_id
source_schema
source_table
If any existing field or index has the same name as one of these but with a mismatched schema definition, the transform will fail and prompt the user to fix this conflict.
{ "result": {}, "requestId": "00000007", "errorCode": 0, "errorMessage": "", "authToken": "replaceWithAuthTokenFromCreateSession" }
API reference for the JSON to different table transform method
The "params"
property is an object that contains an action's parameters. Each action defines its own required and optional properties. See System limits for a comprehensive look at property requirements and limitations.
Property summary
"params"
property summariesProperty | Description | Default | Type | Limits | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
specifies | Required - No default value | array of objects | |||||||||||||
| (optional) specifies features of the table the transform creates if the specified |
| object | ||||||||||||
| (optional) specifies whether or not the created table will be an integration table |
| Boolean |
| |||||||||||
| (optional) specifies how many units of data to retain |
| integer |
| |||||||||||
| (optional) controls how messages are persisted |
| string |
| |||||||||||
| (optional) purges expired messages each time this unit cycles |
| string |
| |||||||||||
| (optional) specifies the name of the transform service | Required when | string |
| |||||||||||
| specifies the name of the transform action | Required - No default value | string |
| |||||||||||
| (optional) allows the user to assign a name to each transform step |
| string | 0 to 250 bytes | |||||||||||
transformName | specifies the name of a transform process. The name cannot be one of the FairCom-provided transform names | Required - No default value | string | 1 to 64 bytes |
The "transformActions"
property is an optional array of "transformAction"
objects. It defaults to an empty array.
The "transformParams"
property is a required object that can be an empty object or contain one or more properties needed by the "transformAction"
.
The "mapOfPropertiesToFields"
property is a required array of "mapOfPropertiesToFields"
objects. It defaults to an empty array.
Example
"mapOfPropertiesToFields": [ { "propertyPath": "out1", "name": "out1", "type": "VARCHAR", "length": 200 } ]
The "propertyPath"
property is a string that specifies the property that will be read in JSON.
The "name"
property is a string that specifies the destination in the integration table for the data that was read from the propertyPath.
The "type"
property specifies the type of the integration table being written to.
The "length"
property is an integer that specifies the length of the "name"
property.
The "newTargetTableFeatures"
property specifies features of the table the transform creates if the specified "targetTableName"
does not already exist in the database. It only applies when the server creates a new table.
The "integrationTable"
property specifies whether or not the created table will be an integration table. It defaults to true. Set to false when you want the action to create a non-integration table because you do not want it to run transforms, forward events to outputs, and contain the integration table fields.
The "retentionPeriod"
property specifies how many units of data to retain. It must be an integer value from 1
to 100
. It refers to the unit of time specified by the "retentionUnit"
property — for example, if "retentionPeriod"
is 14
and "retentionUnit"
is "day"
, then message data is retained for 14 days. This property is optional.
Periodically, the system increments the age of each table partition.
"Minute"
units are incremented at the zero second of each minute, whether the table partition was created 1 second before or 59 seconds before."Day"
units are incremented at midnight GMT, not midnight of the local UTC time zone. A table partition becomes one day old at midnight GMT, whether it was created one second before or 23 hours and 59 seconds before."Week"
units are incremented at midnight GMT on the last day of each week, even if the table partition was created one second before. The last day of the week is Saturday GMT."Month"
units are incremented at midnight GMT on the last day of each month."Year"
units are incremented at midnight GMT on Dec 31.
"retentionPeriod"
implicitly calculates an upper bound on how many partitions are able to accumulate on your hard drive. However, partitions are not deleted just because this calculated number of partitions is reached. The system also does not restrict the deletion of all partitions.If the FairCom database is shut down for a month, when the database is started up again, all partitions that are retained for less than one month are immediately deleted.
If someone purposely or accidentally sets the date to a future date, all partitions immediately become older, and auto-deletion ensues accordingly.
When partitions are auto-purged, some data are maintained "forever"
in a global index. Auto-purge does not prevent these files from growing without bounds and filling up your hard drive.
If not specified, the default found in the services.json file is used. Initially, it is 4 (weeks).
Automatically purging data is important to ensure that retained data does not consume all storage and shut down the computer. The default value of 4 weeks allows FairCom's servers to store 1 TB of messages when 200 topics send one 2K message per second.
Note
If the value is not an integer from
1
to100
, FairCom's servers set it to the default value.Smaller numbers improve SQL performance.
Each time the
"retentionPeriod"
cycles, FairCom's servers automatically and efficiently delete expired data.The
"retentionUnit"
and"retentionPeriod"
properties are used only when the"retentionPolicy"
is set to"autoPurge"
.FairCom's servers only use the
"retentionPeriod"
property when the"retentionPolicy"
is"autoPurge"
.The
"retentionPeriod"
can be changed to retain fewer or more messages. Changing it does not necessarily destroy existing data, but data may expire more quickly or be retained longer.The
"retentionPeriod"
and"retentionUnit"
properties control data granularity as well as the retention time. In other words,"retentionPeriod"
defines how many sets of data are stored, and"retentionUnit"
defines how often data is purged.For example, if
"rententionPeriod"
is set to14
, the server stores 14 sets of data. At the beginning of the 15th cycle, the server automatically purges the oldest set of data. If"retentionUnit"
is set today
, then data will be purged daily. If set to"week"
, then data will be purged weekly.The current calendar date affects purging.
FairCom's servers automatically purge all retained data that has expired. This is noticeable when FairCom's servers come online after having been offline for a long time. When a server comes back online, it automatically purges all expired data.
For example, if a FairCom server is offline for four weeks when it comes online, it will completely purge all retained data that has a retention time of less than 4 weeks.
The "retentionPolicy"
property controls how messages are persisted. This property is optional.
If not specified, the default found in the services.json
file is used. Initially, it is "autoPurge"
.
retentionPolicy
values:"autoPurge"
This is the default. It is automatically applied when a new topic is created. It is preferred because it allows FairCom's servers to automatically remove messages that are older than the retention time. This helps ensure message data does not consume all storage space. It also minimizes storage costs and speeds up data access. The server partitions a table into multiple files so it can efficiently delete expired files.
"neverPurge"
This stores messages on disk and never removes them. This is useful when you need the entire history of the message stream. If message velocity is high, this can consume all storage space and cause an outage. The server creates a non-partitioned table, which is slightly faster than a partitioned table because it stores all records in one file.
Each time this unit cycles, FairCom purges expired messages. For example, if you want a week's worth of messages to be purged once a week, set "retentionUnit"
to "week"
. This property is optional.
If not specified, the default found in the services.json
file is used. Initially, it is "week"
.
This property is used in concert with
"retentionPeriod"
to determine retention time."retentionUnit"
values:"minute"
"hour"
"day"
"week"
"month"
"year"
"forever"
Note
For best performance, set the
"retentionUnit"
to a value that keeps"retentionPeriod"
between5
and30
.When you set
"retentionUnit"
property to"forever"
the server will not purge messages. This setting is the same as setting"retentionPolicy"
to"neverPurge"
.The
"retentionUnit"
and"retentionPeriod"
properties are used only when the"retentionPolicy"
is set to"autoPurge"
.
The "transformService"
property is an optional string that specifies the name of the transform service.
Note
If the "transformStepMethod"
property is set to "javascript"
, the "transformService"
property must be set to "v8TransformService"
.
The "transformStepMethod"
property is a required string that specifies the type of transform, such as the "javascript"
transform method that runs JavaScript to change the table's data or the "jsonToTableFields"
transform method that extracts values from properties in a JSON field and stores them in other fields.
Note
The "transformStepMethod"
property replaces the deprecated "transformActionName"
property.
The value of the "transformStepMethod"
affects the value of the "transformService"
property. The following table defines the possible values of the "transformService"
property when combined with the "transformStepMethod"
. Notice that some transform step methods are built into the server and do not require you to specify the "transformService"
.
|
| Definition |
---|---|---|
|
| |
|
| |
|
| |
|
| The |
|
|
Note
If the "transformStepMethod"
property is set to "javascript"
, the "transformService"
property must be set to "v8TransformService"
.
The "transformStepName"
property is an optional string that allows the user to assign a name to each transform step.