Creating CDS/EDM views from RAW HANA SQL TABLES

If you need to create a CDS/EDM views from raw SQL HANA tables..here is a script. There seems to be no automatic utility in HANA studio.

The below assumes you know SQL and how to create a CDS/EDM view.

A definition of a SQL table, which need to be converted.


CREATE TABLE MY_TABLE
(
COl1 NVARCHAR(3 ),
COL2 INTEGER,
COL3 NVARCHAR(40 ),
COL4 DATE,
COL5 DATE,
COL6 DECIMAL
)
;

Which you want to convert to CDS/EDM Format

namespace ZXXX1.ZXXX_IL.ZXXX_TDEXT.YYY;

@Schema: ‘XXX_RBM’
context YYYY {
@Catalog.tableType : #COLUMN
Entity MY_TABLE {
key ID : Integer;
COl1 : String(3);
COL2 : Integer;
COL3 : String(40);
COL4 : UTCTimestamp;
COL5 : UTCTimestamp;
COL6 : Decimal(12,4);
} ;
};

If the SQL table MY_TABLE exists in some schema the below SQL will generate a script to create the EDM /CDS view. Note the below does nothing just generates a script.

The output of this SQL is a CDS/EDM create script. You will need to run this script in another schema to create the views. The below script will also generate a unique key field for each table – which is required for the CDS version of the table.

drop view table_col_list;
Create view table_col_list as
(
select table_columns.*
from table_columns
where
schema_name ='MYSCHEMA'
-- filter table_name as required..
and table_name = 'MY_TABLE'
);

--drop table CDS_SCRIPT;
--Create table CDS_SCRIPT as
(
select 'namespace ZXXX1.ZXXX_IL.ZXXX_TDEXT.YYY;' as CDS_SCRIPT from dummy union all
select '' as CDS_SCRIPT from dummy union all --new line

select '@Schema: ''XXX_RBM''' from dummy union all
select 'context YYYY { ' from dummy union all
select '' from dummy union all --new line
select CDS_SCRIPT from
(
select * from
(
select distinct table_name , -2 as position, ' @Catalog.tableType : #COLUMN' as CDS_SCRIPT from table_col_list union all
select distinct table_name , -1 as position, ' Entity ' || table_name || ' {' as CDS_SCRIPT from table_col_list union all
-- create ID column - the CDS script needs a unique key , which you can fill with select "$rowid$" from table.
select distinct table_name , 0 as position, lpad('', 8) || substr(rpad('key ID' , 40), 1, 40) || ' : Integer; ' as CDS_SCRIPT from table_col_list union all
select
table_name , position,
-- generate CDS columns
lpad('', 8) || substr(rpad(column_name , 40), 1, 40) || ' : ' ||
-- generate CDS columns types from SQL types
case
when data_type_name = 'NVARCHAR' then 'String' || '(' || length ||')'
when data_type_name = 'VARCHAR' then 'String' || '(' || length ||')'
when data_type_name = 'TIMESTAMP' then 'UTCTimestamp'
when data_type_name = 'INTEGER' then 'Integer'
when data_type_name = 'DECIMAL' then 'Decimal' || '(' || length || ',' || scale || ')'
else data_type_name || ' UNKNOWN Please define '
--see for more types
---https://help.sap.com/doc/52715f71adba4aaeb480d946c742d1f6/2.0.00/en-US/cf394efd3fb4400f9c09d10315028515.html
end
|| '; '
as CDS_SCRIPT
from table_col_list
union all
select distinct table_name , 1000 , ' } ; ' as CDS_SCRIPT from table_col_list union all
select distinct table_name , 1001 , '' as CDS_SCRIPT from table_col_list --new line

) order by 1, 2, 3

)
union all
select '};' as CDS_SCRIPT from dummy
)

Once you have created the CDS views. You may want to also copy the data. The SQL script simply deletes the destination data and reloads. assumes first column is ID. again the below SQL does nothing , you must run the SQL script in the appropriate schema.

drop view table_col_list;
Create view table_col_list as
(
select a.*, col_count from

(select table_columns.*
from table_columns
where
schema_name ='MYSCHEMA'

) a

inner join

(select table_name , count(*) as col_count
from table_columns
where
schema_name ='MYSCHEMA'

group by table_name

) b on (a.table_name = b.table_name)

order by table_name, position

);

-- drop table CDS_SCRIPT;
--Create table CDS_SCRIPT as
(

select CDS_SCRIPT from
(
select * from
(
select distinct table_name , -3 as position, ' truncate table "XXXXXX_RBM"."ZXXX1.ZXXX_IL.ZXXX_TDEXT.YYY::YYY.' || table_name || '" ;' as CDS_SCRIPT from table_col_list union all

select distinct table_name , -2 as position, ' insert into "XXXXXX_RBM"."ZXXX1.ZXXX_IL.ZXXX_TDEXT.YYY::YYY.' || table_name || '" ' as CDS_SCRIPT from table_col_list union all

-- Select clause
select distinct table_name , -1 , ' select ' as CDS_SCRIPT from table_col_list union all
select distinct table_name , 0, lpad('', 8) || '"$rowid$" as ID,' as CDS_SCRIPT from table_col_list union all

select
table_name , position,
-- generate CDS columns
lpad('', 8) || substr(rpad(column_name , 40), 1, 40)

|| case when position col_count then ', ' else '' end -- no comma at end
as CDS_SCRIPT
from table_col_list union all
-- the CDS script need a unique key , which you can fill with select "$rowid$" from table. no trailing comma!

select distinct table_name , 900 as position, ' from "MYSCHEMA"."' || table_name || '";' as CDS_SCRIPT from table_col_list

) order by 1, 2, 3
)
)