この例では、CREATE FOREIGN TABLE文はlocation_column、payload_column、またはdata_definition_columnを指定していません。
CREATE FOREIGN TABLE文
CREATE FOREIGN TABLE riverflow_csv_2,
EXTERNAL SECURITY MyAuthObj
USING (
LOCATION ('/S3/s3.amazonaws.com/YOUR-BUCKET/CSVDATA/')
);
SHOW TABLE文
SHOW TABLE riverflow_csv_2;
結果:
CREATE MULTISET FOREIGN TABLE NU.riverflow_csv_2 ,FALLBACK ,
EXTERNAL SECURITY MyAuthObj ,
MAP = TD_MAP1
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
GageHeight2 DECIMAL(3,2),
Flow DECIMAL(3,2),
site_no INTEGER,
datetime TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI',
Precipitation DECIMAL(3,2),
GageHeight DECIMAL(3,2))
USING
(
LOCATION ('/S3/YOUR-BUCKET.s3.amazonaws.com/CSVDATA/')
MANIFEST ('FALSE')
PATHPATTERN ('$CSVDATA/$site_no/$2018/$byteint4/$filename')
ROWFORMAT ('{"field_delimiter":",","record_delimiter":"\n","character_set":"LATIN"}')
STOREDAS ('TEXTFILE')
HEADER ('TRUE')
STRIP_EXTERIOR_SPACES ('FALSE')
STRIP_ENCLOSING_CHAR ('NONE')
)
NO PRIMARY INDEX ;
SELECT文
SELECT GageHeight2, flow, site_no, datetime, Precipitation, GageHeight FROM riverflow_csv_2;
結果:
GageHeight2 Flow site_no datetime Precipitation GageHeight
----------- ----- ----------- ---------------- ------------- ----------
? .00 9400815 2018-06-29 00:00 .00 -.01
? ? 9429070 2018-07-02 14:35 1.16 ?
? .00 9400815 2018-07-01 00:00 .00 -.01
? .00 9400815 2018-07-02 00:00 .00 -.01
? .00 9400815 2018-07-12 00:00 .00 -.01
? .00 9400815 2018-07-10 00:00 .00 -.01
? .00 9400815 2018-07-09 00:00 .00 -.01
? .00 9400815 2018-07-08 00:00 .00 -.01
? .00 9400815 2018-06-29 00:15 .00 -.01