Transforming HANA SQL tables (Catalog Object) to .hdbtable definition file (Repository Object)

Hi,

in the past I had a project where it was needed to transform existing HANA SQL tables into the .hdbtable format (reason was the easier transport via Delivery Unit for Repository Objects).

For this task I have written a procedure which you can execute and as a result you will get the result which you can copy&paste (right click on the rows -> “Copy rows”) to your new .hdbtable file. Afterwards just activating the .hdbtable file and the table is successfully transformed into the new format:

Example execution:

call CORP_PUBLIC.PR_TRANSFORM_SQL_TABLE_TO_HDBTABLE(‘<SOURCE_SCHEMA>’, ‘<SOURCE_TABLE>’, ‘<TARGET_SCHEMA’, ‘TARGET_TABLE’, ‘<ROW/COLUMNSTORE>’);

<SOURCE_SCHEMA>: Schema where the SQL table is stored

<SOURCE_TABLE>: Name of the SQL table (catalog object)

<TARGET_SCHEMA>: Schema, where the new table should be stored

<TARGET_TABLE>: Name of the target table

<ROW/COLUMNSTORE>: ROWSTORE or COLUMNSTORE –> format of the new table

Procedure definition:

=============================

CREATE PROCEDURE CORP_PUBLIC.PR_TRANSFORM_SQL_TABLE_TO_HDBTABLE

      (IN SOURCE_SCHEMA_NAME NVARCHAR (256),

       IN SOURCE_TABLE_NAME  NVARCHAR (256),

       IN TARGET_SCHEMA_NAME NVARCHAR (256),

       IN TARGET_TABLE_NAME NVARCHAR (256),

       IN TARGET_TABLE_TYPE NVARCHAR (256))

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

result_01 = SELECT ‘table.schemaName = “‘ || :TARGET_SCHEMA_NAME || ‘”;’ AS “RESULT” FROM DUMMY;

result_02 = SELECT ‘table.tableType = ‘ || :TARGET_TABLE_TYPE || ‘;’ AS “RESULT” FROM DUMMY;

result_03 = SELECT ‘table.columns = [‘ AS “RESULT” FROM DUMMY;

result_04 = SELECT

  CASE WHEN ROW_NUMBER() OVER (ORDER BY “POSITION”) = (select max(“ROWNUM”) from

(SELECT

  CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(‘{name = “‘ , “COLUMN_NAME”), ‘”; sqlType = ‘), “DATA_TYPE_NAME”), CASE WHEN “SCALE” IS NULL and “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(‘; length = ‘, “LENGTH”) WHEN “SCALE” IS NOT NULL AND “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(CONCAT(CONCAT(‘; precision = ‘, “LENGTH”), ‘; scale = ‘), “SCALE”) ELSE ” END), CASE WHEN “IS_NULLABLE” = ‘TRUE’ THEN ‘; nullable = true;’ ELSE ‘; nullable = false;’ END),CASE WHEN “DEFAULT_VALUE” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ defaultValue = “‘,”DEFAULT_VALUE”), ‘”;’) END), CASE WHEN “COMMENTS” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ comment = “‘,”DEFAULT_VALUE”), ‘”;’) END), ‘} ];’) AS “RESULT”,

  ROW_NUMBER() OVER (ORDER BY “POSITION”) AS “ROWNUM”

FROM “PUBLIC”.”TABLE_COLUMNS”

WHERE “SCHEMA_NAME” = :SOURCE_SCHEMA_NAME and “TABLE_NAME” = :SOURCE_TABLE_NAME

ORDER BY “POSITION” ASC))

THEN CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(‘{name = “‘ , “COLUMN_NAME”), ‘”; sqlType = ‘), “DATA_TYPE_NAME”), CASE WHEN “SCALE” IS NULL and “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(‘; length = ‘, “LENGTH”) WHEN “SCALE” IS NOT NULL AND “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(CONCAT(CONCAT(‘; precision = ‘, “LENGTH”), ‘; scale = ‘), “SCALE”) ELSE ” END), CASE WHEN “IS_NULLABLE” = ‘TRUE’ THEN ‘; nullable = true;’ ELSE ‘; nullable = false;’ END),CASE WHEN “DEFAULT_VALUE” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ defaultValue = “‘,”DEFAULT_VALUE”), ‘”;’) END), CASE WHEN “COMMENTS” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ comment = “‘,”DEFAULT_VALUE”), ‘”;’) END), ‘} ];’)

ELSE

CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(‘{name = “‘ , “COLUMN_NAME”), ‘”; sqlType = ‘), “DATA_TYPE_NAME”), CASE WHEN “SCALE” IS NULL and “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(‘; length = ‘, “LENGTH”) WHEN “SCALE” IS NOT NULL AND “DATA_TYPE_NAME” NOT IN (‘INTEGER’,’DATE’,’TIMESTAMP’,’CLOB’,’BLOB’,’NCLOB’,’SMALLINT’,’BIGINT’,’SMALLDECIMAL’,’SECONDDATE’) THEN CONCAT(CONCAT(CONCAT(‘; precision = ‘, “LENGTH”), ‘; scale = ‘), “SCALE”) ELSE ” END), CASE WHEN “IS_NULLABLE” = ‘TRUE’ THEN ‘; nullable = true;’ ELSE ‘; nullable = false;’ END),CASE WHEN “DEFAULT_VALUE” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ defaultValue = “‘,”DEFAULT_VALUE”), ‘”;’) END), CASE WHEN “COMMENTS” IS NULL THEN ” ELSE CONCAT(CONCAT(‘ comment = “‘,”DEFAULT_VALUE”), ‘”;’) END), ‘},’)

END AS “RESULT”

FROM “PUBLIC”.”TABLE_COLUMNS”

WHERE “SCHEMA_NAME” = :SOURCE_SCHEMA_NAME and “TABLE_NAME” = :SOURCE_TABLE_NAME

ORDER BY “POSITION” ASC;

result_05 = select CASE WHEN ROW_NUMBER() OVER (ORDER BY “COLUMN_NAME”) = (select max(“ROWNUM”) from(

select ‘”‘ || “COLUMN_NAME” || ‘”,’ AS “RESULT”, ROW_NUMBER() OVER (ORDER BY “COLUMN_NAME”) AS “ROWNUM”

FROM “PUBLIC”.”TABLE_COLUMNS” where SCHEMA_NAME = :SOURCE_SCHEMA_NAME and TABLE_NAME =  :SOURCE_TABLE_NAME and “INDEX_TYPE” = ‘FULL’

)) THEN ‘”‘ || “COLUMN_NAME” || ‘”];’ WHEN ROW_NUMBER() OVER (ORDER BY “COLUMN_NAME”) = 1 THEN ‘table.primaryKey.pkcolumns = [“‘ || “COLUMN_NAME” || ‘”,’ ELSE ‘”‘ || “COLUMN_NAME” || ‘”,’ END AS “RESULT”

FROM “PUBLIC”.”TABLE_COLUMNS” where SCHEMA_NAME = :SOURCE_SCHEMA_NAME and TABLE_NAME = :SOURCE_TABLE_NAME and “INDEX_TYPE” = ‘FULL’;

SELECT “RESULT” from :result_01 UNION SELECT “RESULT” from :result_02 UNION SELECT “RESULT” from :result_03 UNION SELECT “RESULT” from :result_04 UNION SELECT “RESULT” from :result_05;

END;

=============================

If you find any improvements, feel free to contact me. 🙂

BR,

Alex