5️⃣
Cinchy Platform Documentation
Cinchy v5.8
Cinchy v5.8
  • Data Collaboration Overview
  • Release notes
    • Release notes
      • 5.9 release notes
      • 5.8 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)
        • v5.8 (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)
        • v5.8 (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
        • Overview
        • DXD workflow
        • Package the data experience
        • Install the data experience
        • Release package
        • Changelog
        • References
          • Cinchy DXD CLI reference
          • Data Experience Definitions table
          • Data Experience Reference table
      • 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)
      • 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 reference 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
      • Install Connections
      • Install the Worker/Listener
      • Install the Connections 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
    • 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
      • SOAP 1.2 web service (Cinchy Event Triggered)
    • 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
    • CLI commands list
    • Troubleshooting
  • Other Resources
    • Angular SDK
    • JavaScript SQK
Powered by GitBook
On this page
  • Overview
  • Sample files and code
  • Cinchy Table XML
  • Data file
  • Create the data sync
  • Use the Connections UI
  • Use a data sync XML
  • Appendix
  • Password encryption
  • Execution Log table
  • Execution Error table
  1. Data syncs
  2. Build data syncs

Batch data sync example

PreviousPaginationNextReal-time sync example

Last updated 1 year ago

Overview

This example will take you through the creation and execution of a batch data sync where data will be loaded into the Cinchy via a CSV. In this example, we will be loading information into the People table in Cinchy. This is a self-contained example you can recreate in any Cinchy environment without dependencies.

Use Case: You have historically maintained a record of all your employees in a spreadsheet. Knowing that this significantly hinders your data and data management capabilities, you want to sync your file into Cinchy. Once synced, you can manage your employee information through the Cinchy data browser, instead of through a data silo.

For more information, see the documentation on .

Sample files and code

This section contains:

  • The People Table XML schema.

  • A sample source CSV data file to load into Cinchy.

Cinchy Table XML

To create the People table used in this example, you can use the below is the XML. You can also create the table manually, as shown in the section below.

<?xml version="1.0" encoding="utf-16"?>
<Model xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="People Model" version="1.0.0" xmlns="http://www.cinchy.co">
  <Schema>
    <Tables>
      <Table name="People" domain="Sandbox" guid="d5e6409c-471e-4a19-ba9c-df6ff2b606aa" icon="fa fa-table" iconColour="#002060">
        <Columns>
          <Text name="Name" guid="d37935e7-0ebb-4267-ad10-758067221951" allowLinking="false" />
          <Text name="Title" guid="afebfe66-1d59-445d-9373-0f8bc2a5e804" allowLinking="false" />
          <Text name="Company" guid="aaa5aeb5-4be2-4dc7-89a6-2237ae5b9c74" allowLinking="false" />
        </Columns>
        <UniqueConstraints />
      </Table>
    </Tables>
  </Schema>
</Model>

Manual table creation

  1. Log in to your Cinchy platform.

  2. From under My Network, click the create button.

  3. Select Table.

  4. Select From Scratch.

  5. 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. 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 2

Column Name: Title

Data Type: Text

Column 3

Column Name: Company

Data Type: Text

  1. Select Save to save your table.

Data file

You can download the sample CSV file used in this example below.

If you are downloading this file to recreate this exercise, the file path and the file name must be the following:

C:\Data\contacts.csv

You can also update the path parameter in the data sync configuration to match the file path and name of your choosing.

The source file contains the following information which will sync into the target Cinchy table (Image 2).

As we can see, the file has the following columns:

  • First Name

  • Last Name

  • Email Address

  • Title

  • Company

The People table created only has the following columns:

  • Name

  • Title

  • Company

When syncing the data from the source (CSV file) to the target (Cinchy People table), the batch data sync must consider the following:

  • The first and last name from the source must merge into one column in the target (Name).

  • The email address from the sources isn't a column in the target, so this column won't sync into the target.

  • The title column will be an exact match from source to target.

  • The company column will also be an exact match from source to target.

