You can use the $PATH expression to access the external data specified by the LOCATION and PATHPATTERN parameters of the CREATE FOREIGN TABLE statement.
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 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')
The following 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')
- $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 is 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 is 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