17.05 - 例: CSVファイルへのアクセスのためのスキーマ定義を含む外部テーブルの作成 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-175K-JPN
Language
日本語 (日本)

この例では、リモート リポジトリへのアクセス許可とCSVファイルにアクセスするためのスキーマ定義を含む外部テーブルを作成する方法を示します。CREATE storage_format SCHEMAも参照してください。

CREATE AUTHORIZATIONおよびCREATE TABLEを実行するには、次の権限が必要です。
  • CREATE AUTHORIZATION
  • CREATE TABLE
次の文は、外部テーブル定義に含める許可、DefAuth_S3を定義します。
CREATE AUTHORIZATION DefAuth_S3
 AS DEFINER TRUSTED
     USER 'YOUR-ACCESS-KEY-ID'
     PASSWORD 'YOUR-SECRET-ACCESS-KEY';
以下に、外部テーブルでアクセスするrivers.tsvファイルのサンプルを示します。
09379180    LAGUNA CREEK AT DENNEHOTSO, AZ
09379910    COLORADO RIVER BELOW GLEN CANYON DAM, AZ
09380000    COLORADO RIVER AT LEES FERRY, AZ
09382000    PARIA RIVER AT LEES FERRY, AZ
09383300    FILLER DITCH AT GREER, AZ
09383400    LITTLE COLORADO RIVER AT GREER, AZ
09383100    COLORADO R ABV LITTLE COLORADO R NR DESERT VIEW
09379200    CHINLE CREEK NEAR MEXICAN WATER, AZ
09379050    LUKACHUKAI CREEK NEAR LUKACHUKAI, AZ
09379025    CHINLE CREEK AT CHINLE, AZ
次の文では、外部テーブル定義に含めるCSVスキーマを作成します。データにはタブ区切りのデータが含まれるので、field_delimiterは\tに設定されます。
スキーマを作成するには、データベースSYSUDTLIBに対するCREATE DATASET SCHEMA権限が付与されている必要があります。<Teradata Vantage™ - SQLデータ制御言語、B035-1149>を参照してください。
CREATE CSV SCHEMA Rivers_Schema AS
'{"field_delimiter":"\t",
"field_names":["site_no","location"]}';
次の文では、DefAuth_S3許可を使用して外部テーブルを作成します。ペイロード列は、スキーマRivers_Schemaを使用して、CSVのストレージ形式でDATASETデータ型を指定します。USING句のLOCATIONオプションは、rivers.tsvファイルを含むAmazon S3リポジトリを指定します。
CREATE FOREIGN TABLE Rivers_T
, EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3
(
  LOCATION VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
  PAYLOAD DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV WITH
  SCHEMA Rivers_Schema
)
USING (
LOCATION ('/s3/td-usgs.s3.amazonaws.com/RIVERS/rivers.tsv')
);
次の文では、見出しの下でペイロード データをキャストするドット表記法を使用して、Rivers_Tテーブルのビューを作成します。
CREATE VIEW Rivers_V AS (
  SELECT CAST(payload..site_no AS VARCHAR(10)) as Site_no
, CAST(payload..location AS VARCHAR(50)) as Location
FROM Rivers_T);
次のクエリーでは、Rivers_Vビューから5つのサイト番号と場所を選択します。
SELECT TOP 5 Site_no, Location FROM Rivers_V;
このビューによって、大文字と小文字を区別せずに列名を参照できます。これはCSVデータに直接アクセスするには大文字と小文字を区別する必要があるのとは対照的です。
サンプルの結果を以下に示します。
    Site_no     Location
----------  --------------------------------------------------
09379180    LAGUNA CREEK AT DENNEHOTSO, AZ
09379910    COLORADO RIVER BELOW GLEN CANYON DAM, AZ
09379200    CHINLE CREEK NEAR MEXICAN WATER, AZ
09379050    LUKACHUKAI CREEK NEAR LUKACHUKAI, AZ
09379025    CHINLE CREEK AT CHINLE, AZ