Create the data sync

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

  1. You can input all of your necessary information through the intuitive Connections UI. Once saved, all of this data is uploaded as an XML into the Data Sync configurations table.

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

This example will walk you through option one.

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 "Contact Import" (Image 4).

  1. Since this is a local file upload, we also need to set a Parameter. This value will be referenced in the "path" value of the Load Metadata box in step 5. For this example, we will set it to filepath (Image 5).

  1. Navigate to the Source tab. This example uses the .CSV file you downloaded at the beginning of this example as our source.

  2. Under Select a Source, select Delimited File (Image 6).

  1. The "Load Metadata" box will appear; this is where you will define some important values about your source needed for the data sync to execute. Using the below table as your guide, fill in your metadata parameters (Image 7):

Parameter
Description
Example

Source

The source location of your file. This can be either Local, S3, or Azure Blob Storage.

Local

Delimiter

The type of delimiter on your source file.

Since our file is a CSV, the delimiter is a comma, and we uses the ',' value.

Text Qualifier

A text qualifier is a character used to distinguish the point at which the contents of a text field should begin and end.

"&quot;

Header Rows to Ignore

The number of records from the top of the file to ignore before the data starts (includes column header).

1

Path

The path to your source file (See step 3).

@filepath

Choose File

This option will appear once you've correctly set your Path value.

Upload the sample CSV for this example.

  1. Click Load.

  2. In the Available Columns pop-up, select all of the columns that you want to import from the CSV. For this example, we will select them all (noting, however, that we will only map a few of them later) (Image 8).

  1. Click Load.

  2. Once you load your source, the schema section of the page will auto populate with the columns that you selected in step 7 (Image 9). Review the schema to ensure it has the correct Name and Data Type. You may also choose to set any Aliases or add a Description.

  1. Navigate to the Destination tab and select Cinchy Table from the drop down.

  2. In the Load Metadata pop-up, input the Domain and Table name for your destination. This example uses the Sandbox domain and the People table (Image 10).

  3. Select Load Metadata.

  1. Select the columns that you wish to use in your data sync (Image 11). These will be the columns that your source syncs to. This example uses the Name, Title, and Company columns. Note that you will have many Cinchy system table available to use as well. Click Load.

  1. The Connections experience will attempt to automatically map your source and destination columns based on matching names. In the below screenshot, it matched the "Company" and "Title" columns (Image 12). The "Name" target column isn't an exact match for any of the source columns, so you must match that one manually.

  1. Select "First Name" from the Source Column drop down to finish mapping our data sync (Image 13).

  1. Navigate to the Sync Actions tab. Sync actions have two options: Full File and Delta. In 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 14):

Parameter
Description
Example

Sync Key Column Reference

The sync key is a unique key reference when syncing the data from the data source into the Cinchy table. Use this 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

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

Any groups given Admin Access will have the ability to Execute, Write, and Read the data sync.

  1. Navigate to the Jobs tab. Here you will see a record of all successful or failed jobs for this data sync.

  2. Select "Start a Job" (Image 16).

  1. Load your sample .CSV file in the pop-up window (Image 17).

  1. The job will commence. The Execution window that pops up will help you to verify that your data sync is progressing (Image 18).

  1. Navigate to your destination table to ensure that your data populated correctly (Image 19).

Use a data sync XML

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

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

The example below is the completed batch data sync configuration. Review the XML and then refer to the filled XML example.

Blank XML example

The below XML shows a blank data sync for a Delimited File source to a Cinchy Table target.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
        <Parameter name=""/>
    </Parameters>
    <DelimitedDataSource source="" path="" delimiter="" textQualifier="" headerRowsToIgnore="" encoding="" useHeaderRecord="">
        <Schema>
            <Column name="" dataType="" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </DelimitedDataSource>
    <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.

Contact Import

Parameter

Since this is a local file upload, we also need to set a Parameter. This value will be referenced in the "path" value of the Load Metadata box

Parameter

Source

Defines whether your source is Local (PATH), S3, or Azure.

