Execute Query type in dynamic SQL

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.

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"')

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 ;

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.


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.

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

Thank you for the confirmation @hanson