Example: NOS Detects Columns - Teradata VantageCloud Lake

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