Cinchy Platform Documentation
Cinchy v5.7
Cinchy v5.7
  • Data Collaboration Overview
  • Release notes
    • Release notes
      • 5.7 Release Notes
      • 5.6 Release Notes
      • 5.5 Release Notes
      • 5.4 Release Notes
      • 5.3 Release Notes
      • 5.2 Release Notes
      • 5.1 Release Notes
      • 5.0 Release Notes
  • Support
  • Glossary
  • FAQ
  • Deployment guide
    • Deploying Cinchy
      • Plan your deployment
        • Deployment architecture
          • Kubernetes architecture
          • IIS architecture
        • Deployment prerequisites
          • Single Sign-On (SSO) integration
            • Enable TLS 1.2
            • Configure ADFS
            • AD Group Integration
      • Kubernetes
        • Disable your Kubernetes applications
        • Change your file storage configuration
        • Configure AWS IAM for Connections
        • Use Self-Signed SSL Certs (Kubernetes)
        • Deploy the CLI (Kubernetes)
      • IIS
  • Upgrade guide
    • Upgrade Cinchy
      • Cinchy Upgrade Utility
      • Kubernetes upgrades
        • v5.1 (Kubernetes)
        • v5.2 (Kubernetes)
        • v5.3 (Kubernetes)
        • v5.4 (Kubernetes)
        • v5.5 (Kubernetes)
        • v5.6 (Kubernetes)
        • v5.7 (Kubernetes)
        • Upgrade AWS EKS Kubernetes version
        • Update the Kubernetes Image Registry
        • Upgrade Azure Kubernetes Service (AKS)
      • IIS upgrades
        • v4.21 (IIS)
        • v4.x to v5.x (IIS)
        • v5.1 (IIS)
        • v5.2 (IIS)
        • v5.3 (IIS)
        • v5.4 (IIS)
        • v5.5 (IIS)
        • v5.6 (IIS)
        • v5.7 (IIS)
      • Upgrading from v4 to v5
  • Guides for using Cinchy
    • User Guide
      • Data Browser overview
      • The Admin panel
      • User preferences
        • Personal access tokens
      • Table features
      • Data management
      • Queries
      • Version management
        • Versioning best practices
      • Commentary
    • Builder Guide
      • Best practices
      • Create tables
        • Attach files
        • Columns
        • Data controls
          • Data entitlements
          • Data erasure
          • Data compression
        • Formatting rules
        • Indexing & partitioning
        • Linking data
        • Table and column GUIDs
        • System tables
      • Delete tables
        • Restore tables, columns, and rows
      • Saved queries
      • CinchyDXD
        • Build the data experience
        • Package the data experience
        • Install the data experience
        • Update the data experience
        • Repackage the data experience
        • Reinstall the data experience
      • Multilingual support
      • Integration guides
    • Administrator Guide
    • Additional guides
      • Monitor and Log on Kubernetes
        • Grafana
        • OpenSearch dashboards
          • Set up Alerts
        • Monitor via ArgoCD
      • Maintenance
      • Cinchy Secrets Manager
      • GraphQL (Beta)
      • System properties
      • Enable Data At Rest Encryption (DARE)
      • MDQE
      • Application experiences
        • Network map
          • Custom node results
          • Custom results in the Network Map
        • Set up experiences
  • API Guide
    • API overview
      • API authentication
      • API saved queries
      • ExecuteCQL
      • Webhook ingestion
  • CQL
    • Overview
      • CQL examples
      • CQL statements overview
        • Cinchy DML statements
        • Cinchy DDL statements
      • Cinchy supported functions
        • Cinchy functions
        • Cinchy system values
        • Cinchy User Defined Functions (UDFs)
          • Table-valued functions
          • Scalar-valued functions
        • Conversion functions
        • Date and Time types and functions
          • Return System Date and Time values
          • Return Date and Time parts
          • Return Date and Time values from their parts
          • Return Date and Time difference values
          • Modify Date and Time values
          • Validate Date and Time values
        • Logical functions
        • Math functions
        • String functions
        • Geometry and Geography data type and functions
          • OGC methods on Geometry & Geography instances
          • Extended methods on Geometry & Geography instances
        • Full Text Search functions
        • Connections functions
        • JSON functions
    • CQL functions master list
  • Meta-Forms
    • Introduction
    • Install Meta-Forms
      • Deploy Meta-Forms (Kubernetes)
      • Deploy Meta-Forms (IIS)
    • Forms data types
    • Meta-Forms Builder Guide
      • Create a dynamic meta-form with tables
      • Create a dynamic meta-form example with Form Designer
      • Add links to a form
      • Rich text editing in forms
  • Data syncs
    • Get started with data syncs
    • IIS installation
      • Prerequisites
      • Install Connections
      • Install the Worker/Listener
      • Install the CLI and the Maintenance CLI
    • Build data syncs
      • Data sync types
      • Design patterns
      • Sync actions
      • Columns and mappings
        • Calculated column examples
      • Advanced settings
        • Filters
        • Variables
        • Auth requests
        • Request headers
        • Post sync scripts
        • Pagination
      • Batch data sync example
      • Real-time sync example
      • Schedule a data sync
      • Connection functions
    • CLI commands list
    • Troubleshooting
    • Data sync sources
      • Cinchy Event Broker/CDC
        • Cinchy Event Broker/CDC XML config example
      • Cinchy Table
        • Cinchy Table XML config example
      • Cinchy Query
        • Cinchy Query XML config example
      • Copper
      • DB2 (query and table)
      • Dynamics 2015
      • Dynamics
      • DynamoDB
      • File-based sources
        • Binary file
        • Delimited file
        • Excel
        • Fixed width file
        • Parquet
      • Kafka Topic
        • Kafka Topic example config
        • Apache AVRO data format
      • LDAP
      • MongoDB collection
        • MongoDB collection source example
      • Mongo event
      • MongoDB collection (Cinchy event)
      • MS SQL Server (query and table)
      • ODBC Query
      • Oracle (query and table)
      • Polling event
        • Polling event example config
      • REST API
      • REST API (Cinchy event)
      • SAP SuccessFactors
      • Salesforce Object (Bulk API)
      • Salesforce platform event
      • Salesforce push topic
      • Snowflake
        • Snowflake source example config
      • SOAP 1.2 web service
    • Data sync destinations
      • Cinchy Table
      • DB2 table
      • Dynamics
      • Kafka Topic
      • MongoDB collection
      • MS SQL Server table
      • Oracle table
      • REST API
      • Salesforce
      • Snowflake table
      • SOAP 1.2 web service
    • Real-time sync stream sources
      • The Listener Config table
      • Cinchy Event Broker/CDC
      • Data Polling
      • Kafka Topic
      • MongoDB
      • Salesforce Push Topic
      • Salesforce Platform Event
  • Other Resources
    • Angular SDK
    • JavaScript SQK
