Overview:

What are Materialized views?

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.

What are the benefits of using a materialized view?

1. Performance improvement: For the same aggregation function, querying a materialized view typically performs better than querying the source table (s).
2. Freshness: A materialized view query always gives the most recent results, regardless of when materialization last occurred. The query combines the materialized component of the view with the entries in the source table that haven’t yet been materialized (the delta section), always returning the most recent results.
3. Cost reduction: Querying a materialized view uses fewer cluster resources than aggregating over the source table. If simply aggregation is necessary, the retention policy of the source table can be decreased. This configuration lowers the source table’s hot cache expenses.

Contents of the blog:

1. Creating a materialized view in HDL.
2. Surfacing the view in HANA DB.

Prerequisites:

1. The items that your materialized view will refer to are already present in the data lake Relational Engine.
2. The conditions for constructing materialized views in the database are satisfied. See Materialized Views Requirements and Restrictions for Database Option Requirements.
3. Before you create, initialize, or refresh a materialized view, ensure that all limitations have been satisfied. See Materialized Views Requirements and Restrictions for further information.
4. You have EXECUTE permission on the REMOTE EXECUTE procedure of the SAP HANA database relational container schema connected with the data lake Relational Engine relational container (SYSHDL <data lake relational container name>).

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>...)

Should have Privileges:

You have EXECUTE access to the SAP HANA database relational container schema associated with the data lake’s REMOTE EXECUTE procedure.

Example:

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;

DROP a Materialized View statement for Data Lake Relational Engine:

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