List Files | READ_NOS Query | Teradata Vantage - 17.05 - Example: Using READ_NOS to List Files in a Location - 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)

Below are examples of how to use the READ_NOS table operator to list files at a location with extensions or without extensions.

Sample Query and Results for Files with an Extension (.JSON)

This query lists the distinct files for 2018. The function mapping READ_NOS_json_fm includes the authorization DefAuth defined for files with extensions (.JSON).

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 DISTINCT location FROM READ_NOS_json_fm (  
         USING
              LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018')
 ) AS dt ORDER BY 1;

Below is a sample of the output.

 Location                                                       
 -------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/30.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/01.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/02.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/03.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/04.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/05.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/06.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/07/07.json

Sample Query and Results for JSON Data Files Regardless of File Extension

This query lists the distinct files for 2018. Because the files in this location do not have extensions, the query includes the ON clause to cast the files as JSON. The function mapping READ_NOS_json_intable_fm includes the authorization DefAuth and the ANY IN TABLE clause for files without an extension. This same technique can be used for files with unrecognized extensions, or when you want to override the default extension handling.

Create the function mapping READ_NOS_json_intable_fm, if not already created. See Example: Function Mapping Definition Using ANY IN TABLE for JSON Data Files.

Query the JSON file using the READ_NOS_json_intable_fm function mapping:

SELECT DISTINCT location FROM READ_NOS_json_intable_fm (  
       ON (SELECT  CAST (NULL AS JSON))
         USING
              LOCATION ('/s3/td-usgs.s3.amazonaws.com/DATA/09380000/2018')
 ) AS dt ORDER BY 1;

Below is a sample of the output.

 Location                                              
 ----------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/27
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/29
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/30
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/01
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/02
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/03
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/04
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/05
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/06
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/07/07