Example of Foreign Table with Schema for CSV File | Teradata Vantage - Example: Creating a Foreign Table with a Schema Definition to Access a CSV File - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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