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