JSON File | READ_NOS Query | Teradata Vantage - Example: Using READ_NOS to Query an External JSON File - 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 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             
 [...]