v5.0 - v5.5 Data Sync Documentation
  • Overview
  • Release Notes
    • V4.5
    • V4.6
    • v4.7
    • v4.8
    • v4.9
    • v4.10
    • v4.12
    • v4.13
  • v4 Connections Installation Guide
    • v5 Connections and CLI Installation Guide
  • v4 Worker/Listener Installation Guide
    • v5 Worker/Listener Installation Guide
  • Upgrades & Config Changes
  • Builder Guide
    • Overview
    • Types of Data Syncs
    • Subscribing to Event Streams
      • Supported Stream Sources
        • Amazon Simple Queue Service (SQS)
        • Cinchy Change Data Capture
        • Data Polling
        • Kafka Topic
        • MongoDB
        • Salesforce
          • Push Topic
          • Platform Event
    • Configuring a Data Sync
      • Connections Experience & XML Config Reference
        • Info
          • Parameters
            • String Escape
        • Source Config Reference
          • Schema Columns
            • Calculated Column Examples
          • Auth Requests
          • Request Headers
          • Pagination
          • Source Filter
        • Target Destination Config Reference
          • Column Mappings
          • Target Destination Filter
        • Sync Behaviour
        • Post Sync
        • Permissions
        • Jobs
        • Connections Usage Example
      • Data Validation
      • Transformations
        • StringReplacement
      • Supported Data Sources
        • File Based Source
          • Binary File
            • Binary File Data Source Example
          • Delimited File
            • Delimited File Data Source XML Example
          • Fixed Width File
          • Excel
            • Excel Source XML Example
        • Cinchy Event Broker/CDC
          • Cinchy Event Broker/CDC XML Config Example
        • Cinchy Table
          • Cinchy Table XML Config Example
        • Cinchy Query
          • Cinchy Query Data Source Example
        • Copper
        • DB2
        • Dynamics 2015
        • Dynamics
        • DynamoDB
        • Kafka Topic
          • Apache AVRO Data Format
          • Kafka Topic Source Example
        • LDAP
        • MongoDB Collection
          • MongoDB Collection Source XML Example
        • MongoDB Collection (Cinchy Event Triggered)
        • MS SQL Server Query
        • MS SQL Server Table
        • ODBC Table
        • ODBC Query
        • Oracle Table
        • Oracle Query
        • Parquet
        • Polling Event
          • Polling Event Example
        • REST API (Cinchy Event Triggered)
        • REST API
          • REST API XML Example
        • SAP SuccessFactors
        • Salesforce Object (Bulk API)
        • Salesforce Platform Event
        • Salesforce Push Topic
        • Snowflake
          • Snowflake Source XML Example
        • SOAP 1.2 Web Service
      • Supported Sync Targets
        • Cinchy Table
        • DB2 Table
        • Dynamics
        • Kafka Topic
        • MongoDB Collection (Column Based)
        • MS SQL Server Table
        • Oracle Table
        • REST API
        • Salesforce
        • Salesforce Object
        • Snowflake Table
          • Snowflake Table Target XML Example
        • SOAP 1.2 Web Service
    • Common Design Patterns
    • Testing a Data Sync
    • Promoting a Data Sync
    • Scheduling a Data Sync
    • CLI Command List
    • Connections Functions
    • Monitoring
  • Cinchy Platform Documentation
Powered by GitBook
  1. Builder Guide
  2. Subscribing to Event Streams
  3. Supported Stream Sources

Data Polling

This page outlines how to set up a Listener Config for data polling.

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

2. Data Polling Listener Config

The following parameters should be included in your listener configuration entry when setting up a real time sync using data polling on your SQLServer or DB2 server.

2.1 Topic Column

Name

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 is not unique.

Example basic query:

"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 is 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 is not 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, we have 2 related tables, but want to show the contents of one of them based on the 'CursorColumn' from a second table. Since Timestamp values are not 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.:

Example complex query:

"CursorAlias"

Mandatory. This is the alias for a subquery result table. It is 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"

"id"

Example Config

{
  "CursorConfiguration": {
    "FromClause": "[Source Table]",
    "CursorColumn": "Id > 0",
    "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
  "MessageKeyExpression": "id"
}

2.2 Connections Attributes Column

Name

Description

"databaseType"

Mandatory. TSQL or DB2

"connectionString"

Mandatory. This should be the connection string for your data source.

Example Config

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

3. 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 will not be processed in the same batch.

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

Example:

  "MessageKeyExpression": "id"
PreviousCinchy Change Data CaptureNextKafka Topic

Last updated 2 years ago

Was this helpful?

Optional, but recommended to mitigate data loss. for more information on this parameter.

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
FROM
(
...
) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id]
WHERE ts.[Id] > 0
ORDER BY Id
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
See here