Alias for virtual tables

[leonardschwenk]

Hi there,

I have a couple of virtual tables and I want to deploy my application on several spaces with different remote databases.

When im deploying my application first of all I have to manually replace my RemoteSource and DatabaseName within the Virtual tables and the .hdbvirtualtableconfig.

Is there any way to create something like an alias configuration or application variable?

I would like to change my RemoteSourceName and DatabaseName in one place only. For that I want to use an alias which will replace placeholders in the Virtual tables and in the .hdbvirtualtableconfig.

Something like key in the mta.yaml which can be used as an alias for synonyms for example.

Virtual table:

Some Config, which hopefully exists:

RemoteSource=Alias-RemoteSource

DatabaseName=Alias-DatabaseName

Future Virtual table:

Thanks in advance!

Leo

[pfefferf · Accepted solution]

Think a Logical Schema is what you are searching for. In a Logical Schema you can define the Remote, DB, schema info once. In the configs for your virtual tables you can use the logical schema. When you want to change the Remote information you have to do it just once in the logical schema definition then.

[leonardschwenk · Accepted solution]

Great thanks!

From my understanding it should look like this now.

Am I correct ?

Is there a naming convention for the logical_schema? Example -> example-virtualtable-logical_schema

########### virtualtable config ###########

db/src/cfg/example.virtualtableconfig

{
 "com.sap.hana.example::TableOne" : {
    "target" : { 
       "remote"   : "RemoteSourceSystem",
       "database" : "RemoteDatabaseName", 
       "schema"   : "SchemaName", 
       "object"   : "TableOne" 
    }
  },
 "com.sap.hana.example.virtualtable.logical_schema::TableOne" : {
    "target" : { 
       "logical_schema" : "example-virtualtable-logical_schema",
       "object"         : "TableOne" 
    }
  },
   "com.sap.hana.example::TableTwo" : {
    "target" : { 
       "remote"   : "RemoteSourceSystem",
       "database" : "RemoteDatabaseName", 
       "schema"   : "SchemaName", 
       "object"   : "TableTwo" 
    }
  },
 "com.sap.hana.example.virtualtable.logical_schema::TableTwo" : {
    "target" : { 
       "logical_schema" : "example-virtualtable-logical_schema",
       "object"         : "TableTwo" 
    }
  }
}

############ hdblogicalschema #############
db/src/cfg/example.hdblogicalschema
{
 
 "example-virtualtable-logical_schema" : { 
   "target": { 
     "remote"   : "<Your Remote Source>", # just this has to be changed
     "database" : "<Your Database Name>", # just this has to be changed
     "schema"   : "SchemaName"
    }
 }
} 

[leonardschwenk · Accepted solution]

Now I had to time to test it it actually works like this.

I used the virtualtableconfig wrong and used the TableOne twice. This is corrected now in the example below.

########### virtualtable config ###########
db/src/cfg/example.virtualtableconfig
{
 "com.sap.hana.example.virtualtable::TableOne" : {
    "target" : { 
       "logical_schema" : "example_virtualtable_logical_schema",
       "object"         : "TableOne" 
    }
  },
 "com.sap.hana.example.virtualtable::TableTwo" : {
    "target" : { 
       "logical_schema" : "example_virtualtable_logical_schema",
       "object"         : "TableTwo" 
    }
  }
}
############ hdblogicalschema #############
db/src/cfg/example.hdblogicalschema
{
 "example_virtualtable_logical_schema" : { 
   "target": { 
     "remote"   : "<Your Remote Source>", # just this has to be changed
     "database" : "<Your Database Name>", # just this has to be changed
     "schema"   : "SchemaName"
    }
 }
} 
############ virtualtable  #############
db/src/virtualtables/VT_TableOne.hdblogicalschema
VIRTUAL TABLE "com.sap.hana.example.virtualtable::TableOne" AT REMOTE.TableOne

db/src/virtualtables/VT_TableTwo.hdblogicalschema
VIRTUAL TABLE "com.sap.hana.example.virtualtable::TableTwo" AT REMOTE.TableTwo

[Cocquerel]

No, there is no need to modify any .*config file when deploying your .mtar file to other system.

In your *config file, your can refer to "alias" as you said. Those alias should exist as json tag in a user provided service.
I guess you are already using a user provided service via an .hdbgrants file to grant the <yourprojet>#OO user with CREATE VIRTUAL TABLE privilege. You can use the same provided service and add in the JSON the alias with the corresponding physical name of your remote source and remote schema.

The json of your user provided service should look like:
{

....
"RemoteSourceAlias":"RemoteSourcePhisicalName",

"RemoteShemaAlias":"RemoteShemaPhisicalName"

}

In the mta file, you should have already defined a logical name for your user provided service. Let's supposed it is called logicalUserProvidedService.

