Import External Data | Foreign Table | Teradata Vantage - Example: Importing External Data From a Foreign Table into a Permanent Table - Advanced SQL Engine - Teradata Database

This statement defines a foreign table using the authorization object called DefAuth_S3 (created in Example: Specifying a Definer Authorization for a Foreign Table). The foreign table is for CSV-formatted river flow data stored in an Amazon S3 bucket.

CREATE FOREIGN TABLE riverflow_csv,
EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3
(
LOCATION VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
PAYLOAD DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV
)
USING(
LOCATION ('/S3/td-usgs.s3.amazonaws.com/CSVDATA/')
);

This statement defines a view that includes selected columns cast to appropriate data types.

CREATE VIEW riverflow_csv_v AS
(
SELECT
CAST(payload..datetime AS VARCHAR(20)) datetime,
CAST(payload..site_no AS CHAR(8)) Site_no,
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
FROM riverflow_csv
);

This statement defines a table with a primary index on the datetime column.

CREATE MULTISET TABLE riverflow_csv_t (
datetime VARCHAR(20),
site_no CHAR(8),
Flow Float,
GageHeight1 Float,
Precipitation Float,
Temperature Float,
Velocity Float
) PRIMARY INDEX (datetime) ;

This statement inserts rows for the specified columns into the table riverflow_csv_t from the view riverflow_csv_v.

INSERT INTO riverflow_csv_t(
  datetime, Site_no, Flow, GageHeight1, Precipitation, Temperature, Velocity
)
SELECT datetime, Site_no, Flow, GageHeight1, Precipitation, Temperature, Velocity
FROM riverflow_csv_v;

This statement queries the table riverflow_csv_t.

SELECT * FROM riverflow_csv_t;

Sample result.

datetime              site_no                     Flow             GageHeight1           Precipitation             Temperature                Velocity
--------------------  --------  ----------------------  ----------------------  ----------------------  ----------------------  ----------------------
2018-07-05 08:15      09474000   1.39000000000000E 000   3.65000000000000E 000   0.00000000000000E 000                       ?                       ?
2018-06-28 10:15      09497700   5.75000000000000E 000   6.80000000000000E-001   0.00000000000000E 000                       ?                       ?
2018-07-03 16:30      09513780   0.00000000000000E 000  -1.44000000000000E 000   0.00000000000000E 000                       ?                       ?
2018-07-11 21:45      09400815   0.00000000000000E 000  -1.00000000000000E-002   0.00000000000000E 000                       ?                       ?
2018-07-05 08:15      09380000   1.08000000000000E 004   8.84000000000000E 000   0.00000000000000E 000   1.07000000000000E 001                       ?
2018-06-28 10:15      09380000   1.31000000000000E 004   9.36000000000000E 000   0.00000000000000E 000   1.08000000000000E 001                       ?