HANA XSA : hdbvirtualtable dynamic schema name

[mani_ac]

Hi,

I have created a virtual table(.hdbvirtualtable) by using and deployed in DEV space

VIRTUAL TABLE "RS_KNA1" AT "REMOTE"."NULL"."SAPDEV"."KNA1" 

It is failing in QA because the schema name is different .

VIRTUAL TABLE "RS_KNA1" AT "REMOTE"."NULL"."SAPQA"."KNA1" <br>

Is it possible to pass schema name dynamically in MTA project and keep the same code across all the environments
(DEV,QA and PROD)

I checked the hdblogicalschema and hdbvirtualtableconfig options but not sure how to achieve it using that.

Can you please guide .

[Cocquerel]

you can use "schema.configure" property instead of "schema" in your .hdbvirtualtableconfig file.
This way, the value can be retrieved dynamically at deployment time.
The prerequisite is to create a user provided service with a json tag storing the value (RS_KNA1_SCHEMA for example).
So, in DEV, your provided service json would looks like this

{"RS_KNA1_SCHEMA":"SAPDEV"}

and this in QAL

{"RS_KNA1_SCHEMA":"SAPQA"}

then, your hdbvirtualtableconfig would looks like this

{
"RS_KNA1": {
 		"target": {
                        "remote": "MyRemoteSource",
 			"object": "KNA1",
 			"schema.configure": "MyUserProvidedService/RS_KNA1_SCHEMA"
 		}
 	}
}

MyUserProvidedService has to be defined in your mta.yaml file

Alternatively, if your have many virtual tables referring to the same schema, you can use "schema.configure" property in a hdblogicalschema file instead.

[mani_ac]

Thank you mickael.cocquerel . This is nice . The only trouble I have , I am not allowed to create a user provided service in PROD manually so wondering how to achieve it.

[Cocquerel]

mani_ac don't you have HANA Admin guy to contact to ask him to create the user provided service in prod? To make thing easy, you can provide him with a script file that would create this user provided service using xs command line tool prior to the deployment of the mtar file.

[Cocquerel]

mani_ac If you are using jenkins pipeline for deployment in production, you may also include a step to run the xs script prior to the deployment.

[former_member732974]

mickael.cocquerel - Hi Michael,

I am getting the below error when I try the option you suggested.

Can you please suggest.

Error: com.sap.hana.di.virtualtable.config: "/RS_ECC_KNA1/target/schema.configure": invalid xpath [8258506]

at "src/tables/RS_ECC_KNA1.hdbvirtualtableconfig" (6:7,/RS_ECC_KNA1/target/schema.configure)

{
  "RS_ECC_KNA1": {
    "target": {
      "remote": "MyRemoteSource",
      "database": "NULL",
      "schema.configure": "FIN_SCHEMA/RS_KNA1_SCHEMA",
      "object": "KNA1"
    }
  }
}

[Cocquerel]

Hi manikandan_kannan14

I confirm such syntax is working fine on my system. There should be an error either in your mta.yaml file or in the JSON of your user provided service. Could you please share those two ?

Regards,

Michael

[former_member732974]

Hi mickael.cocquerel - please find the syntax

UserProvided Service

{
	"RS_KNA1_SCHEMA": "SAPDEV"
}

.hdblogicalschema

{
 "SAPECC_REMOTE" : {
   "target": { 
      "remote"   : "RS_ECC", 
     "database" : "NULL",
     "schema.configure"   : "FIN_SCHEMA/RS_KNA1_SCHEMA" 
   } 
 } 
} 

mta.yaml

modules:

....
- name: hdb-Database
  type: hdb
  path: db
  requires: 
      - name: FIN_SCHEMA
..
resources:
  - name: FIN_SCHEMA
    parameters:
      service-name: FIN_SCHEMA
    properties:
      the-service-name: '${service-name}'
    type: org.cloudfoundry.existing-service

[Cocquerel]

Your mta.yaml file does not look similar to mine.

...

modules:
  - name: M1Y55_FlowMetrics_CORE-db
    type: hdb
    path: db
    requires:
      - name: cross-container-service-1
        group: SERVICE_REPLACEMENTS
        properties:
          key: logical-db-service
          service: '~{cross-container-service-1-name}'
...

resources:
  - name: cross-container-service-1
    parameters:
      service-name: db-service
    properties:
      cross-container-service-1-name: '${service-name}'
    type: org.cloudfoundry.existing-service
...

Then, in the .hdbvirtualtableconfig , I refer to logical-db-service

...
"schema.configure": "logical-db-service/SDA_xHL_xWE_M1Y55_FLOWMETRICS_CORE", 
...

[mani_ac]

Thank you mickael.cocquerel . I exactly did the same way as you did in mta.yaml , I still get the below error. I am not sure what is the mistake.
Can you please suggest

[Cocquerel]

mani_ac your .hdblogicalschema file should be in cfg folder.
same for .hdbvirtualtableconfig file
Regards,

Michael

[mani_ac]

Hi mickael.cocquerel -

Thank you. Unfortuanately I get the same error. Not sure where is the mistake 😞

[Cocquerel]

mani_ac In my case, I set the schema.configure property in a plain.hdbvirtualtableconfig file instead of .hdblogicalschema file.

that is the only difference I see
Regards,
Michael

[mani_ac]

Thank you mickael.cocquerel . I dont have an idea how to fix this 😞 . I will raise a support ticket to SAP