This example shows how to create a foreign table that includes an authorization for accessing the remote repository and a schema definition for accessing a CSV file. See also CREATE storage_format SCHEMA.
To run CREATE AUTHORIZATION and CREATE TABLE, the user needs these privileges:
- CREATE AUTHORIZATION
- CREATE TABLE
This statement defines an authorization, DefAuth_S3, to be included in the foreign table definition:
CREATE AUTHORIZATION DefAuth_S3 AS DEFINER TRUSTED USER 'YOUR-ACCESS-KEY-ID' PASSWORD 'YOUR-SECRET-ACCESS-KEY';
Below is a sample of the rivers.tsv file to access with the foreign table:
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
The following statement creates the CSV schema to include in the foreign table definition. The data contains tab delimited data, so field_delimiter is set to \t.
To create a schema, the user must have been granted the CREATE DATASET SCHEMA permission on database SYSUDTLIB. See
Teradata Vantage™ - SQL Data Control Language, B035-1149.
CREATE CSV SCHEMA Rivers_Schema AS '{"field_delimiter":"\t", "field_names":["site_no","location"]}';
This statement creates a foreign table using the DefAuth_S3 authorization. The Payload column specifies the DATASET data type with a storage format of CSV using the schema, Rivers_Schema. The USING clause LOCATION option specifies the Amazon S3 repository containing the rivers.tsv file.
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') );
This statement creates a view of the Rivers_T table, using dot notation to cast the Payload data under headings:
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);
This query selects 5 site numbers and locations from the Rivers_V view:
SELECT TOP 5 Site_no, Location FROM Rivers_V;
The view allows you to refer to the column names in a case-insensitive way, unlike the case-sensitivity that would be required to access the CSV data directly.
Sample result:
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