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

[pfefferf]

One way can be to define a hdblogicalschema artefact for each environment (including the environment specific settings like the schema). The defined logical schema than can be used in the hdbvirtualtableconfig.

The relevant question is then, how you deploy the specific information to the specific environments. Here for instance the "include-filter" option of the @sap/hdi-deploy - npm (npmjs.com) can be used. With that it can be defined that only the specific environment relevant hdblogicalschema artefact is deployed.

If you have only one virtual table you could apply the same approach for an environment specific hdbvirtualtableconfig artefact.

[mani_ac]

Hi florian.pfeffer ,

Thank you for the reply. I have many virtual tables,based on your suggestion I created two DEV.hdblogicalschema and PROD.hdblogicalschema. Then one of my hdbvirtualtableconfig looks like below.;

 {
 	"RS_KNA1": {
 		"target": {
 			"logical_schema": "SAPDEV",
 			"object": "KNA1"
 		}
 	}
 }

But we have to hardcode the logicalschema SAPDEV in the hdbvirtualtableconfig. But here I am not sure how to change this(SAPQA) dynamically when deploying the same mtar to QA and PROD. Because logical_schema should be SAPQA when deploying to QA

Can you please suggest how to specify ?

[pfefferf]

The logical schema name must always be the same and should point on the different environments to different (remote) schemas.

For instance:

Logical schema definition for DEV:

{
  "MY_LOGIC_SCHEMA": {
    "target": {
      "remote": "REMOTE1",
      "schema": "SCHEMA1"
    }
  }
}

Logical schema definition for QA:

{
  "MY_LOGIC_SCHEMA": {
    "target": {
      "remote": "REMOTE2",
      "schema": "SCHEMA2"
    }
  }
}

You see the name of the logical schema is the same and can be used in the virtual table configuration statically.

During deployment you can use the include-filter to deploy either the DEV or the QA logical schema definition, depending on the system.

[mani_ac]

Thank you florian.pfeffer .

This makes sense. One last question. There are not a lot of examples around --include-filter so I am not sure how to implement include-filter option and pass dynamically when deploying which system it is .

Can you please guide me here.

package.json - But here I am not sure how to give and pass dynamically say DEV or QA

{
	"name": "deploy",
	"dependencies": {
		"@sap/hdi-deploy": "3.11.9"
	},
	"scripts": {
		"start": "node node_modules/@sap/hdi-deploy/deploy.js --auto-undeploy --include-filter src/DEV.hdblogicalschema parameter environment=DEV;"
	}
}

[pfefferf]

The include-filter option is described in the readme of the @sap/hdi-deploy package.

Regarding the "dynamic" setting in the npm script. Use env variables. Depending on the tooling/setup (e.g. mta) you are using, you have to inject it specifically.

[mani_ac]

Hi florian.pfeffer , Thank you . I tried a whole day with different ways still struggling to achieve this.

I created 3 files with the same name as SPACE name . DEV.hdblogicalschema QA.hdblogicalschema PROD.hdblogicalschema

I tried in mta.yaml to give like this and assumed that $space is prebuilt environment variable but its not working.
Is there a way I can set the file name dynamically? Can you please suggest

path: Database
    requires:
      - name: hdb-container
        properties:
          HDI_DEPLOY_OPTIONS: 
            "auto_undeploy" : true
            "include_filter" : [ "src/${space}.hdblogicalschema" ] 
          TARGET_CONTAINER: '~{hdi-container-name}'