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.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
vjt1596846980081.ditamap
dita:ditavalPath
vjt1596846980081.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.

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

Below 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 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.

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