Powered by GitBook
On this page
  • Overview
  • Use case
  • Sample files and code
  • People table creation
  • New Employees table creation
  • Create the data sync
  • Use the Connections UI
  • Use a data sync XML
  • Appendix
  • Manually set the listener config
  • Password Encryption
  • Execution logs
  • Execution errors

Was this helpful?

Export as PDF
  1. Data syncs
  2. Build data syncs

Real-time sync example

PreviousBatch data sync exampleNextSchedule a data sync

Last updated 1 year ago

Was this helpful?

Overview

This example will take you through the creation and execution of a real-time data sync where data will be synced between two Cinchy tables based on real-time changes.

Use case

Your People table captures a view of various personnel information. Any time a new hire is added to the table, you want that information to be immediately synced into the New Employees table. We can solve this use case using the Cinchy Change Data Capture (CDC) function on our tables. This helps you to better keep track of all incoming people within your company.

You can review our documentation on Cinchy Table Sources You can review our documentation on Cinchy Table destinations

Sample files and code

This section has steps on how to:

  • Create the People table.

  • Create the New Employees table.

When you create tables to use with real-time syncs, make sure you turn on the Cinchy Change Data Capture feature through the Design Table > Change Notifications tab. This makes sure you capture real-time updates.

People table creation

  1. Login to your Cinchy platform.

  2. From under My Network, click the Create > Standard Table > From Scratch.

  3. Create a table with the following properties (Image 1):

    Table Details
    Values

    Table Name

    People

    Icon + Colour

    Default

    Domain

    Sandbox

