READ_NOS Table Operator | SELECT SQL Statement | Teradata Vantage - 17.05 - Example: Using READ_NOS with NOSREAD_RECORD Return Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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