Can SDI FlowGrpah be created via Hana SQL script

[Former Member]

I have a Hana SQL script that I run via Hana Development Studio. The script;

1. creates a VIRTUAL TABLE from a provisioned remote data source

2. creates a COLUMN TABLE based on the VT created in step 1

I would now like to add to code that will create a generic SDI FlowGraph which uses the VT as source, CT as target and a filter to map input to output columns.

Current SQL script:

do begin DECLARE lv_source_schame VARCHAR (200) := 'EDW_LDG_OWNER'; /* CHANGE THE SORUCE SCHEMA NAME */ DECLARE lv_target_schame VARCHAR (200) := 'NETWORK_ENERGY'; /* CHANGE THE TARGET SCHEMA NAME */ DECLARE lv_table VARCHAR (200) := 'OMS_LG'; /* CHANGE THE TABLE NAME (without _VT) */ DECLARE lv_script VARCHAR (4000) := ''; DECLARE CRLF VARCHAR (2) := CHAR(13) || CHAR(10); DECLARE CURSOR c_tables FOR SELECT * FROM TABLE_COLUMNS WHERE (SCHEMA_NAME = :lv_target_schame AND TABLE_NAME = :lv_table || '_VT') ORDER BY COLUMN_ID; DECLARE CURSOR c_keys FOR SELECT * FROM CONSTRAINTS WHERE (SCHEMA_NAME = :lv_target_schame AND TABLE_NAME = :lv_table || '_VT' AND IS_PRIMARY_KEY = 'TRUE') ORDER BY POSITION; --------------------------------------------------------------------------------------------------------------------------------- -- Create VIRTUAL TABLE --------------------------------------------------------------------------------------------------------------------------------- lv_script := '/* CREATE VIRTUAL TABLE from provisioned remote data store */' || CRLF; lv_script := 'CREATE VIRTUAL TABLE "' || lv_target_schame || '"."' || lv_table || '_VT" AT "' || lv_source_schame || '"."<NULL>"."<NULL>"."""' || lv_source_schame || '"".""' || lv_table || '""";'; --------------------------------------------------------------------------------------------------------------------------------- EXEC lv_script; --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- -- Create COLUMN TABLE --------------------------------------------------------------------------------------------------------------------------------- lv_script := '/* CREATE COLUMN TABLE for target data store */' || CRLF; lv_script := lv_script || 'CREATE COLUMN TABLE "' || lv_target_schame || '"."ergon.Data::' || lv_table || '" (' || CRLF; FOR cur_row as c_tables DO lv_script := lv_script || ' "' || cur_row.COLUMN_NAME || RPAD('"', 25-LENGTH(cur_row.COLUMN_NAME)) || cur_row.DATA_TYPE_NAME; IF cur_row.DATA_TYPE_NAME = 'DATE' OR cur_row.DATA_TYPE_NAME = 'TIME' OR cur_row.DATA_TYPE_NAME = 'SECONDDATE' OR cur_row.DATA_TYPE_NAME = 'TIMESTAMP' OR cur_row.DATA_TYPE_NAME = 'TINYINT' OR cur_row.DATA_TYPE_NAME = 'SMALLINT' OR cur_row.DATA_TYPE_NAME = 'INTEGER' OR cur_row.DATA_TYPE_NAME = 'BIGINT' OR cur_row.DATA_TYPE_NAME = 'SMALLDECIMAL' OR cur_row.DATA_TYPE_NAME = 'REAL' OR cur_row.DATA_TYPE_NAME = 'DOUBLE' OR cur_row.DATA_TYPE_NAME = 'TEXT' OR cur_row.DATA_TYPE_NAME = 'BINTEXT' OR cur_row.DATA_TYPE_NAME = 'BOOLEAN' THEN lv_script := lv_script || ','; ELSEIF cur_row.data_type_name = 'VARCHAR' OR cur_row.DATA_TYPE_NAME = 'NVARCHAR' OR cur_row.DATA_TYPE_NAME = 'ALPHANUM' OR cur_row.DATA_TYPE_NAME = 'VARBINARY' OR cur_row.DATA_TYPE_NAME = 'SHORTTEXT' OR cur_row.DATA_TYPE_NAME = 'FLOAT' THEN lv_script := lv_script || '(' || TO_CHAR (cur_row.LENGTH) || '),'; ELSEIF cur_row.data_type_name = 'DECIMAL' THEN lv_script := lv_script || '(' || TO_CHAR (cur_row.LENGTH) || ',' || TO_CHAR (IFNULL (cur_row.SCALE,0)) || '),'; END IF; lv_script := lv_script || CRLF; END FOR; lv_script := lv_script || 'PRIMARY KEY ('; FOR cur_row as c_keys DO lv_script := lv_script || '"' || cur_row.COLUMN_NAME || '",'; END FOR; lv_script := TRIM(TRAILING ',' FROM lv_script) || ')' || CRLF|| ');' || CRLF; lv_script := lv_script || CRLF || CRLF; --------------------------------------------------------------------------------------------------------------------------------- EXEC lv_script; --------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------- -- Create FLOWGRAPH --------------------------------------------------------------------------------------------------------------------------------- lv_script := '/* CREATE FLOWGRAPH */' || CRLF; --------------------------------------------------------------------------------------------------------------------------------- EXEC lv_script; --------------------------------------------------------------------------------------------------------------------------------- END;