CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-redshiftaws-dms
marcin2x4
marcin2x4

Reputation: 1449

Redshift - DMS user fails to load data from S3

I prepared a schema and tables using AWS SCT tool so my DMS job will have a landing place for data. Even thought access to the database and schema has been granted:

GRANT ALL ON DATABASE my_db TO "dms_user";
GRANT ALL ON SCHEMA my_schema TO "dms_user";
GRANT ALL ON ALL TABLES IN SCHEMA my_schema TO "dms_user";
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO "dms_user";

I'm getting error:

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: XX000 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR:  Load into table 'table_test' failed.  Check 'stl_load_errors' system table for details. [1022502]  (ar_odbc_stmt.c:4815)

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  Failed to load schema.table_testfrom S3, file name: LOAD00000001.csv [1022509]  (cloud_imp.c:2386)

2022-03-25T22:26:48 [TARGET_LOAD     ]E:  Failed to load ims_suretyradm_publish.dimaccount from S3, file name: LOAD00000001.csv [1022509]  (cloud_imp.c:2386)

stl_load_errors table is empty...

I'll greatly appreciate any help/guidance on this.

Upvotes: 0

Views: 1106

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

I hope it is not the difference between "my_schema" and "my_schema_name" as these are likely different due to obfuscation error.

There are a number of places things can go sideways. Have you checked the permissions after the grant?

select HAS_SCHEMA_PRIVILEGE('dms_user', 'my_schema', 'create'); 
select HAS_SCHEMA_PRIVILEGE('dms_user', 'my_schema', 'usage');

Another resource is awslabs' Redshift github repo - https://github.com/awslabs/amazon-redshift-utils - there are a number of admin views there that explore permissions. Knowing which step in the process is not doing what you expect will narrow things down.

Also, remember that you will want to change the default ACL for the schema so the new objects created will be useable by the correct people. For example:

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO dms_user;

Upvotes: 1

Related Questions