1 of 8

Real-time sync stream sources

Overview

Sync Stream Sources are an integral part of the real-time sync experience in Connections, and must be configured prior to executing your data sync.

Stream Sources are configured and enabled via the Listener Configuration table or the Connections UI in Cinchy.

While a Stream Source can feasibly be configured at any point during the creation of a real-time data sync, we recommend that you do so as a final step after setting up your Source, Destination, and any other relevant additional settings.

Stream Sources are only used for real-time syncs.

The Listener Config table

Overview

The Listener Configuration table lets you set up your stream source configurations for real-time syncs via the Connections UI, and you can find that information on the relative . It's also important to track your real-time syncs. As of Cinchy v5.7, you can now set up your listener configuration on the Sources tab under the Listener section of the Connections experience. If your data sync requires more than one listener, you must set up additional configurations via the Listener Config table on your Cinchy platform.

You can also use this table to manage and review all the configurations currently set up on your system (Image 1). Most of the columns within the Listener Config table persist across all Stream Sources, however exceptions will be noted. You can find all of these parameters and their relevant descriptions in the tables below.

Configure the listener

The following column parameters can be found in the Listener Config table:

Information about setting up your Topic JSON can be found on the individual real-time sync stream source configuration pages.

Cinchy Event Broker/CDC

Overview

The Cinchy Event Broker/CDC is an event streaming source used to listen for changes on Cinchy tables and push those changes to various data sync destinations.

The Listener Config Table

To set up an Stream Source, you must navigate to the Listener Config table and insert a new row for your data sync (Image 1). Most of the columns within the Listener Config table persist across all Stream Sources, however exceptions will be noted. You can find all of these parameters and their relevant descriptions in the tables below.

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

If you are creating a CDC listener config for a Cinchy Event Triggered REST API data source, pay in mind the following unique constraints:

Column names in the listener config shouldn't contain spaces. If they do, they will be automatically removed. (For example, a column named First Name will become @FirstName)
The replacement variable names are case sensitive.
Column names in the listener config shouldn't be prefixes of other column names. E_.g. if you have a column called Name, you shouldn't have another called Name2 as the value of @Name2 may end up being replaced by the value of @Name suffixed with a 2.

Example Topic JSON

You don't need to provide Connections Attributes when using the Cinchy CDC Stream Source, however you can't leave the field blank. Instead, insert the below text into the column:

Appendix A

messageKeyExpression

Each of your Event Listener message keys a message key. By default, this key is dictated by the Cinchy ID of the record being changed.

When the worker processes your Event Listener messages, it does so in batches, and for efficiency and to guarantee order, messages that contain the same key won't be processed in the same batch.

The messageKeyExpression property allows you to change the default message key to something else.

Possible Use Case

Ensuring records with the same message key can be updated with the proper ordering to reflect an accurate collaboration log history.

Example Syntax

In this example, we want the message key to be based on the [Employee Id] and [Name] column of the table that CDC is enabled on.

Appendix B

Old vs New Filter

The Cinchy Event Broker/CDC Stream Source has the unique capability to use Old and New parameters when filtering data. This filter can be a powerful tool for ensuring that you sync only the specific data that you want.

The "New" and "Old" parameters are based on updates to single records, not columns/rows.

"New" Example:

In the below filter, we only want to sync data where the [Approval State] of a record is newly 'Approved'. For example, if a record was changed from 'Draft' to 'Approved', the filter would sync the record.

Due to internal logic, newly created records will be tagged as both New and Old.

"Old" Example:

In the below filter, we only want to sync data where the [Status] of a record was 'In Progress' but has since been updated to any other [Status]. For example, if a record was changed from 'In Progress' to 'Done', the filter would sync the record.

Due to internal logic, newly created records will be tagged as both New and Old.

Data Polling

Overview

Version 5.4 of the Cinchy platform introduced data polling, which uses the Cinchy Event Listener to continuously monitor and sync data entries from your SQLServer or DB2 server into your Cinchy table. This capability makes data polling a much easier, effective, and streamlined process and avoids implementing the complex orchestration logic that was previous necessary.

This page outlines the necessary Listener Config values that need to be used prior to setting up your data sync.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

