Skip to main content

"alterIntegrationTable"

JSON hub "alterIntegrationTable action modifies settings for existing tables that are safe to modify

The "alterIntegrationTable" action alters table settings that are safe to modify.

Essential information

  • The settings that are safe to modify include:

    • Renaming a table

    • Adding fields

    • Increasing field size

    • Changing table retention policy

    • Changing table metadata

    • Changing a table's transformation pipeline

  • As you refine your integration processes, you may want to rename an integration table to better label the data it holds.

    You can use the "alterIntegrationTable" action with the "newTableName" property to rename an integration table.

    Tip

    You can also use the "tableName" property of the "configureTopic" action to rename an integration table that is automatically created by an MQTT message. This is easy because you can rename the integration table using its MQTT topic.

  • This action cannot shrink the size of fields because this destroys data.

  • This action cannot rename fields because it breaks compatibility with bridges between services (integrations / configurations).

Request examples

Rename table request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "5",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": 
  {
    "databaseName": "faircom",
    "tableName": "test2",
    "newTableName": "test3"
  }
}

Change data retention and metadata request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "1",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": 
  {
    "databaseName": "faircom",
    "tableName": "test2",
    "retentionPeriod": 2,
    "retentionUnit": "month",
    "metadata": {
      "tags": ["test", "deleteme"] 
    }
  }
}

Increase field size request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "2",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "test2",
    "alterFields": [
      {
        "name": "t10",
        "type": "VARCHAR",
        "length": 65500,
        "nullable": true
      }
    ],
    "retentionPeriod": 45,
    "retentionUnit": "day"
  }
}

Add two fields request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "3",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": 
  {
    "databaseName": "faircom",
    "tableName": "test2",
    "addFields": 
    [
      {
        "name": "new_field1",
        "type": "LVARCHAR",
        "nullable": true
      },
      {
        "name": "new_field2",
        "type": "TIMESTAMP",
        "nullable": true
      }
    ]
  }
}

Delete two fields request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "4",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": 
  {
    "databaseName": "faircom",
    "tableName": "test2",
    "deleteFields": 
    [
      "new_field1", "new_field2"
    ]
  }
}

Change the table's transform pipeline request

{
  "api": "hub",
  "apiVersion": "1.0",
  "requestId": "5",
  "authToken": "anAuthTokenProvidedbyServer",
  "action": "alterIntegrationTable",
  "params": 
  {
    "databaseName": "faircom",
    "tableName": "test2",
    "transformName": "firstCreateMe"
  }
}

Response examples

Successful response

{
    "authToken": "anAuthTokenProvidedbyServer",
    "result": {},
    "requestId": "1",
    "errorCode": 0,
    "errorMessage": ""
}

Wrong table name failure response

{
    "authToken": "anAuthTokenProvidedbyServer",
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Not able to find integration table by name [test3/admin/faircom]."
}

Wrong transform name failure response

{
    "authToken": "anAuthTokenProvidedbyServer",
    "result": {},
    "requestId": "5",
    "errorCode": 100,
    "errorMessage": "Transform pipeline [firstCreateMe] was not found."
}

Use the alterIntegrationTable JSON API action to alter settings for existing tables that are safe to modify

API actionsJSON hub APIjsonActionintegration tables actionsalter integration tablealterIntegrationTable

"params"

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.

Properties summary

Table 1. "params" properties summary

Property

Description

Default

Type

Limits (inclusive)

alterFields

defines how to alter a field in a table

[]

array of objects

databaseName

specifies the name of a database

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

1 to 64 bytes

retentionPolicy

specifies how messages persist

"autoPurge"

string

"autoPurge"
"neverPurge"
"doNotPersist"

retentionPeriod

specifies the number of retention units, which controls how long data is retained – see "retentionUnit"

4

integer

1 to 100

retentionUnit

purges expired messages each time this unit cycles – see "retentionPeriod"

"week"

string

"minute"
"hour"
"day"
"week"
"month"
"year"

sourcePayloadFormat

specifies the variant type format of the "source_payload" field

"binary"

string

"JSON"
"XML"
"BINARY"
"JPEG"
"SiemensUDT"
"utf8"

tableName

specifies the name of a table

""

string

1 to 64 bytes

transformName

specifies the name of a transform process. The name cannot be one of the FairCom-provided transform names

""

string

1 to 64 bytes



"databaseName"

The "databaseName" property is an optional string that specifies the database that contains the tables. It defaults to the database name supplied at login.

Note

In the API Explorer, "defaultDatabaseName" is set to "ctreeSQL" in the "createSession" action that happens at login.

Essential information

  • A zero-length "databaseName" is invalid.

  • Its limits are from 0 to 64 bytes.

  • If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.

  • If no default database is specified during "createSession", "defaultDatabaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

"retentionPeriod"

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.

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 to 100, FairCom's servers set it to the default value.

  • Smaller numbers improve SQL performance.

Essential information

  • Each time the "retentionPeriod" cycles, FairCom's servers automatically and efficiently delete expired data.

  • 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 to 14 , 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 to day, 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.

"retentionPolicy"

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.

  • "doNotPersist"

    This stores messages only in RAM, where they are transmitted more quickly. Undelivered messages are lost when FairCom's servers stop and restart. No transforms can be applied to the payload. Message data cannot be bridged across protocols.

Important

Changing the "retentionPolicy" property value to "doNotPersist" removes all existing message history for the topic because it changes how messages are stored on disk.

"retentionUnit"

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". 

Essential information

  • 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" between 5 and 30. 

  • When you set "retentionUnit" property to "forever" the server does not purge messages. This setting is the same as setting "retentionPolicy" to "neverPurge".

  • FairCom MQ only uses the "retentionUnit" property when the "retentionPolicy" is "autoPurge".

"sourcePayloadFormat"

The "sourcePayloadFormat" property is an optional string that defines the variant type format of the "source_payload" field. When omitted or set to null, it defaults to "binary".

Essential information

  • This property is a string containing the following enumerated values:

    •  "binary"

    • "json"

    • "utf8"

    • "siemensUDT"

    • "jpeg"

    • "xml"

  • This property is a hint to the server about the format and type of the MQTT message payload.

  • This property does not cause server to validate the MQTT payload to see if it matches the type you set. The server stores the payload as is without validation. For example, if you set the type to "json", it does not stop the server from receiving and storing a non-JSON value or invalid JSON document in the source payload.

  • The FairCom Edge Explorer application may use the value of this property to determine the default way to display a topic's payload.

  • The transform engine may use the value of this property to help it transform the source payload.

"tableName"

The "tableName" property contains the name of the table in the database where the event occurred. It is a non-zero-length string.

A table name may contain up to 64 ASCII characters and must not start with a number.

A table in DBnotify is defined by "databaseName", "ownerName" and "tableName" or by "dataFilePath".

"transformName"

The "transformName" property is an optional string that contains the unique name of a transform process, which consists of one or more transform steps.

Essential information

  • A transform is a process that works like a pipeline where the output of one transformation can become the input for another transformation.

  • The following actions use the "transformName" property to assign a transform to an integration table:

    • "configureTopic"

    • "createInput"

    • "alterInput"

    • "alterIntegrationTable"

A database is a set of data that is managed by a database server. A database contains data that is organized into tables, records, and fields. A database is a huge composite data type that represents a group of interconnected objects. A database allows records to be joined together for querying, searching, and processing by users, applications, and services.

A database is a security container that controls which accounts have permissions to access specific data.