Path Filtering in CREATE TABLE Definition for CSV | NOS | Teradata Vantage - 17.00 - Filtering Using a Path Filter in the CREATE FOREIGN TABLE Definition - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B035-1214-170K

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

  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 site_no. Use the authorization object created in Restricting Foreign Table Access with an AUTHORIZATION Object:
    CREATE FOREIGN TABLE riverflow_csv_pathfilter
    , EXTERNAL SECURITY DEFINER TRUSTED DefAuth (
    Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
    Payload DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV
    )
    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.

  3. Show the table definition:
    SHOW TABLE riverflow_csv_pathfilter;

    Result: The DDL displays.