AWS redshift->hdb pg(Greenplum)，数据库-S3导入导出(unload, external table) - hdb pg external table

背景

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

digoal’s 大量PostgreSQL文章入口