JSON Examples | Native Object Store ( NOS ) | Teradata Vantage - 17.10 - Examples: Reading JSON Data - Advanced SQL Engine

The following examples show accessing JSON-formatted data from Teradata's public Azure Blob storage container.

The following table shows the LOCATION values for the Teradata-supplied public external object stores that contain JSON data. You can run the examples using any of these locations, after first setting up the function mapping here Setting Up to Run the Examples.

Platform	LOCATION
Amazon S3	/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/
Azure Blob storage	/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/
Google Cloud Storage	/gs/storage.googleapis.com/td-usgs/JSONDATA/

If you want to use your own external object store, see Variable Substitutions for Examples for examples of LOCATION, ACCESS_ID/USER, and ACCESS_KEY/PASSWORD.

Using SHOW AUTHORIZATION

SHOW AUTHORIZATION NOS_USR.MyAuthObj;

If the authorization object does not exist, run the steps in Setting Up to Run the Examples.

Using NOSREAD_RECORD to Return the Payload from the Specified LOCATION

SELECT TOP 2 payload.* FROM (
LOCATION='/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/'
AUTHORIZATION='{"ACCESS_ID":"","ACCESS_KEY":""}'
RETURNTYPE='NOSREAD_RECORD'
) AS d;

See Using NOSREAD_RECORD to Return the Payload from the Specified LOCATION.

Setting Up to Run JSON Examples

CREATE FOREIGN TABLE riverflow_json
, EXTERNAL SECURITY MyAuthObj
USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/') );

See Setting Up to Run JSON Examples.

Sampling External Data In Place Using READ_NOS

SELECT TOP 2 * FROM READ_NOS_FM (
USING
LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
RETURNTYPE('NOSREAD_RECORD')
) AS D;

See Sampling External Data in Place Using READ_NOS.

Sampling External Data In Place Using a Foreign Table

SELECT TOP 2 payload.* FROM riverflow_json;

See Sampling External Data In Place Using a Foreign Table.

Using JSON_KEYS to See Header Names

SELECT DISTINCT * FROM JSON_KEYS (
ON (SELECT payload FROM riverflow_json)
) AS jsonKeys;

See Using JSON_KEYS to See Key Names.

Finding Maximum and Minimum Values

