READ_NOS | DATASET_KEYS | Teradata Vantage - 17.05 - Example: Displaying Keys in a CSV File Using READ_NOS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

You can use the READ_NOS table operator with DATASET_KEYS function and a storage format of CSV to display keys in a CSV file.

Function Mapping Definition

This example uses the function mapping READ_NOS_csv_fm which includes the authorization DefAuth.

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

Create the function mapping:

CREATE FUNCTION MAPPING READ_NOS_csv_fm
FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth
USING
LOCATION ('/S3/td-usgs.s3.amazonaws.com/CSVDATA/'),
RETURNTYPE ('NOSREAD_RECORD'),
ANY IN TABLE;

Sample Query and Results

This query uses the READ_NOS table operator and DATASET_KEYS function to display the keys in the CSV file:

SELECT DISTINCT * FROM DATASET_KEYS (
ON (
SELECT payload FROM READ_NOS_csv_fm (
ON (SEL CAST(NULL AS DATASET STORAGE FORMAT CSV) )
USING
LOCATION ('/S3/td-usgs.s3.amazonaws.com/CSVDATA/')
RETURNTYPE('NOSREAD_RECORD')
) AS read_nos_query
) AS csv_on_keys_query ) AS d ;

Below is a sample of the output.

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