This example accesses external data in JSON format. You can also use READ_NOS to access CSV format data.
Empty Single Column Input Table and LOCATION Parameter
This statement defines a table indata with a single column, Payload with a JSON data type. The table does not have a primary index.
CREATE TABLE indata (payload VARCHAR(64000)) NO PRIMARY INDEX;
This following statement performs a query using the READ_NOS function mapping called READ_NOS_json_intable_fm, and includes a subquery of the table indata.
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 TOP 10 * FROM READ_NOS_json_intable_fm (
ON (SELECT * FROM indata)
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
RETURNTYPE('NOSREAD_RECORD')
) AS d ;
Result:.
Location ObjectVersionId ObjectTimeStamp OffsetIntoObject ObjectLength ExtraField Payload
-------------------------------------------------------------- --------------- --------------- ---------------- ------------ ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 760 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 01:15", "Flow":"14700", "GageHeight":"9.68", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 1216 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 02:00", "Flow":"13600", "GageHeight":"9.46", "Temp":"10.7", "Conductance":"671", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 1368 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 02:15", "Flow":"13200", "GageHeight":"9.38", "Temp":"10.6", "Conductance":"672", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 1064 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 01:45", "Flow":"13900", "GageHeight":"9.53", "Temp":"10.7", "Conductance":"672", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 152 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 00:15", "Flow":"16000", "GageHeight":"9.93", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 0 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 00:00", "Flow":"16200", "GageHeight":"9.97", "Temp":"11.0", "Conductance":"669", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 456 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 00:45", "Flow":"15400", "GageHeight":"9.82", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 912 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 01:30", "Flow":"14300", "GageHeight":"9.61", "Temp":"10.7", "Conductance":"673", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 608 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 01:00", "Flow":"15100", "GageHeight":"9.77", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL NULL 304 151 NULL { "site_no":"09380000", "datetime":"2018-06-27 00:30", "Flow":"15700", "GageHeight":"9.88", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
Multiple Column Input Table and LOCATION Parameter
This statement defines a table using a subquery with the READ_NOS function mapping, READ_NOS_json_intable_fm, and the NOSREAD_KEYS return type.
CREATE TABLE import_data
AS
(
SELECT Location ExtFsLocation,
ObjectVersionId ExtFSKeyVersionID,
ObjectTimeStamp ExtFSKeyTimeStamp,
CAST(0 AS BIGINT) ExtFSRowOffSet,
ObjectLength ExtFSRowLength,
CAST(NULL AS VARCHAR(1024) CHARACTER SET LATIN) Payload
FROM READ_NOS_json_intable_fm (
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
RETURNTYPE('NOSREAD_KEYS')
) AS d ) WITH DATA NO PRIMARY INDEX;
This statement performs a query using a subquery of the import_data table with the READ_NOS function mapping, READ_NOS_json_intable_fm, and the NOSREAD_RECORD return type.
SELECT TOP 5 Payload
FROM READ_NOS_json_intable_fm (
ON (SELECT * FROM import_data)
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06')
RETURNTYPE('NOSREAD_RECORD') ) AS d;
Result:
Payload
-------------------------------------------------------------------------------------------------------------------------------------------------------
{ "site_no":"09380000", "datetime":"2018-06-30 00:15", "Flow":"15600", "GageHeight":"9.85", "Temp":"10.4", "Conductance":"685", "Precipitation":"0.00"}
{ "site_no":"09380000", "datetime":"2018-06-27 00:15", "Flow":"16000", "GageHeight":"9.93", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
{ "site_no":"09380000", "datetime":"2018-06-27 00:00", "Flow":"16200", "GageHeight":"9.97", "Temp":"11.0", "Conductance":"669", "Precipitation":"0.00"}
{ "site_no":"09380000", "datetime":"2018-06-30 00:00", "Flow":"15900", "GageHeight":"9.92", "Temp":"10.4", "Conductance":"685", "Precipitation":"0.00"}
{ "site_no":"09380000", "datetime":"2018-06-28 00:00", "Flow":"15900", "GageHeight":"9.91", "Temp":"11.0", "Conductance":"671", "Precipitation":"0.00"}
Display Keys in the JSON File
This SELECT statement uses the READ_NOS_json_fm function mapping with JSON_KEYS function to display the keys (the field names from the field:value pairs) in the external JSON files at the specified location.
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 * FROM JSON_KEYS (
ON (
SEL payload FROM READ_NOS_json_fm (
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA')
RETURNTYPE('NOSREAD_RECORD')
) AS read_nos_query
) AS json_on_keys_query ) AS d ;
Result:
JSONKeys ---------------- "BatteryVoltage" "Flow" "Precipitation" "Conductance" "Velocity" "datetime" "WaterVelocity" "GageHeight2" "site_no" "Temp" "GageHeight"
Query and Sample Results
This query uses the READ_NOS_json_fm to return selected information for June 28, 2018, where the temperature was over 70 degrees, listed by 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.datetime, payload.site_no, payload.Temp, payload.GageHeight
FROM READ_NOS_json_fm (
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA')
)
AS derived_table
WHERE payload.datetime LIKE '%2018-06-28%'
AND payload.Temp >70
ORDER BY payload.datetime;
Your results 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