Path Filtering in CREATE TABLE Definition for CSV | NOS | Teradata Vantage - 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.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zws1595641486108.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1214
lifecycle
previous
Product Category
Software
Teradata Vantage

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.

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