17.10 - Example: CREATE FOREIGN TABLE Statement Specifies Columns - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

In this example, the CREATE FOREIGN TABLE statement specifies location_column and more than one data_definition_column.

CREATE FOREIGN TABLE Statement

CREATE FOREIGN TABLE riverflow_csv,
  EXTERNAL SECURITY MyAuthObj (
    Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
    GageHeight2 FLOAT,
    flow FLOAT,
    site_no CHAR(8),
    datetime VARCHAR(20),
    Precipitation FLOAT,
    GageHeight FLOAT
    )
    USING (
      LOCATION ('/S3/YOUR-BUCKET.s3.amazonaws.com/CSVDATA/')
    );

SELECT Statement

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

Result:

           GageHeight2                    flow  site_no   datetime                       Precipitation              GageHeight
----------------------  ----------------------  --------  --------------------  ----------------------  ----------------------
2.16000000000000E 000   2.32000000000000E 002  09396100  2018-07-14 00:00       0.00000000000000E 000   2.16000000000000E 000
7.91000000000000E 001   1.56000000000000E 002  09429070  2018-07-02 00:00       8.60000000000000E-001   5.52000000000000E 000
1.50000000000000E 000   4.47000000000000E 001  09396100  2018-07-16 00:00       0.00000000000000E 000   1.50000000000000E 000
                     ?   0.00000000000000E 000  09400815  2018-07-08 00:00       0.00000000000000E 000  -1.00000000000000E-002
2.05000000000000E 000   1.86000000000000E 002  09396100  2018-07-14 00:14                           ?   2.05000000000000E 000
7.90000000000000E 001   1.54000000000000E 002  09429070  2018-07-02 00:15       8.30000000000000E-001   5.60000000000000E 000