Process a string and insert into Redshift

0

HI all.

I am trying to process a string containing multiple rows for example ‘“value1”,“value2",“value3”|“value4",“value5”,“value6" or like '{“id”: 1006410,“title”: “Amazon Redshift Database Developer Guide”}{“id”: 100540,“title”: “Amazon Simple Storage Service User Guide”}' and insert it into a table in Redshift I know this can be done using a programming language but I am trying to do it using just SQL inside a stored procedure.

Is there any SQL function/code that could help with this?

AWS
asked 2 years ago971 views
1 Answer
0

First, create an example table:

create table mytable (column1 varchar(10), column2 varchar(10), column3 varchar(10));

The insert is pretty straight forward:

insert into mytable
(column1, column2, column3)
values
 ('value1', 'value2', 'value3'),
 ('value3', 'value4', 'value5');

However, with Redshift, you will typically be inserting many more rows at a time rather than 1 or 2 rows like you would with an OLTP database like PostgreSQL, MySQL, Oracle, etc.

So, create a file that is pipe delimited like this:

value1|value2|value3
value4|value5|value6

Upload this to s3:

aws s3 cp example2.txt s3://mybucket/

Copy documentation: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

And the upload command with COPY (which is the preferred method):

copy mytable from 's3://mybucket/example2.txt' iam_role default;

You also asked for a procedure to do this insert. Stored Procedure documentation: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

Here is a quick example:

create or replace procedure pr_example2() as
$$
BEGIN
	insert into mytable
	(column1, column2, column3)
	values
	 ('value1', 'value2', 'value3'),
	 ('value3', 'value4', 'value5');
END;
$$
LANGUAGE plpgsql;

And to execute the procedure, you "call" it:

call pr_example2();
profile pictureAWS
EXPERT
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions