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

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

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