By using AWS re:Post, you agree to the Terms of Use

Questions tagged with Amazon Redshift

Sort by most recent

Browse through the questions and answers listed below or filter and sort to narrow down your results.

DMS - Ongoing replication causing duplicate records due to redshift cluster maintenance

Hi, I'm facing an issue with DMS ongoing replication causing a small number of duplicate records due to the target redshift cluster going down for maintenance every week. One of the error messages in the cloudwatch logs is `Failed to start rollback apply transaction`, so it looks like DMS fails to undo a batch, and with `RecoverableErrorCount` set to -1, the process ends up retrying the ongoing replication from the batch it already ran, resulting in the duplicates. Is there a way via the DMS settings to handle this rollback failure issue caused by the cluster maintenance? Or maybe a way to work around the cluster going down for maintenance? Thanks! Below are my settings: Source: Mysql Target: Redshift `{ "ErrorBehavior": { "FailOnNoTablesCaptured": true, "ApplyErrorUpdatePolicy": "SUSPEND_TABLE", "FailOnTransactionConsistencyBreached": true, "RecoverableErrorThrottlingMax": 1800, "DataErrorEscalationPolicy": "SUSPEND_TABLE", "ApplyErrorEscalationCount": 5, "RecoverableErrorStopRetryAfterThrottlingMax": true, "RecoverableErrorThrottling": true, "ApplyErrorFailOnTruncationDdl": false, "DataTruncationErrorPolicy": "SUSPEND_TABLE", "ApplyErrorInsertPolicy": "SUSPEND_TABLE", "EventErrorPolicy": "IGNORE", "ApplyErrorEscalationPolicy": "SUSPEND_TABLE", "RecoverableErrorCount": -1, "DataErrorEscalationCount": 5, "TableErrorEscalationPolicy": "SUSPEND_TABLE", "RecoverableErrorInterval": 5, "ApplyErrorDeletePolicy": "SUSPEND_TABLE", "TableErrorEscalationCount": 5, "FullLoadIgnoreConflicts": true, "DataErrorPolicy": "SUSPEND_TABLE", "TableErrorPolicy": "SUSPEND_TABLE" }, "TTSettings": { "TTS3Settings": null, "TTRecordSettings": null, "EnableTT": false }, "FullLoadSettings": { "CommitRate": 10000, "StopTaskCachedChangesApplied": false, "StopTaskCachedChangesNotApplied": false, "MaxFullLoadSubTasks": 8, "TransactionConsistencyTimeout": 1200, "CreatePkAfterFullLoad": false, "TargetTablePrepMode": "DROP_AND_CREATE" }, "TargetMetadata": { "ParallelApplyBufferSize": 10000, "ParallelApplyQueuesPerThread": 0, "ParallelApplyThreads": 32, "TargetSchema": "", "InlineLobMaxSize": 0, "ParallelLoadQueuesPerThread": 0, "SupportLobs": true, "LobChunkSize": 0, "TaskRecoveryTableEnabled": true, "ParallelLoadThreads": 32, "LobMaxSize": 63, "BatchApplyEnabled": true, "FullLobMode": false, "LimitedSizeLobMode": true, "LoadMaxFileSize": 0, "ParallelLoadBufferSize": 1000 }, "BeforeImageSettings": null, "ControlTablesSettings": { "historyTimeslotInMinutes": 5, "HistoryTimeslotInMinutes": 5, "StatusTableEnabled": true, "SuspendedTablesTableEnabled": true, "HistoryTableEnabled": true, "ControlSchema": "dms_control", "FullLoadExceptionTableEnabled": true }, "LoopbackPreventionSettings": null, "CharacterSetSettings": null, "FailTaskWhenCleanTaskResourceFailed": false, "ChangeProcessingTuning": { "StatementCacheSize": 50, "CommitTimeout": 1, "BatchApplyPreserveTransaction": true, "BatchApplyTimeoutMin": 1, "BatchSplitSize": 0, "BatchApplyTimeoutMax": 30, "MinTransactionSize": 1000, "MemoryKeepTime": 60, "BatchApplyMemoryLimit": 500, "MemoryLimitTotal": 1024 }, "ChangeProcessingDdlHandlingPolicy": { "HandleSourceTableDropped": true, "HandleSourceTableTruncated": true, "HandleSourceTableAltered": true }, "PostProcessingRules": null }`
1
answers
0
votes
77
views
asked 2 months ago

Complex lag windows function help needed

