JSON File | READ_NOS Query | Teradata Vantage - 17.05 - Example: Using READ_NOS to Query an External JSON File - 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 to access an external JSON file that includes the extension .JSON.

File Name Query

This query uses the function mapping READ_NOS_json_fm for the READ_NOS table operator to query the file at the specified location with a return type of NOSREAD_KEYS. The LOCATION parameter specifies a JSON file stored on Amazon S3.

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/JSONDATA/09380000/2018/06/27')
   RETURNTYPE('NOSREAD_KEYS')
) AS derived_table;

The output result shows that the file includes the extension, .JSON.

 Location                                                       
 -------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json

Sample Query and Results

This query uses the READ_NOS_json_fm function mapping to return specific columns searching for entries where the temperature was greater than 11 and the height was more than 10.00, ordered by date and 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.Precipitation, payload.Conductance, payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
  FROM READ_NOS_json_fm (
  USING
    LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27')
    RETURNTYPE('NOSREAD_RECORD')
) AS derived_table  
  WHERE payload.Temp>11.0 AND payload.GageHeight>10.00 
  ORDER BY payload.datetime;

Below is a sample of the output.

 Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight 
 ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 
 16400        0.00                  668                 2018-06-27 14:15 09380000        11.8         10.01             
 16400        0.00                  670                 2018-06-27 14:30 09380000        11.8         10.01             
 16600        0.00                  665                 2018-06-27 14:45 09380000        11.9         10.04             
 16600        0.00                  669                 2018-06-27 15:00 09380000        11.9         10.05             
 16700        0.00                  668                 2018-06-27 15:15 09380000        12.0         10.07             
 16700        0.00                  667                 2018-06-27 15:30 09380000        12.0         10.07             
 16700        0.00                  663                 2018-06-27 15:45 09380000        12.0         10.07             
 16800        0.00                  666                 2018-06-27 16:00 09380000        12.0         10.09             
 16800        0.00                  667                 2018-06-27 16:15 09380000        12.0         10.08             
 16800        0.00                  667                 2018-06-27 16:30 09380000        12.0         10.08             
 16800        0.00                  668                 2018-06-27 16:45 09380000        12.0         10.09             
 16900        0.00                  670                 2018-06-27 17:00 09380000        12.0         10.10             
 16800        0.00                  670                 2018-06-27 17:15 09380000        12.0         10.09