This example shows how use the READ_NOS table operator to query an external CSV file.
Sample Query and Results
This statement uses the function mapping READ_NOS_csv_fm for the READ_NOS table operator to query an external file with the extension, .CSV. This query uses READ_NOS to select specific columns. The LOCATION parameter specifies a CSV file stored on Amazon S3.
First, define the READ_NOS_csv_fm function mapping, if not already done. See Example: Displaying Keys in a CSV File Using READ_NOS.
SELECT payload..Flow, payload..Precipitation, payload..Conductance, payload..datetime, payload..site_no, payload..Temp, payload..GageHeight FROM READ_NOS_csv_fm ( USING LOCATION ('/S3/td-usgs.s3.amazonaws.com/CSVDATA/') RETURNTYPE('NOSREAD_RECORD') ) AS derived_table WHERE payload..Temp>11.0 AND payload..GageHeight>10.00 ORDER BY payload..datetime;
Results:
Payload..Flow Payload..Precipitation Payload..Conductance Payload..datetime Payload..site_no Payload..Temp Payload..GageHeight ------------- ---------------------- -------------------- -------------------- ---------------- ------------- ------------------- 16400 0.00 668 2018-06-27 14:15 09380000 11.8 10.01 16400 0.00 670 2018-06-27 14:30 09380000 11.8 10.01 16600 0.00 665 2018-06-27 14:45 09380000 11.9 10.04 16600 0.00 669 2018-06-27 15:00 09380000 11.9 10.05 16700 0.00 668 2018-06-27 15:15 09380000 12.0 10.07 16700 0.00 667 2018-06-27 15:30 09380000 12.0 10.07 16700 0.00 663 2018-06-27 15:45 09380000 12.0 10.07