Filter to Retrieve Data Efficiently | Native Object Store | Teradata Vantage - Using Filtering to Retrieve Data More Efficiently - Analytics Database - Teradata Vantage

Teradata Vantage™ - Native Object Store Getting Started Guide - 17.20

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
tsq1628112323282.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jjn1567647976698
lifecycle
latest
Product Category
Teradata Vantage

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 (for example, site_no, 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.$site_no='09380000')
  • Path filtering is the most efficient way to read a selected portion on object store.
In path filtering, by default the folders are referred to as $var1, $var2, and so on.
For CSV data, we attempt to use the column name, if it is the same as the folder name, instead of the generic $varX variable, for example, WHERE $path.$site_no='09380000'.

In the example we show the generic syntax with the generic PATHPATTERN.

CSV folder structure

You create a foreign table against this structure like this:

CREATE FOREIGN TABLE table_name
, EXTERNAL SECURITY authorization_object
USING (
LOCATION('YOUR-OBJECT-STORE-URI')
PATHPATTERN('$var1/$var2/$var3/$var4/$var5')
);
PATHPATTERN is optional. As an example, the PATHPATTERN in the CREATE FOREIGN statement matches the structure of the object store shown in the diagram.

You can look at the table definition with SHOW TABLE:

SHOW TABLE table_name;
The table definition for the object store folder in the figure shows that the variables point to specific folders and files in the folder:
  • $var1 points to the CSVDATA folder
  • $var2 points to the 09380000 folder
  • $var3 points to the 2018 folder
  • $var4 points to the 06 folder
  • $var5 points to the files, such as 27.csv, 28.csv, and so on

You can change $var names in the foreign table definition with PATHPATTERN, if you know the structure of your data.

For example:

CREATE FOREIGN TABLE table_name
, EXTERNAL SECURITY authorization_object
USING (
LOCATION('YOUR-OBJECT-STORE-URI')
PATHPATTERN('$datatype/$site_no/$year/$month/$filename')
);

If you defined the table with meaningful names, you use those names instead of $varX when referring to a $path. for example, see Using Path Variables as Columns in a View.

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.