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

REPLACE VIEW riverflowview_json AS (
SELECT
CAST($path.$siteno AS CHAR(10)) TheSite,
CAST($path.$year AS CHAR(4)) TheYear,
CAST($path.$month AS CHAR(2)) TheMonth,
CAST($path.$day AS CHAR(2)) TheDay,
payload.site_no site_no,
payload.datetime datetime,
payload.Flow Flow,
payload.GageHeight GageHieght,
payload.Precipitation Precipitation,
payload.GageHeight2 GageHeight2
FROM riverflow_json_path
WHERE TheSite = payload.site_no);

SELECT TOP 2 * FROM riverflowview_json;

結果:

      TheSite  09396100
      TheYear  2018
     TheMonth  07
       TheDay  14
      site_no  09396100
     datetime  2018-07-14 00:00
         Flow  232
   GageHieght  2.16
Precipitation  0.00
  GageHeight2  2.16

      TheSite  09396100
      TheYear  2018
     TheMonth  07
       TheDay  16
      site_no  09396100
     datetime  2018-07-16 00:00
         Flow  44.7
   GageHieght  1.50
Precipitation  0.00
  GageHeight2  1.50

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

SELECT thesite,COUNT(*)
FROM riverflowview_json WHERE thesite='09396100'
GROUP BY 1;

結果:

TheSite        Count(*)
----------  -----------
09396100           3086

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

結果:

Explanation
--------------------------------------------------------------
[…]
  2) Next, we do a single-AMP RETRIEVE step from
     NOS_USR.riverflow_json_path in view riverflowview_json metadata by
     way of an all-rows scan with a condition of (
     "(TD_SYSFNLIB.NosExtractVarFromPath (NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/s3/td-usgs-public.s3.amazonaws.com', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= '09396100 '") 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 38 rows (26,790 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 38 rows (27,094 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_json_path in view riverflowview_json
     by way of an object-store scan using Spool 5 (Last Use) with a
     condition of ("((TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/s3/td-usgs-public.s3.amazonaws.com', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= '09396100 ') AND (((TD_SYSFNLIB.NosExtractVarFromPath (NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/s3/td-usgs-public.s3.amazonaws.com', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= (TRANSLATE((NOS_USR.riverflow_json_path in view riverflowview_json.Payload .JSONEXTRACTVALUE ( '$.site_no'(VARCHAR(64000), CHARACTER SET LATIN, NOT CASESPECIFIC)))USING LATIN_TO_UNICODE))) AND ((TRANSLATE((NOS_USR.riverflow_json_path in view riverflowview_json.Payload .JSONEXTRACTVALUE ( '$.site_no'(VARCHAR(64000), CHARACTER SET LATIN, NOT CASESPECIFIC)))USING LATIN_TO_UNICODE))= '09396100 '))"), 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 1,498 rows (
     2,100,196 bytes).  The estimated time for this step is 24.02
     seconds.
 […]

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

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