17.05 - 例: NOSREAD_RECORD戻りタイプを含むREAD_NOSの使用 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

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

この例では、JSON形式の外部データにアクセスします。READ_NOSを使用してCSV形式のデータにアクセスすることもできます。

空の単一列入力テーブルとLOCATIONパラメータ

以下の文では、JSONデータ型を持つ単一列、Payloadを含んだテーブルindataを定義します。テーブルにプライマリ インデックスはありません。

CREATE TABLE indata
(payload VARCHAR(64000)) NO PRIMARY INDEX;

以下の文は、READ_NOS_json_intable_fmというREAD_NOS関数マッピングを使用してクエリーを実行し、テーブルindataのサブクエリーを含みます。

関数マッピングREAD_NOS_json_fmをまだ作成していなければ作成します。例: JSONデータ ファイルにANY IN TABLEを使用した関数マッピング定義を参照してください。

READ_NOSを実行します。

SELECT TOP 10 * FROM READ_NOS_json_intable_fm (
ON (SELECT * FROM indata)
USING
 LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
 RETURNTYPE('NOSREAD_RECORD')
) AS d ;

結果:

 Location                                                       ObjectVersionId ObjectTimeStamp OffsetIntoObject ObjectLength ExtraField Payload                                                                                                                                                 
 -------------------------------------------------------------- --------------- --------------- ---------------- ------------ ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------- 
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         760          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 01:15", "Flow":"14700", "GageHeight":"9.68", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                        1216          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 02:00", "Flow":"13600", "GageHeight":"9.46", "Temp":"10.7", "Conductance":"671", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                        1368          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 02:15", "Flow":"13200", "GageHeight":"9.38", "Temp":"10.6", "Conductance":"672", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                        1064          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 01:45", "Flow":"13900", "GageHeight":"9.53", "Temp":"10.7", "Conductance":"672", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         152          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 00:15", "Flow":"16000", "GageHeight":"9.93", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                           0          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 00:00", "Flow":"16200", "GageHeight":"9.97", "Temp":"11.0", "Conductance":"669", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         456          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 00:45", "Flow":"15400", "GageHeight":"9.82", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         912          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 01:30", "Flow":"14300", "GageHeight":"9.61", "Temp":"10.7", "Conductance":"673", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         608          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 01:00", "Flow":"15100", "GageHeight":"9.77", "Temp":"10.8", "Conductance":"672", "Precipitation":"0.00"}
 /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json NULL            NULL                         304          151 NULL       { "site_no":"09380000", "datetime":"2018-06-27 00:30", "Flow":"15700", "GageHeight":"9.88", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}

複数の単一列入力テーブルとLOCATIONパラメータ

以下の文では、READ_NOS_json_intable_fmというREAD_NOS関数マッピングを含むサブクエリー、およびNOSREAD_KEYS戻りタイプを使用してテーブルを定義します。

CREATE TABLE import_data
AS
(
 SELECT Location ExtFsLocation,
 ObjectVersionId ExtFSKeyVersionID,
 ObjectTimeStamp ExtFSKeyTimeStamp,
 CAST(0 AS BIGINT) ExtFSRowOffSet,
 ObjectLength ExtFSRowLength,
 CAST(NULL AS VARCHAR(1024) CHARACTER SET LATIN) Payload
FROM READ_NOS_json_intable_fm (
USING
 LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
 RETURNTYPE('NOSREAD_KEYS')
) AS d ) WITH DATA NO PRIMARY INDEX;

以下の文は、READ_NOS_json_intable_fmというREAD_NOS関数マッピングを含むimport_dataテーブルのサブクエリー、およびNOSREAD_KEYS戻りタイプを使用してクエリーを実行します。

SELECT TOP 5 Payload
FROM READ_NOS_json_intable_fm (
 ON (SELECT * FROM import_data)
 USING
 LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06')
 RETURNTYPE('NOSREAD_RECORD') ) AS d;

