次の例は、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の例を確認します。
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. [...]
<パス フィルタリングでのビューの使用>を参照してください。