CSV File Keys | DATASET_KEYS Function | Teradata Vantage - Example: Displaying Keys in a Foreign Table Containing CSV Data - Advanced SQL Engine - Teradata Database

Table Definition

This statement defines a foreign table that includes a Payload column with the DATASET data type and a storage format of CSV. The LOCATION parameter specifies a CSV file stored on Amazon S3.

Create the DefAuth authorization object, if not already done. See Example: Creating an Authorization Object.

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

Query Keys in a File

This SELECT statement uses the DATASET_KEYS function to display the keys in the records in the .CSV data in the foreign table.

SELECT * FROM DATASET_KEYS
(
ON (SELECT payload FROM riverflow_csv)
) AS csvKeys GROUP BY 1 ORDER BY 1;

The results display as follows.

DatasetKeys
----------------------
BatteryVoltage
Conductance
Flow
GageHeight
GageHeight2
Precipitation
Temp
Velocity
WaterVelocity
datetime
site_no

Sample Query and Results

This query displays specific columns from the foreign table.

SELECT payload..Flow, payload..Precipitation, payload..Conductance,
payload..datetime, payload..site_no, payload..Temp, payload..GageHeight
FROM riverflow_csv
AS derived_table WHERE payload..Temp>11.0 AND payload..GageHeight>10.00 ORDER
BY payload..datetime;

The results display as follows.

        PAYLOAD..Flow 16400
PAYLOAD..Precipitation 0.00
  PAYLOAD..Conductance 668
     PAYLOAD..datetime 2018-06-27 14:15
      PAYLOAD..site_no 09380000
         PAYLOAD..Temp 11.8
   PAYLOAD..GageHeight 10.01
         PAYLOAD..Flow 16400
PAYLOAD..Precipitation 0.00
  PAYLOAD..Conductance 670
     PAYLOAD..datetime 2018-06-27 14:30
      PAYLOAD..site_no 09380000
         PAYLOAD..Temp 11.8
   PAYLOAD..GageHeight 10.01
         PAYLOAD..Flow 16600
PAYLOAD..Precipitation 0.00
  PAYLOAD..Conductance 665
     PAYLOAD..datetime 2018-06-27 14:45
      PAYLOAD..site_no 09380000
         PAYLOAD..Temp 11.9
   PAYLOAD..GageHeight 10.04