This example shows how to use the READ_NOS table operator with the NOSREAD_RAW return type to display the raw data in JSON files.
This query uses the function mapping READ_NOS_json_intable_fm, which includes ANY IN TABLE clause, and a return type of NOSREAD_RAW to display the raw JSON data from the table riverflow_raw.
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.
Perform the READ_NOS:
SELECT payload FROM READ_NOS_json_intable_fm ( ON (SELECT cast ('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json' as varchar(500)) Location, cast (NULL as varchar(500)) ObjectVersionId, cast (NULL as timestamp) ObjectTimeStamp, cast (0 as BIGINT) ExtFSRowOffSet, cast (1 as BIGINT) ObjectLength, cast (NULL as CLOB CHARACTER SET LATIN) payload ) USING LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json') RETURNTYPE('NOSREAD_RAW') ) as d;
Below is a sample of the raw data output.
Payload ----------------------------------------------------------------------------------------------------------------------------- { "site_no":"09424900", "datetime":"2018-06-28 00:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"79.5"}{ "site_no":"09424900", "datetime":"2018-06-28 00:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.9"} { "site_no":"09424900", "datetime":"2018-06-28 00:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.2"} { "site_no":"09424900", "datetime":"2018-06-28 00:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"77.8"} { "site_no":"09424900", "datetime":"2018-06-28 01:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.5"} { "site_no":"09424900", "datetime":"2018-06-28 01:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.1"} { "site_no":"09424900", "datetime":"2018-06-28 01:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"75.7"} { "site_no":"09424900", "datetime":"2018-06-28 01:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"74.5"}