HANA Data Lake Materialized Views


Overview:

What are Materialized views?

A materialised 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 materialised view typically performs better than querying the source table (s).\
    1. Freshness: A materialised view query always gives the most recent results, regardless of when materialisation last occurred. The query combines the materialised component of the view with the entries in the source table that haven’t yet been materialised (the delta section), always returning the most recent results.
    1. Cost reduction: Querying a materialised 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
    3. Generating a SoF materialized view from a SQL-on-Files query result set

1.Creating a materialized view in HDL.

       —To store data from a query in HDLRE, we use what is called Materialized views.

Prerequisites:

  1. The items that your materialised view will refer to are already present in the data lake Relational Engine.
  2. The conditions for constructing materialised views in the database are satisfied. See Materialized Views Requirements and Restrictions for Database Option Requirements.
  3. Before you create, initialise, or refresh a materialised 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>).

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. Relational Engine container

Example:

In the data lake Relational Engine sample instance, the following example builds a materialised view containing private information about workers. It is designed to refresh automatically when staleness is detected.

CREATE MATERIALIZED VIEW EmployeeConfid2 AS

SELECT EmployeeID, Employees.DepartmentID,

    SocialSecurityNumber, Salary, ManagerID,

    Departments.DepartmentName, Departments.DepartmentHeadID

FROM GROUPO.Employees, GROUPO.Departments

WHERE Employees.DepartmentID=Departments.DepartmentID

AUTO FULL REFRESH;

Another example:

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;

DROP a Materialized View statement for Data Lake Relational Engine:

Removes a materialized view from the database.

DROP MATERIALIZED VIEW [ IF EXISTS ] [ <owner>.]<view-name>

Example:

DROP MATERIALIZED VIEW IF EXISTS CUSTOMERVIEW;

 2. Surfacing the view in HANA DB

To surface a view in the HANA Database, first we need to create a view through a ‘REMOTE_EXECUTE’ which helps to Execute SQL commands on the remote data lake Relational Engine source. ‘SYSHDL_TIERING_CONTAINER is the remote container source that we one has created.

Firstly, connect to DBADMIN or any other HANA DB User.

Over here I am connected as a HANA DB User and I am executing the following commands.

Note: There Should be a CUSTOMER table already defined in the HDLRE.

We use the “CREATE VIEW” statement so that it Creates a virtual table whose contents (columns and rows) are defined by a query.

The following SQL statement will CREATE view CUSTOMERVIEW from the SELECT statement on which the view is based through a REMOTE_EXECUTE in HDLRE

 

call SYSHDL_TIERING_CONTAINER.REMOTE_EXECUTE

(‘

    CREATE VIEW CUSTOMERVIEW AS

        SELECT * FROM “CUSTOMER”

‘ );

The following SQL statement will CREATE a VIRTUAL TABLE HDL_CUSTOMERVIEW in HDLRE.
Run the following command as HDLADMIN or a HDLRE User.

 

I am running the below SQL statement as a HDLRE User.

 

 

CREATE VIRTUAL TABLE HDL_CUSTOMERVIEW

(

   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”;

 

3. Generating a SoF materialized view from a SQL-on-Files query result set

SQL on Files is a capability of the Data Lake Files service in SAP HANA Cloud, data lake that allows you to query files that contain structured data that are sitting in your data lake file container.

Use SQL on Files to lower the cost of analyzing large amounts of data of unknown value that is sitting in files.

SQL on Files allows you to perform some pre-exploration and data filtering on the data before moving aggregations of it, or all of it, into a database such as Data Lake, IQ, NSE disk storage, or SAP HANA Cloud, HANA database. You can even create views on the data.

Login to HDLADMIN or to your HDLRE User and perform the following operations:

–Creating a SoF Structure

CREATE TABLE TPCH_SOF.ORDERS(

    O_ORDERKEY           bigint                        ,

    O_CUSTKEY            integer                       ,

    O_ORDERSTATUS        varchar(2)                    ,

    O_TOTALPRICE         decimal(15,2)                 ,

    O_ORDERDATE          date                          ,

    O_ORDERPRIORITY      varchar(15)                   ,

    O_CLERK              varchar(15)                   ,

    O_SHIPPRIORITY       integer                       ,

    O_COMMENT            varchar(79)                   ,

) IN FILES_SERVICE;

CREATE EXISTING TABLE ORDERS(

    O_ORDERKEY           bigint                        ,

    O_CUSTKEY            integer                       ,

    O_ORDERSTATUS        varchar(2)                    ,

    O_TOTALPRICE         decimal(15,2)                 ,

    O_ORDERDATE          date                          ,

    O_ORDERPRIORITY      varchar(15)                   ,

    O_CLERK              varchar(15)                   ,

    O_SHIPPRIORITY       integer                       ,

    O_COMMENT            varchar(79)                   ,

)  AT ‘MY_REMOTE_SERVER..TPCH_SOF.ORDERS’;

Get specific year and month data

Select * FROM ORDERS WHERE YEAR(O_ORDERDATE)=2025 AND MONTH(O_ORDERDATE)=05;

–Now generating a SoF materialized view from a SQL-on-Files query result set

CREATE MATERIALIZED VIEW ORDERSSOFVIEW AS

Select O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE, O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT

FROM ORDERS

WHERE YEAR(O_ORDERDATE)=2025 AND MONTH(O_ORDERDATE)=05

AUTO FULL REFRESH;

One can also refer the doc for creating view in SoF – Views in SQL on Files | SAP Help Portal

Conclusion:

That’s how one can easily create and manage Materialized views in HANA and in the HDLRE and could also Create and run user defined and system views that reference SQL on Files virtual tables.

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