17.05 - 例: CSVデータの読み取り - 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のパブリック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の例を確認します。

SHOW AUTHORIZATIONの使用

SHOW AUTHORIZATION NOS_USR.DefAuth;

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

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.
[…]

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