Import/Export Catalog Objects with Dependencies for Multi-TenantDB in SAP HANA 2.0 SPS04

Introduction

In this blog post you will learn how to import/export catalog objects with dependencies for Multi-TenantDB in SAP HANA 2.0 SPS 04. IMPORT/EXPORT in SAP HANA Database is a useful feature. It is often used to transfer the data between different SAP HANA Databases for different purpose. In most of the cases, you may need to use Cross DB Access based on your business requirements. Let’s assume there are two different tenants, one is DB1 and the other is DB2. And a calculation view (ZCV_REVENUE) in DB1 is referencing many table and views in DB2. In this situation, if customers export ZCV_REVENUE with dependencies, only ZCV_REVENUE is exported, the referenced objects in tenant DB2 has to be exported manually one by one.

What’s new

From SAP HANA 2.0 SPS04 on, user can export/import the cross-database access dependencies with IMPORT/EXPORT. We will show some examples to help you to understand this feature. Please note you should create the remote schema DB2_DEMO in DB2 manually and grant the necessary privileges for the user which has remote identity.

The following is a example to show how to use this feature in SAP HANA 2.0 SPS04.

1. Setup Cross DB Access in SAP HANA system. Tenant DB2 is the remote database. Tenant DB1 is the local one.

2. Create schema DB2_DEMO and table DB2_TEST_TABLE in Tenant DB2. Then insert some test data in this table.

For example, the following statements create schema DB2_DEMO and table DB2_TEST_TABLE in

Tenant DB2.
--In Tenant DB2
CREATE SCHEMA DB2_DEMO;
SET SCHEMA DB2_DEMO;

CREATE TABLE DB2_TEST_TABLE
(
A INT,
B VARCHAR(255)
);

INSERT INTO DB2_TEST_TABLE VALUES(1, '1');
INSERT INTO DB2_TEST_TABLE VALUES(1, '2');
INSERT INTO DB2_TEST_TABLE VALUES(2, '3');
INSERT INTO DB2_TEST_TABLE VALUES(3, '4');

3. Create schema DB1_DEMO and table DB1_TEST_TABLE in Tenant DB1. Then insert some test data in this table. Finally, create a calculation scenario DB1_DEMO.ZCV_REVENUE and related column views.

For example, the following statements create schema DB1_DEMO, table DB1_TEST_TABLE and calculation scenario DB1_DEMO.ZCV_REVENUE with related column views in Tenant DB2.

--In Tenant DB1
CREATE SCHEMA DB1_DEMO;
SET SCHEMA DB1_DEMO;

CREATE TABLE DB1_TEST_TABLE
(
A INT,
B VARCHAR(255)
);

INSERT INTO DB1_TEST_TABLE VALUES(1, '5');
INSERT INTO DB1_TEST_TABLE VALUES(1, '6');
INSERT INTO DB1_TEST_TABLE VALUES(2, '7');
INSERT INTO DB1_TEST_TABLE VALUES(3, '8');

