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. :smile:
BR,
Alex
2 Comments
