CODEWITHSUNDEEP
amazon-web-servicesstored-proceduresamazon-redshift
datahack
datahack

Reputation: 691

Redshift copy from S3 inside stored procedure

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?

Upvotes: 6

Views: 1590

Answers (2)

Daxiongben
Daxiongben

Reputation: 1

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

Upvotes: 0

Ernani Murtinho
Ernani Murtinho

Reputation: 31

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.

Upvotes: 3

Related Questions