This example shows how to use READ_NOS to query a JSON file without an extension (.JSON)
Verify File Name
This query uses the function mapping READ_NOS_json_fm to check for file extension, .JSON, by querying the location column with a return type of NOSREAD_KEYS.
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/DATA/09380000/2018/06/28')
RETURNTYPE('NOSREAD_KEYS')
) AS derived_table;
The result shows that the file 28 does not have an extension.
Location ----------------------------------------------------- /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28
Sample Query and Results
This statement includes the ON clause to define the file 28 as a JSON file.
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.
SELECT payload.Flow, payload.Precipitation, payload.Conductance,
payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
FROM READ_NOS_json_intable_fm (
ON (SELECT CAST (NULL AS JSON(8388096)))
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28')
) AS derived_table
WHERE payload.Temp>11.0 AND payload.GageHeight>10.00 ORDER BY payload.datetime;
Below is a sample of the results.
Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 16400 0.00 673 2018-06-28 13:30 09380000 11.3 10.01 16400 0.00 673 2018-06-28 13:45 09380000 11.4 10.01 16400 0.00 675 2018-06-28 14:00 09380000 11.4 10.01 16600 0.00 678 2018-06-28 14:15 09380000 11.4 10.05 16600 0.00 671 2018-06-28 14:30 09380000 11.5 10.04 16700 0.00 677 2018-06-28 14:45 09380000 11.5 10.06 16800 0.00 680 2018-06-28 15:00 09380000 11.5 10.08 16700 0.00 680 2018-06-28 15:15 09380000 11.6 10.07 16800 0.00 675 2018-06-28 15:30 09380000 11.6 10.09 16800 0.00 675 2018-06-28 15:45 09380000 11.6 10.08 16800 0.00 682 2018-06-28 16:00 09380000 11.6 10.09