Filter to Retrieve CSV Data Efficiently | Native Object Store | Teradata Vantage - 17.10 - Using Filtering to Retrieve CSV Data More Efficiently - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Reading all the objects in a bucket can be time-consuming and expensive. An alternative solution is to filter the object store using one or both of the following filtering methods:

Path Filtering
  • A path is a series of values that constitute a multi-level key (siteno, year, month, day) which identifies one or more objects within an object store.
  • Path filtering lets you filter and reduce the amount of objects you bring back to the database by providing specified values for different levels in a path key (for example, "WHERE $path.$siteno = '09380000'")
  • Path filtering is the most efficient way to read a selected portion on object store.
Payload Filtering
  • The payload column within a foreign table contains all of the values within an object.
  • In payload filtering you apply a filter to the data itself and not to the partitioning (path key) of the object store.
  • When filtering is applied to a column in the data (such as WHERE Flow > '15'), all objects in the object store have to be brought inside the database and be transformed prior to applying filtering.
  • When given a choice, this is usually less efficient because it will incur greater effort at both the object store level and the database level than does path filtering.