SAP HANA Express - XS Advanced Model - User Provided Service to connect to non-HDI DB schemas

Annotation 2017-01-05: Nothing important happened today. Just the average dinner with fried pork loin steak, potatoes and baked mushrooms seasoned with black pepper, garlic, salt and butter. As always, I ate a lot!

HANA - in order to access a table within another container, or connect to existing database schemas which aren’t managed by HDI (this would be the case for replicated schemas or ERP ABAP schemas for instance) - need to create the User Provided Service (xs cups) manually from the command line;

Sample pass parameters as JSON to create a service non-interactively:

# xs cups CROSS_SCHEMA_SFLIGHT -p "{\"host\":\"<host>\",\"port\":\"30013\",\"user\":\"<user>\",\"password\":\"<pwd>\",\"driver\":\"com.sap.db.jdbc.Driver\",\"tags\":[\"hana\"] , \"schema\" : \"SFLIGHT\" }"

Then modify the requires section of the core-db module in the mta.yaml:

   - name: CrossSchemaService
     group: SERVICE_REPLACEMENTS
     properties:
       key: hdi-sflight-service
       service: ~{sflight-service-name} 

Then modify the resources section of the mta.yaml:

  - name: CrossSchemaService

    type: org.cloudfoundry.existing-service

    parameters:

      service-name: CROSS_SCHEMA_SFLIGHT "same service name created in the command line

    properties:

      sflight-service-name: ${service-name}

Note that indentation is important when editing the mta.yaml !!!

Then create a new folder called cfg under the core-db folder. Create a new file in the cfg

folder called SFLIGHT.hdbgrants with the following code:

{

  "hdi-sflight-service": {

    "object_owner" : {

      "schema_privileges":[ 

        { 

          "reference":"SFLIGHT",

          "privileges_with_grant_option":["SELECT", "SELECT METADATA"]

        }

      ]

    },

    "application_user" : {

      "schema_privileges":[ 

        { 

          "reference":"SFLIGHT",

          "privileges_with_grant_option":["SELECT", "SELECT METADATA"]

        }

      ]

    }

  }

}

Then create a new file called sflight.hdbsynonym in the data folder within the src folder. Enter the corresponding Synonym, Table and Schema name. As a result the tables will be available to be consumed within CDS  artifacts.

The above information was extracted from the HANA5 exercises, and it is also available in the Developer Guide for SAP HANA XSA Model (SP12).