
A materialized view is a database object that holds a query's results. It might be a local copy of distant data, a subset of a table's rows and/or columns, the outcome of a join, or a summary produced by an aggregate function, for instance.
1. Creating a materialized view in HDL.
To store data from a query in HDLRE, we use what is called Materialized views.
Refer doc - Creating a Data Lake Relational Engine Materialized View | SAP Help Portal
Syntax for creating a Materialized view in HDL
CREATE MATERIALIZED VIEW [<owner.>]<view-name> [ ( <alt-column-names>) ] AS <select-statement> [ { AUTO | MANUAL } FULL REFRESH ] <alt-column-names> ::= ( <column-name>[, <column-name>...)
You have EXECUTE access to the SAP HANA database relational container schema associated with the data lake's REMOTE EXECUTE procedure.
First, we create the CUSTOMER and ORDERS tables in HDLRE through a HANA Connection.
Open a SQL console connected to your HANA Connection (DBADMIN or HANA User) and then we Create a new data lake Relational Engine relational container first.
We run the below SQL statement to create the Relational Container.
CALL SYSHDL.CREATE_CONTAINER('TIERING_CONTAINER','<DBADMIN or any other HANA USER>');
DBX screenshot:
Creating the Table definitions:
This data lake Relational Engine (SAP HANA DB-managed) SQL statement can be used when Connected to SAP HANA database as a SAP HANA database user and using the REMOTE_EXECUTE () procedure.
To pass DDL statements directly to HDLRE, a REMOTE EXECUTE () call is used.
CALL SYSHDL_TIERING_CONTAINER.REMOTE_EXECUTE (' CREATE TABLE "CUSTOMER" ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) ); CREATE TABLE "ORDERS" ( O_ORDERKEY bigint not null, O_CUSTKEY integer not null, O_ORDERSTATUS varchar(2) not null, O_TOTALPRICE decimal(15,2) not null, O_ORDERDATE date not null, O_ORDERPRIORITY varchar(15) not null, O_CLERK varchar(15) not null, O_SHIPPRIORITY integer not null, O_COMMENT varchar(79) not null, primary key (O_ORDERKEY) ); ');
CREATE MATERIALIZED VIEW CUSTOMERVIEW AS SELECT CUSTOMER.C_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_PHONE, ORDERS.O_ORDERSTATUS, ORDERS.O_TOTALPRICE FROM CUSTOMER, ORDERS WHERE CUSTOMER.C_CUSTKEY=ORDERS.O_CUSTKEY AUTO FULL REFRESH;
And in order to run the above SQL statement from a HANA Connection, we need to wrap it up in a REMOTE_EXECUTE (). Like below
CALL SYSHDL_TIERING_CONTAINER_TEST.REMOTE_EXECUTE (' CREATE MATERIALIZED VIEW CUSTOMERVIEW AS SELECT CUSTOMER.C_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_PHONE, ORDERS.O_ORDERSTATUS, ORDERS.O_TOTALPRICE FROM CUSTOMER, ORDERS WHERE CUSTOMER.C_CUSTKEY=ORDERS.O_CUSTKEY AUTO FULL REFRESH; ');
2. Surfacing a MATERILIZED VIEW IN HANA
In order to expose the Materialized view to HANA, we will need to create a virtual table in HANA that will reference the Materialized view that we created above.
We will be creating the VIRTUAL HANA tables using the DBADMIN connection
CREATE VIRTUAL TABLE CUSTOMER ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) ) AT "SYSHDL_TIERING_CONTAINER_SOURCE"."NULL"."SYSHDL_TIERING_CONTAINER"."CUSTOMERVIEW" WITH REMOTE;
This is the basic generalized syntax which Removes a materialized view from the database.
DROP MATERIALIZED VIEW [ IF EXISTS ] [ <owner>.]<view-name>
Again, to remove the Materialized view we need to wrap it up under the REMOTE_EXECUTE () call. Like below.
CALL SYSHDL_TIERING_CONTAINER_TEST.REMOTE_EXECUTE (' DROP MATERIALIZED VIEW IF EXISTS CUSTOMERVIEW; ');
ALTER a Materialized View:
This is the basic generalized syntax for Altering a Materialized view:
ALTER MATERIALIZED VIEW [ <owner>]<view-name> { SET HIDDEN | { ENABLE | DISABLE } | RENAME { PARTITION | SUBPARTITION } <range-partition-name> TO <new-range-partition-name> | SPLIT { PARTITION | SUBPARTITION } <split-object> | MERGE { PARTITION | SUBPARTITION } <partition-name-1> INTO <partition-name-2> | PARTITION BY { <range-partitioning-scheme> | <hash-partitioning-scheme> | <hash-range-partitioning-scheme> } | SUBPARTITION BY RANGE <range-partition-decl> | ADD { PARTITION | SUBPARTITION } BY RANGE <range-partition-decl> | UNPARTITION | [ { AUTO | MANUAL } FULL REFRESH ] }
Some cleanup:
1. Drop the Materliazed view that was created using the below syntax
CALL SYSHDL_TIERING_CONTAINER_TEST.REMOTE_EXECUTE (' DROP MATERIALIZED VIEW IF EXISTS CUSTOMERVIEW; ');
2. Drop the Table definitions that were created
CALL SYSHDL_TIERING_CONTAINER.REMOTE_EXECUTE (' DROP TABLE CUSTOMER; DROP TABLE ORDERS; '); DROP TABLE CUSTOMER WITH REMOTE
3. Drop the Remote Container
CALL SYSHDL.DELETE_CONTAINER('<relational_container_name>');
Conclusion:
That’s how one can easily create and manage Materialized views in HDLRE and that could easily surfaced onto HANA.
Would love to read any suggestions or feedbacks on the blog post. Please do give a like if you found the information useful also feel free to follow me to get information on similar content.
Request everyone reading the blog to also go through the following links for any further assistance.
SAP HANA Cloud, data lake — post and answer questions here,
and read other posts on the topic you wish to discover here
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
24 | |
21 | |
15 | |
14 | |
12 | |
10 | |
9 | |
7 | |
7 | |
6 |