Hi,
We are having to dump fairly large data sets to S3 & are having to rely on dynamic sql because of the issue Dynamically load S3 config, credentials & outfile
Here’s some pseudo code…
delimiter //
CREATE OR REPLACE PROCEDURE my_proc (
in_file_path VARCHAR(1024),
in_aws_access_key_id VARCHAR(128),
in_aws_secret_access_key VARCHAR(128),
in_aws_role_arn VARCHAR(2048)
) AS
/*
Examples: CALL my_proc('<file-path>', '<redacted>', '<redacted>', '<redacted>');
*/
DECLARE
_sql TEXT = '';
qry QUERY(COL1 INT, COL2 INT) = SELECT 1,2;
BEGIN
_sql = CONCAT ('SELECT * FROM ', qry, '
INTO S3 ''', in_file_path, '''
CONFIG ''{"region":"<redacted>"}''
CREDENTIALS ''{"aws_access_key_id":"', in_aws_access_key_id, '", "aws_secret_access_key":"', in_aws_secret_access_key, '", "role_arn":"', in_aws_role_arn, '"}''
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"');
EXECUTE IMMEDIATE _sql;
END //
delimiter ;
This results in “ER_NON_SCALAR_IN_SCALAR_EXPR: Compilation error in function my_proc
near line 9: A non-scalar value ‘qry
’ is used as a scalar”
I have tried multiple approaches:
- pass the query as param to the stored proc
- declare the qry as a variable in the dynamic sql itself
- return the qry from another stored proc & then try to call the stored proc within the dynamic sql
… etc.
Any ideas on how this can be accomplished will be much appreciated.
Thanks.