'How to load and transform data from Table to another table in Amazon Redshift?

I am loading data from S3 to Redshift Database , now have a requirement to Perform ETL on that table and after filtering data , load into another table in another schema in Redshift. How I can load and transform data from one table to another table in Redshift?

I have two schemas staging and another one for reports. But I want remove duplicates and correct some data like timestamp and dates before loading the data from staging to reporting table.

sqlamazon-redshiftetl


Solution 1:[1]

ETL stands for Extract, Transform, and Load. It sounds like you only want to transform the data from one table to another, correct? Or is the requirement that the data be extracted from Redshift, transformed by some tool, and then reloaded into the same Redshift? This seems inefficient but I've seen odder requirements. I'm going to assume that you just need to transform (T).

This can be done (depending on the transformation you need) in a few SQL statements. If the target (destination) table doesn't exist then you will want to create it. (Yes, this can be done implicitly by Redshift but for an important table you will want to control the definition.) This is done with the CREATE TABLE statement. https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

Then you will want to use the INSERT statement to put data into this table. https://docs.aws.amazon.com/redshift/latest/dg/c_Examples_of_INSERT_30.html This statement can take as the source of data a SELECT statement which can modify your data in many ways. https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_synopsis.html So the combined statement will have a base structure like

INSERT INTO <schema2>.<table2> SELECT <transformed data> FROM <schema1>.<table1>;

Not knowing the transformation you are intending I cannot get more specific than this.

If the intent of you question is otherwise please update.

Solution 2:[2]

One of the option would be to schedule a query to periodically do the etl job More details can be found here here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Weiner
Solution 2 prem krishnan

Related Questions