Introduction
The assumption is that HVR is already installed and the configuration of HVR Hub Server has been completed. The assumption is that the Local Processing Hub Server is configured to run on http://malta:5540.
The below example will create and activate a channel called o2pg, which has an oracle as the source and ppostgres as the target location with just 2 actions called Capture on the Oracle side, Integrate on the postgres side. Additionally, some other location connections will be shown using REST API calls.
Get Authorization Bearer Code
There are two options: Either use REST or use the command line hvrlogin. If using hvrlogin, the authentication code can be saved into a reusable environment variable called $HVR_LOGIN_ACCESS_TOKEN
The REST API to get the authorization token for the user to connect to HVR; e.g. user is hvradmin in this example;
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 called oras, which connects without an HVR agent to oracle 18C
curl -X POST 'http://malta:5540/api/v0/hubs/hvhub6/definition/locs' \-H 'Authorization: Bearer <copy Authorization bearer from http://localhost:5540/auth/v1/password call>”' \-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/bashR="http://malta:5540"user=hvradminpass=Mypasswdhub=hvrhub6eval $(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 Postgres Location
The below example will create a Postgres location called pgt without a hvr agent
curl -X POST http://malta: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 called o2pg
The below example creates a channel called o2pg, adding location groups source and target, add ing 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://malta: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 o2pg
The below example adds 9 tables from schema hvtpcc and adds these tables in a tablegroup called TPCC.
curl -X POST http://malta: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"}]}}'
Now the channel configuration is completed and 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://malta: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 activate 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://malta: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
SQL server with hvragent allowing anonymous access
On the location where an hvragent is needed, start hvragent like this
hvragentlistener -d <port number>, the first time the agent is started on this port, it will be in setup mode
in example port 55302 is used on malta
hvragentlistener -d 55302
Get Agent_Client_Public_Certificate
curl -X GET 'http://malta: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://malta: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":"malta","Agent_Port":"55302"},"agent_props":{"Setup_Mode_Timed_Until":null,"Only_From_Client_Public_Certificates":{"http://malta:5540/":"<Agent_Client_Public_Certificate from http://malta:5540/api/v0/repos/props call>"},"Anonymous_Access":{"allow":true}},"setup_timed":true}'
Add location called sqlserver
curl -X POST http://malta: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":"malta","Agent_Port":"55302"},"loc":"sqlserver"}'
Snowflake with hvragent using username/password authentication
On the location where an hvragent is needed, start hvragent like this
hvragentlistener -d <port number>, the first time the agent is started on this port, it will be in setup mode
in example port 6343 is used on aws ec2 instance ec2-34-243-68-170.eu-west-1.compute.amazonaws.com
hvragentlistener -d 6343
Create agent admin user
This step is optional, but having at least 1 agent admin user, gives the possibility to re-configure the agent when not in setup mode afterward.
curl -X POST http://malta: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 hvragent will be done by using a username and password, network traffic will be encrypted using Agent_client_public_certificate from the hub.
curl -X POST http://malta: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://malta: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://malta: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"}'