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

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

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