If this domain doesn't exist, either create it or make sure to update this parameter where required during the data sync.

  1. Click Columns in the left hand navigation to create the columns for the table.

  2. Click the "Click Here to Add" button and add the following columns:

    Column Details
    Values

    Column 1

    Column Name: Name

    Data Type: Text

    Column 2

    Column Name: Title

    Data Type: Text

    Column 3

    Column Name: Phone Number

    Data Type: Text

    Column 4

    Column Name: City Data Type: Text

  3. Select Change Notifications in the left hand navigation and select Publish Change Notifications.

  4. Select Save to save your table.

New Employees table creation

  1. Within the Cinchy platform, from under My Network, select Create

  2. Select Table

  3. Select From Scratch

  4. Create a table with the following properties (Image 2):

Table Details
Values

Table Name

New Employees

Icon + Colour

Default

Domain

Sandbox (if this domain doesn't exist, either create it or make sure to update this parameter where required during the data sync)

  1. Select Columns in the left hand navigation to create the columns for the table.

  2. Select the "Click Here to Add" button and add the following columns:

Column Details
Values

Column 1

Column Name: Name

Data Type: Text

Column 3

Column Name: Title

Data Type: Text

  1. Click the Save button to save your table.

Create the data sync

You have two options when you create a data sync in Cinchy.

  1. You can input all the necessary information through the Connections UI. Once saved, this data uploads as an XML file into the Data Sync configurations table.

  2. You can bypass the UI and upload your XML config directly into the Data Sync configuration table yourself.

This example will walk you through both options.

Use the Connections UI

  1. Within your Cinchy platform, navigate to the Connections Experience (Image 3).

  1. In the Info tab, input the name of your data sync. This example uses "New Hires" (Image 4).

Configure the source

As of 5.7, you can now configure the Topic JSON in the Source tab under the Listener section. If you still need to manually configure the Topic JSON, see the appendix for more information.

  1. Navigate to the Source tab.

  2. Under Select a Source, select Cinchy Event Broker (Image 5).

  3. Under Listener, select the People table.

Configure the destination

  1. Navigate to the Destination tab and select Cinchy Table from the drop down (Image 6).

  2. In the Connection section, input the Domain and Table name for your destination. This example uses the Sandbox domain and the People table.

  3. Click Load.

  1. Select the columns that you wish to use in your data sync (Image 8). These will be the columns that your source syncs to your target. This example uses the Name and Title columns. You also have many Cinchy system table available to use.

  2. Click Load.

  1. The Connections experience will try to automatically map your source and destination columns based on matching names. In the below screenshot, it has been able to correctly match the Name and Title columns (Image 8).

Sync actions

  1. Navigate to the Sync Actions tab. Two options are available for data syncs: Full File and Delta. For this example, select Full File.

Full load processing means that the entire amount of data is imported iteratively the first time a data source is loaded into the data studio. Delta processing means loading the data incrementally (loading the source data at specific pre-established intervals).
  1. Set the following parameters (Image 9):

    Parameter
    Description
    Example

    Sync Key Column Reference

    The SyncKey is used as a unique key reference when syncing the data from the data source into the Cinchy table. It's used to match data between the source and the target. This allows for updates to occur on changed records.

    Name

    New Record Behaviour

    This defines the action taken when a new record is found in the sync source. This can be either Insert or Ignore.

    Insert

    Dropped Record Behaviour

    This defines the action taken when a dropped record is found in the sync source.

    This can be either Delete, Ignore, or Expire.

    Delete

    Changed Record Behaviour

    This defines the action taken when a changed record is found in the sync source.

    This can be either Update, Ignore, or Conditional.

    Update

  2. Navigate to the Permissions tab. Here you will define your group access controls for your data sync. You can set this how you like. This example gives all users access to Execute, Write, and Read our sync (Image 10).

Any groups given Admin Access will have the ability to Execute, Write, and Read the data sync.
<figure><img src="../../.gitbook/assets/image (346).png" alt=""><figcaption><p>Image 10: Sync Permissions</p></figcaption></figure>
  1. Click Save.

  2. Test your data sync by adding a new row to your People table. Ensure that the data is then updated across to the New Employees table (Images 11 & 12).

Use a data sync XML

Instead of using the Connections UI, you can also set up a data sync by uploading a correctly formatted XML into the Data Sync Configs table within Cinchy.

We only recommend doing so once you have a good understanding of how data syncs work. Not all sources and targets follow the same XML pattern.

Blank XML example

The below XML shows what a blank data sync could look like for our Cinchy Event Broker/CDC to Cinchy Table real-time sync with full file synchronization.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="" version="1.0.0" xmlns="http://www.cinchy.co">
    <CinchyEventBrokerDataSource runQuery="false">
        <Schema>
            <Column name="" dataType="" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </CinchyEventBrokerDataSource>
    <CinchyTableTarget reconcileData="true" domain="" table="" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="" targetColumn=""/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name=""/>
        </SyncKey>
        <NewRecordBehaviour type=""/>
        <DroppedRecordBehaviour type=""/>
        <ChangedRecordBehaviour type=""/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Filled XML example

The below filled XML example matches the Connections UI configuration made in Use the Connections UI . You can review the parameters used in the table below.

Parameter
Description
Example

Name

The name of your data sync.

New Hires

Column Name

The name(s) of the source columns that you wish to sync.

"Name" "Title"

Column Data Type

The data type that corresponds to our selected source columns.

"Text"

Domain

The domain of your Cinchy Target table.

Sandbox

Table

The name of your Cinchy Target table.

New Employees

Column Mapping Source Column

The name(s) of the source columns that you are syncing.

"Name" "Title"

Column Mapping Target Column

The name(s) of the target column as it maps to the specified source column.

"Name" "Title"

Sync Key Column Reference Name

The SyncKey is used as a unique key reference when syncing the data from the data source into the Cinchy table. It's used to match data between the source and the target. This allows for updates to occur on changed records.

"Name"

New Record Behaviour Type

This defines what will happen when new records are found in the source.

INSERT

Dropped Record Behaviour Type

This defines what will happen when dropped records are found in the source.

DELETE

Changed Record Behaviour Type

This defines what will happen when changed records are found in the source.

UPDATE

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="New Hires" version="1.0.0" xmlns="http://www.cinchy.co">
    <CinchyEventBrokerDataSource runQuery="false">
        <Schema>
            <Column name="Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Title" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </CinchyEventBrokerDataSource>
    <CinchyTableTarget reconcileData="true" domain="sandbox" table="New Employees" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="Name" targetColumn="Name"/>
            <ColumnMapping sourceColumn="Title" targetColumn="Title"/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name="Name"/>
        </SyncKey>
        <NewRecordBehaviour type="INSERT"/>
        <DroppedRecordBehaviour type="DELETE"/>
        <ChangedRecordBehaviour type="UPDATE"/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Use the data sync XML

  1. Once you have completed your Data Sync XML, navigate to the Data Sync Configurations table in Cinchy (Image 13).

  1. In a new row, paste the Data Sync XML into the Config XML column.

  2. Define your group permissions in the applicable columns. This example gives All Users the Admin Access.

The Name and Config Version columns will be auto populated as they values are coming from the Config XML.

Be sure when you are pasting into the Config XML column that you double click into the column before pasting, otherwise each line of the XML will appear as an individual record in the Data Sync Configurations table.

Parameter
Description
Example

-s (server)

Required. The full path to the Cinchy server without the protocol (cinchy.co/Cinchy).

"pilot.cinchy.co/Training/Cinchy/"

-u (user id)

Required. The user id to login to Cinchy that has execution access to the data sync.

"admin"

-p (password)

Required. The password of the above User ID parameter. This can optionally be encrypted. For a walkthrough on how to use the CLI to encrypt the password, refer to the Appendix section.

"DESuEGqfffsamx55yl256hjuPYxa4ncc+5+bLkoVIFpgs0Lq6hkcU="

-f (feed)

Required. The name of the Data Sync Configuration as defined in Cinchy

"Contact Import"

  1. Launch PowerShell and navigate to the Cinchy CLI directory.

  2. Enter and execute the following into PowerShell:

.\Cinchy.CLI.exe syncdata -s "pilot.cinchy.co/Training/Cinchy/" -u "admin" -p "DESuEGqmx55yl2PYxa4ncc+5+bLkoVIFpgs0Lq6hkcU=" -f "Contact Import"
  1. Test your data sync by adding a new row to your People table. Ensure that the data is then updated across to the New Employees table (Images 14 & 15).

Appendix

Manually set the listener config

  1. Navigate to the Listener Config table in Cinchy (Image 16).

  1. In a new row, add in your listener config data using the below table as a guide:

Column
Description
Example

Name

The name of your Listener Config

New Hire Sync

Event Connector Type

Select from the drop-down list which event stream you are listening in on.

Cinchy CDC

Topic

This column expects a JSON value with certain specific information. Please review the Topic Column table below for details.

Connection Attributes

This section isn't required for data syncs using the Cinchy Event Broker/CDC, so we can just enter `{}`

{}

Status

This sets where your config is Enabled or Disabled. You can leave this blank until you want to turn on your config.

Data Sync Config

The name of the Data Sync Config you created in the Connections UI or via XML.

New Hires

Auto Offset Reset

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 start reading any events.

Latest

                                                                                |

Topic JSON example

{
  "tableGuid": "3daba5da-5e07-4d35-8d7c-d451a2c9068e",
  "fields": [
    {
      "column": "Name"
    },
    {
      "column": "Title"
    }
  ]
}

Password Encryption

Before executing the data sync command, encrypt the password using PowerShell.

To encrypt a password using PowerShell, complete the following:

  1. Launch PowerShell and navigate to the Cinchy CLI directory (note, you can always type PowerShell in the windows explore path for the Cinchy CLI directory).

  2. Enter the following into PowerShell .\Cinchy.CLI.exe encrypt -t "password".

  3. Hit enter to execute the command.

  4. Copy the password so you have it accessible at batch execution time.

You will need to replace password with your specific password.

Execution logs

The Execution Log table is a system table in Cinchy that logs the outputs of all data syncs. You can always review the entries in this table for information on the progression of your syncs (Image 17).

Execution errors

The Execution Errors table is a system table in Cinchy that logs any errors that may occur in a data sync (Image 18). Any data sync errors will also be logged in the temp directory outlined in the data sync execution command (-d "C:\Cinchy\temp")

For Return Columns, select All.

Make sure it's set to Enabled. Your real-time data sync should now be listening to your People table ready to push updates to your New Employees table.

Ensure it's set to Enabled. Your real-time data sync should now be listening to your People table ready to push updates to your New Employees table.

To execute your Data Sync you will use the CLI. If you don't have this downloaded,

In this example we will be using the following Data Sync Commands, however, for the full list of commands click .

This section provides information on how to manually set up the listener config using the Listener Config table. While this example shows how to configure the sync using the Cinchy Event Broker/CDC, Cinchy also supports other Event Stream Sources. For more information, see .

{
    "tableGuid": "3daba5da-5e07-4d35-8d7c-d451a2c9068e",
    "fields": [
        {
            "column": "Name"
        },
        {
            "column": "Title"
        }
    ],
}
here.
here.
refer to the CLI commands list page
here
the supported real-time sync stream sources
Navigate to the Cinchy Listener Config table and validate your configuration.
Navigate to the Cinchy Listener Config table and set up your configuration.
Image 1: The People table
Image 2: New Employees Table
Image 3: The Connections Experience
Image 4: The Info Tab
Image 6: Load the Metadata
Image 7: Select your columns
Image 8: Map your columns
Image 9: Sync Behaviour
Image 11: Test your Sync
Image 12: Test your Sync
Image 13: Data Sync Config Table
Image 14: Test your Sync
Image 15: Test your Sync
Image 16: Listener Config table
Image 17: Execution Logs
Image 18: Execution Errors table
Image 5: Configure Topic JSON