Polling event

Overview

Version 5.4 of the Cinchy platform introduced data polling, a source option which uses the Cinchy Event Listener to continuously monitor and sync data entries from your Oracle, SQL Server, 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.

The Polling Event source supports real-time syncs.

The Polling Event Source supports Oracle, DB2 and SQL Server databases.

Info tab

You can find the parameters in the Info tab below (Image 1).

Values

Parameter	Description	Example
Title	Mandatory. Input a name for your data sync	Polling Event Sync
Variables	Optional. Review our documentation on Variables here for more information about this field.
Permissions	Data syncs are role based access systems where you can give specific groups read, write, execute, and/or all of the above with admin access. Inputting at least an Admin Group is mandatory.

Image 1: The Info Tab

Source tab

The following table outlines the mandatory and optional parameters you will find on the Source tab (Image 2).

Source Details

The following parameters will help to define your data sync source and how it functions.

Parameter	Description	Example
Source	Mandatory. Select your source from the drop down menu.	Polling Event

Listener Configuration

To set up a real-time sync, you must configure your Listener values. You can do so through the Connections UI.

Note that If there is more than one listener associated with your data sync, you will need to configure the addition listeners via the Listener Configuration table.

Reset behaviour

Parameter	Description	Example
Auto Offset Reset	Earliest, Latest or None. In the case where the listener is started and either there is no last message ID, or when the last message ID is invalid (due to it being deleted or it's just a new listener), it will use this column as a fallback to determine where to start reading events from. Earliest will start reading from the beginning on the queue (when the CDC was enabled on the table). This might be a suggested configuration if your use case is recoverable or re-runnable and if you need to reprocess all events to ensure accuracy. Latest will fetch the last value after whatever was last processed. This is the typical configuration. None won't read or start reading any events. You are able to switch between Auto Offset Reset types after your initial configuration through the process outlined here.	None

Optional AppSettings configurations

• DataPollingConcurrencyIndex: This property allows only a certain number of threads to run queries against the source database, which works to reduce the load against the database.

  • The default number of threads is set to 12.

  • To configure this property, navigate to your appSettings.json deployment file > "DataPollingConcurrencyIndex": <numberOfThreads>

• QueueWriteConcurrencyIndex: This property allows only a certain number of threads to be concurrently sending messages to the queue. This works to provide a more consistent batching by the worker and reduce your batching errors. run queries against the source database, which works to reduce the load against the database.

  • The default number of threads is set to 12.

  • To configure this property, navigate to your appSettings.json deployment file > "QueueWriteConcurrencyIndex": <numberOfThreads>.

  • Note that this index is shared across all listener configs, meaning that if it's set to 1 only one listener config will be pushing the messages to the queue at a single moment in time.

// App Settings JSON Example
// Example of the configurable propeties: DataPollingConcurrencyIndex (set to "1" and QueueWriteConcurrencyIndex (set to "1")
"AppSettings": {
    "GetNewListenerConfigsInterval": "",
    "StateFileWriteDelaySeconds": "",
    "KafkaClientConfig": {
      "BootstrapServers": ""
    },
    "KafkaRealtimeDatasyncTopic": "",
    "KafkaJobCancellationTopic": "",
    "DataPollingConcurrencyIndex":  1,
    "QueueWriteConcurrencyIndex":  1
  }

Topic JSON

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

Parameter	Description	Example
CursorConfiguration	Mandatory. The parameters here are used in a basic query which searches for all records in a particular table. Note that in our example we need to use a sub-query to prevent an infinite loop if the "CursorColumn" parameter isn't unique.	Example basic query: SELECT Id, Name 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. This example has 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. In `ReturnDataConfiguration`, our parameters area of concern is everything outside of first open parenthesis `(` and last closing parenthesis `)`. For 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 Timestamp > '2022-11-18 11:34:09 AM' AND Timestamp <= '2022-11-19 11:34:09 AM' 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
CursorConfiguration.CursorColumnDataType	Mandatory. This property works in tandem with an update that ensures that the database query always moves the offset, regardless of if the query returned the records or not—this helps to ensure that the performance of the source database isn't being weighed down by constantly running heavy queries on a wide range of records when the queries returned no data. This value of this mandatory property must match the column type of the source database system for proper casting of parameters.	int
CursorConfiguration. Distinct	Mandatory. This property is a true/false Boolean type that, when set to true, applies a distinct clause on your query to avoid any duplicate records.	true

**Example Topic JSON**

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

  },
  "Delay": 10
}

Connection Attributes

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	Mandatory. TSQL, Oracle, or DB2	TSQL
connectionString	Mandatory. This should be the connection string for your data source.

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

Schema

The Schema section is where you define which source columns you want to sync in your connection. You can repeat the values for multiple columns.

Parameter	Description	Example
Name	Mandatory. The name of your column as it appears in the source.	Name
Alias	Optional. You may choose to use an alias on your column so that it has a different name in the data sync.
Data Type	Mandatory. The data type of the column values.	Text
Description	Optional. You may choose to add a description to your column.

Select Show Advanced for more options for the Schema section.

Parameter	Description	Example
Mandatory	If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected If just Mandatory is checked on a column, then all rows are synced with the execution log status of failed, and the source error of "Mandatory Rule Violation" If just Validated is checked on a column, then all rows are synced.
Validate Data	If both Mandatory and Validated are checked on a column, then rows where the column is empty are rejected If just Validated is checked on a column, then all rows are synced.
Trim Whitespace	Optional if data type = text. For Text data types, you can choose whether to trim the whitespace._
Max Length	Optional if data type = text. You can input a numerical value in this field that represents the maximum length of the data that can be synced in your column. If the value is exceeded, the row will be rejected (you can find this error in the Execution Log).

You can choose to add in a Transformation > String Replacement by inputting the following:

Parameter	Description	Example
Pattern	Mandatory if using a Transformation. The pattern for your string replacement.
Replacement	What you want to replace your pattern with.

Note that you can have more than one String Replacement

Filter

You have the option to add a source filter to your data sync. Please review the documentation here for more information on source filters.

Image 2: The Source Tab

Next steps

• Configure your Destination

• Define your Sync Actions.

• Add in your Post Sync Scripts, if required.

• If more than one listener is needed for a real-time sync, configure it/them via the Listener Config table.

• To run a real-time sync, enable your Listener from the Execution tab.

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"