SELECT MAX(CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99'))
, MIN(CAST(payload.Flow AS FLOAT) ( FORMAT '-(5)ZZZ9.99'))
FROM riverflow_json;

See Finding Maximum and Minimum Values.

Querying for a Specific Set of Rows

SELECT MAX(CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99'))
FROM riverflow_json
WHERE payload.site_no = '09394500';

See Querying for a Specific Set of Rows.

Loading External Data into the Database Using CREATE TABLE AS … WITH DATA

CREATE MULTISET TABLE riverflowprecip_json(site_no,flow,gageheight,datetime) AS (
SELECT payload.site_no, payload.Flow, payload.GageHeight, payload.datetime
FROM riverflow_json
WHERE payload.Precipitation > 0
) WITH DATA
NO PRIMARY INDEX;

Display the number of rows in the table:

SELECT COUNT(*) FROM riverflowprecip_json;

See Loading External Data into the Database Using CREATE TABLE AS ... WITH DATA.

Loading External Data into the Database Using READ_NOS and CREATE TABLE AS

CREATE MULTISET TABLE riverflowperm_json(site_no,flow,gageheight,datetime) AS (
SELECT payload.site_no, payload.Flow, payload.GageHeight, payload.datetime
FROM ( LOCATION='/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/' ) AS d
) WITH DATA;

Display the number of rows in the table:

SELECT COUNT (*) FROM riverflowperm_json;

See Loading External Data into the Database Using READ_NOS and CREATE TABLE AS.

Loading External Data into the Database Using INSERT ... SELECT

CREATE TABLE RiverFlowPermInsert_json (
DateTS CHAR(20)
,SiteNo CHAR(8)
,Flow FLOAT FORMAT '-ZZZZ9.99'
,Velocity FLOAT FORMAT '-ZZZ9.99'
,GageHeight FLOAT FORMAT '-ZZZ9.99'
,Temp FLOAT FORMAT '-ZZZ9.99' )
PRIMARY INDEX (SiteNo);

Insert the external data into the database table:

INSERT INTO RiverFlowPermInsert_json
SELECT payload.datetime,payload.site_no,payload.Flow,payload.Velocity,payload.GageHeight,payload.Temp
WHERE payload.site_no='09429070'
FROM riverflow_json;

Query the data from the database table:

SELECT TOP 2 * FROM RiverFlowPermInsert_json;

See Loading External Data into the Database Using INSERT ... SELECT.

Joining External Data and Database Tables

Create a database dimension table to join to:

CREATE SET TABLE rivernames(
site_no CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
name CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( site_no ) ;

Create the foreign table or ask your database administrator to create the foreign table that is used to populate the dimension table:

CREATE FOREIGN TABLE nos_rivernames
, EXTERNAL SECURITY  MyAuthObj
USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );

Populate the dimension table:

INSERT INTO rivernames
SELECT CAST(site_no AS CHAR(8)), name
FROM nos_rivernames;

Join the dimension table and external data (foreign table):

SELECT DISTINCT name(CHAR(100))
FROM riverflow_json rf, rivernames rn
WHERE rf.payload.site_no = cast(rn.site_no as INTEGER)
AND rf.payload.Precipitation > 0.1
ORDER BY 1;

See Joining External Data and Database Tables.

Filtering on the Payload Column of a Foreign Table

Create a foreign table or ask your database administrator to create the foreign table using a custom PATHPATTERN:

CREATE FOREIGN TABLE riverflow_json_path
, EXTERNAL SECURITY MyAuthObj
USING (
LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
PATHPATTERN('$data/$siteno/$year/$month/$day') );

Run a query using a payload filter only:

SELECT TOP 2 payload.GageHeight,payload.Flow
FROM riverflow_json_path
WHERE payload.site_no = '09394500';

Run a similar query using equivalent path values in the filter:

SELECT TOP 2 payload.GageHeight,payload.Flow
FROM riverflow_json_path
WHERE $path.$siteno = '09394500';

See Filtering on the Payload Column of a Foreign Table.

Querying a Foreign Table with Path Filtering

Use path filtering to narrow down search results:

SELECT TOP 2 payload.*
FROM riverflow_json_path
WHERE $PATH.$year = '2018'
AND $PATH.$month = '07'
AND $PATH.$day = '01.json'
AND $PATH.$siteno = '09394500';

See Querying a Foreign Table with Path Filtering.

Combining Path and Payload Filtering

Combine path and payload filtering:

SELECT COUNT(*)
FROM riverflow_json_path
WHERE payload.GageHeight > 5
AND $path.$siteno = '09394500'
AND $path.$year = '2018'
AND $path.$month = '07';

See Combining Path and Payload Filtering.

Creating a Basic View

Create the view of the foreign table:

CREATE VIEW riverflowview_json(site_no,datetime,flow,gageheight,precipitation,gageheight2) AS (
SELECT payload.site_no,payload.datetime,payload.Flow,payload.GageHeight,payload.Precipitation,payload.GageHeight2
FROM riverflow_json_path );

Query the view:

SELECT TOP 2 * FROM riverflowview_json;

See Creating a Basic View.

Using Path Variables as Columns in a View

REPLACE VIEW riverflowview_json AS (
SELECT
CAST($path.$siteno AS CHAR(10)) TheSite,
CAST($path.$year AS CHAR(4)) TheYear,
CAST($path.$month AS CHAR(2)) TheMonth,
CAST(SUBSTR($path.$day, 1, 2) AS CHAR(2)) TheDay,
payload.site_no site_no,
payload.datetime datetime,
payload.Flow Flow,
payload.GageHeight GageHieght,
payload.Precipitation Precipitation,
payload.GageHeight2 GageHeight2
FROM riverflow_json_path);

See Using Path Variables as Columns in a View.

Using a Foreign Table View with Path Filtering

Replace the view of the foreign table:

REPLACE VIEW riverflowview_json AS (
SELECT
CAST($path.$siteno AS CHAR(10)) TheSite,
CAST($path.$year AS CHAR(4)) TheYear,
CAST($path.$month AS CHAR(2)) TheMonth,
CAST($path.$day AS CHAR(2)) TheDay,
payload.site_no site_no,
payload.datetime datetime,
payload.Flow Flow,
payload.GageHeight GageHieght,
payload.Precipitation Precipitation,
payload.GageHeight2 GageHeight2
FROM riverflow_json_path
WHERE TheSite = payload.site_no);