Published at November 21, 2019by Jörg Brandeis

With version 2.0 of the SAP HANA database, another useful operator was added to SQLScript for declarative code: MAP_MERGE. It solves the occasional problem where a table function needs to be executed for all N rows of a table. The N result tables should then be combined via UNION. The MAP_MERGE operator can parallelize this task.

Without MAP_MERGE, you need imperative coding with a loop over the table. The operator lets you avoid this sequential processing. The example in Listing 1.52 shows the use of the operator. Here, the number of records is determined for a set of table names. The corresponding dynamic SQL (see also 6.7, Executing Dynamic SQL) for the table name is generated and executed in the table function MAP_TABLE_ROW_COUNT.

--Creating the MAP function
CREATE FUNCTION map_table_row_count(IN iv_tabname nvarchar(30))
RETURNS TABLE (tabname  NVARCHAR(30),
               rowcount INT )
AS BEGIN
    DECLARE lv_sql NVARCHAR(100);
    DECLARE lt_result TABLE( tabname  NVARCHAR(30),
                             rowcount INT );
    EXEC 'SELECT ''' 
         || :iv_tabname 
         || ''' AS tabname, '
         || 'COUNT(*) as rowcount '
         || 'FROM '
         || :iv_tabname 
         INTO lt_result;
    RETURN SELECT * FROM :lt_result;
END;

--Calling the MAP_MERGE operator in an anonymous block 
DO BEGIN 

  lt_table = SELECT left(table_name,30) AS tabname
               FROM m_cs_tables
              WHERE schema_name = 'SYSTEM';             
  lt_result = MAP_MERGE( :lt_table, 
                         map_table_row_count(
                                      :lt_table.tabname) );
  SELECT * FROM :lt_result;      
END;

Listing: Using the MAP_MERGE operator

In the anonymous block, all table names from the SYSTEM schema are read. The MAP_MERGE operator and the table function MAP_TABLE_ROW_COUNT are then used to count the entries. You can see the result in the following figure.

Figure: Result of the listing

Both MAP_MERGE and MAP_REDUCE belong to the area of declarative programming. They are especially useful when procedures or functions would otherwise have to be called in a loop. This is particularly advantageous because the MAP and REDUCE functions often contain imperative logic that can thus be executed in parallel.