This example shows how to query a foreign table containing CSV data and define a view of the foreign table.
Foreign 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/') );
Sample Query of Foreign Table and Results
This query displays specific columns from the foreign table.
SELECT payload..BatteryVoltage , payload..Conductance , payload..Flow , payload..GageHeight , payload..site_no , payload..datetime FROM riverflow_csv WHERE payload..BatteryVoltage > 12;
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 PAYLOAD..Flow 16600 PAYLOAD..Precipitation 0.00 PAYLOAD..Conductance 669 PAYLOAD..datetime 2018-06-27 15:00 PAYLOAD..site_no 09380000 PAYLOAD..Temp 11.9 PAYLOAD..GageHeight 10.05
For readability, the output is displayed vertically.
View Definition
This statement defines the view of the foreign table casting specific column names. Using a view offers the following advantages over using the foreign table directly:
- You can rename the columns.
- You can cast the columns as reasonable data types.
- Referencing view column names is case-insensitive.
CREATE VIEW riverflow_csv_v AS ( SELECT CAST(payload..Conductance AS FLOAT) AS Conductance , CAST(payload..Flow AS FLOAT) AS Flow , CAST(payload..GageHeight AS FLOAT) AS GageHeight , CAST(payload..site_no AS VARCHAR(20)) AS site_no , CAST(payload..datetime AS VARCHAR(10)) AS datetime FROM riverflow_csv WHERE Conductance > 12);
Sample Query and Results
SELECT * FROM riverflow_csv_v;
Result:
Conductance Flow GageHeight site_no datetime ---------------------- ---------------------- ---------------------- -------------------- ---------- 6.71000000000000E 002 1.77000000000000E 004 1.02500000000000E 001 09380000 2018-07-11 6.70000000000000E 002 1.75000000000000E 004 1.02100000000000E 001 09380000 2018-07-18 6.82000000000000E 002 1.72000000000000E 004 1.01600000000000E 001 09380000 2018-07-19 6.70000000000000E 002 1.77000000000000E 004 1.02500000000000E 001 09380000 2018-07-24 6.71000000000000E 002 1.74000000000000E 004 1.01900000000000E 001 09380000 2018-07-11 6.71000000000000E 002 1.71000000000000E 004 1.01500000000000E 001 09380000 2018-07-18 6.77000000000000E 002 1.69000000000000E 004 1.01100000000000E 001 09380000 2018-07-19 6.71000000000000E 002 1.73000000000000E 004 1.01800000000000E 001 09380000 2018-07-24 6.69000000000000E 002 1.71000000000000E 004 1.01500000000000E 001 09380000 2018-07-11 6.69000000000000E 002 1.68000000000000E 004 1.00900000000000E 001 09380000 2018-07-18