次の例は、TeradataのパブリックAmazon S3バケットにあるCSV形式のデータにアクセスする方法を示しています。
次の表は、CSVデータを格納するすべてのTeradata提供のパブリック外部オブジェクト ストアに対するLOCATION値を示しています。例を実行するための設定で関数マッピングを設定した後で、これらの場所のいずれかを使用して例を実行できます。
プラットフォーム | LOCATION |
---|---|
Amazon S3 | /s3/td-usgs-public.s3.amazonaws.com/CSVDATA/ |
Azure BLOBストレージ | /az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/CSVDATA/ |
Google Cloud Storage | /gs/storage.googleapis.com/td-usgs/CSVDATA/ |
独自の外部オブジェクト ストアを使用する場合は、例の変数置換を参照してLOCATION、ACCESS_ID/USER、およびACCESS_KEY/PASSWORDの例を確認します。
CSVの例を実行するための設定
CREATE FOREIGN TABLE riverflow_csv , EXTERNAL SECURITY DEFINER TRUSTED DefAuth ( Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC, Payload DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV ) USING (LOCATION('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/'));
<CSVの例を実行するための設定>を参照してください。
READ_NOSを使用した所定の位置にある外部データのサンプリング
SELECT TOP 2 payload..* FROM READ_NOS_FM ( ON ( SELECT CAST( NULL AS DATASET STORAGE FORMAT CSV ) ) USING LOCATION('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/') RETURNTYPE('NOSREAD_RECORD') ) AS D;
サンプル結果:
Payload..* ------------------------------------------------------------------ ["11.6","10000","09380000","2018-07-01 00:00","662","0.00","8.65"] ["10.6","17600","09380000","2018-07-03 00:00","674","0.00","10.23"]
<READ_NOSを使用した所定の位置にある外部データのサンプリング>を参照してください。
外部テーブルを使用した所定の位置にある外部データのサンプリング
SELECT TOP 2 payload FROM riverflow_csv;
サンプル結果:
Payload ----------------------------------------------------------------------------------- BatteryVoltage,Flow,site_no,datetime,Precipitation,GageHeight ,0.00,09400815,2018-07-12 00:15,0.00,-0.01 BatteryVoltage,Flow,site_no,datetime,Precipitation,GageHeight ,0.00,09400815,2018-07-12 00:45,0.00,-0.01
<外部テーブルを使用した所定の位置にある外部データのサンプリング>を参照してください。
DATASET_KEYSを使用したヘッダー名の表示
SELECT DISTINCT * FROM DATASET_KEYS (ON (SELECT payload FROM riverflow_csv)) AS csvKeys;
サンプル結果:
DatasetKeys ----------------- Precipitation BatteryVoltage Temp Velocity Conductance WaterVelocity site_no GageHeight Flow GageHeight2 datetime
<DATASET_KEYSを使用したヘッダー名の表示>を参照してください。
最大値と最小値の検索
SELECT MAX(CAST(payload..Flow AS FLOAT) ( FORMAT '-ZZZZ9.99')) , MIN(CAST(payload..Flow AS FLOAT) ( FORMAT '-(5)ZZZ9.99')) FROM riverflow_csv;
サンプル結果:
Maximum(Payload..Flow) Minimum(Payload..Flow) ---------------------- ---------------------- 19000.00 -9.72
<最大値と最小値の検索>を参照してください。
特定の行セットのクエリー
SELECT MAX(CAST(payload..Flow AS FLOAT) ( FORMAT '-ZZZZ9.99')) FROM riverflow_csv WHERE payload..site_no = '09394500';
サンプル結果:
Maximum(Payload..Flow) ---------------------- 1230.00
<特定の行セットのクエリー>を参照してください。
READ_NOSを使用したデータのプレビュー
SELECT TOP 2 payload FROM READ_NOS_FM ( ON ( SELECT CAST( NULL AS DATASET STORAGE FORMAT CSV ) ) USING LOCATION('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/') RETURNTYPE('NOSREAD_RECORD') ) AS D;
サンプル結果:
Payload --------------------------------------------------------------------------------- Temp,Flow,site_no,datetime,Conductance,Precipitation,GageHeight 11.4,11400,09380000,2018-07-02 00:00,654,0.00,8.97 Temp,Flow,site_no,datetime,Conductance,Precipitation,GageHeight 10.6,17600,09380000,2018-07-03 00:00,674,0.00,10.23
<READ_NOSを使用したデータのプレビュー>を参照してください。
キー構造の表示
SELECT TOP 2 LOCATION FROM READ_NOS_FM( USING RETURNTYPE('NOSREAD_KEYS') LOCATION('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/') ) AS D;
サンプル結果:
Location ------------------------------------------------------------ /S3/td-usgs.s3.amazonaws.com/CSVDATA/09380000/2018/07/02.csv /S3/td-usgs.s3.amazonaws.com/CSVDATA/09380000/2018/07/03.csv
<キー構造の表示>を参照してください。
CREATE TABLE AS...WITH DATAを使用したデータベースへの外部データのロード
CREATE MULTISET TABLE riverflowprecip_csv AS ( SELECT CAST(payload..site_no AS CHAR(8)) SiteNo, CAST(payload..Flow AS FLOAT) Flow, CAST(payload..GageHeight AS FLOAT) GageHeight FROM riverflow_csv WHERE payload..Precipitation > 0 ) WITH DATA NO PRIMARY INDEX;
テーブル内の行数を表示します。
SELECT COUNT(*) FROM riverflowprecip_csv;
サンプル結果:
Count(*) ----------- 396
<CREATE TABLE AS...WITH DATAを使用したデータベースへの外部データのロード>を参照してください。
READ_NOSおよびCREATE TABLE ASを使用したデータベースへの外部データのロード
CREATE MULTISET TABLE riverflowperm_csv AS ( SELECT CAST(payload..site_no AS CHAR(8)) Site_no, CAST(payload..Flow AS FLOAT) Flow, CAST(payload..GageHeight AS FLOAT) GageHeight1, CAST(payload..Precipitation AS FLOAT) Precipitation, CAST(payload..Temp AS FLOAT) Temperature, CAST(payload..Velocity AS FLOAT) Velocity FROM READ_NOS_FM ( ON (SELECT CAST(NULL AS DATASET STORAGE FORMAT CSV)) USING LOCATION('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/') ) AS D ) WITH DATA;
テーブル内の行数を表示します。
SELECT COUNT (*) FROM riverflowperm_csv;
サンプル結果:
Count(*) ----------- 36301
<READ_NOSおよびCREATE TABLE ASを使用したデータベースへの外部データのロード>を参照してください。
INSERT SELECTを使用したデータベースへの外部データのロード
CREATE TABLE RiverFlowPermInsert_csv (DateTS CHAR(20) ,SiteNo CHAR(8) ,Flow FLOAT FORMAT '-ZZZZ9.99' ,Velocity FLOAT FORMAT '-ZZZ9.99' ,GageHeight FLOAT FORMAT '-ZZZ9.99' ,Temp FLOAT FORMAT '-ZZZ9.99') PRIMARY INDEX (SiteNo);
外部データをデータベース テーブルに挿入します。
INSERT INTO RiverFlowPermInsert_csv SELECT payload..datetime ,payload..site_no ,Cast (payload..Flow AS FLOAT) ,Cast (payload..Velocity AS FLOAT) ,Cast (payload..GageHeight AS FLOAT) ,Cast (payload..Temp AS FLOAT) WHERE payload..site_no = '09429070' FROM riverflow_csv;
データベース テーブルのデータを問合わせます。
SELECT TOP 2 * FROM RiverFlowPermInsert_csv;
サンプル結果:
DateTS SiteNo Flow Velocity GageHeight Temp -------------------- -------- -------- -------- ---------- ------- 2018-07-02 01:00 09429070 149.00 0.78 5.75 78.80 2018-07-02 00:30 09429070 145.00 0.77 5.68 78.90
<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('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );
ディメンション テーブルにデータを入力します。
INSERT INTO rivernames SELECT payload..site_no, payload..name FROM nos_rivernames;
ディメンション テーブルと外部データ(外部テーブル)を結合します。
SELECT DISTINCT name(CHAR(60)) FROM riverflow_csv rf, rivernames rn WHERE rf.payload..site_no = rn.site_no AND rf.payload..Precipitation > 0.1 ORDER BY 1;
サンプル結果:
name ---------------------------------- CIBECUE CREEK NEAR OVERGAARD COLORADO RIVER AT LEES FERRY GILA RIVER AT KELVIN LITTLE COLORADO RIVER AT WOODRUFF NEW RIVER NEAR ROCK SPRINGS NEWMAN CANYON ABOVE UPPER LAKE MARY POLACCA WASH NEAR SECOND MESA PUERCO RIVER NEAR CHAMBERS SALT RIVER NEAR CHRYSOTILE SANTA MARIA RIVER NEAR BAGDAD
<外部データとデータベース テーブルの結合>を参照してください。
CREATE FOREIGN TABLE定義でのパス フィルタを使用したフィルタリング
CREATE FOREIGN TABLE riverflow_csv_pathfilter
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth (
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
Payload DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV
)
USING
(
LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/site_no')
PATHPATTERN('$data/$siteno/$year/$month/$day') );
ここでsite_noはサイト番号の値です(0938など)。
テーブル定義を表示します。
SHOW TABLE riverflow_csv_pathfilter;
<CREATE FOREIGN TABLE定義でのパス フィルタを使用したフィルタリング>を参照してください。
外部テーブルのペイロード列でのフィルタリング
カスタムPATHPATTERNを使用して外部テーブルを作成するか、データベース管理者に作成を依頼します。
CREATE FOREIGN TABLE riverflow_csv_path , EXTERNAL SECURITY DEFINER TRUSTED DefAuth ( Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC, Payload DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV ) USING ( LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/') PATHPATTERN('$data/$siteno/$year/$month/$day') );
ペイロード フィルタのみを使用してクエリーを実行します。
SELECT TOP 2 CAST(payload..GageHeight AS FLOAT)( FORMAT '-ZZZ9.99') TheGageHeight, CAST (payload..Flow AS FLOAT)(FORMAT '-ZZZ9999.99') TheFlow FROM riverflow_csv_path WHERE payload..site_no = '09380000';
サンプル結果:
TheGageHeight TheFlow ------------- ----------- 10.21 17500.00 10.20 17400.00
フィルタ内の同等のパス値を使用して、同様のクエリーを実行します。
SELECT TOP 2 CAST(payload..GageHeight AS FLOAT)( FORMAT '-ZZZ9.99') TheGageHeight, CAST (payload..Flow AS FLOAT)(FORMAT '-ZZZ9999.99') TheFlow FROM riverflow_csv_path WHERE $path.$siteno = '09380000';
サンプル結果:
TheGageHeight TheFlow ------------- ----------- 10.16 17200.00 10.22 17500.00
<外部テーブルのペイロード列でのフィルタリング>を参照してください。
パス フィルタリングを使用した外部テーブルの問合わせ
パス フィルタリングを使用して検索結果を絞り込みます。
SELECT TOP 2 payload..* FROM riverflow_csv_path WHERE $PATH.$year = '2018' AND $PATH.$month = '07' AND $PATH.$day = '01.csv' AND $PATH.$siteno = '09400815';
サンプル結果:
Payload..* ---------------------------------------------------------- [null,"0.00","09400815","2018-07-01 00:15","0.00","-0.01"] [null,"0.00","09400815","2018-07-01 00:45","0.00","-0.01"]
<パス フィルタリングを使用した外部テーブルの問合わせ>を参照してください。
パスとペイロード フィルタリングの結合
パスとペイロード フィルタリングを組み合わせます。
SELECT COUNT(*) FROM riverflow_csv_path WHERE payload..GageHeight > 9.5 AND $path.$siteno = 09380000 AND $path.$year = '2018' AND $path.$month = '06';
サンプル結果:
Count(*) ----------- 185
<パスとペイロード フィルタリングの結合>を参照してください。
基本ビューの作成
外部テーブルのビューを作成します。
CREATE VIEW riverflowview_csv AS ( SELECT CAST(payload..Flow AS FLOAT) ( FORMAT '-ZZZZ9.99') Flow, CAST(payload..GageHeight AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight1, CAST(payload..Precipitation AS FLOAT) ( FORMAT '-ZZZ9.99') Precipitation, CAST(payload..Temp AS FLOAT) ( FORMAT '-ZZZ9.99') Temperature, CAST(payload..Velocity AS FLOAT) ( FORMAT '-ZZZ9.99') Velocity, CAST(payload..BatteryVoltage AS FLOAT) ( FORMAT '-ZZZ9.99') BatteryVoltage, CAST(payload..GageHeight2 AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight2 FROM riverflow_csv_path );
ビューを問合わせます。
SELECT TOP 2 * FROM riverflowview_csv;
サンプル結果:
Flow GageHeight1 Precipitation Temperature Velocity BatteryVoltage GageHeight2 ------ ----------- ------------- ----------- -------- -------------- ----------- 186.00 2.05 ? ? ? ? 2.05 232.00 2.16 0.00 ? ? ? 2.16
<基本ビューの作成>を参照してください。
ビュー内の列としてのパス変数の使用
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, CAST(payload..Flow AS FLOAT) ( FORMAT '-ZZZZ9.99') Flow, CAST(payload..GageHeight AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight1, CAST(payload..Precipitation AS FLOAT) ( FORMAT '-ZZZ9.99') Precipitation, CAST(payload..Temp AS FLOAT) ( FORMAT '-ZZZ9.99') Temperature, CAST(payload..Velocity AS FLOAT) ( FORMAT '-ZZZ9.99') Velocity, CAST(payload..BatteryVoltage AS FLOAT) ( FORMAT '-ZZZ9.99') BatteryVoltage, CAST(payload..GageHeight2 AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight2 FROM riverflow_csv_path);
<ビュー内の列としてのパス変数の使用>を参照してください。
パス フィルタリングでの外部テーブル ビューの使用
外部テーブルのビューを置き換えます。
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, CAST(payload..Flow AS FLOAT) ( FORMAT '-ZZZZ9.99') Flow, CAST(payload..GageHeight AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight1, CAST(payload..Precipitation AS FLOAT) ( FORMAT '-ZZZ9.99') Precipitation, CAST(payload..Temp AS FLOAT) ( FORMAT '-ZZZ9.99') Temperature, CAST(payload..Velocity AS FLOAT) ( FORMAT '-ZZZ9.99') Velocity, CAST(payload..BatteryVoltage AS FLOAT) ( FORMAT '-ZZZ9.99') BatteryVoltage, CAST(payload..GageHeight2 AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight2 FROM riverflow_csv_path WHERE TheSite = payload..site_no);
ビューを問合わせます。
SELECT TOP 2 * FROM riverflowview_csv;
サンプル結果:
TheSite TheYear TheMonth TheDay Site_no Flow GageHeight1 Precipitation Temperature Velocity BatteryVoltage -------- ------- -------- ------ ----------- ------ ----------- ------------- ----------- -------- -------------- 09396100 2018 07 16 09396100 24.40 1.33 0.00 ? ? ? 09396100 2018 07 16 09396100 113.00 1.83 0.00 ? ? ?
ビューをフィルタ処理します。
SELECT thesite,COUNT(*) FROM riverflowview_csv WHERE thesite='09396100' GROUP BY 1;
サンプル結果:
TheSite Count(*) ---------- ----------- 09396100 3086
クエリーでEXPLAINを実行して、フィルタ処理がどのように行なわれているかを確認します。
EXPLAIN SELECT thesite,COUNT(*) FROM riverflowview_csv WHERE thesite='09396100' GROUP BY 1;
サンプル結果:
Explanation -------------------------------------------------------------------------- […] 3) We do a single-AMP RETRIEVE step from OB.riverflow_csv_path in view riverflowview_csv metadata by way of an all-rows scan with a condition of ("(TD_SYSFNLIB.NosExtractVarFromPath ( OB.riverflow_csv_path in view riverflowview_csv.Location, '/s3/s3.amazonaws.com/td-usgs', 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 38 rows (26,790 bytes). The estimated time for this step is 0.55 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 38 rows ( 27,094 bytes). The estimated time for this step is 0.08 seconds. 5) We do an all-AMPs SUM step in TD_MAP1 to aggregate from OB.riverflow_csv_path in view riverflowview_csv by way of external metadata in Spool 2 (Last Use) with a condition of ("(TD_SYSFNLIB.NosExtractVarFromPath (OB.riverflow_csv_path in view riverflowview_csv.Location, '/s3/td-usgs.s3.amazonaws.com/', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= (TRANSLATE((OB.riverflow_csv_path in view riverflowview_csv.Payload .EXTRACTVALUE ( '242E2E736974655F6E6F'XB(VARBYTE(63000)), 'list'(VARCHAR(20), CHARACTER SET LATIN, NOT CASESPECIFIC)))USING LATIN_TO_UNICODE))"), 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 504 rows (706,608 bytes). The estimated time for this step is 0.32 seconds. […]
<パス フィルタリングでのビューの使用>を参照してください。