Im racking my brain about how to do this and I dont think its possible but not sure what to even search for. So the below is a subset of the data (using example numbers) I am trying to use the lag function to populate the flag column. ``` Status Flag Connected -> Exiting Maillink Exiting -> Not equal to Connected OnCorp ( All rows stay OnCorp until we see Connected) Connected -> Reconnecting Mailink (It should stay Mailink for all rows until we see "Exiting" status ) ``` Is it possible in Sql? Using lag function in case statement to compare previous state is causing error [Sample Data][1] [1]: https://i.stack.imgur.com/tdTOm.png Sql Code (But erroring out ) -: ``` WITH base as ( select o.computername, o.currentuser, o.datetime, message, CASE WHEN LEN(split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1)) <> 0 THEN split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1) WHEN (message like 'WARNING%' OR message like 'OpenVPN%') THEN 'Start' WHEN message = 'SIGTERM[hard,] received, process exiting' THEN 'Exit1' WHEN message = 'Closing TUN/TAP interface' THEN 'Exit2' ELSE 'NO Status' END State1, CASE WHEN State1 = 'Start' THEN 1 WHEN State1 = 'RESOLVE' THEN 2 WHEN State1 = 'WAIT' THEN 3 WHEN State1 = 'AUTH' THEN 4 WHEN State1 = 'GET_CONFIG' THEN 5 WHEN State1 = 'ASSIGN_IP' THEN 6 WHEN State1 = 'ADD_ROUTES' THEN 7 WHEN State1 = 'CONNECTED' THEN 8 WHEN State1 = 'EXITING' THEN 9 END orderofoperation --row_number() over (partition by o.computername,o.currentuser,DATE(o.datetime) order by o.computername,o.currentuser,o.datetime) as rownumber from maillink_openvpn_logs_ext_schema.open_vpn_filtered o where o.message != 'message' and currentuser = 'wuellie' --and State1 in ('EXITING' ,'OpenVPN','RESOLVE','WAIT','AUTH','ASSIGN_IP','GET_CONFIG','ADD_ROUTES') order by o.datetime,orderofoperation ) select final.*, case when final.previous_record_state is null then ‘oncorp’ when final.currentstate = ‘CONNECTED’ then ‘maillink’ when final.previous_record_state is not null and final.previous_record_state not in (‘CONNECTED’) then lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime when previous_record_state in (‘EXITING’) and lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime = ‘maillink’ then ‘oncorp’ else lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime end as flag from ( select b.computername,b.currentuser,b.State1 currentstate,b.datetime, lag(State1) over (partition by b.computername,b.currentuser,(b.datetime) ::DATE order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as previous_record_state --lag(datetime) over (partition by b.computername,b.currentuser,b.datetime ::DATE order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as nextdatetime from base b order by b.datetime,orderofoperation )final ``` ``` Computername username State datetime onCorp/mailink xyx ads start 2022-07-06T06:45:51 ONCORP xyx ads RESOLVE 2022-07-06T07:06:45 ONCORP xyx ads WAIT 2022-07-06T07:06:45 ONCORP xyx ads AUTH 2022-07-06T07:07:00 ONCORP xyx ads GET_CONFIG 2022-07-06T07:07:00 ONCORP xyx ads ADD_ROUTES 2022-07-06T07:07:01 ONCORP xyx ads CONNECTED 2022-07-06T07:07:01 MAILINK xyx ads EXITING 2022-07-06T07:07:01 MAILINK xyx ads RESOLVE 2022-07-06T07:07:46 ONCORP xyx ads WAIT 2022-07-06T07:07:46 ONCORP xyx ads AUTH 2022-07-06T07:07:50 ONCORP xyx ads GET_CONFIG 2022-07-06T07:07:51 ONCORP xyx ads ADD_ROUTES 2022-07-06T07:07:51 ONCORP xyx ads CONNECTED 2022-07-06T07:07:52 MAILINK xyx ads RECONNECTED 2022-07-06T07:08:01 MAILINK xyx ads WAIT 2022-07-06T07:08:02 MAILINK xyx ads AUTH 2022-07-06T07:08:09 MAILINK xyx ads RECONNECTED 2022-07-06T07:08:10 MAILINK xyx ads CONNECTED 2022-07-06T07:08:15 MAILINK xyx ads RECONNECTED 2022-07-06T07:08:20 MAILINK xyx ads CONNECTED 2022-07-06T07:09:01 MAILINK xyx ads EXITING 2022-07-06T07:10:50 MAILINK xyx ads START 2022-07-06T07:11:50 ONCORP ```
0
answers
0
votes
41
views
asked 2 months ago