AnalyticDB:Use an external table to import data from OSS

How it works

The gpossext feature extends AnalyticDB for PostgreSQL with an oss:// protocol handler. After installing the oss_ext extension, you define a readable external table that maps to OSS objects. When you run INSERT INTO ... SELECT * FROM, each compute node reads a subset of OSS objects in parallel via a polling mechanism, then redistributes rows to the correct destination partitions.

gpossext reads TEXT and CSV files, with or without GZIP compression.

The syntax used to create and use external tables is the same as that of Greenplum Database, except for the syntax of location-related parameters.

Prerequisites

Before you begin, make sure you have:

• An AnalyticDB for PostgreSQL V6.0 instance

• An OSS bucket in the same region as your instance

• An AccessKey ID and AccessKey secret with read access to the bucket

• Source data already uploaded to OSS as TEXT or CSV files (plain or GZIP-compressed)

Import data from OSS

CREATE EXTENSION IF NOT 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] ]
ossprotocol:
    oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name|filepath=[folder/[folder/]...]/file_name]
     id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

-- Check a sample of rows
SELECT * FROM <External table> LIMIT 10;

-- Inspect the query plan to confirm parallel reads
EXPLAIN INSERT INTO <Destination table> SELECT * FROM <External table>;

INSERT INTO <Destination table> SELECT * FROM <External table>;

Example

This example imports stock data from OSS into a table named example.

1. Install the extension.

CREATE EXTENSION IF NOT EXISTS oss_ext;

2. Create the destination table.

CREATE TABLE example
    (date text, time text, open float,
     high float, low float, volume int)
    DISTRIBUTED BY (date);

For best import performance, use column-oriented storage with compression. For example, you can specify the following clause: WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576). For more information, see CREATE TABLE.

3. Create the external table. Choose the path parameter that matches how your files are organized in OSS.

Using prefix (imports all objects with a matching path prefix, supports GZIP):

CREATE READABLE EXTERNAL TABLE ossexample
    (date text, time text, open float, high float,
    low float, volume int)
    location('oss://oss-cn-hangzhou.aliyuncs.com
    prefix=osstest/example id=XXX
    key=XXX bucket=testbucket compressiontype=gzip')
    FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
    ENCODING 'utf8'
    LOG ERRORS SEGMENT REJECT LIMIT 5;

Using dir (imports all objects directly in the directory, no GZIP):

CREATE READABLE EXTERNAL TABLE ossexample
    (date text, time text, open float, high float,
    low float, volume int)
    location('oss://oss-cn-hangzhou.aliyuncs.com
    dir=osstest/ id=XXX
    key=XXX bucket=testbucket')
    FORMAT 'csv'
    LOG ERRORS SEGMENT REJECT LIMIT 5;

Using filepath (imports the specified object):

CREATE READABLE EXTERNAL TABLE ossexample
    (date text, time text, open float, high float,
    low float, volume int)
    location('oss://oss-cn-hangzhou.aliyuncs.com
    filepath=osstest/example.csv id=XXX
    key=XXX bucket=testbucket')
    FORMAT 'csv'
    LOG ERRORS SEGMENT REJECT LIMIT 5;

4. Import data.

INSERT INTO example SELECT * FROM ossexample;

The query plan confirms that all four compute nodes read from OSS in parallel:

EXPLAIN INSERT INTO example SELECT * FROM ossexample;
                                            QUERY PLAN
-------------------------------------------------------------------------------------------------
 Insert (slice0; segments: 4)  (rows=250000 width=92)
   ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..11000.00 rows=250000 width=92)
         Hash Key: ossexample.date
         ->  External Scan on ossexample  (cost=0.00..11000.00 rows=250000 width=92)
(4 rows)

TEXT and CSV format reference

The following parameters control how gpossext parses TEXT and CSV files. Specify them in the FORMAT clause of your CREATE EXTERNAL TABLE statement.

• \n: line delimiter (newline character)

• DELIMITER: column separator. If specified, QUOTE must also be specified. Common choices: ,, |, \t.

• QUOTE: wraps column values that contain special characters. Must differ from DELIMITER. Default: ". Values containing QUOTE characters must also use ESCAPE.

• ESCAPE: placed before any character that needs to be treated as a literal. Default: same as QUOTE. Backslash (\) is also commonly used.

All control characters must be single-byte.

Default control characters

Troubleshooting

SELECT * FROM gp_read_error_log('external_table_name');

SELECT gp_truncate_error_log('external_table_name');

What's next

• OSS domain names — find the correct endpoint for your region

• OSS documentation — manage buckets and objects

• CREATE TABLE — full syntax for table storage options

• Error responses — OSS error code reference

• Error handlingError handling — timeout and error handling guidance