How to Create Locations and Channels Using REST API

Question

How can I create locations and channels using the REST API?

Environment

DSS 6

Answer

The assumption is that DSS is already installed, and the configuration of DSS Hub Server has been completed. In the following examples, it is assumed that the DSS Hub Server is configured to run on the port 5540 of the server named myserver.

The below example will create and activate a channel named o2pg, which has an Oracle as the source and PostgreSQL as the target location with two actions - Capture on the Oracle side and Integrate on the PostgreSQL side. Additionally, few other location connections will be shown using REST API calls.

Get Authorization Bearer Code

The two options to get the authorization bearer code are - using the REST API or the command dsslogin.

If you are using the command dsslogin, the authentication code can be saved into a reusable environment variable named $DSS_LOGIN_ACCESS_TOKEN.

The REST API to get the authorization token for the user to connect to DSS. In the following example, the user is dssadmin:

curl -X POST "http://localhost:5540/auth/v1/password"
-H "Content-Type: application/json"
--data '{"username": "dssadmin", "password": "<dssadmin's password>", "refresh": "token"}'

Create an Oracle Location

The below example creates a location named oras, which connects without a DSS Agent to Oracle 18C:

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/definition/locs" \\
-H 'Authorization: Bearer ”' \\
-H 'Content-Type: application/json' \\
--data '{"props":{"Capture\_Method":"DIRECT","Oracle\_Home":"/distr/oracle/1800","Oracle\_SID":"DSS1800","Database\_User":"hvtpcc","Database\_Password":"tpcc","Class":"oracle"},"loc":"oras"}'

Or if using a bash script, such as the one below:

#!/bin/bash
R="http://myserver:5540"
user=dssadmin
pass=Mypasswd
hub=dsshub6
eval $(echo $pass | dsslogin -R$R -u$user -s -a)

curl -X POST "$R/api/v0/hubs/dsshub6/definition/locs" \\
-H "Authorization: Bearer $DSS\_LOGIN\_ACCESS\_TOKEN" \\
-H 'Content-Type: application/json' \\
--data '{"props":{"Capture\_Method":"DIRECT","Oracle\_Home":"/distr/oracle/1800","Oracle\_SID":"DSS1800","Database\_User":"hvtpcc","Database\_Password":"passwd","Class":"oracle"},"loc":"oras"}'

Create a PostgreSQL Location

The below example will create a PostgreSQL location named pgt without an DSS Agent:

curl -X POST "http://myserver:5540/api/v0/hubs/dsshub6/definition/locs"
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>" 
-H 'Content-Type: application/json'
--data '{"props":{"Database_Host":"localhost","Database_Port":5463,"Database_Name":"hvcen","Database_User":"jigsaw","Database_Password":"passwd","Class":"Postgresql"},"loc":"pgt"}'

Create a Channel

The below example creates a channel named o2pg, adding location groups SOURCE and TARGET, adding oras and pgt as location group members, as well as adding Capture and Integrate actions on the location groups SOURCE(Capture), TARGET(Integrate):

curl -X POST "http://myserver:5540/api/v0/hubs/dsshub6/definition/channels" \
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>" \
-H 'Content-Type: application/json' \
--data '{"description":"","loc_groups":{"SOURCE":{"members":["oras"]},"TARGET":{"members":["pgt"]}},"actions":[{"type":"Capture","loc_scope":"SOURCE","table_scope":"","params":{}},{"type":"Integrate","loc_scope":"TARGET","table_scope":"","params":{}}],"channel":"o2pg"}'

Add Tables to Channel

The below example takes 9 tables from schema hvtpcc and adds these tables in a table group named TPCC.

curl -X POST "http://myserver:5540/api/v0/hubs/dsshub6/channels/o2pg/locs/oras/adapt/apply"
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>"
-H 'Content-Type: application/json'
--data '{"show_views":true,"add_tables":true,"add_table_group":{"group":"TPCC"},"mapspec":{"tables":[{"schema":"hvtpcc","base_name":"customer"},{"schema":"hvtpcc","base_name":"district"},{"schema":"hvtpcc","base_name":"history"},{"schema":"hvtpcc","base_name":"item"},{"schema":"hvtpcc","base_name":"new_order"},{"schema":"hvtpcc","base_name":"order_line"},{"schema":"hvtpcc","base_name":"orders"},{"schema":"hvtpcc","base_name":"stock"},{"schema":"hvtpcc","base_name":"warehouse"}]}}'

The channel configuration is completed, now it can be activated as per the below instructions.

Create a Refresh Event

The below example assumes no refresh has ever been done. A refresh event is created which, if this event gets started, will create absent tables.

curl -X POST "http://myserver:5540/api/v0/hubs/dsshub6/channels/o2pg/refresh" \
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>" \ 
-H 'Content-Type: application/json' \  
--data '{"start_immediate":false,"source_loc":"oras","target_loc":"pgt","create_tables":{"keep_structure":false,"force_recreate":false,"index":true,"keep_existing_data":false,"recreate_if_mismatch":false},"granularity":"bulk","start_next_jobs":["integ"],"online_refresh":"read_write"}'

The above will result in a posted_ev_id, which will be used in the channel activation to start the refresh event.

Activate the Channel

