Example: Using AWS Assume Role Authorization with WRITE_NOS - Teradata Vantage

Assume Role gives existing AWS IAM users and service accounts temporary access to AWS resources in other accounts.

1. Create a small sample table:

   CREATE TABLE t11 (
   c1 SMALLINT, 
   c2 INT, 
   c3 VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC, 
   c4 VARCHAR(10) CHARACTER SET UNICODE CASESPECIFIC, 
   c5 DATE, 
   c6 DATE
   );

2. Add data to the table:

   INSERT INTO t11(1, 11, 'store1', 'paper', DATE '2010-06-01', DATE '2010-07-01');
   INSERT INTO t11(2, 12, 'store1', 'pen', DATE '2010-06-01', DATE '2010-07-01');
   INSERT INTO t11(3, 13, 'store1', 'ipad', DATE '2010-06-01', DATE '2010-07-01');

3. Create the authorization object or ask an administrator to create it:

   CREATE AUTHORIZATION S3_auth
   AS INVOKER TRUSTED
   USING
   AUTHSERVICETYPE 'ASSUME_ROLE'
   ROLENAME 'role_name'
   EXTERNALID 'external_id';

   Where:

   role_name is the Amazon Resource Name (ARN) of the role to assume. For example: arn:aws:iam::844102931058:role/STSAssumeRole_TestRole

   external_id is is the external ID that is assuming the role. For example: 3f68bc61-a455-4742-8039-8a03f0600db52.

4. Run WRITE_NOS using the authorization object. The following example selects data from the Teradata table named t11 and writes it to external object storage at the LOCATION specified. Finally, a count is done to verify the correct amount of data was stored:

   SELECT count(*) FROM WRITE_NOS
   (ON (SELECT * FROM t11)
     USING
     LOCATION('/s3/s3.amazonaws.com/stsassumerole-testbucket/wr_test/')
     AUTHORIZATION(S3_auth)
     STOREDAS('PARQUET') ) as dt;