テスト テーブルの設定 - Advanced SQL Engine - Teradata Database

CREATE AUTHORIZATION DefAuth
AS DEFINER TRUSTED
USER ''
PASSWORD '';

CREATE FUNCTION MAPPING READ_NOS_Optional
FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth
USING
BUFFERSIZE,
SAMPLE_PERC,
ROWFORMAT,
RETURNTYPE,
HEADER,
MANIFEST,
LOCATION,
STOREDAS,
FULLSCAN,
ANY IN TABLE;

CREATE FOREIGN TABLE RiverFlow,
EXTERNAL SECURITY DEFINER TRUSTED DefAuth 
(
    Location varchar(2048) CHARACTER SET UNICODE CASESPECIFIC,
    Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE
)
USING 
(
    LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
);

LOCATIONには、USGSの河川流量データを格納している外部オブジェクト ストアのURIが含まれています。例に示す場所は、TeradataのパブリックS3バケットです。

SELECT COUNT(*) FROM RiverFlow;

結果:

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

SELECT DISTINCT * FROM JSON_KEYS (ON (SELECT Payload FROM RiverFlow)) AS j;

結果:

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

CREATE TABLE RiverFlowPerm AS
(
SELECT
 CAST(Payload.site_no AS VARCHAR(8) CHARACTER SET UNICODE) SiteNo,
 CAST(Payload.datetime AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI') DateTime,
 CAST(Payload.Flow AS FLOAT) Flow,
 CAST(Payload.GageHeight AS FLOAT) GageHeight1,
 CAST(Payload.Precipitation AS FLOAT) Precipitation,
 CAST(Payload.Temp AS FLOAT) Temperature,
 CAST(Payload.Velocity AS FLOAT) Velocity,
 CAST(Payload.BatteryVoltage AS FLOAT) BatteryVoltage,
 CAST(Payload.GageHeight2 AS FLOAT) GageHeight2,
 CAST(Payload.WaterVelocity AS FLOAT) WaterVelocity,
 CAST(Payload.Conductance AS FLOAT) Conductance
FROM READ_NOS_Optional (
    ON (SELECT CAST(NULL AS JSON CHARACTER SET UNICODE))
    USING
  LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
    ) AS D
) WITH DATA;

LOCATIONには、USGSの河川流量データが格納された外部オブジェクト ストアのURIが含まれています。このクエリーは、外部オブジェクト ストアからデータを読み込むためにREAD_NOSを使用して永久テーブルを作成します。

SELECT COUNT(*) FROM RiverFlowPerm;

結果: このカウントは、以前のカウントと一致します。

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

SELECT SiteNo, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;

結果:

SiteNo       Count(*)
--------  -----------
09380000         2945
09394500         2947
09396100         3086
09400568         2943
09400815         3679
[...]

SELECT Conductance, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;

結果:

           Conductance     Count(*)
----------------------  -----------
                     ?        33356
 6.50000000000000E 002            1
 6.51000000000000E 002            1
 6.52000000000000E 002            5
 6.53000000000000E 002            6
 6.54000000000000E 002           18
[...]

SELECT TOP 5 DateTime, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;

結果:

           DateTime     Count(*)
-------------------  -----------
2018-06-27 00:00:00           12
2018-06-27 00:07:00            1
2018-06-27 00:15:00           12
2018-06-27 00:30:00           12
2018-06-27 00:45:00           12