17.05 - 例: Parquetデータの読み取り - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store 入門ガイド

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1214-175K-JPN
Language
日本語 (日本)

次の例は、TeradataのパブリックGoogle Cloud StorageバケットにあるParquet形式のデータにアクセスする方法を示しています。

次の表は、Parquetデータを格納しているTeradata提供のパブリック外部オブジェクト ストアに対するLOCATION値を示しています。例を実行するための設定で関数マッピングを設定した後で、これらの場所のいずれかを使用して例を実行できます。

プラットフォーム LOCATION
Amazon S3 /s3/td-usgs-public.s3.amazonaws.com/PARQUETDATA/
Azure BLOBストレージ /az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/PARQUETDATA/
Google Cloud Storage /gs/storage.googleapis.com/td-usgs/PARQUETDATA/

独自の外部オブジェクト ストアを使用する場合は、例の変数置換を参照してLOCATION、ACCESS_ID/USER、およびACCESS_KEY/PASSWORDの例を確認します。

SHOW AUTHORIZATIONの使用

SHOW AUTHORIZATION NOS_USR.DefAuth;

許可オブジェクトが存在しない場合は、例を実行するための設定のステップを実行します。

Parquetの例を実行するための設定

CREATE FOREIGN TABLE riverflow_parquet
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC
, GageHeight2 DOUBLE PRECISION FORMAT '-ZZZ9.99'
, Flow DOUBLE PRECISION FORMAT '-ZZZZ9.99'
, site_no BIGINT
, datetime VARCHAR(16) CHARACTER SET UNICODE CASESPECIFIC
, Precipitation DOUBLE PRECISION FORMAT '-ZZZ9.99'
, GageHeight DOUBLE PRECISION FORMAT '-ZZZ9.99'
)
USING (
LOCATION ('/gs/storage.googleapis.com/td-usgs/PARQUETDATA/')
STOREDAS ('PARQUET')
) NO PRIMARY INDEX
, PARTITION BY COLUMN ;

<Parquetの例を実行するための設定>を参照してください。

外部テーブルを使用した所定の位置にある外部データのサンプリング

SELECT TOP 2 * FROM riverflow_parquet;

サンプル結果:

     Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09396100/2018/07/16.parquet
  GageHeight2     1.42
         Flow     34.10
      site_no              9396100
     datetime 2018-07-16 00:15
Precipitation     0.00
   GageHeight     1.42
     Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09396100/2018/07/16.parquet
  GageHeight2     1.50
         Flow     44.70
      site_no              9396100
     datetime 2018-07-16 00:00
Precipitation     0.00
   GageHeight     1.50

<外部テーブルを使用した所定の位置にある外部データのサンプリング>を参照してください。

最大値と最小値の検索

SELECT MAX (Flow)
, MIN (Flow)
FROM riverflow_parquet;

サンプル結果:

Maximum(Flow) Minimum(Flow)
------------- -------------
      1570.00          0.00

<最大値と最小値の検索>を参照してください。

特定の行セットのクエリー

SELECT MAX(Flow)
FROM riverflow_parquet
WHERE site_no = '09394500';

サンプル結果:

Maximum(Flow)
-------------
       232.00

<特定の行セットのクエリー>を参照してください。

READ_NOSを使用したParquetスキーマのプレビュー

SELECT TOP 2 * FROM READ_NOS_FM (
USING
STOREDAS('PARQUET')
FULLSCAN('TRUE')
RETURNTYPE('NOSREAD_PARQUET_SCHEMA')
LOCATION ('/gs/storage.googleapis.com/td-usgs/PARQUETDATA/')
) AS D;

サンプル結果:

                 Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/07/03.parquet
        ParquetColumnName GageHeight2
ParquetColumnPhysicalType DOUBLE
 ParquetColumnLogicalType NONE
             TDColumnType FLOAT
   ParquetColumnPrecision           0
       ParquetColumnScale           0
   ParquetColumnMinLength           0
   ParquetColumnMaxLength           0
      ParquetColumnIsNull    1
         ParquetColumnPos    1
                 Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/07/01.parquet
        ParquetColumnName GageHeight2
ParquetColumnPhysicalType DOUBLE
 ParquetColumnLogicalType NONE
             TDColumnType FLOAT
   ParquetColumnPrecision           0
       ParquetColumnScale           0
   ParquetColumnMinLength           0
   ParquetColumnMaxLength           0
      ParquetColumnIsNull    1
         ParquetColumnPos    1

<READ_NOSを使用したParquetスキーマのプレビュー>を参照してください。

キー構造の表示

SELECT TOP 2 LOCATION FROM READ_NOS_FM (
USING
RETURNTYPE ('NOSREAD_KEYS')
LOCATION('/gs/storage.googleapis.com/td-usgs/PARQUETDATA/')
) AS D;

サンプル結果:

Location
------------------------------------------------------------
/GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/07/03.parquet
/GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/06/28.parquet

