例: JSONデータの読み取り - Advanced SQL Engine - Teradata Database

SHOW AUTHORIZATIONの使用

SHOW AUTHORIZATION NOS_USR.MyAuthObj;

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

指定したLOCATIONのペイロードを返すためのNOSREAD_RECORDの使用

SELECT TOP 2 payload.* FROM (
LOCATION='/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/'
AUTHORIZATION='{"ACCESS_ID":"","ACCESS_KEY":""}'
RETURNTYPE='NOSREAD_RECORD'
) AS d;

<指定したLOCATIONのペイロードを返すためのNOSREAD_RECORDの使用>を参照してください。

JSONの例を実行するための設定

CREATE FOREIGN TABLE riverflow_json
, EXTERNAL SECURITY MyAuthObj
USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/') );

<JSONの例を実行するための設定>を参照してください。

READ_NOSを使用した所定の位置にある外部データのサンプリング

SELECT TOP 2 * FROM READ_NOS_FM (
USING
LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
RETURNTYPE('NOSREAD_RECORD')
) AS D;

<READ_NOSを使用した所定の位置にある外部データのサンプリング>を参照してください。

外部テーブルを使用した所定の位置にある外部データのサンプリング

SELECT TOP 2 payload.* FROM riverflow_json;

<外部テーブルを使用した所定の位置にある外部データのサンプリング>を参照してください。

JSON_KEYSを使用したヘッダー名の表示

SELECT DISTINCT * FROM JSON_KEYS (
ON (SELECT payload FROM riverflow_json)
) AS jsonKeys;

<JSON_KEYSを使用したキー名の表示>を参照してください。

最大値と最小値の検索

