How to Create Locations and Channels Using REST API

Introduction

The assumption is that Local Data Processing is already , and the of Local Processing Hub Server has been completed. In the following examples, it is assumed that the Local Processing 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 - on the Oracle side and 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 or the command .

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 Local Data Processing. 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"}'
content_copy

Create an Oracle Location

The below example creates a location named oras, which connects without a High-Volume 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"}'
content_copy

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"}'
content_copy

Create a PostgreSQL Location

The below example will create a PostgreSQL location named pgt without a High-Volume 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"}'
content_copy

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 and 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"}'
content_copy

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"}]}}'
content_copy

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

Create a Refresh Event

The below example assumes no 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"}'
content_copy

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 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}'
content_copy

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 allowing anonymous access. On the location where HVA is needed, start the HVA Listener service using the command . The first time when the agent is started, it will be in .

For information about the command syntax for starting the HVA Listener service, see section .

In the following example, the HVA 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>”
content_copy

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}'
content_copy

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"}'
content_copy

Snowflake

This section describes the steps for creating a Snowflake location with High-Volume Agent (HVA) using username/password authentication. On the location where HVA is needed, start the HVA Listener service using the command . The first time when the agent is started, it will be in the .

For information about the command syntax for starting the HVA Listener service, see section .

In the following example, the HVA 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 HVA 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"}'
content_copy

Create an Agent User for Snowflake Location

The authentication to the HVA 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"}'
content_copy

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}'
content_copy

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"}'
content_copy