Hi,
I was trying to create a procedure, in which I am trying to create a table dynamically with the columns passed as argument.
In the same procedure, I am trying to create a pipeline dynamically on the same table with a csv file.
I am using dynamic SQL to create table and the Pipeline.
I am able to create the procedure successfully, but when executing , I am getting error that create pipeline is not compatible with the procedure.
Can you suggest if there is anyway I can create the pipeline dynamically on run time dynamically.
I can see in below link that create pipeline is not available in procedure.
Procedure:
SET sql_mode = ‘PIPES_AS_CONCAT’;
DELIMITER //
CREATE PROCEDURE SP_CREATE_TABLE(table_name varchar(200), columns text, file_path text ) AS
DECLARE
sql_query text;
BEGIN
sql_query = 'CREATE TABLE ' || table_name || ' (' || columns || ');';
EXECUTE IMMEDIATE sql_query;
sql_query = 'CREATE PIPELINE ' || table_name || '_PIPELINE' || ' AS LOAD DATA FS \'' || file_path || '/' || table_name || '.csv\'' || ' INTO TABLE ' || table_name || ' FIELDS TERMINATED BY \',\' ENCLOSED BY \'\' LINES TERMINATED BY \'\\n\' STARTING BY \'\' IGNORE 1 LINES';
EXECUTE IMMEDIATE sql_query;
END //
DELIMITER ;
call SP_CREATE_TABLE(“test_table”, “DIVISION VARCHAR(100),AREA VARCHAR(100),PROFILE VARCHAR(200)”, “/xx/yy/z/”);
ERROR 2240 (HY000): Unhandled exception
Type: ER_UNSUPPORTED_DDL_IN_SP
Message: The query CREATE PIPELINE test_table_PIPELINE AS LOAD DATA FS '/xx/yy/z//test_table.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' E... contains DDL that is unsupported in stored procedures
Thanks
Manas