Batch Data Sync Example
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 and can be replicated in any Cinchy environment without dependencies.
Example Use Case: You have historically maintained a record of all of 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.
This section contains:
- The People Table XML schema. Alternatively, this can be manually created through the Cinchy Data Browser
- A sample source CSV data file to load into Cinchy.
To create the People table used in this example, you can use the below is the XML Alternatively, you can create the table manually using the steps in section 2.1.1.
<?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>
- 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 does not exist please either create it or make sure to update this parameter where required during the data sync) |
Image 1: The People table
6. Click Columns in the left hand navigation to create the columns for the table.
7. Click the "Click Here to Add" button and aadd 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 |
9. Click the Save button to save your table.
The sample CSV file used in this example can be downloaded below.
People Load File.csv
264B
Text
Sample People Load File
Please note, if you are downloading this file to recreate this exercise the file path and the file name will need to be:
C:\Data\contacts.csv
Alternatively, you can 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 be synced into our target Cinchy table (Image 2).
Image 2: The source file.
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 our source (CSV file) to our target (Cinchy People table) our batch data sync will need to take into consideration the following:
- The first and last name from the source will need to end up merged into one column in our target (Name)
- Email address from our sources is not a column in the target, therefore his column will not be synced into the target
- The title column will be a one to one match from source to target
- The company column will also be a one to one match from source to target
There are two options when you want to create a data sync in Cinchy.
- 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.
- Or, you can bypass the UI and upload your XML config directly into the Data Sync configuration table yourself.
This example will walk you through option one.
- 1.Within your Cinchy platform, navigate to the Connections Experience (Image 3).
Image 3: The Connections Experience
- 2.In the Info tab, input the name of your data sync. For this example we are using "Contact Import" (Image 4).
Image 4: Name your data sync
- 3.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).
Image 5: Set your Parameter
- 3.Navigate to the Source tab. As a reminder, we are using the .CSV file you downloaded at the beginning of this example as our source.
- 4.Under "Select a Source", choose Delimited File (Image 6).
Image 6: Select a Source
- 5.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. | "" |
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. In this case, it is the Parameter that was set in step 3. | @filepath |
Choose File | This option will appear once you've correctly set your Path value. | Upload the sample CSV for this example. |
Image 7: Load Metadata
- 6.Click Load.
- 7.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).
Image 8: Available Columns
- 8.Click Load.
- 9.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.
Image 9: Source Schema
- 10.Navigate to the Destination tab and select Cinchy Table from the drop down.
- 11.In the Load Metadata pop-up, input the Domain and Table name for your destination. In this example, we are using the Sandbox domain and the People table (Image 10).
- 12.Click Load.
Image 10: Load Metadata
- 12.Select the columns that you wish to use in your data sync (Image 11). These will be the columns that your source syncs to. In this example, we are using the Name, Title, and Company columns. Note that you will have many Cinchy system table available to use as well. Click Load.
Image 11: Select your columns
- 13.The Connections experience will attempt to automatically map your source and destination columns based on matching names. In the below screenshot, it has been able to correctly match the "Company" and "Title" columns (Image 12). The "Name" target column is not a 1:1 match for any of our source columns, so we must match that one manually.
Image 12: Column Mappings
- 14.Select "First Name" from the Source Column drop down to finish mapping our data sync (Image 13).
Image 13: Column Mapping
- 15.Navigate to the Sync Behaviour tab. There are two options for data syncs: 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, on the other hand, means loading the data incrementally, loading the source data at specific pre-established intervals.
- 16.Set the following parameters (Image 14):
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 is 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 what action is taken when a new record is found in the sync source. This can be either Insert or Ignore. | Insert |
Dropped Record Behaviour | This defines what action is 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 what action is taken when a changed record is found in the sync source. This can be either Update, Ignore, or Conditional. | Update |
Image 14: Sync Behaviour
- 17.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. For this example, we are giving all user 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.
Image 15: Permissions
- 18.Navigate to the Jobs tab. Here you will see a record of all successful or failed jobs for this data sync.
- 19.Select "Start a Job" (Image 16).
Image 16: Start your Job
- 20.Load your sample .CSV file in the pop-up window (Image 17).
Image 17: Load your CSV
- 21.The job will commence. The Execution window that pops up will help you to verify that your data sync is progressing (Image 18).
Image 18: Job execution
- 22.Navigate to your destination table to ensure that your data populated correctly (Image 19).
Image 19: Confirming your changes
In lieu 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 recommend only doing so once you have a good grasp on how data sync work. Note that not all sources/targets follow the same XML pattern.
Below is the completed batch data sync configuration for this example. Review the XMLs and then proceed to section 3.2.3 for further instructions.
The below XML shows what a blank data sync could look like 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>
The below filled XML example matches the Connections UI configuration we made in step 3.1. 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 is the value that was set for the "Parameter" value, preceded by the '@' sign. | @Parameter |
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. | ""e; |
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. Note that in this example we have selected more columns than we will map, in order 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. It is 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="Contact Import" version="1.0.0" xmlns="http://www.cinchy.co">
<Parameters>
<Parameter name="parameter"/>
</Parameters>
<DelimitedDataSource source="PATH" path="@parameter" delimiter="," textQualifier=""" 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>
- 1.Once you have completed your Data Sync XML, navigate to the Data Sync Configurations table in Cinchy (Image 20).
Image 20: Data Sync Configurations table
- 2.In a new row, paste the Data Sync XML into the Config XML column (Image 21).
- 3.Define your group permissions in the applicable columns. In our example, we have given 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.
Image 21: 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.
- 3.To execute your Data Sync you will use the CLI. If you do not have this downloaded, refer to the documentation here.
- 4.In this example we will be using the following Data Sync Commands, however, for the full list of commands click here.
Parameter | Description | Example |
|---|---|---|
-s (server) | Required. The full path to the Cinchy server without the protocol (e.g. cinchy.co/Cinchy). | "pilot.cinchy.co/Training/Cinchy/" |
-u (userid) | 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" |
- 5.Launch Powershell and navigate to the Cinchy CLI directory.
- 6.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"
- 7.Once executed, navigate to your destination table to validate that your data synced correctly (Image 22).
Image 22: Validate your sync
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 (e.g. notepad, Visual Studio Code, word etc.) so you have it accessible at batch execution time
Please note, you will need to replace "password" with your specific password.
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.
Image 23: Execution Log
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 will also be logged in the temp directory outlined in the data sync execution command (e.g.
-d "C:\Cinchy\temp")
Image 24: Execution Errors
Last modified 5mo ago