CREATE CALCULATION SCENARIO "DB1_DEMO"."ZCV_REVENUE" USING '[{"__CalculationNode__": true,"name": "DB1_TEST_TABLE","operation": {"__TableDSNodeData__": true,"source": {"__IndexName__": true,"schema": "DB1_DEMO","name": "DB1_TEST_TABLE"},"dataSourceFlags": 0},"attributeVec": [{"__Attribute__": true,"name": "A","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"attributeType": 0},{"__Attribute__": true,"name": "B","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"attributeType": 0}]},{"__CalculationNode__": true,"name": "DB2_TEST_TABLE","operation": {"__TableDSNodeData__": true,"source": {"__IndexName__": true,"database": "DB2","schema": "DB2_DEMO","name": "DB2_TEST_TABLE"},"dataSourceFlags": 0},"attributeVec": [{"__Attribute__": true,"name": "A","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"attributeType": 0},{"__Attribute__": true,"name": "B","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"attributeType": 0}]},{"__CalculationNode__": true,"name": "Join_1","inputVec": [{"__Input__": true,"name": "DB1_TEST_TABLE","mappingVec": [{"__Mapping__": true,"type": 1,"target": "A","source": "A","length": 0},{"__Mapping__": true,"type": 1,"target": "B","source": "B","length": 0}]},{"__Input__": true,"name": "DB2_TEST_TABLE","mappingVec": [{"__Mapping__": true,"type": 1,"target": "A_1","source": "A","length": 0},{"__Mapping__": true,"type": 1,"target": "B_1","source": "B","length": 0},{"__Mapping__": true,"type": 1,"target": "A","source": "A","length": 0}]}],"operation": {"__JoinOpNodeData__": true,"joinType": 0,"joinAttributeVec": ["A"],"cardinality": 64},"attributeVec": [{"__Attribute__": true,"name": "A","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"attributeType": 0},{"__Attribute__": true,"name": "B","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"attributeType": 0},{"__Attribute__": true,"name": "A_1","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"attributeType": 0},{"__Attribute__": true,"name": "B_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"attributeType": 0}],"debugNodeDataInfo" :  {"__DebugNodeDataInfo__": true,"nodeName": "Join_1"}},{"__CalculationNode__": true,"name": "finalProjection","isDefaultNode": true,"inputVec": [{"__Input__": true,"name": "Join_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "A","source": "A","length": 0},{"__Mapping__": true,"type": 1,"target": "B","source": "B","length": 0},{"__Mapping__": true,"type": 1,"target": "A_1","source": "A_1","length": 0},{"__Mapping__": true,"type": 1,"target": "B_1","source": "B_1","length": 0}]}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "A","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"description": "A","attributeType": 0},{"__Attribute__": true,"name": "B","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"description": "B","attributeType": 0},{"__Attribute__": true,"name": "A_1","role": 1,"datatype": {"__DataType__": true,"type": 73,"sqlType": 3,"sqlLength": 5},"description": "A_1","attributeType": 0},{"__Attribute__": true,"name": "B_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 36,"sqlLength": 255},"description": "B_1","attributeType": 0}],"debugNodeDataInfo" :  {"__DebugNodeDataInfo__": true,"nodeName": "Projection"}},{"__Variable__": true,"name": "$$language$$","typeMask": 512,"usage": 0,"isGlobal": true},{"__Variable__": true,"name": "$$client$$","typeMask": 512,"usage": 0,"isGlobal": true},{"__CalcScenarioMetaData__": true,"externalScenarioName": "tmp::ZCV_REVENUE"}]';
CREATE COLUMN VIEW "DB1_DEMO"."ZCV_REVENUE" WITH PARAMETERS (indexType=11,
'PARENTCALCINDEXSCHEMA'='DB1_DEMO',
'PARENTCALCINDEX'='ZCV_REVENUE',
'PARENTCALCNODE'='finalProjection');
COMMENT ON VIEW "DB1_DEMO"."ZCV_REVENUE" is 'ZCV_REVENUE';
COMMENT ON COLUMN "DB1_DEMO"."ZCV_REVENUE"."A" is 'A';
COMMENT ON COLUMN "DB1_DEMO"."ZCV_REVENUE"."B" is 'B';
COMMENT ON COLUMN "DB1_DEMO"."ZCV_REVENUE"."A_1" is 'A_1';
COMMENT ON COLUMN "DB1_DEMO"."ZCV_REVENUE"."B_1" is 'B_1';

4. Check object dependencies using following statement.

For example, the statement for checking object dependencies.

SELECT * FROM OBJECT_DEPENDENCIES WHERE DEPENDENT_OBJECT_NAME = 'ZCV_REVENUE' AND DEPENDENT_SCHEMA_NAME = 'DB1_DEMO' AND DEPENDENT_DATABASE_NAME = 'DB1';

5. The result of the dependencies looks like the following.

For example, the result of object dependencies.

BASE_DATABASE_NAME;BASE_SCHEMA_NAME;BASE_OBJECT_NAME;BASE_OBJECT_TYPE;DEPENDENT_DATABASE_NAME;DEPENDENT_SCHEMA_NAME;DEPENDENT_OBJECT_NAME;DEPENDENT_OBJECT_TYPE;DEPENDENCY_TYPE
DB1;DB1_DEMO;DB1_TEST_TABLE;TABLE;DB1;DB1_DEMO;ZCV_REVENUE;VIEW;1
DB2;DB2_DEMO;DB2_TEST_TABLE;TABLE;DB1;DB1_DEMO;ZCV_REVENUE;VIEW;1

6. Export calculation scenario with dependencies in Tenant DB1.

For example, the following statement exports the object with remote dependency.

EXPORT "DB1_DEMO"."ZCV_REVENUE" INTO '/tmp';

Please note you can only import the objects with the same database name of exported objects. You have to import each Tenant database with the example below. In this case, you have to import first based objects in Tenant DB2 because the calculation view will be invalid in Tenant DB1 without the referred objects in Tenant DB2.

You can use following statements to import the exported objects.

For example, the following statements import the cross-database access dependencies.

--In Tenant DB2
IMPORT "DB2"."DB2_DEMO"."*" FROM '/tmp';
--In Tenant DB1
IMPORT "DB1"."DB1_DEMO"."*" FROM '/tmp';

Or using the following IMPORT statements.

For example, the following statements import the cross-database access dependencies.

--In Tenant DB2
IMPORT ALL FROM '/tmp';
--In Tenant DB1
IMPORT ALL FROM '/tmp';

Limitation

This feature doesn’t support to IMPORT/EXPORT of “SAP Support Mode”.