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 }`
0
answers
0
votes
29
views
asked 5 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
26
views
asked 6 days ago

User <awsuser> is is not authorized to assume IAM Role while copy from DynamoDB Table cross account.

Hi AWS, I am trying to copy data from DynamDB table in account A to redshift cluster in account B. The dynamodb table is encrypted with customer managed kms key and it is standard table with On-demand Capacity Mode. These are the CloudFormation Templates: DynamoDB Table Account: ``` # version: 1.0 AWSTemplateFormatVersion: "2010-09-09" Resources: RootRole: Type: "AWS::IAM::Role" Properties: AssumeRolePolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Principal: AWS: - arn:aws:iam::<redshift_account>:root - arn:aws:iam::<dynamodb_account>:root Action: - "sts:AssumeRole" Path: "/" RoleName: "terraform_iam_role" IAMPolicy: Type: "AWS::IAM::Policy" Properties: PolicyName: drdc_iam_policy PolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Action: - "ec2:*" - "ecs:*" - "redshift-serverless:*" - "redshift:*" - "iam:*" - "ec2:*" - "cloudwatch:*" - "s3:*" - "logs:*" - "cloudtrail:*" - "sns:*" - "lambda:*" - "kms:*" - "route53:*" - "dynamodb:*" Resource: "*" - Effect: Allow Action: - iam:PassRole Resource: arn:aws:iam::<dynamodb_account>:role/aws-service-role/dynamodb.application-autoscaling.amazonaws.com/AWSServiceRoleForApplicationAutoScaling_DynamoDBTable Roles: - Ref: RootRole ``` ========================== Redshift Cluster Account: ``` # version: 1.0 AWSTemplateFormatVersion: "2010-09-09" Resources: RootRole: Type: "AWS::IAM::Role" Properties: AssumeRolePolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Principal: Service: - redshift.amazonaws.com - redshift-serverless.amazonaws.com - scheduler.redshift.amazonaws.com - dynamodb.amazonaws.com AWS: - arn:aws:iam::<redshift_account>:root - arn:aws:iam::<dynamodb_account>:root Action: - "sts:AssumeRole" Path: "/" RoleName: "terraform_iam_role" IAMPolicy: Type: "AWS::IAM::Policy" Properties: PolicyName: drdc_iam_policy PolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Action: - "ec2:*" - "ecs:*" - "redshift-serverless:*" - "redshift:*" - "iam:*" - "ec2:*" - "cloudwatch:*" - "s3:*" - "logs:*" - "cloudtrail:*" - "sns:*" - "lambda:*" - "kms:*" - "route53:*" Resource: "*" - Effect: Allow Action: - iam:PassRole Resource: - !Sub arn:aws:iam::${AWS::AccountId}:role/aws-service-role/redshift.amazonaws.com/AWSServiceRoleForRedshift - !Sub arn:aws:iam::${AWS::AccountId}:role/drdc_lambda_execution_redshift_role - !Sub arn:aws:iam::${AWS::AccountId}:role/terraform_iam_role Roles: - Ref: RootRole ``` When I am running the command from redshift query editor in account B: ``` COPY sales FROM 'dynamodb://sales' iam_role 'arn:aws:iam::<redshift_account>:role/terraform_iam_role,arn:aws:iam::<dynamodb_account>:role/terraform_iam_role' readratio 50; ``` I am experiencing the following error: ERROR: User arn:aws:redshift:ca-central-1:<redshift_account>:dbuser:redshift-postgres-cluster/awsuser is not authorized to assume IAM Role arn:aws:iam::<reshift_account>:role/terraform_iam_role,arn:aws:iam::<dynamodb_account>:role/terraform_iam_role. Detail: ----------------------------------------------- error: User arn:aws:redshift:ca-central-1:<redshift_account>:dbuser:redshift-postgres-cluster/awsuser is not authorized to assume IAM Role arn:aws:iam::<dynamodb_account>:role/terraform_iam_role,arn:aws:iam::<dynamodb_account>:role/terraform_iam_role. code: 8001 context: IAM Role=arn:aws:iam::<redshift_account>:role/terraform_iam_role,arn:aws:iam::203188538396:role/terraform_iam_role query: 201398 location: xen_aws_credentials_mgr.cpp:498 process: query0_125_201398 [pid=14950] --------------------- Can you please confirm whether cross account is possible in this case or am I missing something from IAM permissions point of view or there is something wrong in the COPY command I ran. Thanks
1
answers
0
votes
23
views
profile picture
asked 12 days ago
  • 1
  • 12 / page