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