Path Filtering in CREATE TABLE Definition | NOS | VantageCloud Lake - Filtering Using a Path Filter in the CREATE FOREIGN TABLE Definition - Teradata Vantage

When a subset of objects in external object storage have been identified for future attention, Teradata recommends using a path filtering value based on the folder 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, filtering is expressed in the LOCATION clause by value.

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.

1. To run NOS-related commands, log on to the database as a user with the required privileges.
2. Create a foreign table or ask your database administrator to create the foreign table specifying a LOCATION that filters on one of the PATHPATTERN variables:

   CREATE FOREIGN TABLE table_name
   , EXTERNAL SECURITY authorization_object
   USING (
   LOCATION ('YOUR-OBJECT-STORE-URI/')
   PATHPATTERN('$var1, $var2, $var3, $var4, $var5') );

   See Variable Substitutions for Examples for the credentials and location values for the sample river flow data set.

   You can use SHOW TABLE table_name to see the table definition.

Example: Filtering Using a Path Filter in the CREATE FOREIGN TABLE Definition

In the example, filtering is expressed in the LOCATION clause. Only data from river sites that match the site_no (for example, that begin with "0938") are returned through this foreign table definition.

If not already created, create the authorization object or as your database administrator to create it. Use the authorization object created in Example: Controlling Access with an Authorization Object Using User and Password Credentials.

CREATE FOREIGN TABLE riverflow_pathfilter
, EXTERNAL SECURITY  MyAuthObj
USING (
LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/')
PATHPATTERN('$data/$site_no/$year/$month/$day') );

The site_no is used to filter the data.

Show the table definition:

SHOW TABLE riverflow_pathfilter;

Your result is similar to the following:

CREATE MULTISET FOREIGN TABLE NOS_USR.riverflow_pathfilter ,FALLBACK ,
     EXTERNAL SECURITY NOS_USR.MYAUTHOBJ ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Temp DECIMAL(3,1),
      Flow SMALLINT,
      site_no INTEGER,
      datetime TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI',
      Conductance SMALLINT,
      Precipitation DECIMAL(3,2),
      GageHeight DECIMAL(4,2))
USING
(
      LOCATION  ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/')
      PATHPATTERN  ('$data/$site_no/$year/$month/$day')
      MANIFEST  ('FALSE')
      ROWFORMAT  ('{"field_delimiter":",","record_delimiter":"\n","character_set":"LATIN"}')
      STOREDAS  ('TEXTFILE')
      HEADER  ('TRUE')
      STRIP_EXTERIOR_SPACES  ('FALSE')
      STRIP_ENCLOSING_CHAR  ('NONE')
)
NO PRIMARY INDEX ;