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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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