パス フィルタリングでの外部テーブル ビューの使用 - Advanced SQL Engine - Teradata Database

REPLACE VIEW riverflowview_csv AS (
SELECT
CAST($path.$siteno AS CHAR(10)) TheSite,
CAST($path.$year AS CHAR(4)) TheYear,
CAST($path.$month AS CHAR(2)) TheMonth,
CAST(SUBSTR($path.$day, 1, 2) AS CHAR(2)) TheDay,
Flow,
GageHeight,
Precipitation,
Temp,
Conductance
FROM riverflow_csv_pathfilter
WHERE TheSite = site_no);

SELECT TOP 2 * FROM riverflowview_csv;

結果は次のようになります。

TheSite     TheYear  TheMonth  TheDay    Flow  GageHeight  Precipitation   Temp  Conductance
----------  -------  --------  ------  ------  ----------  -------------  -----  -----------
09380000    2018     07        18       17500       10.21            .00   11.0          670
09380000    2018     07        19       17200       10.16            .00   10.5          682

属性が欠落している列は、レコードに属性が含まれていない場合にNULLを返します。

SELECT thesite,COUNT(*)                                            
FROM riverflowview_csv WHERE thesite=09380000'
GROUP BY 1;

結果は次のようになります。

TheSite        Count(*)
----------  -----------
09380000           2945

EXPLAIN
SELECT thesite,COUNT(*)                                            
FROM riverflowview_csv WHERE thesite=09380000'
GROUP BY 1;

結果は次のようになります。

Explanation
--------------------------------------------------------------------------
[…]
  2) Next, we do a single-AMP RETRIEVE step from
     NOS_USR.riverflow_csv_pathfilter in view riverflowview_csv
     metadata by way of an all-rows scan with a condition of ( "(TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_csv_pathfilter in view riverflowview_csv.Location, '/s3/td-usgs-public.s3.amazonaws.com', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= '09380000 '") into Spool 2 (one-amp), which is built locally on that AMP.
     Then we do a SORT to order Spool 2 by the sort key.  The size of
     Spool 2 is estimated with no confidence to be 4 rows (2,820 bytes).
     The estimated time for this step is 0.55 seconds.
  3) We do a single-AMP RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan into Spool 5 (all_amps), which is binpacked and
     redistributed by size to all AMPs in TD_Map1.  The size of Spool 5
     is estimated with no confidence to be 4 rows (2,852 bytes).  The
     estimated time for this step is 0.06 seconds.
  4) We do an all-AMPs SUM step in TD_MAP1 to aggregate from
     NOS_USR.riverflow_csv_pathfilter in view riverflowview_csv
     by way of an object-store scan using Spool 5 (Last Use) with a
     condition of ("(TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_csv_pathfilter in view riverflowview_csv.Location, '/s3/td-usgs-public.s3.amazonaws.com', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC)(FLOAT, FORMAT '-9.99999999999999E-999'))= (NOS_USR.riverflow_csv_pathfilter in view riverflowview_csv.site_no)"), and the grouping identifier in field
     1.  Aggregate intermediate results are computed globally, then
     placed in Spool 4 in TD_Map1.  The size of Spool 4 is estimated
     with no confidence to be 222 rows (311,244 bytes).  The estimated
     time for this step is 2.18 seconds.
[…]

(2)ではパス フィルタリングを行なっています。メタデータ スプールを構築するためのパス フィルタリング式として定数09380000を使用しています。メタデータ スプールは、クエリーが実際に処理するオブジェクトのリストを識別するスプール テーブルです。

(4)では従来の行フィルタリングを行なっています。場所の文字列から抽出されたサイト番号とオブジェクト ストア内の実際のデータの値を比較します。