Dynamically load S3 config, credentials & outfile

Off-Topic
1

Hello,

We have a requirement where we have to do large data exports to S3 and need the app (calling the stored proc) to be able to control the file name & location.

Below stored procedure for reference.
From what I understand, the out_file cannot be a variable. And I am left with the only option of dynamic SQL. Is anyone able to confirm this? Did I miss something?

delimiter //
CREATE OR REPLACE PROCEDURE my_proc (out_file VARCHAR(255), conditions...)
AS
/*
    Examples: CALL my_proc ('bucket/target', conditions...);
*/
DECLARE
BEGIN
        SELECT * FROM my_table WHERE <conditions>
        INTO S3 out_file 
        CONFIG '<redacted>' CREDENTIALS '<redacted>';
END //
delimiter ;

Thanks in advance!

2

Same question applies to the S3 Config & Credentials as well. Can those be loaded from variables?

3

I think that didn’t used to work. But I just tried this on an internal 7.3 build and it worked:

    delimiter //
    create procedure p() as 
    declare fn varchar(80) = '/tmp/t.csv';
    begin
      select a from t into outfile fn;
    end //
    delimiter ;

I don’t know what release added that capability. I’ll ask someone else to comment.

1 Like
4

I was able to repro the issue. It seems we don’t have test coverage for this case. We’ve filed a task to resolve the issue :slight_smile:

Haha I guess dynamic sql is the way to go right now… I’ll make a note on the task to double check on CONFIG and CREDENTIALS also.

5

Thank you for the quick verification. To clarify, we are on version 7.1.13. Please keep us posted on this. For now, I shall look into dynamic sql.