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
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

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