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

redshift unload 将QUERY结果导出到s3
https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

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数据
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html

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。

https://help.aliyun.com/document_detail/35457.html

没有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]  

参考
https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

https://help.aliyun.com/document_detail/35457.html

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html
转自阿里云德哥