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