背景

REDSHIFT通过unload将数据从REDSHIFT QUERY结果将数据导出到S3，通过EXTERNAL TABLE将数据从S3导入到REDSHIFT。

redshift unload 将QUERY结果导出到s3

UNLOAD ('select-statement')

TO 's3://object-path/name-prefix' authorization [ option [ ... ] ]

where option is

{ MANIFEST [ VERBOSE ]

| HEADER | DELIMITER [ AS ] 'delimiter-char' | FIXEDWIDTH [ AS ] 'fixedwidth-spec' } | ENCRYPTED | BZIP2 | GZIP | ADDQUOTES | NULL [ AS ] 'null-string' | ESCAPE | ALLOWOVERWRITE | PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] [ MAXFILESIZE [AS] max-size [ MB | GB ] ] redshift 使用EXTERNAL TABLE读取s3数据

CREATE EXTERNAL TABLE

external_schema.table_name (column_name data_type [, …] ) [ PARTITIONED BY (col_name data_type [, … ] )] [ { ROW FORMAT DELIMITED row_format | ROW FORMAT SERDE 'serde_name' [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ] STORED AS file_format LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' } [ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ] 阿里云hdb pg 使用EXTERNAL TABLE读取OSS数据、将数据导出到OSS阿里云hdb pg 使用EXTERNAL TABLE读取OSS数据、将数据导出到OSS。

没有UNLOAD命令。

创建命令为：CREATE EXTENSION IF NOT EXISTS oss_ext;

删除命令为：DROP EXTENSION IF EXISTS oss_ext;

CREATE [READABLE] EXTERNAL TABLE tablename

( columnname datatype [, ...] | LIKE othertable ) LOCATION ('ossprotocol') 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 ('ossprotocol') 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 ] ossprotocol: oss://oss_endpoint prefix=prefix_name

id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

ossprotocol:

oss://oss_endpoint dir=[folder/[folder/]...]/file_name

id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

ossprotocol:

oss://oss_endpoint filepath=[folder/[folder/]...]/file_name

id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

参考

转自阿里云德哥