I have a trigger in Oracle. Can anyone please help me with how it can be replicated to Redshift? DynamoDB managed stream kind of functionality will also work.
-
3no triggers in redshift.Jon Scott– Jon Scott2018-01-29 13:59:36 +00:00Commented Jan 29, 2018 at 13:59
-
1Redshift is used to build data warehouses for really big data - TB scale. You wouldn't use triggers in an Oracle data warehouse built on top of RACPanagiotis Kanavos– Panagiotis Kanavos2018-01-29 16:01:29 +00:00Commented Jan 29, 2018 at 16:01
-
If you're trying to replicate the functionality of a transactional database like Oracle by using Redshift, then you should think again about whether Redshift is the appropriate platform to be using on AWS. It's a very different kind of database than Oracle or SQL Server. You my be better served using an Aurora instance instead.Nathan Griffiths– Nathan Griffiths2018-01-31 19:42:10 +00:00Commented Jan 31, 2018 at 19:42
Add a comment
|
1 Answer
Redshift does not support triggers because it's a data warehousing system which is designed to be able to import large amounts of data in a limited time. So, if every row insert would be able to fire a trigger the performance of batch inserts would suffer. This is probably why Redshift developers didn't bother to support this and I agree with them. The trigger type of behavior should be a part of business application logic that runs in OLTP environment and not the data warehousing logic. If you want to run some code in DW after inserting or updating data you have to do it as another step of your data pipeline.
2 Comments
Surajit Kundu
Do you have any idea how this functionality can be achieved in Redshift with the little cost of performance.
AlexYes
@SurajitKundu it's too generic question... as I told, after loading data into Redshift you can run a SQL script against the new data with the logic that was supposed to be run in a trigger. If you append to some large table and want to run this logic exclusively on the new rows you can create a staging table for the new data first, do all post-processing in SQL and then append the result to the target table