JSON File | READ_NOS Query | Teradata Vantage - 17.05 - Example: Using READ_NOS to Query an External JSON File Without an Extension - 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 READ_NOS to query a JSON file without an extension (.JSON)

Verify File Name

This query uses the function mapping READ_NOS_json_fm to check for file extension, .JSON, by querying the location column with a return type of NOSREAD_KEYS.

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 location
  FROM READ_NOS_json_fm
 (
  USING
   LOCATION('/S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28')
   RETURNTYPE('NOSREAD_KEYS')
) AS derived_table;

The result shows that the file 28 does not have an extension.

Location                                              
 ----------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28

Sample Query and Results

This statement includes the ON clause to define the file 28 as a JSON file.

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.

SELECT payload.Flow, payload.Precipitation, payload.Conductance,   
  payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
FROM READ_NOS_json_intable_fm (
  ON (SELECT CAST (NULL AS JSON(8388096)))
  USING
  LOCATION('/S3/td-usgs.s3.amazonaws.com/DATA/09380000/2018/06/28')
) AS derived_table 
  WHERE payload.Temp>11.0 AND payload.GageHeight>10.00 ORDER BY payload.datetime;

Below is a sample of the results.

Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight 
 ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 
 16400        0.00                  673                 2018-06-28 13:30 09380000        11.3         10.01             
 16400        0.00                  673                 2018-06-28 13:45 09380000        11.4         10.01             
 16400        0.00                  675                 2018-06-28 14:00 09380000        11.4         10.01             
 16600        0.00                  678                 2018-06-28 14:15 09380000        11.4         10.05             
 16600        0.00                  671                 2018-06-28 14:30 09380000        11.5         10.04             
 16700        0.00                  677                 2018-06-28 14:45 09380000        11.5         10.06             
 16800        0.00                  680                 2018-06-28 15:00 09380000        11.5         10.08             
 16700        0.00                  680                 2018-06-28 15:15 09380000        11.6         10.07             
 16800        0.00                  675                 2018-06-28 15:30 09380000        11.6         10.09             
 16800        0.00                  675                 2018-06-28 15:45 09380000        11.6         10.08             
 16800        0.00                  682                 2018-06-28 16:00 09380000        11.6         10.09