FROM [SourceTable] WHERE Id IN (SELECT TOP (100) Id FROM [SourceTable] WHERE Id > 0 AND Name IS NOT NULL ORDER BY Id) AND Id > 0 AND Name IS NOT NULL | | FromClause | Mandatory. This must contain at least the table name but can also contain Joined tables as written in SQL language. | Example: [Source Table] | | CursorColumn | Mandatory. Column name that's used in any 'WHERE' condition(s) and for ordering the result of a query | Example: [Id] | | BatchSize | Mandatory. Minimum size of a batch of data per query. This can be larger to prevent infinite loops if the CursorColumn isn't unique. | Example: 100 | | FilterCondition | All filtering options used in any 'WHERE' condition(s) of the query | Example: Name IS NOT NULL | | Columns | Mandatory. A list of columns that we want to show in a result. | Example:Id, Name | | ReturnDataConfiguration |

The parameters here are used in more complex queries. In our example, there are 2 related tables, but want to show the contents of one of them based on the CursorColumn from a second table. Since Timestamp values aren't unique, we need to find all combinations of Id, Timestamp that match the filter condition in a subquery, and then join this result with the outer-query to get the final result. Note that in ReturnDataConfiguration, our parameters area of concern is everything outside of first open parenthesis ( and last closing parenthesis ), i.e.:

