How to Create Locations and Channels Using REST API

Question

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

Environment

HVR 6

Answer

The assumption is that HVR is already installed, and the configuration of HVR Hub Server has been completed. In the following examples, it is assumed that the HVR 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 hvrlogin.

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

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

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

Create an Oracle Location

The below example creates a location named oras, which connects without a HVR 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":"HVR1800","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=hvradmin
pass=Mypasswd
hub=hvrhub6
eval $(echo $pass | hvrlogin -R$R -u$user -s -a)

curl -X POST "$R/api/v0/hubs/hvrhub6/definition/locs" \\
-H "Authorization: Bearer $HVR\_LOGIN\_ACCESS\_TOKEN" \\
-H 'Content-Type: application/json' \\
--data '{"props":{"Capture\_Method":"DIRECT","Oracle\_Home":"/distr/oracle/1800","Oracle\_SID":"HVR1800","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 HVR Agent:

curl -X POST "http://myserver:5540/api/v0/hubs/hvrhub6/definition/locs"
-H "Authorization: Bearer $HVR_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/hvrhub6/definition/channels" \
-H "Authorization: Bearer $HVR_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/hvrhub6/channels/o2pg/locs/oras/adapt/apply"
-H "Authorization: Bearer $HVR_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/hvrhub6/channels/o2pg/refresh" \
-H "Authorization: Bearer $HVR_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/hvrhub6/channels/o2pg/activate" \
-H "Authorization: Bearer $HVR_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 HVR Agent allowing anonymous access. On the location where HVR Agent is needed, start the HVR Agent Listener service using the command hvragentlistener. The first time when the agent is started, it will be in setup mode.

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

In the following example, the HVR 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 $HVR_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 $HVR_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 $HVR_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 HVR Agent using username/password authentication. On the location where HVR Agent is needed, start the HVR Agent Listener service using the command hvragentlistener. The first time when the agent is started, it will be in the setup mode.

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

In the following example, the HVR 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 HVR Agent when not in setup mode.

curl -X POST "http://myserver:5540/api/v0/hubs/hvhub6/new_loc/agent/users" \
-H “Authorization: Bearer $HVR_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 HVR 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 $HVR_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":"hvr","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 $HVR_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":"hvr","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 $HVR_LOGIN_ACCESS_TOKEN or <copy from http://localhost:5540/auth/v1/password call>” \
-H 'Content-Type: application/json' \
--data '{"props":{"Database_Host":"hvr_partner.west-europe.azure.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":"hvr","Agent_Password":"mypasswd"},"loc":"snow"}'