Using Row Hashing for Incremental Integrations - Revision history

Jkuehlthau: /* C# */

2018-07-13T19:49:14Z

← Older revision		Revision as of 19:49, 13 July 2018
Line 83:		Line 83:
	End Sub		End Sub
	</source>		</source>

	~~==C#==~~
	~~This could also be done with C#:~~
	~~<source lang="csharp">~~
	~~public static string MD5Hash(string input)~~
	{
	~~StringBuilder hash = new StringBuilder();~~
	~~MD5CryptoServiceProvider md5provider = new MD5CryptoServiceProvider();~~
	~~byte[] bytes = md5provider.ComputeHash(new UTF8Encoding().GetBytes(input));~~

	~~for (int i = 0; i < bytes.Length; i++)~~
	{
	~~hash.Append(bytes[i].ToString("x2"));~~
	}
	~~return hash.ToString();~~
	}
	~~</source>~~
	~~This code sample was borrowed from https://coderwall.com/p/4puszg/c-convert-string-to-md5-hash.~~

Jkuehlthau: /* Hashing Partial Records */

2018-07-09T19:54:56Z

Hashing Partial Records

← Older revision		Revision as of 19:54, 9 July 2018
Line 24:		Line 24:
	I had an integration that pulled data from a single table into Accounts, Contacts, and Opportunities. Each of these target tables used different fields from the source. I didn't want to process every record as it changed, just the ones I cared about. So, I created VBScript before and after save procedures to save the last rowhash and compare that to the new rowhash. If they are different, then I process the record.		I had an integration that pulled data from a single table into Accounts, Contacts, and Opportunities. Each of these target tables used different fields from the source. I didn't want to process every record as it changed, just the ones I cared about. So, I created VBScript before and after save procedures to save the last rowhash and compare that to the new rowhash. If they are different, then I process the record.

			==VB Script==
	'''Before Save'''<br>		'''Before Save'''<br>
	Repeat Each Row		Repeat Each Row
Line 83:		Line 84:
	</source>		</source>

	This could also be done with C#.		==C#==
			This could also be done with C#:
	<source lang="csharp">		<source lang="csharp">
	public static string MD5Hash(string input)		public static string MD5Hash(string input)

Jkuehlthau: /* Hashing Partial Records */

2018-07-09T19:54:21Z

Hashing Partial Records

← Older revision		Revision as of 19:54, 9 July 2018
Line 82:		Line 82:
	End Sub		End Sub
	</source>		</source>

			This could also be done with C#.
			<source lang="csharp">
			public static string MD5Hash(string input)
			{
			StringBuilder hash = new StringBuilder();
			MD5CryptoServiceProvider md5provider = new MD5CryptoServiceProvider();
			byte[] bytes = md5provider.ComputeHash(new UTF8Encoding().GetBytes(input));

			for (int i = 0; i < bytes.Length; i++)
			{
			hash.Append(bytes[i].ToString("x2"));
			}
			return hash.ToString();
			}
			</source>
			This code sample was borrowed from https://coderwall.com/p/4puszg/c-convert-string-to-md5-hash.

Jkuehlthau: /* Hashing Partial Records */

2016-08-19T20:16:28Z

Hashing Partial Records

← Older revision		Revision as of 20:16, 19 August 2016
Line 63:		Line 63:
	Dim UTF8		Dim UTF8
	Set UTF8 = CreateObject("System.Text.UTF8Encoding")		Set UTF8 = CreateObject("System.Text.UTF8Encoding")
	stringToUTFBytes = UTF8.GetBytes_4("@@ORG:quote_id@@@@ORG:customer@@@@ORG:billing_address@@@@ORG:billing_city@@@@ORG:billing_state@@@@ORG:billing_zip@@@@ORG:address@@@@ORG:city@@@@ORG:state@@@@ORG:zip@@@@ORG:phone@@@@ORG:customer_notes@@@@ORG:representative_id@@@@ORG:billing_name@@@@ORG:current_price@@@@ORG:operating_division@@@@ORG:current_provider@@")		stringToUTFBytes = UTF8.GetBytes_4("@@ORG:quote_id@@@@ORG:customer@@@@ORG:billing_address@@")

	Dim MD5		Dim MD5

Jkuehlthau: /* Hashing The Whole Record= */

2016-08-19T20:16:06Z

Hashing The Whole Record=

← Older revision		Revision as of 20:16, 19 August 2016
Line 1:		Line 1:
	=Hashing The Whole Record==		=Hashing The Whole Record=
	Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Starfish ran. This is accomplished using LastRunDate and applying it as a filter against your origin query. See [[Retrieve Records Modified After Last Run DateTime]]. This reduces the set of data returned and greatly speeds up syncronizations.		Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Starfish ran. This is accomplished using LastRunDate and applying it as a filter against your origin query. See [[Retrieve Records Modified After Last Run DateTime]]. This reduces the set of data returned and greatly speeds up syncronizations.

