How To Create Locations and Channels Using Command Line Interface

Question

How can I create locations and channels using the command line interface (CLI)?

Environment

HVR 6

Answer

The assumption is that HVR is already installed and the configuration of HVR Hub Server has been completed. The assumption is that the HVR Hub Server is configured to run on http://malta:5540.

The following example will create and activate a channel called o2pg, which has Oracle as a source and PostgreSQL as a target location with just two actions called Capture on the Oracle side and Integrate on the PostgreSQL side.

Create Authorization bearer

When running the below commands remotely from the hubserver, authorization is required otherwise when running below commands you can get error F_JW0555: Refresh token invalid or expired.

All commands can be executed locally (so on the same server where hvrhubserver is running) or remotely from hvrhubserver.

hvrlogin -R http://malta:5540 -uhvradmin

Create an Oracle location

Create a JSON input file for the Oracle location. The below examples creates a JSON input for an Oracle location called oras, which connects without a HVR Agent to Oracle 18C:

{
  "changes": [
    {
      "add_loc": {
        "props": {
          "Capture_Method": "DIRECT",
          "Class": "oracle",
          "Database_Password": "mypasswd",
          "Database_User": "hvtpcc",
          "Oracle_Home": "/distr/oracle/1800",
          "Oracle_SID": "HVR1800"
        },
        "loc": "oras"
      }
    }
  ]
}

Create the Oracle location using the following command for your use case:
If running the command locally on the hub server: hvrdefinitionimport -loras hvhub6 orac.json
If running the command from a remote machine: hvrdefinitionimport -loras -R http://malta:5540 hvhub6 orac.json

Create a PostgreSQL location

Create a JSON input file for the PostgreSQL location. The below example creates a JSON input for a PostgreSQL location called pgt:

{
  "changes": [
    {
      "add_loc": {
        "props": {
          "Class": "postgresql",
          "Database_Host": "localhost",
          "Database_Name": "hvcen",
          "Database_Password": "mypasswd",
          "Database_Port": 5463,
          "Database_User": "jigsaw"
        },
        "loc": "pgt"
      }
    }
  ]
}

Create the PostgreSQL location using the following command for your use case:
If running the command locally on the hub server: hvrdefinitionimport -lpgt hvhub6 postgres.json
If running the command from a remote machine: hvrdefinitionimport -lpgt -R http://malta:5540 hvhub6 postgres.json

Create a channel called o2pg

The below example creates a channel called o2pg, adding location groups source and target, adding oras and pgt as location group members, and adding Capture and Integrate actions on the location groups source(Capture) and target(Integrate).

Create a JSON input file for the channel o2pg:

{
  "changes": [
    {
      "add_channel": {
        "description": "oracle 2 pg channel",
        "loc_groups": {
          "SRC": {
            "members": [
              "oras"
            ]
          },
          "TGT": {
            "members": [
              "pgt"
            ]
          }
        },
        "actions": [
          {
            "loc_scope": "SRC",
            "table_scope": "*",
            "type": "Capture",
            "params": {}
          },
          {
            "loc_scope": "TGT",
            "table_scope": "*",
            "type": "Integrate",
            "params": {}
          }
        ],
        "channel": "o2pg"
      }
    },
  ]
}

Create the channel using the following command for your use case:
If running the command locally on the hub server: hvrdefinitionimport hvhub6 o2pg.json
If running the command from a remote machine: hvrdefinitionimport -R http://malta:5540 hvhub6 o2pg.json

Add tables to channel o2pg

The below example adds nine tables from schema hvtpcc. Use the following command for your use case:

If running the command locally on the hub server: hvradapt -loras hvhub6 o2pg

If running the command from a remote machine: hvradapt -loras -R http://malta:5540 hvhub6 o2pg

Create a refresh event

The below example assumes no refresh has ever been done. A refresh event is created that, if this event gets started, will create absent tables. Use the following command for your use case:

If running the command locally on the hub server: hvrrefresh -J integ -s -r oras -l pgt -cbkr -gb -qrw hvhub6 o2pg

If running the command from a remote machine: hvrrefresh -J integ -s -r oras -l pgt -cbkr -gb -qrw -R http://malta:5540 hvhub6 o2pg

Activate the channel

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

If running the command locally on the hub server: hvractivate -J cap -J refr -p2 hvhub6 o2pg

If running the command from a remote machine: hvractivate -J cap -J refr -p2 -R http://malta:5540 hvhub6 o2pg

Additional location examples

SQL Server with hvragent allowing anonymous access

In the location where an hvragent is needed, start hvragent like this:

hvragentlistener -d <port number>,

NOTE: The first time the agent is started on this port, it will be in setup mode.

In the below example, the port 55302 is used on malta:

hvragentlistener -d 55302

Get Agent_Client_Public_Certificate

hvrreposconfig -R http://malta:5540

Copy the content of the Agent_Client_Public_Certificate.

Modify HVR Agent property to allow anonymous authentication

hvragentconfig -h hvhub6 -r malta:55302 -S Anonymous_Access='{"allow":"true"}' Only_From_Client_Public_Certificates='{"http://malta:5540":"<copied value from hvrreposconfig>"}' Setup_Mode_Timed_Until=

Add location called sqlserver

Create a JSON file for the location, e.g sqlserver.json:

{
  "changes": [
    {
      "add_loc": {
        "props": {
          "Agent_Host": "malta",
          "Agent_Port": 55302,
          "Capture_Method": "DIRECT",
          "Class": "sqlserver",
          "Database_Name": "tpcc",
          "Database_Password": "mypasswd",
          "Database_User": "sa",
          "Log_Truncater": "CAP_JOB",
          "SqlServer_Server": "LT-HERMAN",
          "Supplemental_Logging": "CDCTAB_ARTICLE"
        },
        "loc": "sqlserver"
      }
    }
  ]
}

Create the location:
hvrdefinitionimport -lsqlserver hvhub6 sqlserver.json

Snowflake with hvragent using username/password authentication

In the location where an hvragent is needed, start hvragent like this:

hvragentlistener -d <port number>,

NOTE: The first time the HVR Agent is started on this port, it will be in setup mode.

In the below example, the 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 (optional)

This step is optional, but if you have at least one agent admin user, it gives the possibility to re-configure the agent when not in setup mode afterwards:

hvragentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c hvradmin

It will ask you to type a password for the user.

Create agent user for Snowflake location

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

hvragentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c hvr

It will ask you to type a password for the user.

Modify agent property to allow username/password authentication

hvragentconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S User_Access='{"herman":{"level":"AgentAdmin"}}' Setup_Mode_Timed_Until=

Create snowflake location

Create a JSON file with following content:

{
  "changes": [
    {
      "add_loc": {
        "props": {
          "Agent_Host": "ec2-34-243-68-170.eu-west-1.compute.amazonaws.com",
          "Agent_Password": "mypasswd",
          "Agent_Port": 6343,
          "Agent_User": "hvr",
          "Class": "snowflake",
          "Database_Host": "hvr_partner.west-europe.azure.snowflakecomputing.com",
          "Database_Name": "hvdb",
          "Database_Password": "mypasswd",
          "Database_Port": 443,
          "Database_Schema": "hvschema",
          "Database_User": "herman",
          "ODBC_Driver": "SnowflakeDSIIDriver",
          "Snowflake_Role": "public",
          "Snowflake_Warehouse": "hvware"
        },
        "loc": "snow"
      }
    }
  ]
}

Run the following command:
hvrdefinitionimport -lsnow hvhub6 snowflake.json