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