Hi everyone, in this blog post I'm gonna show you how to query data across databases. As we know, the concept of multitenant database containers (MDC) was introduced in SAP HANA SPS09. With MDC, now we can run multiple tenant databases on one SAP HANA system and that enables strong separation of data and users as well. But do you know the cross-database queries are also supported in SAP HANA SPS09? For example, it's possible for one user in its tenant database to query data from another tenant database. But how can we achieve this? Is it enabled by default? Maybe you have a lot of questions. Don't worry. Let's first have a look at some basics of the cross-database queries and I'll show you an example step by step.
First of all, I'd like to share with you some useful materials about this topic. You can find everything I showed in this post from the following materials. If you're interested, you can read them and make the same example with mine.
Here I just borrowed page 41 from SAP HANA SPS 09 - What's New? - Security, since I think this page highlighted the key concepts well.
The key concepts can be summarized as:
Based on the key concepts, we can create a scenario to test the cross-database queries in SAP HANA SPS09 MDC environment.
As the above image displayed,
Now comes the problem. How can we achieve this? :???:
Notice: At the beginning of each step, I'll first show <DATABASE>(<USERNAME>) in this step.
1. SYSTEMDB(SYSTEM) Create three tenant databases
CREATE DATABASE DB1 SYSTEM USER PASSWORD Manager1;
CREATE DATABASE DB2 SYSTEM USER PASSWORD Manager2;
CREATE DATABASE DB3 SYSTEM USER PASSWORD Manager3;
2. SYSTEMDB(SYSTEM) Make the configuration in red box at the system layer, Administration -> Configuration -> global.ini -> cross_database_access
We first enable the cross-database queries and then set DB1 and DB2 as targets for DB3.
3. DB3(SYSTEM) Create USER_C in the tenant database DB3
CREATE USER USER_C PASSWORD aA111111;
4. DB1(SYSTEM) Create USER_A in the tenant database DB1 and associate USER_A with USER_C in the tenant database DB3
CREATE USER USER_A PASSWORD aA111111;
ALTER USER USER_A ADD REMOTE IDENTITY USER_C AT DATABASE DB3;
You can also use CREATE USER ... WITH REMOTE IDENTITY ... statement directly like in step 5.
5. DB2(SYSTEM) Create USER_B in the tenant database DB2 and associate USER_B with USER_C in the tenant database DB3
CREATE USER USER_B PASSWORD aA111111 WITH REMOTE IDENTITY USER_C AT DATABASE DB3;
You can also use CREATE USER plus ALTER USER statements like in step 4.
6. DB1(USER_A) Create the PRODUCT table and insert some products.
CREATE COLUMN TABLE USER_A.PRODUCT (
ID INTEGER,
NAME NVARCHAR(100),
LOC NVARCHAR(100),
DESC NVARCHAR(100)
);
INSERT INTO USER_A.PRODUCT VALUES (1001, 'Product A', 'CN', 'Made in China');
INSERT INTO USER_A.PRODUCT VALUES (1002, 'Product B', 'CN', 'Made in China');
7. DB3(USER_C) Can't wait anymore? OK. Let's first let USER_C query products made in China
SELECT * FROM DB1.USER_A.PRODUCT;
Here you go.
8. DB2(USER_B) Create the PRODUCT table and insert some products
CREATE COLUMN TABLE USER_B.PRODUCT (
ID INTEGER,
NAME NVARCHAR(100),
LOC NVARCHAR(100),
DESC NVARCHAR(100)
);
INSERT INTO USER_B.PRODUCT VALUES (2001, 'Product C', 'US', 'Made in US');
INSERT INTO USER_B.PRODUCT VALUES (2002, 'Product D', 'US', 'Made in US');
9. DB3(USER_C) Now let USER_C query products made in US.
SELECT * FROM DB2.USER_B.PRODUCT;
10. DB3(USER_C) Create the PRODUCT view and query the data from this view.
CREATE VIEW USER_C.PRODUCT AS
SELECT * FROM DB1.USER_A.PRODUCT
UNION ALL
SELECT * FROM DB2.USER_B.PRODUCT;
SELECT * FROM USER_C.PRODUCT;
Till now we've successfully achieved the scenario we created. :cool:
- DB3(USER_C) Failed to insert data into table DB1.USER_A.PRODUCT since only SELECT is allowed.
INSERT INTO DB1.USER_A.PRODUCT VALUES (1003, 'Product E', 'CN', 'Made in China');
- DB1(USER_A) Failed to let USER_A query data from the view DB3.USER_C.PRODUCT since the unidirectional association.
SELECT * FROM DB3.USER_C.PRODUCT;
- DB2(SYSTEM) Failed to associate USER_B with USER_A at the tenant database DB1 since only one user is allowed.
ALTER USER USER_B ADD REMOTE IDENTITY USER_A AT DATABASE DB1;
- DB1(SYSTEM) Have a look at remote users.
SELECT * FROM REMOTE_USERS;
You can do the same with DB2(SYSYEM).
That's it! Hope you enjoyed reading my blog and making cross-database queries successfully. :smile:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
38 | |
19 | |
13 | |
13 | |
11 | |
10 | |
10 | |
10 | |
8 | |
8 |