CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public;
DROP EXTENSION IF EXISTS cos_ext;
CREATE [READABLE] EXTERNAL TABLE tablename( columnname datatype [, ...] | LIKE othertable )LOCATION (cos_ext_params)FORMAT 'TEXT'[( [HEADER][DELIMITER [AS] 'delimiter' | 'OFF'][NULL [AS] 'null string'][ESCAPE [AS] 'escape' | 'OFF'][NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'][FILL MISSING FIELDS] )]| 'CSV'[( [HEADER][QUOTE [AS] 'quote'][DELIMITER [AS] 'delimiter'][NULL [AS] 'null string'][FORCE NOT NULL column [, ...]][ESCAPE [AS] 'escape'][NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'][FILL MISSING FIELDS] )][ ENCODING 'encoding' ][ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count[ROWS | PERCENT] ]
CREATE WRITABLE EXTERNAL TABLE table_name( column_name data_type [, ...] | LIKE other_table )LOCATION (cos_ext_params)FORMAT 'TEXT'[( [DELIMITER [AS] 'delimiter'][NULL [AS] 'null string'][ESCAPE [AS] 'escape' | 'OFF'] )]| 'CSV'[([QUOTE [AS] 'quote'][DELIMITER [AS] 'delimiter'][NULL [AS] 'null string'][FORCE QUOTE column [, ...] ][ESCAPE [AS] 'escape'] )][ ENCODING 'encoding' ][ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
cos://cos_endpoint/bucket/prefix secretId=id secretKey=key compressType=[none|gzip] https=[true|false]
参数 | 格式 | 必填 | 说明 |
URL | COS V4: cos://cos.{REGION}.myqcloud.com/{BUCKET}/{PREFIX} COS V5: cos:// {BUCKET}-{APPID}.cos.{REGION}.myqcloud.com/{PREFIX} | 是 | 参见 URL 参数说明 |
secretId | 无 | 是 | 访问 API 使用的密钥 ID,参见 API 密钥管理 |
secretKey | 无 | 是 | 访问 API 使用的密钥 Key,参见 API 密钥管理 |
HTTPS | true Ι false | 否 | 是否使用 HTTPS 访问 COS,默认为 true |
compressType | gzip | 否 | COS 文件是否压缩,默认为空,不压缩 |
CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public;
CREATE TABLE cos_local_tbl (c1 int, c2 text, c3 int)DISTRIBUTED BY (c1);
CREATE READABLE EXTERNAL TABLE cos_tbl (c1 int, c2 text, c3 int)LOCATION('cos://cos.ap-guangzhou.myqcloud.com/simple-bucket/from_cos/ secretKey=xxx secretId=xxx')FORMAT 'csv';
1,simple line 1,12,simple line 1,13,simple line 1,14,simple line 1,15,simple line 1,16,simple line 2,17,simple line 2,18,simple line 2,19,simple line 2,1
INSERT INTO cos_local_tbl SELECT * FROM cos_tbl;
SELECT count(1) FROM cos_local_tbl;SELECT count(1) FROM cos_tbl;
CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public;
CREATE TABLE cos_local_tbl (c1 int, c2 text, c3 int)DISTRIBUTED BY (c1);
CREATE WRITABLE EXTERNAL TABLE cos_tbl_wr (c1 int, c2 text, c3 int)LOCATION('cos://cos.ap-guangzhou.myqcloud.com/simple-bucket/to-cos/ secretKey=xxx secretId=xxx')FORMAT 'csv';
insert into cos_local_tbl values(1, 'simple line 1' , 1),(2, 'simple line 2', 2),(3, 'simple line 3', 3) ,(4, 'simple line 4', 4) ,(5, 'simple line 5', 5) ,(6, 'simple line 6', 6) ,(7, 'simple line 7', 7) ,(8, 'simple line 8', 8) ,(9, 'simple line 9', 9);
INSERT INTO cos_tbl_wr SELECT * FROM cos_local_tbl;
CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public;
1,simple line 1,12,simple line 1,13,simple line 1,14,simple line 1,15,simple line 1,16,simple line 2,17,simple line 2,18,simple line 2,19,simple line 2,1
CREATE READABLE EXTERNAL TABLE cos_tbl_dml (c1 int, c2 text, c3 int)LOCATION('cos://cos.ap-guangzhou.myqcloud.com/simple-bucket/for-dml/ secretKey=xxx secretId=xxx')FORMAT ‘csv’;
SELECT c2, sum(c1) FROM cos_tbl GROUP BY c2;
本页内容是否解决了您的问题?