Introduction to Querying S3 Data with Teradata READ_NOS

Teradata Setup Activities

CREATE USER NOS_DEMO_USER FROM DBC AS PASSWORD=NOS_DEMO_USER, PERM=50e8;
GRANT EXECUTE FUNCTION on TD_SYSFNLIB.READ_NOS to NOS_DEMO_USER;

Teradata-supplied Sample Store

----------------------------------------------
* Teradata public store
* Contains river flow data sourced
from USGS Surface-Water Data Sets
(https://waterdata.usgs.gov/nwis/sw).
----------------------------------------------

s3://td-usgs-public
└───
├── /<data-file-format>
│ ├── /<river-site-id>
│ │ └── /<yyyy>
│ │ ├── /<mm>
│ │ │ ├── /<dd>.<ext>
....
....
AccessKeyId: ''
SecretAccessKey: ''
Bucketname: td-usgs-public
S3 Endpoint: s3.amazonaws.com
AccessKeyId: ''
SecretAccessKey: ''

Creating an S3 Store

$ aws \
--profile aws_acct_admin \
--region us-west-1 \
s3 mb s3://my-private-surface-water-data-store
$ aws \
--profile aws_acct_admin \
iam create-user --user-name my-private-surface-water-data-user
{
"User": {
"Path": "/",
"UserName": "my-private-surface-water-data-user",
"UserId": "uuuuuuuuuuuuuuu",
"Arn": "arn:aws:iam::xxxxxxxxxxxx:user/my-private-surface-water-data-user",
....
}
}
$ aws \
--profile aws_acct_admin \
iam create-access-key --user-name my-private-surface-water-data-user
{
"AccessKey": {
...
"AccessKeyId": "AKIATZSC8AVA4PNC6VLC",
...
"SecretAccessKey": "qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw",

...
}
}
$ aws \
--profile aws_acct_admin \
iam put-user-policy \
--user-name my-private-surface-water-data-user \
--policy-name my-private-surface-water-data-policy \
--policy-document \
'{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Stmt1",
"Action": [
"s3:GetBucketLocation",
"s3:ListBucket"
],
"Effect": "Allow",
"Resource": "arn:aws:s3:::my-private-surface-water-data-store"
},
{
"Sid": "Stmt2",
"Action": [
"s3:GetObject"
],
"Effect": "Allow",
"Resource": "arn:aws:s3:::my-private-surface-water-data-store/*"
}
]
}'
$ aws \
--profile aws_acct_admin \
s3 sync s3://td-usgs-public \
s3://my-private-surface-water-data-store
Bucketname: my-private-surface-water-data-store
S3 Endpoint: s3.us-west-1.amazonaws.com
AccessKeyId: AKIATZSC8AVA4PNC6VLC
SecretAccessKey: qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw

Teradata READ_NOS Operator

SELECT *
FROM READ_NOS (
USING
LOCATION ('/connector/bucket.endpoint/[key_prefix]')

AUTHORIZATION (
'{
"Access_ID":"<ACCESS-KEY>"
,"Access_Key":"<SECRET-ACCESS-KEY>"
}'
)

RETURNTYPE ( { 'NOSREAD_RECORD' | 'NOSREAD_KEYS' } )
) AS D
;

LOCATION (‘/connector/bucket.endpoint/[key_prefix]’)

AUTHORIZATION (
'{
"Access_ID":"<ACCESS-KEY>"
,"Access_Key":"<SECRET-ACCESS-KEY>"
}'
)

RETURNTYPE ( { ‘NOSREAD_RECORD’ | ‘NOSREAD_KEYS’ } )

READ_NOS Examples

bucketname: my-private-surface-water-data-store
S3 service Endpoint: s3.us-west-1.amazonaws.com
AccessKeyId: AKIATZSC8AVA4PNC6VLC
SecretAccessKey: qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw
SELECT TOP 5 location
FROM
READ_NOS (
USING
LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/')
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC" ,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
RETURNTYPE ('NOSREAD_KEYS')
) AS D
ORDER BY 1;
Example 1, query output
s3://my-private-surface-water-data-store
└───
├── /CSVDATA
│ ├── /09380000
│ │ └── /2018
│ │ ├── /06
│ │ │ ├── /27.csv
│ │ │ ├── /28.csv
│ │ │ ├── /29.csv
│ │ │ ├── /30.csv
│ │ ├── /07
│ │ │ ├── /01.csv
...
...
Example 1, breakdown of Location
SELECT 
COUNT(*) AS FILE_COUNT
FROM
(
SELECT *
FROM READ_NOS (
USING
LOCATION ( '/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/' )
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC"
,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
RETURNTYPE ('NOSREAD_KEYS') ) AS D
) T
Example 2, Query Output
First row returned in resultset from Example 1
SELECT TOP 5 *
FROM
READ_NOS (
USING
LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/CSVDATA/09380000/2018/06/27.csv')
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC"
,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
RETURNTYPE ('NOSREAD_RECORD')
) AS D
Example 3 output
$  aws s3 \
--profile aws_acct_admin \
cp s3://my-private-surface-water-data-store/CSVDATA/09380000/2018/06/27.csv - \
| head -n5
Verify Example 3 output using AWS CLI

Conclusion

Access_ID = ''
Access_Key = ''

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store