When a subset of objects in an object store have been identified for future attention, Teradata recommends using a path filtering value based on key structure values in the definition of the foreign table. The LOCATION clause in the foreign table definition can specify specific values at one or more key structure levels to limit the objects that are returned. However, each time a different filter is required, either an additional foreign table must be created, or the previous table must be dropped and recreated. To avoid that issue, you can use the PATHPATTERN clause as part of the CREATE FOREIGN TABLE.
In the example below, filtering is expressed in the LOCATION clause. Only data from river sites that match the site_no (for example, that begin with "0938") will be returned through this foreign table definition.
The examples and results show a sample river flow data set. To use your own data, replace the table and column names, and authorization object.
- To run NOS-related commands, log on to the database as a user with the required privileges.
Create a foreign table or ask your database administrator to create the foreign table specifying a LOCATION that filters on site_no. Use the authorization object created in Controlling Foreign Table Access with an AUTHORIZATION Object:
CREATE FOREIGN TABLE riverflow_json_pathfilter , EXTERNAL SECURITY DEFINER TRUSTED DefAuth ( Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC, Payload JSON INLINE LENGTH 32000 CHARACTER SET UNICODE ) USING ( LOCATION('YOUR-STORAGE-ACCOUNT/site_no') PATHPATTERN('$data/$siteno/$year/$month/$day') );
Where site_no is a site number value; for example, 0938.
See Variable Substitutions for Examples for the credentials and location values for the sample river flow data set.
Show the table definition:
SHOW TABLE riverflow_json_pathfilter;
Result: The DDL displays.