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

File Name Query

If not already done, create the authorization object. See Example: Create an Authorization Object.

This query uses READ_NOS 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.

Perform the READ_NOS:

SELECT location FROM (
LOCATION='/S3/td-usgs-public.s3.amazonaws.com/JSONDATA/09380000/2018/06/27'
AUTHORIZATION=MyAuthObj
RETURNTYPE='NOSREAD_KEYS'
) AS derived_table;

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

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

Sample Query and Results

This query uses READ_NOS 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.

If not already done, create the authorization object. See Example: Create an Authorization Object.

Perform the READ_NOS:

SELECT payload.Flow, payload.Precipitation, payload.Conductance,
payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
FROM (
LOCATION='/S3/td-usgs-public.s3.amazonaws.com/JSONDATA/09380000/2018/06/27'
AUTHORIZATION=MyAuthObj
) AS derived_table
WHERE payload.Temp > 11.0 AND payload.GageHeight > 10.00
ORDER BY payload.datetime;

Your result will be similar to the following:

 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             
 [...]