Redshift copy from S3 inside stored procedure

Question

I would like to prepare a manifest file using Lambda and then execute the stored procedure providing input parameter manifest_location.

Stored procedure signature:

CREATE OR REPLACE PROCEDURE stage.sp_stage_user_activity_page_events(manifest_location varchar(256))

and I would like to use this parameter as follows:

COPY stage.user_activity_event 
FROM manifest_location
IAM_ROLE 'arn:aws:iam::XXX:role/redshift-s3-read-only-role'
IGNOREHEADER 1
REMOVEQUOTES
DELIMITER ','
LZOP
MANIFEST;

but Redshift is giving me ERROR:

syntax error at or near "$1" Where: SQL statement in PL/PgSQL function "sp_stage_user_activity_page_events" near line 21

How can I achieve this?

Broaden

I am facing the same problem @Broaden did you find a solution? – Truncheon

Answer 1

Could you try the command below?

EXECUTE 'COPY '|| parameter_for_table ||' FROM '||CHR(39)|| parameter_for_s3_path ||CHR(39)||' IAM_ROLE '||CHR(39)|| parameter_for_iam_role ||CHR(39)||' FORMAT AS PARQUET;';

Please, let me know if you have any more questions, because I created one stored_procedure, as below today:

CREATE TABLE table_for_error_log (message varchar);
CREATE OR REPLACE procedure sp_proc_name(parameter_for_table in VARCHAR, parameter_for_s3_path in VARCHAR, parameter_for_iam_role in VARCHAR)
AS
$$
BEGIN
    EXECUTE 'COPY '|| parameter_for_table ||' FROM '||CHR(39)|| parameter_for_s3_path ||CHR(39)||' IAM_ROLE '||CHR(39)|| parameter_for_iam_role ||CHR(39)||' FORMAT AS PARQUET;';
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
    INSERT INTO table_for_error_log VALUES ('Error message: ' || SQLERRM);
END;
$$ LANGUAGE plpgsql;

It's already tested and worked.

Just replace it with your own command.

Gaudery

Answer 2

i don't have enough reputation to upvote the above answer but it works!. you can also use the quote_literal function eg: DATEFORMAT as ' || quote_literal('auto') || 'TIMEFORMAT as '|| quoteliteral('MM/DD/YYYY HH24:MI:SS') || etc..

Topsyturvy

Hi! Please try to reserve answers for actual answers in future. You can gain the required reputation for commenting by posting your own questions and answers. – String