AWSredshift->hdbpg(Greenplum)，資料庫-S3匯入匯出(unload,externaltable)-hdbpgexternaltable

背景
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
轉自阿里雲德哥