The below example will kick off the refresh event from the previous step and activate the channel. Once this is finished, Capture and Integrate jobs will be running.

curl -X POST "http://myserver:5540/api/v0/hubs/dsshub6/channels/o2pg/activate" \
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN <copy from http://localhost:5540/auth/v1/password call>" \
-H 'Content-Type: application/json' \
--data '{"start_next_jobs":["cap"],"start_next_ev_ids":["<posted_ev_id from previous Refresh call>"],"parallel_locs":2,"replace_enroll":false}'

Additional Location Examples

This section lists few other location examples that can be used instead of Oracle or PostgreSQL in the above sections.

SQL Server

This section describes the steps for creating a SQL Server location with DSS Agent allowing anonymous access. On the location where DSS Agent is needed, start the DSS Agent Listener service using the command dssagentlistener. The first time when the agent is started, it will be in setup mode.

For information about the command syntax for starting the DSS Agent Listener service, see section Examples.

In the following example, the DSS Agent Listener service is started on the port 55302 of the server myserver.

Get Agent_Client_Public_Certificate

curl -X GET "http://myserver:5540/api/v0/repos/props" \
-H "Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN <copy from http://localhost:5540/auth/v1/password call>”

Modify Agent Property to Allow Anonymous Authentication

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/new_loc/agent/props_patch" \
-H “Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN <copy from http://localhost:5540/auth/v1/password call>” \
-H 'Content-Type: application/json' \
--data '{"loc_props":{"Class":"sqlserver","Agent_Host":"myserver","Agent_Port":"55302"},"agent_props":{"Setup_Mode_Timed_Until":null,"Only_From_Client_Public_Certificates":{"http://myserver:5540/":"<Agent_Client_Public_Certificate from http://myserver:5540/api/v0/repos/props call>"},"Anonymous_Access":{"allow":true}},"setup_timed":true}'

Add Location

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/definition/locs"
-H “Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN <copy from http://localhost:5540/auth/v1/password call>”
-H 'Content-Type: application/json'
--data '{"props":{"Capture_Method":"DIRECT","Supplemental_Logging":"CDCTAB_ARTICLE","Log_Truncater":"CAP_JOB","SqlServer_Server":"LT-HERMAN","Database_Name":"tpcc","Database_User":"sa","Database_Password":"pwd","Class":"sqlserver","Agent_Host":"myserver","Agent_Port":"55302"},"loc":"sqlserver"}'

Snowflake

This section describes the steps for creating a Snowflake location with DSS Agent using username/password authentication. On the location where DSS Agent is needed, start the DSS Agent Listener service using the command dssagentlistener. The first time when the agent is started, it will be in the setup mode.

For information about the command syntax for starting the DSS Agent Listener service, see section Examples.

In the following example, the DSS Agent Listener service is started on the port 6343 of AWS EC2 instance ec2-34-243-68-170.eu-west-1.compute.amazonaws.com.

Create Agent Admin User

This step is optional, but having at least one agent admin user gives the ability to re-configure the DSS Agent when not in setup mode.

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/new_loc/agent/users" \
-H “Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>” \
-H 'Content-Type: application/json' \
--data '{"loc_props":{"Class":"snowflake","Agent_Host":"ec2-34-243-68-170.eu-west-1.compute.amazonaws.com","Agent_Port":"6343","setup_timed":true,"user":"herman","authentication":"local","password":"mypasswd"}'

Create an Agent User for Snowflake Location

The authentication to the DSS Agent will be done by using a username and password, network traffic will be encrypted using the Agent_client_public_certificate from the hub.

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/new_loc/agent/users" \
-H “Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>” \ 
-H 'Content-Type: application/json' \
--data '{"loc_props":{"Class":"snowflake","Agent_Host":"ec2-34-243-68-170.eu-west-1.compute.amazonaws.com","Agent_Port":"6343"},"setup_timed":true,"user":"dss","authentication":"local","password":"mypasswd"}'

Modify Agent Property to Allow Username/Password Authentication

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/new_loc/agent/props_patch" \
-H “Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>” \
-H 'Content-Type: application/json' \
--data '{"loc_props":{"Class":"snowflake","Agent_Host":"ec2-34-243-68-170.eu-west-1.compute.amazonaws.com","Agent_Port":"6343","Agent_User":"dss","Agent_Password":"mypasswd"},"agent_props":{"Setup_Mode_Timed_Until":null,"User_Access":{"herman":{"level":"AgentAdmin"}}},"setup_timed":true}'

Create Snowflake Location

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/definition/locs" \
-H 'Authorization: Bearer $DSS_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>” \
-H 'Content-Type: application/json' \
--data '{"props":{"Database_Host":"myorganization.myaccount.snowflakecomputing.com","Database_Port":443,"Snowflake_Role":"public","Snowflake_Warehouse":"hvware","Database_Name":"hvdb","Database_Schema":"hvschema","Database_User":"herman","Database_Password":"mypasswd","ODBC_Driver":"SnowflakeDSIIDriver","Class":"snowflake","Agent_Host":"ec2-34-243-68-170.eu-west-1.compute.amazonaws.com","Agent_Port":"6343","Agent_User":"dss","Agent_Password":"mypasswd"},"loc":"snow"}'