PATH

Path

Since this is a local upload, this is the path to your source file. In this case, it's the value that was set for the "Parameter" value, preceded by the '@' sign.

@Parameter

Delimiter

The delimiter type on your source file.

Since our file is a CSV, the delimiter is a comma, and we uses the ',' value.

Text Qualifier

A text qualifier is a character used to distinguish the point at which the contents of a text field should begin and end.

"&quote;

Header Rows to Ignore

The number of records from the top of the file to ignore before the data starts (includes column header).

1

Column Name

The name(s) of the source columns that you wish to sync. In this example there are more selected columns than mapped to show how Connections ignores unmapped data.

"First Name" "Last Name" "Email Address: "Title" "Company"

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.

People

Column Mapping Source Column

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

"Company" "Title" "First Name"

Column Mapping Target Column

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

"Company" "Title" "Name"

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. Use it 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="Contact Import" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
        <Parameter name="parameter"/>
    </Parameters>
    <DelimitedDataSource source="PATH" path="@parameter" delimiter="," textQualifier="&quot;" headerRowsToIgnore="1" encoding="UTF8" useHeaderRecord="false">
        <Schema>
            <Column name="First Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Last Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Email Address" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Title" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Company" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </DelimitedDataSource>
    <CinchyTableTarget reconcileData="true" domain="sandbox" table="People" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="Company" targetColumn="Company"/>
            <ColumnMapping sourceColumn="Title" targetColumn="Title"/>
            <ColumnMapping sourceColumn="First Name" targetColumn="Name"/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name="Name"/>
        </SyncKey>
        <NewRecordBehaviour type="INSERT"/>
        <DroppedRecordBehaviour type="DELETE"/>
        <ChangedRecordBehaviour type="UPDATE"/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Using the data sync XML

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

  1. In a new row, paste the Data Sync XML into the Config XML column (Image 21).

  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.

Tip: Click on the below image to enlarge it.

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.

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

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

  3. 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. Once executed, navigate to your destination table to validate that your data synced correctly (Image 22).

Appendix

Password encryption

The following tables show the necesary information to encrypt a password from the CLI.

Actions

Command
Description

encrypt

Generate an encrypted string.

exportdata

Exports data from a Cinchy saved query to a delimited file.

syncdata

Synchronizes data in a Cinchy table with a specified source using a batch job definition.

help

Display more information on a specific command.

version

Display version information.

Arguments

Command/Flag
Description

-h, --https

Flag indicating connections to Cinchy should be over https.

-s, --server

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

-u, --userid

Required. The user id for accessing Cinchy.

-p, --password

Required. The password of the specified user.

-t, --text

Required. The full text to encrypt.

-a, --tls

TLS protocol version.

--help

Display this help screen.

--version

Display version information.

Example

dotnet Cinchy.Connections.CLI.dll -h -s sandbox.cinchy.net/ts-mssql-1 -u Simon -p cinchy -t cinchy

Execution Log table

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

Execution Error table

The Execution Errors table is a system table in Cinchy that logs any errors that may occur in a data sync (Image 24). Any data sync errors log to the temp directory outlined in the data sync execution command. For example, -d "C:\Cinchy\temp".

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 .

Delimited File Sources
refer to the CLI commands list page.
here
264B
People Load File.csv
Sample People Load File
Image 1: The People table
Image 2: The source file.
Image 3: The Connections Experience
Image 4: Name your data sync
Image 5: Set your Parameter
Image 6: Select a Source
Image 7: Load Metadata
Image 8: Available Columns
Image 9: Source Schema
Image 10: Load Metadata
Image 11: Select your columns
Image 12: Column Mappings
Image 13: Column Mapping
Image 14: Sync Behaviour
Image 15: Permissions
Image 16: Start your Job
Image 17: Load your CSV
Image 18: Job execution
Image 19: Confirming your changes
Image 20: Data Sync Configurations table
Image 21: Config XML
Image 22: Validate your sync
Image 23: Execution Log
Image 24: Execution Errors