You can configure your DataSync Agent to share data from ServiceNow or Salesforce to an Amazon Redshift database by changing some additional configurations in your agent.xml file. The Perspectium DataSync approach to sharing data from your app to your Amazon Redshift database uses an S3 bucket as a staging area where replicated data is stored before being populated in a Redshift table(s).
Prerequisites
First, you will need to Install the DataSync Agent
You will also need to create a ServiceNow dynamic share/bulk share or create a Salesforce dynamic share/bulk share.
Make sure to stop running your DataSync Agent before making any Agent configuration changes.
Finally, you will need to create an Amazon S3 bucket and launch an Amazon Redshift cluster.
Procedure
To set up your DataSync Agent to share application data to an Amazon Redshift database via an Amazon S3 bucket, follow these steps:
Add joda-time and aws-java-sdk libraries
Add the following .jar files to your DataSync Agent's extlib directory:
Install and configure your DataSync Agent
Follow the steps to Install the DataSync Agent or the DataSync for Salesforce Agent and make sure that your agent.xml file is configured correctly.
Access your agent.xml configuration file
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Update database directives to point to Amazon Redshift
Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and update the following database-related directives:
| Directive | Value to enter |
|---|---|
| <database_type> | redshift |
| <database_server> | Endpoint URL for your Redshift database |
| <database_port> | Port number for your Redshift database |
| <database_user> | Username used to access your Redshift database |
| <database_password> | Password used to access your Redshift database |
Update the <handler> directive
Within the <subscribe> → <task> directive(s), enter a new value for the <handler> directive:
| Directive | Value to enter |
|---|---|
| <handler> | com.perspectium.replicator.sql.RedshiftSQLSubscriber |
Add directives for AWS
Still within the <subscribe> → <task> directive(s), nest the following directives:
| Directive | Description |
|---|---|
| <access_key> | Access Key associated with your AWS account |
| <secret_access_key> | Secret Access Key associated with your AWS account |
| <region> | Region that your Redshift server resides in |
| <s3_bucket> | Name of your AWS S3 bucket |
Your agent.xml file should look similar to the example shown below:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task>
<task_name>test_redshift1_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="exampleuser">amqps://example.perspectium.net</message_connection>
<instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="example.user">https://myinstance.service-now.com</instance_connection>
<handler>com.perspectium.replicator.sql.RedshiftSQLSubscriber</handler>
<decryption_key>The cow jumped over the moon</decryption_key>
<database_type>redshift</database_type>
<database_server>pspexample.example.region.redshift.amazonaws.com</database_server>
<database_port>5439</database_port>
<database_user>exampleuser</database_user>
<database_password>encrypted:vlOtU71yu8N/EFIJH85SSAZYM2li7vh9u6asPldRqe0=</database_password>
<database>pspdev</database>
<access_key>AccessKey</access_key>
<secret_access_key>SecretAccessKey</secret_access_key>
<region>us-west-2</region>
<s3_bucket>examples3bucket</s3_bucket>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>30</polling_interval>
</agent>
</config>
Save your agent.xml file
Save the changes you made to your agent.xml file and close the file.
Run your DataSync Agent
After configuring your agent.xml file to support replication to your Amazon Redshift database, start running your DataSync Agent again.