<キー構造の表示>を参照してください。

CREATE TABLE AS...WITH DATAを使用したデータベースへの外部データのロード

CREATE MULTISET TABLE riverflowprecip_parquet AS (
SELECT CAST(site_no AS CHAR(8)) SiteNo,
CAST(Flow AS FLOAT) ( FORMAT '-ZZZZ9.99') Flow,
CAST(GageHeight AS FLOAT) ( FORMAT '-(3)ZZ9.99') GageHeight,
CAST(datetime AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI') Date_Time
FROM riverflow_parquet
WHERE Precipitation > 0
) WITH DATA
NO PRIMARY INDEX;

テーブル内の行数を表示します。

SELECT COUNT(*) FROM riverflowprecip_parquet;

サンプル結果:

Count(*)
-----------
        155

<CREATE TABLE AS...WITH DATAを使用したデータベースへの外部データのロード>を参照してください。

INSERT SELECTを使用したデータベースへの外部データのロード

CREATE TABLE RiverFlowPermInsert_parquet
(DateTS CHAR(20)
,SiteNo CHAR(8)
,Flow FLOAT FORMAT '-ZZZZ9.99'
,GageHeight FLOAT FORMAT '-ZZZ9.99'
,Precipitation FLOAT FORMAT '-ZZZ9.99')
PRIMARY INDEX (SiteNo);

外部データをデータベース テーブルに挿入します。

INSERT INTO RiverFlowPermInsert_parquet
SELECT datetime
,CAST (site_no AS CHAR(8))
,CAST (Flow AS FLOAT)
,CAST (GageHeight AS FLOAT)
,CAST (Precipitation AS FLOAT)
WHERE site_no='09394500'
FROM riverflow_parquet;

データベース テーブルのデータを問合わせます。

SELECT TOP 2 * FROM RiverFlowPermInsert_parquet;

サンプル結果:

DateTS SiteNo Flow GageHeight Precipitation
-------------------- -------- --------- ---------- -------------
2018-07-12 01:00     9394500       4.84       5.13          0.00
2018-07-12 00:30     9394500       4.84       5.13          0.00

<INSERT SELECTを使用したデータベースへの外部データのロード>を参照してください。

外部データとデータベース テーブルの結合

結合先のデータベース ディメンション テーブルを作成します。

CREATE SET TABLE rivernames (
site_no CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
name CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( site_no );

ディメンション テーブルへのデータの入力に使用する外部テーブルを作成するか、データベース管理者に作成を依頼します。

CREATE FOREIGN TABLE nos_rivernames
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
( Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
PAYLOAD DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV
)
USING (
LOCATION('/gs/storage.googleapis.com/RIVERS/rivers.csv')
);

ディメンション テーブルにデータを入力します。

INSERT INTO rivernames
SELECT payload..site_no, payload..name
FROM nos_rivernames;

ディメンション テーブルと外部データ(外部テーブル)を結合します。

SELECT DISTINCT name(CHAR(60))
FROM riverflow_parquet rf, rivernames rn
WHERE rf.site_no = rn.site_no
AND rf.Precipitation > 0.1
ORDER BY 1;

サンプル結果:

name
----------------------------------
GILA RIVER AT KELVIN
LITTLE COLORADO RIVER AT WOODRUFF
NEW RIVER NEAR ROCK SPRINGS
POLACCA WASH NEAR SECOND MESA
PUERCO RIVER NEAR CHAMBERS
SALT RIVER NEAR CHRYSOTILE

<外部データとデータベース テーブルの結合>を参照してください。

外部テーブルからの外部Parquetデータのフィルタリング

CREATE FOREIGN TABLE riverflow_parquet_path
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC
, GageHeight2 DOUBLE PRECISION FORMAT '-ZZZ9.99'
, Flow DOUBLE PRECISION FORMAT '-ZZZZ9.99'
, site_no BIGINT
, datetime VARCHAR(16) CHARACTER SET UNICODE CASESPECIFIC
, Precipitation DOUBLE PRECISION FORMAT '-ZZZ9.99'
, GageHeight DOUBLE PRECISION FORMAT '-ZZZ9.99'
)
USING (
LOCATION ('/gs/storage.googleapis.com/td-usgs/PARQUETDATA/')
PATHPATTERN ('$data/$siteno/$year/$month/$day')
STOREDAS ('PARQUET')
) NO PRIMARY INDEX
, PARTITION BY COLUMN;

Parquetデータの特定の列でフィルタ処理します。

SELECT TOP 2 GageHeight TheGageHeight
, Flow TheFlow
FROM riverflow_parquet_path
WHERE site_no = '09394500';

サンプル結果:

TheGageHeight TheFlow
------------- ---------
         5.56     31.20
         5.13      4.84

<外部テーブルからの外部Parquetデータのフィルタリング>を参照してください。

パス フィルタリングを使用した外部テーブルの問合わせ

パス フィルタリングを使用して検索結果を絞り込みます。

SELECT TOP 2 *
FROM riverflow_parquet_path
WHERE $PATH.$year = '2018'
AND $PATH.$month = '07'
AND $PATH.$day = '01.parquet'
AND $PATH.$siteno = '09394500';

サンプル結果:

     Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/07/01.parquet
  GageHeight2     4.81
         Flow      0.00
      site_no              9394500
     datetime 2018-07-01 00:15
Precipitation     0.00
   GageHeight     4.75
     Location /GS/storage.googleapis.com/td-usgs/PARQUETDATA/09394500/2018/07/01.parquet
  GageHeight2     4.82
         Flow      0.01
      site_no              9394500
     datetime 2018-07-01 00:00
Precipitation     0.00
   GageHeight     4.75

パス フィルタリングを使用してsitenoでフィルタ処理します。

SELECT TOP 2 GageHeight TheGageHeight
, Flow TheFlow
FROM riverflow_parquet_path
WHERE $path.$siteno = '09394500';

サンプル結果:

TheGageHeight TheFlow
------------- ---------
         5.04      3.53
         5.13      4.84

<パス フィルタリングを使用した外部テーブルの問合わせ>を参照してください。

パスとペイロード フィルタリングの結合

パスとペイロード フィルタリングを組み合わせます。

SELECT COUNT(*)
FROM riverflow_parquet_path
WHERE GageHeight > 5
AND $path.$siteno = 09394500
AND $path.$year = '2018'
AND $path.$month = '07';

サンプル結果:

   Count(*)
-----------
        834

<パスと列フィルタリングの結合>を参照してください。

ビュー内の列としてのパス変数の使用

外部テーブルのビューを作成します。

CREATE VIEW riverflowview_parquet 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 GageHeight1,
Precipitation,
GageHeight2
FROM riverflow_parquet_path);