SELECT MAX(CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99'))
, MIN(CAST(payload.Flow AS FLOAT) ( FORMAT '-(5)ZZZ9.99'))
FROM riverflow_json;

<最大値と最小値の検索>を参照してください。

特定の行セットのクエリー

SELECT MAX(CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99'))
FROM riverflow_json
WHERE payload.site_no = '09394500';

<特定の行セットのクエリー>を参照してください。

CREATE TABLE AS … WITH DATAを使用したデータベースへの外部データのロード

CREATE MULTISET TABLE riverflowprecip_json(site_no,flow,gageheight,datetime) AS (
SELECT payload.site_no, payload.Flow, payload.GageHeight, payload.datetime
FROM riverflow_json
WHERE payload.Precipitation > 0
) WITH DATA
NO PRIMARY INDEX;

テーブル内の行数を表示します。

SELECT COUNT(*) FROM riverflowprecip_json;

<CREATE TABLE AS ... WITH DATAを使用したデータベースへの外部データのロード>を参照してください。

READ_NOSおよびCREATE TABLE ASを使用したデータベースへの外部データのロード

CREATE MULTISET TABLE riverflowperm_json(site_no,flow,gageheight,datetime) AS (
SELECT payload.site_no, payload.Flow, payload.GageHeight, payload.datetime
FROM ( LOCATION='/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/' ) AS d
) WITH DATA;

テーブル内の行数を表示します。

SELECT COUNT (*) FROM riverflowperm_json;

<READ_NOSおよびCREATE TABLE ASを使用したデータベースへの外部データのロード>を参照してください。

INSERT ... SELECTを使用したデータベースへの外部データのロード

CREATE TABLE RiverFlowPermInsert_json (
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_json
SELECT payload.datetime,payload.site_no,payload.Flow,payload.Velocity,payload.GageHeight,payload.Temp
WHERE payload.site_no='09429070'
FROM riverflow_json;

データベース テーブルのデータを問合わせます。

SELECT TOP 2 * FROM RiverFlowPermInsert_json;

<INSERT ... SELECTを使用したデータベースへの外部データのロード>を参照してください。

外部データとデータベース テーブルの結合

結合先のデータベース ディメンション テーブルを作成します。

CREATE SET TABLE rivernames(
site_no CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
name CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( site_no ) ;

ディメンション テーブルへのデータの入力に使用する外部テーブルを作成するか、データベース管理者に作成を依頼します。

CREATE FOREIGN TABLE nos_rivernames
, EXTERNAL SECURITY  MyAuthObj
USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );

ディメンション テーブルにデータを入力します。

INSERT INTO rivernames
SELECT CAST(site_no AS CHAR(8)), name
FROM nos_rivernames;

ディメンション テーブルと外部データ(外部テーブル)を結合します。

SELECT DISTINCT name(CHAR(100))
FROM riverflow_json rf, rivernames rn
WHERE rf.payload.site_no = cast(rn.site_no as INTEGER)
AND rf.payload.Precipitation > 0.1
ORDER BY 1;

<外部データとデータベース テーブルの結合>を参照してください。

外部テーブルのペイロード列でのフィルタリング

カスタムPATHPATTERNを使用して外部テーブルを作成するか、データベース管理者に作成を依頼します。

CREATE FOREIGN TABLE riverflow_json_path
, EXTERNAL SECURITY MyAuthObj
USING (
LOCATION ('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
PATHPATTERN('$data/$siteno/$year/$month/$day') );

ペイロード フィルタのみを使用してクエリーを実行します。

SELECT TOP 2 payload.GageHeight,payload.Flow
FROM riverflow_json_path
WHERE payload.site_no = '09394500';

フィルタ内の同等のパス値を使用して、同様のクエリーを実行します。

SELECT TOP 2 payload.GageHeight,payload.Flow
FROM riverflow_json_path
WHERE $path.$siteno = '09394500';

<外部テーブルのペイロード列でのフィルタリング>を参照してください。

パス フィルタリングを使用した外部テーブルの問合わせ

パス フィルタリングを使用して検索結果を絞り込みます。

SELECT TOP 2 payload.*
FROM riverflow_json_path
WHERE $PATH.$year = '2018'
AND $PATH.$month = '07'
AND $PATH.$day = '01.json'
AND $PATH.$siteno = '09394500';

<パス フィルタリングを使用した外部テーブルの問合わせ>を参照してください。

パスとペイロード フィルタリングの結合

パスとペイロード フィルタリングを組み合わせます。

SELECT COUNT(*)
FROM riverflow_json_path
WHERE payload.GageHeight > 5
AND $path.$siteno = '09394500'
AND $path.$year = '2018'
AND $path.$month = '07';

<パスとペイロード フィルタリングの結合>を参照してください。

基本ビューの作成

外部テーブルのビューを作成します。

CREATE VIEW riverflowview_json(site_no,datetime,flow,gageheight,precipitation,gageheight2) AS (
SELECT payload.site_no,payload.datetime,payload.Flow,payload.GageHeight,payload.Precipitation,payload.GageHeight2
FROM riverflow_json_path );

ビューを問合わせます。

SELECT TOP 2 * FROM riverflowview_json;

<基本ビューの作成>を参照してください。

ビュー内の列としてのパス変数の使用

REPLACE VIEW riverflowview_json 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,
payload.site_no site_no,
payload.datetime datetime,
payload.Flow Flow,
payload.GageHeight GageHieght,
payload.Precipitation Precipitation,
payload.GageHeight2 GageHeight2
FROM riverflow_json_path);

<ビュー内の列としてのパス変数の使用>を参照してください。

パス フィルタリングでの外部テーブル ビューの使用

外部テーブルのビューを置き換えます。

REPLACE VIEW riverflowview_json AS (
SELECT
CAST($path.$siteno AS CHAR(10)) TheSite,
CAST($path.$year AS CHAR(4)) TheYear,
CAST($path.$month AS CHAR(2)) TheMonth,
CAST($path.$day AS CHAR(2)) TheDay,
payload.site_no site_no,
payload.datetime datetime,
payload.Flow Flow,
payload.GageHeight GageHieght,
payload.Precipitation Precipitation,
payload.GageHeight2 GageHeight2
FROM riverflow_json_path
WHERE TheSite = payload.site_no);

ビューを問合わせます。

SELECT TOP 2 * FROM riverflowview_json;

ビューをフィルタ処理します。

SELECT thesite,COUNT(*)
FROM riverflowview_json WHERE thesite='09396100'
GROUP BY 1;

クエリーでEXPLAINを実行して、フィルタ処理がどのように行なわれているかを確認します。

EXPLAIN
SELECT thesite,COUNT(*)
FROM riverflowview_json WHERE thesite='09396100'
GROUP BY 1;

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