17.05 - Including Credentials in the READ_NOS Statement - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.05
created_date
January 2021
category
Programming Reference
featnum
B035-1214-175K

Instead of creating an authorization object and a function mapping, you can include credentials in the USING clause of the READ_NOS statement in the ACCESS_ID and ACCESS_KEY keywords.

Example: Accessing a Public Bucket Using NOSREAD_KEYS to List the Files in the Specified LOCATION

The following example includes the ACCESS_ID and ACCESS_KEY in the READ_NOS statement. It accesses a Teradata-supplied public bucket.

SELECT TOP 2 location(CHAR(100)), ObjectLength FROM READ_NOS (
     USING
     LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/')
     ACCESS_ID ('')
     ACCESS_KEY ('')
     RETURNTYPE ('NOSREAD_KEYS')
     )
AS d;

For public buckets, ACCESS_ID and ACCESS_KEY are empty strings.

ObjectLength is the size of the external files in the specified LOCATION of the object store. NOSREAD_KEYS retrieves a list of files from the path specified by LOCATION.

Replace LOCATION, ACCESS_ID, and ACCESS_KEY to access your own external storage.

Result:

Location  /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/30.csv
ObjectLength  5009

Location  /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/29.csv
ObjectLength  5088

For readability, the output is displayed vertically.

Example: Accessing a Public Bucket Using NOSREAD_RECORD to Return the Payload from the Specified LOCATION

The following example accesses a Teradata-supplied public bucket containing CSV data:

SELECT TOP 2 payload..* FROM READ_NOS (
     USING
     LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/')
     ACCESS_ID ('')
     ACCESS_KEY ('')
     RETURNTYPE ('NOSREAD_RECORD')
     )
AS d;

For public buckets, ACCESS_ID and ACCESS_KEY are empty strings. If you are accessing a bucket or container that is not public, include the credentials inside the single quotes for ACCESS_ID and ACCESS_KEY.

Payload is the data stored in the external location specified by LOCATION. NOSREAD_RECORD returns one row for each external record.

Replace LOCATION, ACCESS_ID, and ACCESS_KEY to access your own external storage.

Result:

Payload..*
---------------------------------------------------------------------------
["11.6","10000","09380000","2018-07-01 00:00","662","0.00","8.65"]
["11.4","11400","09380000","2018-07-02 00:00","654","0.00","8.97"]

The following example accesses a Teradata-supplied public bucket containing JSON data:

SELECT TOP 2 payload.* FROM READ_NOS (
     USING
     LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
     ACCESS_ID ('')
     ACCESS_KEY ('')
     RETURNTYPE ('NOSREAD_RECORD')
     )
AS d;

For public buckets, ACCESS_ID and ACCESS_KEY are empty strings. If you are accessing a bucket or container that is not public, include the credentials inside the single quotes for ACCESS_ID and ACCESS_KEY.

Payload is the data stored in the external location specified by LOCATION. NOSREAD_RECORD returns one row for each external record.

Replace LOCATION, ACCESS_ID, and ACCESS_KEY to access your own external storage.

Result:

Payload.*
---------------------------------------------------------------------------
["09380000","2018-07-02 00:00","11400","8.97","11.4","654","0.00"]
["09380000","2018-06-30 00:00","15900","9.92","10.4","685","0.00"]