この例では、READ_NOSテーブル演算子をNOSREAD_RAW戻りタイプとともに使用し、生データをJSONファイルに表示する方法を示します。
このクエリーは、ANY IN TABLE句を含む関数マッピングREAD_NOS_json_intable_fmと、NOSREAD_RAWの戻りタイプを使用して、テーブルriverflow_rawの生JSONデータを表示します。
関数マッピングREAD_NOS_json_fmをまだ作成していなければ作成します。例: JSONデータ ファイルにANY IN TABLEを使用した関数マッピング定義を参照してください。
READ_NOSを実行します。
SELECT payload
FROM READ_NOS_json_intable_fm (
ON (SELECT
cast ('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json'
as varchar(500)) Location,
cast (NULL as varchar(500)) ObjectVersionId,
cast (NULL as timestamp) ObjectTimeStamp,
cast (0 as BIGINT) ExtFSRowOffSet,
cast (1 as BIGINT) ObjectLength,
cast (NULL as CLOB CHARACTER SET LATIN) payload )
USING
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09424900/2018/06/28.json')
RETURNTYPE('NOSREAD_RAW') ) as d;
生データの出力サンプルを以下に示します。
Payload
-----------------------------------------------------------------------------------------------------------------------------
{ "site_no":"09424900", "datetime":"2018-06-28 00:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"79.5"}{ "site_no":"09424900", "datetime":"2018-06-28 00:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.9"} { "site_no":"09424900", "datetime":"2018-06-28 00:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"78.2"} { "site_no":"09424900", "datetime":"2018-06-28 00:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"77.8"} { "site_no":"09424900", "datetime":"2018-06-28 01:00", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.5"} { "site_no":"09424900", "datetime":"2018-06-28 01:15", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"76.1"} { "site_no":"09424900", "datetime":"2018-06-28 01:30", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"75.7"} { "site_no":"09424900", "datetime":"2018-06-28 01:45", "Flow":"0.00", "GageHeight":"-0.13", "Precipitation":"0.00", "Temp":"74.5"}