This example shows how to use the READ_NOS table operator to access an external JSON file that includes the extension .JSON.
File Name Query
This query uses the function mapping READ_NOS_json_fm for the READ_NOS table operator to query the file at the specified location with a return type of NOSREAD_KEYS. The LOCATION parameter specifies a JSON file stored on Amazon S3.
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/09380000/2018/06/27') RETURNTYPE('NOSREAD_KEYS') ) AS derived_table;
The output result shows that the file includes the extension, .JSON.
Location -------------------------------------------------------------- /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json
Sample Query and Results
This query uses the READ_NOS_json_fm function mapping to return specific columns searching for entries where the temperature was greater than 11 and the height was more than 10.00, ordered by date and time.
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 payload.Flow, payload.Precipitation, payload.Conductance, payload.datetime, payload.site_no, payload.Temp, payload.GageHeight FROM READ_NOS_json_fm ( USING LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27') RETURNTYPE('NOSREAD_RECORD') ) AS derived_table WHERE payload.Temp>11.0 AND payload.GageHeight>10.00 ORDER BY payload.datetime;
Below is a sample of the output.
Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 16400 0.00 668 2018-06-27 14:15 09380000 11.8 10.01 16400 0.00 670 2018-06-27 14:30 09380000 11.8 10.01 16600 0.00 665 2018-06-27 14:45 09380000 11.9 10.04 16600 0.00 669 2018-06-27 15:00 09380000 11.9 10.05 16700 0.00 668 2018-06-27 15:15 09380000 12.0 10.07 16700 0.00 667 2018-06-27 15:30 09380000 12.0 10.07 16700 0.00 663 2018-06-27 15:45 09380000 12.0 10.07 16800 0.00 666 2018-06-27 16:00 09380000 12.0 10.09 16800 0.00 667 2018-06-27 16:15 09380000 12.0 10.08 16800 0.00 667 2018-06-27 16:30 09380000 12.0 10.08 16800 0.00 668 2018-06-27 16:45 09380000 12.0 10.09 16900 0.00 670 2018-06-27 17:00 09380000 12.0 10.10 16800 0.00 670 2018-06-27 17:15 09380000 12.0 10.09