Your .hdbvirtualtableconfig file should then looks like this
{ "MyVirtualTable" : { "target" : { "remote.configure" : "logicalUserProvidedService/RemoteSourceAlias",

"schema.configure" : "logicalUserProvidedService/RemoteShemaAlias",

"object" : "MyTable" } }

[leonardschwenk]

Hi Mickael,

I added a comment on Florians answer.

I not 100%sure if that already covers what you meant but I think so, please correct me if im wrong.

Thanks for you answer!

[Cocquerel]

Hi Leonard,
If your are using "logical_schema" instead of "schema", the name of the schema will be hardcoded into .hdblogicalschema file instead of .hdbvirtualtable but it will not solve your issue.
The "magic" of using *config file is that all tags could be mapped dynamically at deployment time. Just add ".remote" as suffix in the tag name and then, you can refer to a user provided service to set the name dynamically. See details here https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/7ef53fb04ecc49a3ae647c21a0...

[leonardschwenk]

Hi Mickael,

Im still confused but on another level.

I feels like im missing something because im already using synonyms and it seems like "schema.configure" can access them actual schema dynamically. But I dont have a configure file! And when I want to use a database I need a *.configure file because the database name has to be hardcorded from my understanding.

How does such *.configure file look like ?

I can't really fine an example.

Thanks already you answer helped me a lot!

[Cocquerel]

Hi Leonard,
I guess you are using "schema.configure" to access an external object from an other hdi container. In this case, there is already a service related to this other container with a tag called "schema" in the JSON that gives the name of the shema of this hdi container. So, indeed, in this case, you do not need to add manually an alias into a custom service as it is already existing.

Regarding the remote source, there is no .hdbremotesource file existing. It has to be created manually by an administrator on the target system prior to deploy the mtar file. The tag database should not be used in the .hdbvirtualtable file because it is already specified in the remote source.

[leonardschwenk]

Hi Mickael,

thanks for bring clarity to this topic.

The remote source is already created and with this a database name exists you are correct about this.

I im not sure if just "database.configure" would just work like *magic* because the remote source is already know.

But then where to I configure the remote.source name also also by just remote.configure?

Thanks a lot. I posted the solution I which now working for me and should be enought into the comments of Florians answer above.

Where I now just have one logical_schema and can use this for all my virualtables. Which is creat just basicly just one manual adjustment.

Cheers,

Leo

[Cocquerel]

Hi Leonard,

no need to define neither "database" nor "database.configure" in the .hdbvirtualtable file as it is already defined in the existing remote source.
The remote source name should be defined using a generic alias name in "remote.configure".

Prior to deploy the mtar, the user provided service should exist and the mapping between the alias and the real name of source should be defined there. You can create the user provided service using the xs command "xs create-user-provided-service" (see details here https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/351e2ed7b7ce4badbdc435897e... ) or using XSA Cockpit.

In the same way, you can define a mapping for the schema name. This way, you would avoid this "one manual adjustment" of the .hdblogicalschema file.

[former_member337435]

Hello mickael.cocquerel ,

With reference to your answer below

The json of your user provided service should look like:
{

....
"RemoteSourceAlias":"RemoteSourcePhisicalName",

"RemoteShemaAlias":"RemoteShemaPhisicalName"

}

How can we give the alias name in the hdbgrants file for the REMOTE source name. That is not accepting a variable from a User Provided Service.

{
  "A_remote_grantor": {
    "object_owner" : {
      "global_object_privileges" : [
        {
          "name" : "REMOTE_SOURCE_NAME",//  We are trying to update this from an alias created in the User Provided Service
          "type" : "REMOTE SOURCE",
          "privileges" : [ "CREATE VIRTUAL TABLE","CREATE REMOTE SUBSCRIPTION", "CREATE VIRTUAL FUNCTION", "LINKED DATABASE", "REMOTE EXECUTE", "REMOTE TABLE ADMIN", "PROCESS REMOTE SUBSCRIPTION EXCEPTION" ],
          "privileges_with_grant_option" : [ "CREATE VIRTUAL TABLE","CREATE REMOTE SUBSCRIPTION", "CREATE VIRTUAL FUNCTION", "LINKED DATABASE", "REMOTE EXECUTE", "REMOTE TABLE ADMIN", "PROCESS REMOTE SUBSCRIPTION EXCEPTION" ]
		}]
    },
    "application_user": {
      "global_object_privileges": [
        {
          "name" : "REMOTE_SOURCE_NAME",
          "type" : "REMOTE SOURCE",
          "privileges" : [ "CREATE VIRTUAL TABLE","CREATE REMOTE SUBSCRIPTION", "CREATE VIRTUAL FUNCTION", "LINKED DATABASE", "REMOTE EXECUTE", "REMOTE TABLE ADMIN", "PROCESS REMOTE SUBSCRIPTION EXCEPTION" ],
          "privileges_with_grant_option" : [ "CREATE VIRTUAL TABLE" ]
		}]
    }
	}

[Cocquerel]

lalitha16 , the possibility to refer to variable from User Provided service is only possible in cfg/ folder for files with a .*config suffix. So, it is not available for your .hdbgrants file . (see https://help.sap.com/docs/SAP_HANA_PLATFORM/4505d0bdaf4948449b7f7379d24d0f0d/7ef53fb04ecc49a3ae647c2... )
The workaround for your case is to create manually a runtime role with same name in all systems but referring different remote source names. Then, in your hdbgrants file, you refer to this role name.

[former_member337435]

Sounds good, Thanks mickael.cocquerel !