この例では、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