READ_NOS Table Operator | SELECT SQL Statement | Teradata Vantage - 17.05 - Example: Using READ_NOS with NOSREAD_KEYS Return Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

This is an example of how to use the READ_NOS table operator with a return type of NOSREAD_KEYS to list external files at a specified location.

List of Files at a Location

This statement performs a query of a JSON file using the READ_NOS_json_fm function mapping. The files specified by the LOCATION option include the extensions, .json and .json.gz. If the files have extension other than .json, .json.gz, .csv, or .tsv, you must specify an ON clause where the payload of the input table must match the payload returned by READ_NOS.

Create the function mapping READ_NOS_json_fm, if not already created. See Example: Function Mapping Definition Using EXTERNAL SECURITY Clause for JSON Data Files.

Perform the READ_NOS:

SELECT location
FROM READ_NOS_json_fm (
USING
LOCATION ('/S3/td-usgs.s3.amazonaws.com/JSONDATA')
RETURNTYPE('NOSREAD_KEYS')
) AS d;

Your results will be similar to the following:

 Location                                                       
 -------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/07/03.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/07/21.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400568/2018/07/02.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/07/24.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09423560/2018/07/11.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/06/27.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09474000/2018/07/12.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09474000/2018/07/11.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/07/23.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09423560/2018/07/26.json