Path Filtering in CREATE TABLE Definition | NOS | Teradata Vantage - 17.10 - Filtering Using a Path Filter in the CREATE FOREIGN TABLE Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1214-171K
Language
English (United States)

When a subset of objects in an object store 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") will be 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 Controlling Foreign Table Access with an AUTHORIZATION Object.

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 will be 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 ;