<ビュー内の列としてのパス変数の使用>を参照してください。

パス フィルタリングでの外部テーブル ビューの使用

REPLACE VIEW riverflowview_parquet 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 GageHeight1,
Precipitation,
GageHeight2
FROM riverflow_parquet_path
WHERE TheSite = site_no);

ビューを問合わせます。

SELECT TOP 2 * FROM riverflowview_parquet;

サンプル結果:

      TheSite  09396100
      TheYear  2018
     TheMonth  07
       TheDay  16
         Flow      34.10
  GageHeight1      1.42
Precipitation      0.00
  GageHeight2      1.42

      TheSite  09396100
      TheYear  2018
     TheMonth  07
       TheDay  16
         Flow      44.70
  GageHeight1      1.50
Precipitation      0.00
  GageHeight2      1.50

ビューをフィルタ処理します。

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

サンプル結果:

TheSite        Count(*)
----------  -----------
09396100           2906

クエリーでEXPLAINを実行して、フィルタ処理がどのように行なわれているかを確認します。

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

サンプル結果:

Explanation
------------------------------------------------------------------------
[...]
3) We do a single-AMP RETRIEVE step from
NOS_USR.riverflow_parquet_path in view riverflowview_parquet
metadata by way of an all-rows scan with a condition of (
"(TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_parquet_path in view riverflowview_parquet.Location, '/S3/td-usgs.s3.amazonaws.com/', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= '09396100 '")
into Spool 3 (all_amps), which is built locally on that AMP. Then
we do a SORT to order Spool 3 by the sort key as the field_id
list( 3, 2). The size of Spool 3 is estimated with no confidence
to be 17 rows (11,985 bytes). The estimated time for this step is
0.60 seconds.
4) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 3 (Last Use)
by way of an all-rows scan into Spool 2 (all_amps), which is
binpacked and redistributed by size to all AMPs in TD_Map1. The
size of Spool 2 is estimated with no confidence to be 17 rows (
12,121 bytes). The estimated time for this step is 0.16 seconds.
5) We do an all-AMPs SUM step in TD_MAP1 to aggregate from 3 column
partitions of NOS_USR.riverflow_parquet_path in view
riverflowview_parquet by way of external metadata in Spool 2 (Last
Use) with a condition of ("(TD_SYSFNLIB.NosExtractVarFromPath (
NOS_USR.riverflow_parquet_path in view riverflowview_parquet.Location, '/S3/td-usgs.s3.amazonaws.com/', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC)(FLOAT, FORMAT '-9.99999999999999E-999'))= (NOS_USR.riverflow_parquet_path in view riverflowview_parquet.site_no)"), and the grouping identifier
in field 1. Aggregate Intermediate Results are computed globally,
then placed in Spool 5 in TD_Map1. The size of Spool 5 is
estimated with no confidence to be 1,215 rows (1,703,430 bytes).
The estimated time for this step is 0.77 seconds.
[...]

<パス フィルタリングでのビューの使用>を参照してください。