This example shows how to use the DATASET_KEYS function to display the keys in CSV data in a foreign table.
Table Definition
This statement defines a foreign table that includes a Payload column with the DATASET data type and a storage format of CSV. The LOCATION parameter specifies a CSV file stored on Amazon S3.
Create the DefAuth authorization object, if not already done. See Example: Creating an Authorization Object.
CREATE FOREIGN TABLE riverflow_csv, EXTERNAL SECURITY DEFINER TRUSTED DefAuth ( LOCATION VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC, PAYLOAD DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV ) USING ( LOCATION ('/s3/td-usgs.s3.amazonaws.com/CSVDATA/') );
Query Keys in a File
This SELECT statement uses the DATASET_KEYS function to display the keys in the records in the .CSV data in the foreign table.
SELECT * FROM DATASET_KEYS ( ON (SELECT payload FROM riverflow_csv) ) AS csvKeys GROUP BY 1 ORDER BY 1;
The results display as follows.
DatasetKeys ---------------------- BatteryVoltage Conductance Flow GageHeight GageHeight2 Precipitation Temp Velocity WaterVelocity datetime site_no
Sample Query and Results
This query displays specific columns from the foreign table.
SELECT payload..Flow, payload..Precipitation, payload..Conductance, payload..datetime, payload..site_no, payload..Temp, payload..GageHeight FROM riverflow_csv AS derived_table WHERE payload..Temp>11.0 AND payload..GageHeight>10.00 ORDER BY payload..datetime;
The results display as follows.
PAYLOAD..Flow 16400 PAYLOAD..Precipitation 0.00 PAYLOAD..Conductance 668 PAYLOAD..datetime 2018-06-27 14:15 PAYLOAD..site_no 09380000 PAYLOAD..Temp 11.8 PAYLOAD..GageHeight 10.01 PAYLOAD..Flow 16400 PAYLOAD..Precipitation 0.00 PAYLOAD..Conductance 670 PAYLOAD..datetime 2018-06-27 14:30 PAYLOAD..site_no 09380000 PAYLOAD..Temp 11.8 PAYLOAD..GageHeight 10.01 PAYLOAD..Flow 16600 PAYLOAD..Precipitation 0.00 PAYLOAD..Conductance 665 PAYLOAD..datetime 2018-06-27 14:45 PAYLOAD..site_no 09380000 PAYLOAD..Temp 11.9 PAYLOAD..GageHeight 10.04
For readability, the output is displayed vertically.