This example accesses external data in JSON format. You can also use READ_NOS to access CSV and Parquet data.
If not already done, create the authorization object. See Example: Create an Authorization Object.
Perform the READ_NOS:
SELECT TOP 2 * FROM ( LOCATION='/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json' AUTHORIZATION=MyAuthObj RETURNTYPE='NOSREAD_RECORD' ) AS d ;
The NOSREAD_RECORD return type is the default mode, so it is optional to specify it in the command.
Your result will be similar to the following:
Location /S3/s3.amazonaws.com/td-usgs-public/JSONDATA/09380000/2018/06/27.json
ObjectVersionId ?
ObjectTimeStamp ?
OffsetIntoObject 304
ObjectLength 151
ExtraField ?
Payload { "site_no":"09380000", "datetime":"2018-06-27 00:30", "Flow":"15700", "GageHeight":"9.88", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
Location /S3/s3.amazonaws.com/td-usgs-public/JSONDATA/09380000/2018/06/27.json
ObjectVersionId ?
ObjectTimeStamp ?
OffsetIntoObject 608
ObjectLength 151
ExtraField ?
Payload { "site_no":"09380000", "datetime":"2018-06-27 01:00", "Flow":"15100", "GageHeight":"9.77", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
The output is displayed vertically for readability.
Query and Sample Results
This query uses the READ_NOS to return selected information for June 28, 2018, where the temperature was over 70 degrees, listed by time:
SELECT payload.Flow, payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
FROM (
LOCATION='/S3/td-usgs-public.s3.amazonaws.com/JSONDATA/'
AUTHORIZATION=MyAuthObj
)
AS derived_table
WHERE payload.datetime LIKE '%2018-06-28%'
AND payload.Temp >70
ORDER BY payload.datetime;
Your result will be similar to the following:
Payload.Flow Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight ------------ ---------------- --------------- ------------ ------------------ 80.7 2018-06-28 00:00 09423560 71.5 7.39 222 2018-06-28 00:00 09429070 78.2 4.67 0.00 2018-06-28 00:00 09424900 79.5 -0.13 84.4 2018-06-28 00:15 09423560 71.4 7.43 218 2018-06-28 00:15 09429070 78.1 4.70 0.00 2018-06-28 00:15 09424900 78.9 -0.13 84.5 2018-06-28 00:30 09423560 71.4 7.45 238 2018-06-28 00:30 09429070 78.0 4.74 0.00 2018-06-28 00:30 09424900 78.2 -0.13 87.2 2018-06-28 00:45 09423560 71.3 7.49 224 2018-06-28 00:45 09429070 78.0 4.77 0.00 2018-06-28 00:45 09424900 77.8 -0.13 87.7 2018-06-28 01:00 09423560 71.2 7.52 223 2018-06-28 01:00 09429070 77.9 4.86 0.00 2018-06-28 01:00 09424900 76.5 -0.13