FROM
( ... ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0 ORDER
BY Id

Example complex query:

SELECT
ts.[Id],ts.[Name] FROM ( SELECT Id,Timestamp FROM [Table2] WHERE Timestamp IN
(SELECT TOP (2) Timestamp FROM [Table2] WHERE Timestamp > '2022-11-18 11:34:09
AM' AND Timestamp <= '2022- 11-19 11:34:09 AM' AND 1=1 ORDER BY Timestamp)
AND 1=1 ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0
ORDER BY Id

| | CursorAlias | Mandatory. This is the alias for a subquery result table. It's used in 'JoinClause', and can be used in 'Columns' if we want to return values from a subquery table. | Example: "t" | | JoinClause | Mandatory. Our result table to which we join the subquery result, plus the condition of the join. | Example: [Table1] ts ON ts.[Id] = t.[Id] | | FilterCondition | All filtering options used in any 'WHERE' conditions. | Example: "ts.[Id] > 0" | | OrderByClause | Mandatory. This is the column(s) that we want to order our final result by. | Example: "Id" | | Columns | Mandatory. A list of columns that we want to show in the final result. | Example: "ts.[Id]" "ts.[name]" | | Delay | Mandatory. This represents the delay, in second, between data sync cycles once it no longer finds any new data. | Example: 10 | | messageKeyExpresssion | Optional, but recommended to mitigate data loss. See Appendix A for more information on this parameter. | id |

Example Topic JSON

{
  "CursorConfiguration": {
    "FromClause": "[Source Table]",
    "CursorColumn": "Id",
    "BatchSize": 100,
    "FilterCondition": "Name IS NOT NULL",
    "Columns": [
      "Id", "Name"
    ]
  },
  "ReturnDataConfiguration": {
    "CursorAlias": "t",
    "JoinClause": "[Table1] ts ON ts.[id] = t.[id]",
    "FilterCondition": "ts.[id] > 0",
    "OrderByClause": "id",
    "Columns": [
      "ts.[Id]"
      "ts.[Name]",
    ]

  },
  "Delay": 10
}

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

{
  "databaseType": "TSQL",
  "connectionString": "Server=;Database=;User ID=cinchy;password=example;Trusted_Connection=False;Connection Timeout=30;Min Pool Size=10;",
}

Appendix A

messageKeyExpression

The messageKeyExpression parameter is an optional, but recommended, parameter that can be used to ensure that you aren't faced with a unique constraint violation during your data sync. This violation could occur if both an insert and an update statement happened at nearly the same time. If you choose not to use the messageKeyExpression parameter, you could face data loss in your sync.

This parameter was added to the Data Polling event stream in Cinchy v5.6.

Each of your Event Listener message keys a message key. By default, this key is unique for every message in the queue.

When the worker processes your Event Listener messages it does so in batches and, for efficiency and to guarantee order, messages that contain the same key won't be processed in the same batch.

The messageKeyExpression property allows you to change the default message key to something else.

Example:

  "MessageKeyExpression": "id"

Kafka Topic

Overview

The page outlines the parameters that should be included in your listener configuration when setting up a real time sync with a Kafka stream source. This process currently supports **JSON string or AVRO serialized format.**To listen in on multiple topics, you will need to configure multiple listener configs.

To listen in on multiple topics, you will need to configure multiple listener configs.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

Example Topic JSON

{
  "topicName": "<(mandatory) kafka topic name to listen messages on>",
  "messageFormat": "<(optional) Put "AVRO" if the messages are serialized in AVRO>"
}

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

{
  "bootstrapServers": "< (mandatory) kafka bootstrap servers in a comma-separated list in the form of host:port>",
  "saslMechanism": "<PLAIN|SCRAM-SHA-256|SCRAM-SHA-512>",
  "saslPassword": "",
  "saslUsername": "",
  "schemaRegistrySettings": {
    "url": "<(optional) This is required if your data follows a schema when serialized in Avro. A comma-separated list of URLs for schema registry instances that are used to register or lookup schemas. >",
   "basicAuthCredentialsSource": "<(optional) Specifies the Kafka configuration property "schema.registry.basic.auth.credentials.source" that provides the basic authentication credentials, this can be "UserInfo" | "SaslInherit">",
   "basicAuthUserInfo": "<(optional) Basic Auth credentials specified in the form of username:password>",
   "sslKeystorePassword": "<(optional) The client keystore (PKCS#12) password>"
  }
  "securityProtocol": "Plaintext | SaslPlaintext | SaslSsl"
}

MongoDB

Overview

The MongoDB stream source works similar to Cinchy's Change Data Capture functionality. The listener subscribes to monitor the change stream of a specific collection in the database of the MongoDB server. Any actions performed on document(s) inside of that collection are picked up by the listener and sent to the queue.

Limitations

In order to use change streams in MongoDB, there are a few requirements your environment must meet.

The database must be in a or .
The database must use the storage engine.
The replica set or sharded cluster must use replica set protocol .

The Listener Config Table

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

Example Topic JSON

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

Salesforce Push Topic

Overview

A Salesforce Push Topic is a supported Sync Source that you can use in your Cinchy data syncs. The below documentation will refer to the parameters necessary to set up your Push Topic as part of your sync configuration.

Scenarios

You can use a Push Topic already configured in Salesforce, or have Cinchy Event Listener create the Push Topic for you.

Scenario 1: Push Topic already exists in Salesforce.

Cinchy will compare the JSON with the properties on the push topic in Salesforce by name. If the attributes match, the listener will start listening on the push topic.

Scenario 2: Push Topic already exists in Salesforce and the configuration doesn't match.

Cinchy will compare the JSON with the properties on the push topic in Salesforce by name. If any of the attributes don't match, Cinchy will sync the push topic from Salesforce into Cinchy and disable the listener.

Scenario 3: Push Topic doesn't exist in Salesforce.

If the Push Topic name doesn't exist in Salesforce, Cinchy will attempt to create the Push Topic. If it's successful, it will sync in the Id from Salesforce and start listening on the push topic.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

Example Topic JSON

{
  "Id": "",
  "Name": "LeadsTopic",
  "Query": "SELECT Id, Name, Email FROM Lead",
  "ApiVersion": 47.0,
  "NotifyForOperationCreate": true,
  "NotifyForOperationUpdate": true,
  "NotifyForOperationUndelete": true,
  "NotifyForOperationDelete": true,
  "NotifyForFields": "Referenced"
}

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

{
 "ApiVersion": 47.0,
 "GrantType": "password",
 "ClientId": "Bn8UmtiLydmYQV6//qCL5dqfNUMhqchdk959hu0XXgauGMYAmYoyWN8FD+voGuMwGyJa7onrc60q1Hu6QFsQXHVA==",
 "ClientSecret": "DyU1hqde3cWwkPOwK97T6rzwqv6t3bgQeCGq/fUx+tKI=",
 "UserName": "dXNlcm5hbWVAZW1haWwuY29t",
 "Password": "cGFzc3dvcmRwYXNzd29yZA=="
 "InstanceAuthUrl": "https://login.salesforce.com/services/oauth2/token"
}

Salesforce Platform Event

Overview

A Salesforce Platform Event is a supported Sync Source that you can use in your Cinchy data syncs. The below documentation will refer to the parameters necessary to set up your Platform Event as part of your sync configuration.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

Example Topic JSON

{  "Name": "Notification__e"

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

{
 "ApiVersion": 47.0,
 "GrantType": "password",
 "ClientId": "Bn8UmtiLydmYQV6//qCL5dqfNUMhqchdk959hu0XXgauGMYAmYoyWN8FD+voGuMwGyJa7onrc60q1Hu6QFsQXHVA==",
 "ClientSecret": "DyU1hqde3cWwkPOwK97T6rzwqv6t3bgQeCGq/fUx+tKI=",
 "UserName": "dXNlcm5hbWVAZW1haWwuY29t",
 "Password": "cGFzc3dvcmRwYXNzd29yZA=="
 "InstanceAuthUrl": "https://login.salesforce.com/services/oauth2/token"
}

Cinchy Event Broker/CDC

Overview

The Cinchy Event Broker/CDC is an event streaming source used to listen for changes on Cinchy tables and push those changes to various data sync destinations.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

Parameter

Description

Example

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

If you are creating a CDC listener config for a Cinchy Event Triggered REST API data source, pay in mind the following unique constraints:

Column names in the listener config shouldn't contain spaces. If they do, they will be automatically removed. (For example, a column named First Name will become @FirstName)
The replacement variable names are case sensitive.
Column names in the listener config shouldn't be prefixes of other column names. E_.g. if you have a column called Name, you shouldn't have another called Name2 as the value of @Name2 may end up being replaced by the value of @Name suffixed with a 2.

Parameter

Description

Example

Example Topic JSON

{
    "tableGuid": "16523e54-4242-4156-835a-0e572e862304",
    "fields": [
        {
            "column": "Name"
        },
        {
            "column": "Age"
        }
    ],
"filter": "New.[Approval State] = 'Approved' AND Old.[Approval State] != 'Approved'",
   "batchSize": 10
}

You don't need to provide Connections Attributes when using the Cinchy CDC Stream Source, however you can't leave the field blank. Instead, insert the below text into the column:

{}

Appendix A

messageKeyExpression

Each of your Event Listener message keys a message key. By default, this key is dictated by the Cinchy ID of the record being changed.

When the worker processes your Event Listener messages, it does so in batches, and for efficiency and to guarantee order, messages that contain the same key won't be processed in the same batch.

The messageKeyExpression property allows you to change the default message key to something else.

Possible Use Case

Ensuring records with the same message key can be updated with the proper ordering to reflect an accurate collaboration log history.

Example Syntax

In this example, we want the message key to be based on the [Employee Id] and [Name] column of the table that CDC is enabled on.

{ "messageKeyExpression": "CONCAT(New.[Employee Id], '-', New.[Name])", … }

Appendix B

Old vs New Filter

The "New" and "Old" parameters are based on updates to single records, not columns/rows.

"New" Example:

Due to internal logic, newly created records will be tagged as both New and Old.

"filter": "New.[Approval State] = 'Approved'

"Old" Example:

Due to internal logic, newly created records will be tagged as both New and Old.

"filter": "Old.[Status] = 'In Progress'

Data Polling

Overview

This page outlines the necessary Listener Config values that need to be used prior to setting up your data sync.

The Listener Config Table

The following column parameters can be found in the Listener Config table:

Parameter

Description

Example

The below table can be used to help create your Topic JSON needed to set up a real-time sync.

Parameter

Description

Example

FROM
( ... ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0 ORDER
BY Id

Example complex query:

SELECT
ts.[Id],ts.[Name] FROM ( SELECT Id,Timestamp FROM [Table2] WHERE Timestamp IN
(SELECT TOP (2) Timestamp FROM [Table2] WHERE Timestamp > '2022-11-18 11:34:09
AM' AND Timestamp <= '2022- 11-19 11:34:09 AM' AND 1=1 ORDER BY Timestamp)
AND 1=1 ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0
ORDER BY Id

Example Topic JSON

{
  "CursorConfiguration": {
    "FromClause": "[Source Table]",
    "CursorColumn": "Id",
    "BatchSize": 100,
    "FilterCondition": "Name IS NOT NULL",
    "Columns": [
      "Id", "Name"
    ]
  },
  "ReturnDataConfiguration": {
    "CursorAlias": "t",
    "JoinClause": "[Table1] ts ON ts.[id] = t.[id]",
    "FilterCondition": "ts.[id] > 0",
    "OrderByClause": "id",
    "Columns": [
      "ts.[Id]"
      "ts.[Name]",
    ]

  },
  "Delay": 10
}

The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.

Parameter

Description

Example

{
  "databaseType": "TSQL",
  "connectionString": "Server=;Database=;User ID=cinchy;password=example;Trusted_Connection=False;Connection Timeout=30;Min Pool Size=10;",
}

Appendix A

messageKeyExpression

This parameter was added to the Data Polling event stream in Cinchy v5.6.

Each of your Event Listener message keys a message key. By default, this key is unique for every message in the queue.

When the worker processes your Event Listener messages it does so in batches and, for efficiency and to guarantee order, messages that contain the same key won't be processed in the same batch.

The messageKeyExpression property allows you to change the default message key to something else.

Example:

  "MessageKeyExpression": "id"