結果:

 Payload                                                                                                                                                 
 ------------------------------------------------------------------------------------------------------------------------------------------------------- 
 { "site_no":"09380000", "datetime":"2018-06-30 00:15", "Flow":"15600", "GageHeight":"9.85", "Temp":"10.4", "Conductance":"685", "Precipitation":"0.00"}
 { "site_no":"09380000", "datetime":"2018-06-27 00:15", "Flow":"16000", "GageHeight":"9.93", "Temp":"10.9", "Conductance":"668", "Precipitation":"0.00"}
 { "site_no":"09380000", "datetime":"2018-06-27 00:00", "Flow":"16200", "GageHeight":"9.97", "Temp":"11.0", "Conductance":"669", "Precipitation":"0.00"}
 { "site_no":"09380000", "datetime":"2018-06-30 00:00", "Flow":"15900", "GageHeight":"9.92", "Temp":"10.4", "Conductance":"685", "Precipitation":"0.00"}
 { "site_no":"09380000", "datetime":"2018-06-28 00:00", "Flow":"15900", "GageHeight":"9.91", "Temp":"11.0", "Conductance":"671", "Precipitation":"0.00"}

JSONファイルでのキーの表示

以下のSELECT文では、READ_NOS_json_fm関数マッピングをJSON_KEYS関数とともに使用して、指定された場所の外部JSONファイルでのキー(フィールド:値ペアのフィールド名)を表示します。

関数マッピングREAD_NOS_json_fmをまだ作成していない場合は作成します。例: JSONデータ ファイルに対するEXTERNAL SECURITY句を使用した関数マッピング定義を参照してください。

READ_NOSを実行します。

SELECT DISTINCT * FROM JSON_KEYS (
ON (
  SEL payload FROM READ_NOS_json_fm (
    USING
    LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA')
    RETURNTYPE('NOSREAD_RECORD')
  ) AS read_nos_query
) AS json_on_keys_query ) AS d ;

結果:

JSONKeys         
 ---------------- 
 "BatteryVoltage"
 "Flow"          
 "Precipitation" 
 "Conductance"   
 "Velocity"      
 "datetime"      
 "WaterVelocity" 
 "GageHeight2"   
 "site_no"       
 "Temp"          
 "GageHeight"

クエリーとサンプルの結果

このクエリーでは、READ_NOS_json_fmを使用して、選択されている2018年6月28日の情報を返します。ここでは温度が70度を超えるリストが時間順に表示されます。

関数マッピングREAD_NOS_json_fmをまだ作成していない場合は作成します。例: JSONデータ ファイルに対するEXTERNAL SECURITY句を使用した関数マッピング定義を参照してください。

READ_NOSを実行します。

SELECT payload.Flow, payload.datetime, payload.site_no, payload.Temp, payload.GageHeight
  FROM READ_NOS_json_fm (
  USING
    LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA')
  ) 
AS derived_table 
  WHERE payload.datetime LIKE '%2018-06-28%' 
  AND payload.Temp >70 
  ORDER BY payload.datetime;

結果は次のようになります。

Payload.Flow Payload.datetime Payload.site_no Payload.Temp Payload.GageHeight 
 ------------ ---------------- --------------- ------------ ------------------ 
 80.7         2018-06-28 00:00 09423560        71.5         7.39              
 222          2018-06-28 00:00 09429070        78.2         4.67              
 0.00         2018-06-28 00:00 09424900        79.5         -0.13             
 84.4         2018-06-28 00:15 09423560        71.4         7.43              
 218          2018-06-28 00:15 09429070        78.1         4.70              
 0.00         2018-06-28 00:15 09424900        78.9         -0.13             
 84.5         2018-06-28 00:30 09423560        71.4         7.45              
 238          2018-06-28 00:30 09429070        78.0         4.74              
 0.00         2018-06-28 00:30 09424900        78.2         -0.13             
 87.2         2018-06-28 00:45 09423560        71.3         7.49              
 224          2018-06-28 00:45 09429070        78.0         4.77              
 0.00         2018-06-28 00:45 09424900        77.8         -0.13             
 87.7         2018-06-28 01:00 09423560        71.2         7.52              
 223          2018-06-28 01:00 09429070        77.9         4.86              
 0.00         2018-06-28 01:00 09424900        76.5         -0.13