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

Teradata Vantage™ - Native Object Store 入門ガイド
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
2021年1月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/zws1595641486108.ditamap
dita:ditavalPath
ja-JP/zws1595641486108.ditaval
dita:id
B035-1214
Product Category
Software
Teradata Vantage

次の例は、TeradataのパブリックAzure Blobストレージ コンテナからJSON形式のデータにアクセスする方法を示しています。

次の表は、JSONデータを格納するTeradata提供のパブリック外部オブジェクト ストアに対するLOCATION値を示しています。例を実行するための設定で関数マッピングを設定した後で、これらの場所のいずれかを使用して例を実行できます。

プラットフォーム LOCATION
Amazon S3 /s3/td-usgs-public.s3.amazonaws.com/JSONDATA/
Azure BLOBストレージ /az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/
Google Cloud Storage /gs/storage.googleapis.com/td-usgs/JSONDATA/

独自の外部オブジェクト ストアを使用する場合は、例の変数置換を参照してLOCATION、ACCESS_ID/USER、およびACCESS_KEY/PASSWORDの例を確認します。

SHOW AUTHORIZATIONの使用

SHOW AUTHORIZATION NOS_USR.DefAuth;

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

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

CREATE FOREIGN TABLE riverflow_json
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
LOCATION VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC
, Payload JSON INLINE LENGTH 32000 CHARACTER SET UNICODE
)
USING(
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/') );

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

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

SELECT TOP 2 payload.* FROM READ_NOS_FM (
ON ( SELECT CAST( NULL AS JSON ) )
USING
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/')
) AS D;

サンプル結果:

Payload.*
-------------------------------------------------------------------
["09380000","2018-07-04 00:00","17700","10.24","10.6","678","0.00"]
["09380000","2018-06-30 00:00","15900","9.92","10.4","685","0.00"]

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

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

SELECT TOP 2 * FROM riverflow_json;

サンプル結果:

LOCATION
--------------------------------------------------------------
/AZ/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/09396100/2018/07/14.json
/AZ/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/09396100/2018/07/14.json

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

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

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

サンプル結果:

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

<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;

サンプル結果:

Maximum(Payload.Flow) Minimum(Payload.Flow)
--------------------- ---------------------
19000.00                              -9.72

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

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

SELECT MAX(CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99'))
FROM riverflow_json
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 JSON) )
USING
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/')
RETURNTYPE('NOSREAD_RECORD')
) AS D;

サンプル結果:

Payload
--------------------------------------------------------------------
{ "site_no":"09380000", "datetime":"2018-07-04 00:00", "Flow":"17700", "GageHeight":"10.24", "Temp":"10.6", "Conductance":"678", "Precipitation":"0.00"}
{ "site_no":"09380000", "datetime":"2018-07-03 00:00", "Flow":"17600", "GageHeight":"10.23", "Temp":"10.6", "Conductance":"674", "Precipitation":"0.00"}

ペイロード列は、サンプル結果で切り捨てられていることに注意します。

<READ_NOSを使用したデータのプレビュー>を参照してください。

キー構造の表示

SELECT TOP 2 LOCATION FROM READ_NOS_FM (
USING
RETURNTYPE ('NOSREAD_KEYS')
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/')
) AS D;

サンプル結果:

Location
------------------------------------------------------------
/AZ/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/09380000/2018/07/04.json
/AZ/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/09380000/2018/06/30.json

<キー構造の表示>を参照してください。

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

CREATE MULTISET TABLE riverflowprecip_json AS (
SELECT CAST(Payload.site_no AS CHAR(8)) SiteNo,
CAST(Payload.Flow AS FLOAT) ( FORMAT '-ZZZZ9.99') Flow,
CAST(Payload.GageHeight AS FLOAT) ( FORMAT '-ZZZ9.99') GageHeight,
CAST(Payload.datetime AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI') Date_Time
FROM riverflow_json
WHERE Payload.Precipitation > 0
) WITH DATA
NO PRIMARY INDEX;

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

SELECT COUNT(*) FROM riverflowprecip_json;

サンプル結果:

Count(*)
-----------
        396

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

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

CREATE MULTISET TABLE riverflowperm_json AS
(
SELECT
CAST(payload.site_no AS CHAR(8)) Site_no,
CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZ9.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
FROM READ_NOS_FM (
ON (SELECT CAST(NULL AS JSON))
USING
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/')
) AS D
) WITH DATA;

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

SELECT COUNT (*) FROM riverflowperm_json;

サンプル結果:

Count(*)
-----------
        36301

<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
,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_json;

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

SELECT TOP 2 * FROM RiverFlowPermInsert_json;

サンプル結果:

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('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/RIVERS/rivers.csv')
);

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

INSERT INTO rivernames
SELECT payload..site_no, payload..name
FROM nos_rivernames;

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

