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

Questions tagged with Amazon Redshift

Sort by most recent
  • 1
  • 12 / page

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
59
views
asked 13 days 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
35
views
asked 13 days ago
  • 1
  • 12 / page