CSV File Without Extension | READ_NOS Query | Teradata Vantage - Example: Using READ_NOS to Query an External CSV File Without an Extension - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

This example shows how use the READ_NOS table operator to query external CSV files without an extension (.CSV).

Function Mapping Definition

This function mapping READ_NOS_csv_no_ext_fm for the READ_NOS table operator includes the authorization DefAuth.

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

Perform the READ_NOS:

CREATE FUNCTION MAPPING READ_NOS_csv_no_ext_fm
FOR TD_SYSFNLIB.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 DATASET_KEYS function with the function mapping READ_NOS_csv_no_ext_fm to list the keys in a .CSV file.

SELECT DISTINCT * FROM DATASET_KEYS (
ON (
SELECT payload FROM READ_NOS_csv_no_ext_fm (
ON (SELECT 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 ;

Result:

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