SELECT DISTINCT name(CHAR(60))
FROM riverflow_json 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_json_pathfilter
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
Payload JSON INLINE LENGTH 32000 CHARACTER SET UNICODE
)
USING (
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/site_no')
PATHPATTERN('$data/$siteno/$year/$month/$day')
);

ここでsite_noはサイト番号の値です(0938など)。

テーブル定義を表示します。

SHOW TABLE riverflow_json_pathfilter;

<CREATE FOREIGN TABLE定義でのパス フィルタを使用したフィルタリング>を参照してください。

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

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

CREATE FOREIGN TABLE riverflow_json_path
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
Payload JSON INLINE LENGTH 32000 CHARACTER SET UNICODE
)
USING (
LOCATION('/az/akiaxox5jikeotfww4ul.blob.core.windows.net/td-usgs/JSONDATA/')
PATHPATTERN('$data/$siteno/$year/$month/$day')
);

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

SELECT TOP 2
CAST(payload.GageHeight AS FLOAT)( FORMAT '-ZZZ9.99') TheGageHeight,
CAST(payload.Flow AS FLOAT)(FORMAT '-ZZZZ9.99') TheFlow
FROM riverflow_json_path
WHERE payload.site_no = '09394500';

サンプル結果:

TheGageHeight    TheFlow
-------------  ---------
         6.39      45.60
         5.04       3.53

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

SELECT TOP 2
CAST(payload.GageHeight AS FLOAT)( FORMAT '-ZZZ9.99') TheGageHeight,
CAST (payload.Flow AS FLOAT)(FORMAT '-ZZZZ9.99') TheFlow
FROM riverflow_json_path
WHERE $path.$siteno = '09394500';

サンプル結果:

TheGageHeight    TheFlow
-------------  ---------
         6.40      62.90
         5.04       3.53

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

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

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

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';

サンプル結果:

Payload.*
---------------------------------------------------------------------------
["09394500","2018-07-01 00:15","0.00","4.75","0.00","4.81"]
["09394500","2018-07-01 00:00","0.01","4.75","0.00","4.82"]

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

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

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

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

サンプル結果:

   Count(*)
-----------
       1602

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

基本ビューの作成

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

CREATE VIEW riverflowview_json AS (
SELECT
CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZ9.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_json_path );

ビューを問合わせます。

SELECT TOP 2 * FROM riverflowview_json;

サンプル結果:

    Flow  GageHeight1  Precipitation  Temperature  Velocity  BatteryVoltage  GageHeight2
--------  -----------  -------------  -----------  --------  --------------  -----------
  186.00         2.05           0.00            ?         ?               ?         2.05
  232.00         2.16           0.00            ?         ?               ?         2.16

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

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

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,
CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZ9.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_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,
CAST(payload.Flow AS FLOAT) ( FORMAT '-ZZZ9.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_json_path
WHERE TheSite = payload.site_no);

ビューを問合わせます。

SELECT TOP 2 * FROM riverflowview_json;

サンプル結果:

TheSite     TheYear  TheMonth  TheDay      Flow GageHeight1 Precipitation Temperature Velocity BatteryVoltage GageHeight2
----------  -------  --------  ------  -------- ----------- ------------- ----------- -------- -------------- -----------
09429070    2018     07        02        156.00        5.52             ?       79.10     0.86              ?           ?
09396100    2018     07        14        232.00        2.16          0.00           ?        ?              ?        2.16

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

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

サンプル結果:

TheSite        Count(*)
----------  -----------
09396100           3086

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

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

サンプル結果:

Explanation
--------------------------------------------------------------
[…]
3) We do a single-AMP RETRIEVE step from NOS_USR.riverflow_json_path
in view riverflowview_json metadata by way of an all-rows scan
with a condition of ("(TD_SYSFNLIB.NosExtractVarFromPath (
NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/S3/td-usgs.s3.amazonaws.com/', 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.74 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.16 seconds.
5) We do an all-AMPs SUM step in TD_MAP1 to aggregate from
NOS_USR.riverflow_json_path in view riverflowview_json
by way of external metadata in Spool 2 (Last Use) with a condition
of ("((TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/S3/td-usgs.s3.amazonaws.com/', 2 ))= (NOS_USR.riverflow_json_path in view riverflowview_json.Payload .JSONEXTRACTVALUE ( '$.site_no'(VARCHAR(32000), CHARACTER SET UNICODE, NOT CASESPECIFIC)) )) AND ((TD_SYSFNLIB.NosExtractVarFromPath ( NOS_USR.riverflow_json_path in view riverflowview_json.Location, '/S3/td-usgs.s3.amazonaws.com/', 2 )(CHAR(10), CHARACTER SET UNICODE, NOT CASESPECIFIC))= (NOS_USR.riverflow_json_path in view riverflowview_json.Payload .JSONEXTRACTVALUE ( '$.site_no'(VARCHAR(32000), CHARACTER SET UNICODE, NOT CASESPECIFIC))))"), 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 1,085 rows (1,521,170 bytes). The estimated time
for this step is 0.52 seconds.
[…]

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