Process a string and insert into Redshift


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?

asked 2 years ago1105 views
1 Answer

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)
 ('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:


Upload this to s3:

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

Copy documentation:

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:

Here is a quick example:

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

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

call pr_example2();
profile pictureAWS
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