You can configure your Perspectium DataSync Agent to share data from ServiceNow or Salesforce to a Snowflake database by changing some additional configurations in your agent.xml file.

(info) NOTE: The Snowflake JDBC driver 3.8.0 will come packaged with your Perspectium DataSync Agent in order to address a known issue with use of OSCP for certificate revocation status.

Prerequisites


(warning) First, you will need to install a DataSync Agent.

(warning) You will also need to create a ServiceNow dynamic share/bulk share or create a Salesforce dynamic share/bulk share.

(warning) Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.

Procedure

To set up your DataSync Agent to share application data to a Snowflake database, follow these steps:


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 Snowflake

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:

DirectiveValue to enter
<database_type>snowflake
<database_server>

URL for your Snowflake database

<database_port>1433
<database_user>Username used to access your Snowflake database
<database_password>Password used to access your Snowflake database

(info) NOTE: If you want to send data to specific warehouses or schemas in Snowflake, you will need to have the following privileges granted to your Snowflake user account:

  • warehouse: usage
  • database: usage
  • schema: usage, create table

Then, request the databases.xml file for your DataSync Agent by contacting Perspectium Support. Once you've received the databases.xml file, follow these steps:

  • Open the file in a text-editing application and locate the configurations for <database_type>snowflake</database_type>. Within the parent <database> directive, locate the <database_uri> directive and delete the schema=public&amp; from within this directive's value.
  • Locate the <database_uri_database_parms> directive and replace the ? within this directive's value with &amp;
  • Save the databases.xml file and put the file within the conf directory that was created upon installation of your DataSync Agent.
  • Still within the DataSync Agent conf directory, locate and then open the agent.xml file in a text-editing application.
  • Within the agent.xml file, locate the <database_parms> directive and add the following for this directive's value: warehouse=MY_WAREHOUSE&amp;schema=MY_SCHEMA (where MY_WAREHOUSE is the Snowflake warehouse you want to share data to and MY_SCHEMA is the Snowflake schema you want to share data to).

Add directive(s) for Snowflake

Locate the <task> directive(s) within your <subscribe> directive, and nest the following directive:

DirectiveDescription
<ensure_table_exists/>Self-closing tag that runs a check to ensure the Snowflake database table exists

(info) NOTE: If using a DataSync for ServiceNow Agent, it is also recommended to nest the <use_cache/> directive within each <task> directive for <subscribe>.

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 instances="4">
                              <task_name>OnDemandAgent_subscribe</task_name>
                              <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPbQ5/gZDZBSjYcHe6gjfJI=" queue="psp.out.replicator.snowflakeexample" use_basic_consume="true" user="exampleuser">https://exampleperspectium.net</message_connection>
							  <instance_connection password="examplepassword" user="example.user">https://myinstance.service-now.com</instance_connection>
                              <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                              <decryption_key>The cow jumped over the moon</decryption_key>
                              <database_type>snowflake</database_type>
                              <database_server>example.snowflakecomputing.com</database_server>
                              <database_user>exampleuser</database_user>
                              <database_password>examplepassword</database_password>
                              <database_parms>warehouse=example_warehouse&amp;schema=example_schema&amp;role=example_role</database_parms>
                              <database>psp_repl</database>
                              <use_cache/>
                              <ensure_table_exists/>
                      </task>
              </subscribe>
              <max_reads_per_connect>4000</max_reads_per_connect>
              <polling_interval>5</polling_interval>
              <skip_message_set_processing/>
      </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 Snowflake database, start running your DataSync Agent again.