Accessing External Data | SELECT SQL Statement | Teradata Vantage - $PATH Expression for External Data Access - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
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.

By default, the PATHPATTERN parameter contains the following value:

$Var1/$Var2/$Var3/$Var4/$Var5/$Var6/$Var7/$Var8/$Var9/$Var10
/$Var11/$Var12/$Var13/$Var14/$Var15/$Var16/$Var17/$Var18/$Var19/$Var20

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// mydata ' ' mydata//
mydata//logs mydata ' ' mydata//logs
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.s3.amazonaws.com/JSONDATA')

Below is a sample listing of the files in the external repository.

 Location                                                       
 -------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/07/03.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/09400815/2018/07/21.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400568/2018/07/02.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/07/24.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09423560/2018/07/11.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09400815/2018/06/27.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09474000/2018/07/12.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09429070/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09474000/2018/07/11.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/07/23.json
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09423560/2018/07/26.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 key prefix levels listed below:
  • $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.

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

Following is a sample of the output results.

 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';

Following is a sample of the output results.

 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