READ_NOS returns raw data | SELECT SQL Statement | Teradata Vantage - 17.05 - Example: Using READ_NOS with NOSREAD_RAW 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 shows how to use the READ_NOS table operator with the NOSREAD_RAW return type to display the raw data in JSON files.

This query uses the function mapping READ_NOS_json_intable_fm, which includes ANY IN TABLE clause, and a return type of NOSREAD_RAW to display the raw JSON data from the table riverflow_raw.

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 payload
FROM READ_NOS_json_intable_fm (
ON (SELECT
     cast ('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json'
           as varchar(500)) Location,
     cast (NULL as varchar(500)) ObjectVersionId,
     cast (NULL as timestamp) ObjectTimeStamp,
     cast (0 as BIGINT)       ExtFSRowOffSet,
     cast (1 as BIGINT)       ObjectLength,
     cast (NULL as CLOB CHARACTER SET LATIN) payload  )
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json')
RETURNTYPE('NOSREAD_RAW') ) as d;

Below is a sample of the raw data output.

Payload
-----------------------------------------------------------------------------------------------------------------------------
{ "site_no":"09424900", "datetime":"2018-06-28 00:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"79.5"}{ "site_no":"09424900", "datetime":"2018-06-28 00:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.9"} { "site_no":"09424900", "datetime":"2018-06-28 00:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.2"} { "site_no":"09424900", "datetime":"2018-06-28 00:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"77.8"} { "site_no":"09424900", "datetime":"2018-06-28 01:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.5"} { "site_no":"09424900", "datetime":"2018-06-28 01:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.1"} { "site_no":"09424900", "datetime":"2018-06-28 01:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"75.7"} { "site_no":"09424900", "datetime":"2018-06-28 01:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"74.5"}