Line 20:		Line 20:

	Please note the first time you run the job, all records will be considered new because no hashes will exist in the Xref. Subsequent runs will then use the stored hashes.		Please note the first time you run the job, all records will be considered new because no hashes will exist in the Xref. Subsequent runs will then use the stored hashes.


	=Hashing Partial Records=		=Hashing Partial Records=

Jkuehlthau at 20:15, 19 August 2016

2016-08-19T20:15:49Z

@@ Line 1: / Line 1: @@
 Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Starfish ran. This is accomplished using LastRunDate and applying it as a filter against your origin query. See [[Retrieve Records Modified After Last Run DateTime]]. This reduces the set of data returned and greatly speeds up syncronizations.
 However if your source system does not contain a "Last Modified Date" field this becomes tricky. One option we have in Starfish ETL is store and compare a hash of the record. This still involves reading all data from the origin, but only passing records to the destination that have been changed. Use the following steps to set this up.
 . Enable UseDatabaseXref - See [[Engine Web.Config Settings]].
@@ Line 20: / Line 20: @@
 Please note the first time you run the job, all records will be considered new because no hashes will exist in the Xref. Subsequent runs will then use the stored hashes.

Ahoekstra at 13:33, 30 October 2015

2015-10-30T13:33:18Z

← Older revision		Revision as of 13:33, 30 October 2015
Line 6:		Line 6:
	1. Enable UseDatabaseXref - See [[Engine Web.Config Settings]].		1. Enable UseDatabaseXref - See [[Engine Web.Config Settings]].

	2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". See [[Cross-reference (Xref)]].		2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". Ensure to check the append checkbox on the write side. See [[Cross-reference (Xref)]].

	3. Perform the comparison. Create a new Exec Before VBScript Procedure, call it "Compare Hash"~~. Ensure to check the append checkbox on the write side~~. This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.		3. Perform the comparison. Create a new Exec Before VBScript Procedure, call it "Compare Hash". This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.
	<source lang="vb">		<source lang="vb">
	Sub VBScriptProcedure		Sub VBScriptProcedure

Ahoekstra at 13:32, 30 October 2015

2015-10-30T13:32:47Z

← Older revision		Revision as of 13:32, 30 October 2015
Line 8:		Line 8:
	2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". See [[Cross-reference (Xref)]].		2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". See [[Cross-reference (Xref)]].

	3. Perform the comparison. Create a new Exec Before VBScript Procedure, call it "Compare Hash". This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.		3. Perform the comparison. Create a new Exec Before VBScript Procedure, call it "Compare Hash". Ensure to check the append checkbox on the write side. This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.
	<source lang="vb">		<source lang="vb">
	Sub VBScriptProcedure		Sub VBScriptProcedure

Ahoekstra at 13:29, 30 October 2015

2015-10-30T13:29:18Z

← Older revision		Revision as of 13:29, 30 October 2015
Line 14:		Line 14:
	Starfish.GotoNextRow		Starfish.GotoNextRow
	Else		Else
	XrefWrite "~~vendor~~-hash", "@@ORG:ContactID@@", "@@ORG:#RowHashCode@@"		XrefWrite "contact-hash", "@@ORG:ContactID@@", "@@ORG:#RowHashCode@@"
	End If		End If
	End Sub		End Sub
	</source>		</source>

			Please note the first time you run the job, all records will be considered new because no hashes will exist in the Xref. Subsequent runs will then use the stored hashes.

Ahoekstra at 13:24, 30 October 2015

2015-10-30T13:24:46Z

← Older revision		Revision as of 13:24, 30 October 2015
Line 8:		Line 8:
	2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". See [[Cross-reference (Xref)]].		2. Initialize an Xref list for ''both '''read''' and '''write''''', in our example "contact-hash". See [[Cross-reference (Xref)]].

	3. Perform the comparison. This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.		3. Perform the comparison. Create a new Exec Before VBScript Procedure, call it "Compare Hash". This code will compare the origin's row hash to that stored in the xref. If it matches, the row is skipped (the data hasn't changed). If it does not match the record has been changed. If there is no match, this is a new record. In this case we store the new hash and allow the job execution to continue - you should set up an Update/Insert stage to process the record. In this example, ContactID is the primary key field from your origin table.
	<source lang="vb">		<source lang="vb">
	Sub VBScriptProcedure		Sub VBScriptProcedure