Accessing External Data | SELECT SQL Statement | Teradata Vantage - $PATH Expression for External Data Access - 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™

You can use the $PATH expression to access the external data specified by the LOCATION and PATHPATTERN parameters of the CREATE FOREIGN TABLE statement. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The PATHPATTERN variables correspond to the levels in the key prefix of the LOCATION in the foreign table definition.

Path Pattern, Key Filtering, and $PATH Expressions

For example, the PATHPATTERN is specified as:

$var1/$var2

For various key prefixes, the following table list the output of $PATH expressions in a SELECT query:

Key Prefix $PATH.$Var1 $PATH.$Var2 $PATH
mydata mydata ' ' mydata
mydata/logs/sensor mydata logs mydata/logs/sensor
mydata/ mydata ' ' mydata/
mydata/logs mydata logs mydata/logs
NULL ' ' ' ' ' '

PATHPATTERN Consisting of Variables that You Specify

You can specify your own variables in the PATHPATTERN option of the foreign table definition.

For example, the LOCATION parameter definition of the foreign table riverflow is as follows.

LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA')

This is a sample listing of the files in the external repository:

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

The connector, bucket, endpoint, and key prefix are as follows.

connector bucket endpoint key_prefix
S3 td-usgs s3.amazonaws.com JSONDATA/09423560/2018/07/26.json

The PATHPATTERN parameter for riverflow is defined as follows.

PATHPATTERN  ('$data_format/$location/$year/$month/$day')
This PATHPATTERN corresponds to the following key prefix levels:
  • $PATH.$data_format ='JSONDATA'
  • $PATH.$location ='09423560'
  • $PATH.$year ='2018'
  • $PATH.$month ='07'
  • $PATH.$day ='26.json'

Sample Query Using the $PATH Variable to Select from the Location Column

You can use the $PATH expression to filter query results on the Location column, for example.

Create the foreign table specifying variables in the PATHPATTERN:

CREATE FOREIGN TABLE riverflow (
  Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,  
  Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE
) 
USING 
(   
  LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
  PATHPATTERN ('$data_format/$location/$year/$month/$day')
)   
NO PRIMARY INDEX;

Run the query:

SELECT DISTINCT location FROM riverflow
WHERE $PATH.$month ='06';

Your result will be similar to the following:

 Location                                                       
 -------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/06/27.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400568/2018/06/30.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09396100/2018/06/29.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/30.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09513780/2018/06/30.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09513780/2018/06/27.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/30.json

Sample Query Using the $PATH Variable to Select from the Payload Column

You can use the $PATH expression to filter query results on the Payload column, for example.

SELECT payload.Flow, payload.Precipitation, payload.Conductance,   
  payload.datetime, payload.site_no, payload.Temp, payload.GageHeight FROM riverflow
WHERE $PATH.$day ='27.json';

Your result will be similar to the following:

 Payload.Flow Payload.Precipitation Payload.Conductance Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight 
 ------------ --------------------- ------------------- ---------------- --------------- ------------ ------------------ 
 0.00         0.00                  NULL                2018-06-27 00:00 09400815        NULL         -0.01             
 16200        0.00                  669                 2018-06-27 00:00 09380000        11.0         9.97              
 0.00         0.00                  NULL                2018-06-27 00:00 09396100        NULL         0.49              
 0.00         0.00                  NULL                2018-06-27 00:00 09513780        NULL         -1.44             
                                    NULL                2018-06-27 00:07 09400815        NULL                           
 16000        0.00                  668                 2018-06-27 00:15 09380000        10.9         9.93              
 0.00         0.00                  NULL                2018-06-27 00:15 09396100        NULL         0.49              
 0.00         0.00                  NULL                2018-06-27 00:15 09513780        NULL         -1.44             
 0.00         0.00                  NULL                2018-06-27 00:15 09400815        NULL         -0.01             
 15700        0.00                  668                 2018-06-27 00:30 09380000        10.9         9.88              
 0.00         0.00                  NULL                2018-06-27 00:30 09396100        NULL         0.48              
 0.00         0.00                  NULL                2018-06-27 00:30 09513780        NULL         -1.44             
 0.00         0.00                  NULL                2018-06-27 00:30 09400815        NULL         -0.01             
 15400        0.00                  672                 2018-06-27 00:45 09380000        10.8         9.82              
 0.00         0.00                  NULL                2018-06-27 00:45 09396100        NULL         0.49              
 0.00         0.00                  NULL                2018-06-27 00:45 09513780        NULL         -1.44