Execute Query type in dynamic SQL

#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:

  1. pass the query as param to the stored proc
  2. declare the qry as a variable in the dynamic sql itself
  3. 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.

#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"')
#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 ;
#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.


#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.

#6

Inline variable substitution for file names was added in 7.3. And yes, Links are also new in 7.3.

#7

Thank you for the confirmation @hanson