Technical Articles
Replicate artifacts data from an HDI Container in SAP Business Application Studio to SAP HANA On_Premise
The SAP HANA Deployment Infrastructure (HDI) is a service layer of the SAP HANA database and helps to create runtime database objects from design-time artifacts.
It uses containers to store design-time artifacts and the corresponding deployed run-time objects.
Inside the database, the HDI container is represented as a schema but is owned by a technical user and isolated from other database objects. Only local object access inside the container is allowed and to access objects outside the container explicit grants is required from the object owner.
In this blog, you will learn to create HDI module for database content in the BAS, enable access to the schema of an HDI container to a database object consumer and replicate the data from object.
Create Hana Database Project
1. Create SAP HANA Native Application Dev space
6. Retain the module name as default “db” and click Next
Create database artifacts
1. Navigate to View > Find Command > Create Database Artifact > Choose Artifact type as Table > Provide Artifact Name > Create
2. Create table with required details and click Deploy in SAP HANA PROJECTS View
3. Create CSV file
4. Enter the data in CSV file and click Deploy in SAP HANA PROJECTS View
5. Create hdbtabledata to import the data from CSV into Table
6. Define the mappings in the hdbtabledata file and click Deploy in SAP HANA PROJECTS View
Grant Access to an SAP HDI Container’s Schema
To grant access privileges for the entire container schema where the database objects are located to a database object consumer USER, perform the following steps.
Grant access to the HDI container’s schema by executing the below:
1. In DBX, connect to HDI container and Open SQL Admin Console
2. Grant access to the HDI container’s schema by executing the below statements
CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES; INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( ‘SELECT’, ”, ‘USER’ ); CALL C#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); DROP TABLE #PRIVILEGES; |
3. Confirm that the USER can now access the database objects in the container’s schema.
Replicate artifacts data from HDI container into SAP HANA On Premise
Create remote source to the Hana Cloud Database
1. In an SQL console, connected to the SAP HANA on-premise database, create a remote source to the HC database using HANA adapter:
CREATE REMOTE SOURCE “<SOURCE_NAME>” ADAPTER “HanaAdapter” AT LOCATION AGENT “<AGENT>” CONFIGURATION ‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?> <ConnectionProperties name=”configurations”> <PropertyGroup name=”database” displayName=”Database”> <PropertyEntry name=”host” displayName=”Host”><HANA_CLOUD_HOST></PropertyEntry> <PropertyEntry name=”port” displayName=”Port Number”><PORT></PropertyEntry> <PropertyEntry name=”schema” displayName=”Schema”><SCHEMA_NAME></PropertyEntry> </PropertyGroup> <PropertyGroup name=”cdc_properties” displayName=”CDC Properties”> <PropertyEntry name=”prefix” displayName=”System Object Prefix”>HADP_</PropertyEntry> <PropertyEntry name=”ddl_scan_interval” displayName=”DDL Scan Interval in Minutes”>1</PropertyEntry> </PropertyGroup> </ConnectionProperties> ‘ WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘<CredentialEntry name=”credential”> <user><USERNAME></user> <password><PASSWORD></password> </CredentialEntry>’; |
2. Create the virtual table VT in the target system based on the table “EMPLYOEE DETAILS” in the “MY_SCHEMA” schema of the source system
CREATE VIRTUAL TABLE VT AT “REMOTE_SOURCE”.”<NULL>”.”MY_SCHEMA”.”EMPLOYEE_DETAILS”; |
3. Create the replica table RT using the virtual table VT as the source for the definition
CREATE COLUMN TABLE RT LIKE VT; |
4. Create a remote subscription, specifying VT as the virtual table on which the changes are to be captured and RT as the target table to which they are to be applied
CREATE REMOTE SUBSCRIPTION SUB ON VT TARGET TABLE RT; |
5. Initialize real-time data processing using ALTER REMOTE SUBSCRIPTION with QUEUE command
ALTER REMOTE SUBSCRIPTION SUB QUEUE ; |
6. Activate replication using the DISTRIBUTE command
ALTER REMOTE SUBSCRIPTION SUB DISTRIBUTE; |
7. Check for the real time data replicated on to the target table
SELECT * FROM RT; |
8. Deactivate replication and Drop the replication when it is no longer needed
ALTER REMOTE SUBSCRIPTION SUB RESET; DROP REMOTE SUBSCRIPTION SUB; |
Conclusion
In this blog we have seen how to grant access to HDI’s schema, setup real time replication using HANA adapter, create a virtual table, a target table, a remote subscription, and then activate replication on the target system.
What's the minimal HANA on-premise version that supports replication via hanaodbc adapter ? Is it only with HANA Cloud as remote source or is it also working with HANA on-premise remote source ?
Hi Michael,
SAP HANA 2.0 SPS 05 or higher is supported to replicate data between HANA on-premise system and SAP HANA database in HANA Cloud using hanaodbc adapter. HANA on-premise can also be used as remote source.
Regards,
Apoorva
I have tried replicating data using hanaodbc adapter between 2 on-premise HANA 2 SPS5 systems.
When running 'CREATE REMOTE SUBSCRIPTION ...' statement, I'm getting the following error :
SAP DBTech JDBC: [403]: internal error: [SAP AG][LIBODBCHDB SO][HDBODBC] General error;2 general error: cannot turn ddl auto-commit off in auto-commit mode
Hi Michael,
RTR is only supported between HANA on-premise system and SAP HANA database in HANA Cloud.
Starting from 2.00.059.05 and 2.00.064, blocked creating remote subscription by using RTR in release binary.
For more details you can refer to WIKI
Regards,
Apoorva
Hi Apoorva,
I have no user to connect to your WIKI link. Is it for SAP Internal only? Does the same information available somewhere for SAP Customers ?
Is it in the roadmap to support RTR between 2 HANA on-premise systems ?
Regards,
Michael
Hi Michael,
RTR is not officially supported between Hana On-Prem to On-Prem and it’s the same for internal SAP customers.
As per the information obtained from RTR contact point, there is no plan to support the RTR.
Regards,
Apoorva
Hi Apoorva,
Sorry to insist but it is not fully clear to me. Let me resume my uderstanding.
So, On-Prem (starting from HANA 2.0 SPS 05) to Cloud and Cloud to Cloud are supported as mentioned here https://help.sap.com/docs/HANA_CLOUD_DATABASE/477aa413a36c4a95878460696fcc8896/2937dc0404e04f91be3aff16ebd7acaa.html
On-Prem to On-Prem is not supported and it is not in the roadmap.
What is not clear to me is the case you show in this blog : Cloud to On-Prem. I don't understand the meaning of your sentence : "Starting from 2.00.059.05 and 2.00.064, blocked creating remote subscription by using RTR in release binary." I have not found any information that this scenario is supported. In note 3218836, it is saying it is not supported with Rev 64. So, cloud you please clarify which on-prem version is required to perform remote table replication via SDA ODBC (hanaodbc) adapter to an on-premise target HANA database as you shown in your blog ?
Regards,
Michael
Hi Michael,
To make its simple, no version of SP5 or SP6 is supported for OP to OP remote table replication. In blog changes are made and Hana adapter is used.
Regards,
Apoorva
Hi Apoorva,
Thank you for clarification and having updated your blog.
Regards,
Michael