geet
January 7, 2021, 2:11am
1
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.
hanson
January 7, 2021, 8:06pm
2
Is it possible for you to include the full structure of qry inline in the _sql string, then run it with EXECUTE IMMEDIATE? That should work.
e.g.
_sql = CONCAT ('SELECT 1, 2 FROM dual ', '
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"')
hanson
January 7, 2021, 8:26pm
3
Also, we do support substitution of certain elements using just an inline scalar variable. I don’t know if the config and credentials are supported. This works, however:
delimiter //
create or replace procedure p( ) as
declare
outFilename varchar(100) = '/tmp/tmp.csv';
begin
select 1, 2
from dual
into outfile outFilename;
end //
delimiter ;
hanson
January 7, 2021, 8:31pm
4
Also, check out the new LINKS feature. You might be able to sidestep the whole problem by putting the credentials in a link and using the link in your query.
geet
January 7, 2021, 8:59pm
5
No, the query is too complex.
This does not work in version 7.1.13. Can you please confirm what is the minimum version this works in? Maybe I can request an upgrade.
This looks quite promising. Again, looks like the minimum version required is 7.3.
Thank you for your replies.
hanson
January 7, 2021, 9:42pm
6
Inline variable substitution for file names was added in 7.3. And yes, Links are also new in 7.3.
geet
January 11, 2021, 6:34pm
7
Thank you for the confirmation @hanson