Example: NOS Detects Columns - Advanced SQL Engine - Teradata Database

In this example, the CREATE FOREIGN TABLE statement specifies no location_column, payload_column, or data_definition_column.

CREATE FOREIGN TABLE Statement

CREATE FOREIGN TABLE riverflow_csv_2, EXTERNAL SECURITY MyAuthObj USING ( LOCATION ('/S3/s3.amazonaws.com/YOUR-BUCKET/CSVDATA/') );

SHOW TABLE Statement

SHOW TABLE riverflow_csv_2;

Result:

CREATE MULTISET FOREIGN TABLE NU.riverflow_csv_2 ,FALLBACK , EXTERNAL SECURITY MyAuthObj , MAP = TD_MAP1 ( Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC, GageHeight2 DECIMAL(3,2), Flow DECIMAL(3,2), site_no INTEGER, datetime TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI', Precipitation DECIMAL(3,2), GageHeight DECIMAL(3,2)) USING ( LOCATION ('/S3/YOUR-BUCKET.s3.amazonaws.com/CSVDATA/') MANIFEST ('FALSE') PATHPATTERN ('$CSVDATA/$site_no/$2018/$byteint4/$filename') ROWFORMAT ('{"field_delimiter":",","record_delimiter":"\n","character_set":"LATIN"}') STOREDAS ('TEXTFILE') HEADER ('TRUE') STRIP_EXTERIOR_SPACES ('FALSE') STRIP_ENCLOSING_CHAR ('NONE') ) NO PRIMARY INDEX ;

SELECT Statement

SELECT GageHeight2, flow, site_no, datetime, Precipitation, GageHeight FROM riverflow_csv_2;

Result:

GageHeight2 Flow site_no datetime Precipitation GageHeight ----------- ----- ----------- ---------------- ------------- ---------- ? .00 9400815 2018-06-29 00:00 .00 -.01 ? ? 9429070 2018-07-02 14:35 1.16 ? ? .00 9400815 2018-07-01 00:00 .00 -.01 ? .00 9400815 2018-07-02 00:00 .00 -.01 ? .00 9400815 2018-07-12 00:00 .00 -.01 ? .00 9400815 2018-07-10 00:00 .00 -.01 ? .00 9400815 2018-07-09 00:00 .00 -.01 ? .00 9400815 2018-07-08 00:00 .00 -.01 ? .00 9400815 2018-06-29 00:15 .00 -.01