Como importar dados usando COPY ou exportar dados usando UNLOAD para o OSS - AnalyticDB: Central de Documentação do Alibaba Cloud

O comando COPY importa dados do Object Storage Service (OSS) para uma tabela do AnalyticDB for PostgreSQL. O comando UNLOAD exporta resultados de consultas de uma tabela do AnalyticDB for PostgreSQL para o OSS. Ambas as instruções operam por meio de tabelas externas do OSS. Para obter informações básicas, consulte Usar tabelas externas do OSS para acessar dados do OSS.

Pré-requisitos

Antes de começar, verifique se você possui:

Uma instância do AnalyticDB for PostgreSQL
Um bucket do OSS acessível com suas credenciais AccessKey
A extensão oss_fdw instalada na instância

COPY

Utilize a instrução COPY para importar dados de um caminho de bucket do OSS ou de um arquivo de manifesto para uma tabela do AnalyticDB for PostgreSQL.

Sintaxe

COPY <table_name>
[ <column_list> ]
FROM <data_source>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST ]
[ option '<value>' [ ... ] ]

Parâmetros

Parâmetro	Obrigatório	Descrição
`table_name`	Sim	A tabela de destino do AnalyticDB for PostgreSQL. A tabela já deve existir na instância.
`column_list`	Não	As colunas nas quais os dados serão gravados. Se omitido, os dados serão gravados em todas as colunas.
`data_source`	Sim	O caminho do OSS para leitura. Formato: `oss://<bucket_name>/path_prefix`.
`access_key_id`	Sim	O AccessKey ID de uma conta Alibaba Cloud ou usuário do Resource Access Management (RAM) com acesso ao OSS. Recomenda-se usar um usuário RAM com as permissões mínimas necessárias em vez das credenciais da conta raiz. Para obter instruções, consulte Obter um par de AccessKey.
`secret_access_key`	Sim	O AccessKey secret correspondente ao AccessKey ID. Para obter instruções, consulte Obter um par de AccessKey.
`[ FORMAT ] [ AS ] <data_format>`	Não	O formato dos dados de origem. O padrão é CSV. Valores suportados: BINARY, CSV, JSON, JSONLINE, ORC, PARQUET, TEXT. `FORMAT AS CSV`, `FORMAT CSV` e `CSV` são equivalentes.
`MANIFEST`	Não	Trata o `data_source` como um arquivo de manifesto em vez de um caminho de dados. Consulte Formato de arquivo MANIFEST abaixo.
`option 'value'`	Não	Opções adicionais no formato `key value`. Consulte a tabela de opções abaixo.

Opções

Opção	Tipo	Obrigatório	Descrição
`ENDPOINT`	STRING	Sim	O endpoint do OSS. Para obter uma lista de endpoints por região, consulte Regiões e endpoints.
`FDW`	STRING	Sim	O nome da extensão oss_fdw. Necessário para criar um servidor OSS temporário para a instrução COPY.
`FORMAT`, `FILETYPE`, `DELIMITER`, `ESCAPE` e outros	—	—	Opções para criação da tabela externa temporária do OSS. Para mais detalhes, consulte Visão geral das tabelas externas do OSS.

Formato de arquivo MANIFEST

Quando MANIFEST é especificado, o data_source deve apontar para um arquivo JSON com a seguinte estrutura:

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_seg0_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_seg0_0.csv", "mandatory": true}
  ]
}

Campo	Descrição
`entries`	Um array de objetos do OSS. Os objetos podem estar distribuídos em diferentes buckets e caminhos, mas todos devem ser acessíveis com o mesmo AccessKey ID e secret.
`url`	O caminho completo de um objeto do OSS.
`mandatory`	Se definido como `true`, a importação falhará caso o objeto não seja encontrado.

Tolerância a falhas

Durante a importação de dados, algumas linhas podem apresentar falhas de análise. Configure as opções a seguir para tratar erros sem interromper todo o processo de importação:

Opção	Descrição
`log_errors 'true'`	Registra linhas malformadas em um log de erros em vez de causar falha imediata.
`segment_reject_limit '<n>'`	Interrompe a importação e retorna um erro se o número de linhas rejeitadas atingir `n` (por exemplo, `'10'`) ou se a taxa de rejeição alcançar `n`% (por exemplo, `'10%'`). Utilize um limiar de contagem para orçamentos de erros previsíveis; use um limiar percentual para conjuntos de dados de tamanho variável, onde contagens absolutas são mais difíceis de estimar.

Após a importação, consulte o log de erros:

SELECT * FROM gp_read_error_log('<table_name>');

Logs de erros consomem espaço de armazenamento. Exclua-os quando não forem mais necessários:

SELECT gp_truncate_error_log('<table_name>');

Exemplos

Importar colunas selecionadas de CSV

Crie a tabela de destino.

CREATE TABLE local_t2 (a int, b float8, c text);

Importe dados para as colunas a e c. A coluna b receberá NULL.

COPY local_t2 (a, c)
FROM 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Verifique a importação.

SELECT * FROM local_t2 LIMIT 10;

Saída esperada:

 a  | b |                c
----+---+----------------------------------
  12 |   | a24cba6ebdc5e0c485cd88ef60b72fea
  15 |   | c4d3028f5205fab98e5f43c7945db4ba
  20 |   | 769884311db01f400e21a903a3f1cb50
...
(10 rows)

Confira se os dados nas colunas a e c da tabela local_t2 correspondem aos dados da tabela local_t.

SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t2 t;

Saída esperada:

 col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)

SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t t;

Saída esperada:

 col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)

Importe arquivos ORC ou Parquet usando a mesma sintaxe com um FORMAT diferente:

-- ORC
COPY tt
FROM 'oss://adbpg-regress/q_oss_orc_list/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS ORC
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

-- Parquet
COPY tp
FROM 'oss://adbpg-regress/test_parquet/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS PARQUET
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Importar a partir de um arquivo de manifesto

Crie a tabela de destino.

CREATE TABLE local_manifest (a int, c text);

Crie um arquivo de manifesto que referencie objetos de múltiplos buckets.

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1450404435_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1856683967_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1880804901_seg0_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1008521914_seg1_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1234881553_seg2_0.csv", "mandatory": true},
    {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1711667760_seg0_0.csv", "mandatory": true}
  ]
}

Execute a importação usando o caminho do arquivo de manifesto.

COPY local_manifest
FROM 'oss://adbpg-regress-2/unload_manifest/t_manifest'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Tratar erros de importação com tolerância a falhas

Crie a tabela de destino.

CREATE TABLE sales (id integer, value float8, x text) DISTRIBUTED BY (id);

Realize a importação com o registro de erros ativado. O processo continua mesmo se houver falhas em linhas, mas é interrompido ao encontrar 10 ou mais erros.

COPY sales
FROM 'oss://adbpg-const/error_sales/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
log_errors 'true'
segment_reject_limit '10'
endpoint 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Saída esperada:

NOTICE:  found 3 data formatting errors (3 or more input rows), rejected related input data
COPY FOREIGN TABLE

Consulte os detalhes dos erros.

SELECT * FROM gp_read_error_log('sales');

Saída esperada:

         cmdtime            |                    relname                     |        filename         | linenum | bytenum |                          errmsg                           | rawdata | rawbytes
----------------------------+------------------------------------------------+-------------------------+---------+---------+-----------------------------------------------------------+---------+----------
 2021-02-08 14:24:04.225238 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225238 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       3 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225269 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.3.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
(3 rows)

UNLOAD

Use a instrução UNLOAD para exportar resultados de consultas de uma tabela do AnalyticDB for PostgreSQL para o OSS.

Notas de uso

Ao exportar para CSV, coloque as seguintes opções entre aspas duplas e escreva-as em letras minúsculas: delimiter, quote, null, header, escape e encoding. Sem aspas, esses nomes de opção podem ser interpretados como palavras-chave SQL, causando erros de sintaxe.

UNLOAD ('SELECT * FROM test')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT csv
"delimiter" '|'
"quote" '"'
"null" ''
"header" 'true'
"escape" 'E'
"encoding" 'utf-8'
FDW 'oss_fdw'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com';

Sintaxe

UNLOAD ('<select_statement>')
TO <destination_url>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST [ '<manifest_url>' ] ]
[ PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] ]
[ option '<value>' [ ... ] ]

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_statement`	Sim	Uma instrução SELECT cujos resultados serão gravados no OSS.
`destination_url`	Sim	O caminho do OSS para gravação. Formato: `oss://<bucket_name>/path_prefix`.
`access_key_id`	Sim	O AccessKey ID de uma conta Alibaba Cloud ou usuário RAM com acesso ao OSS. Recomenda-se usar um usuário RAM com as permissões mínimas necessárias em vez das credenciais da conta raiz. Para obter instruções, consulte Obter um par de AccessKey.
`secret_access_key`	Sim	O AccessKey secret correspondente ao AccessKey ID. Para obter instruções, consulte Obter um par de AccessKey.
`[ FORMAT ] [ AS ] <data_format>`	Não	O formato dos dados exportados. O padrão é CSV. Valores suportados: CSV, ORC, TEXT.
`MANIFEST [ '<manifest_url>' ]`	Não	Gera um arquivo de manifesto listando todos os objetos exportados. Se `manifest_url` for especificado, o manifesto será gravado nesse caminho (que deve terminar com o sufixo `manifest`) e poderá estar em um bucket diferente dos arquivos de dados. Se omitido, o prefixo do caminho do manifesto corresponderá ao dos arquivos de dados. Caso o arquivo de manifesto já exista, defina `ALLOWOVERWRITE 'true'` para sobrescrevê-lo.
`PARALLEL`	Não	Controla a exportação paralela. Padrão: `ON`. Quando definido como `ON`, cada nó de computação grava um arquivo de saída separado — o número de arquivos de saída é igual ao número de nós de computação da sua instância. Quando definido como `OFF` ou `FALSE`, os dados são exportados serialmente e salvos em um único arquivo, desde que o tamanho total seja de 8 GB ou menos.
`option 'value'`	Não	Opções adicionais no formato `key value`. Consulte a tabela de opções abaixo.

Opções

Opção	Tipo	Obrigatório	Descrição
`ENDPOINT`	STRING	Sim	O endpoint do OSS. Para obter uma lista de endpoints por região, consulte Regiões e endpoints.
`FDW`	STRING	Sim	O nome da extensão oss_fdw. Necessário para criar um servidor OSS temporário para a instrução UNLOAD.
`FORMAT`, `FILETYPE`, `DELIMITER`, `ESCAPE` e outros	—	—	Opções para criação da tabela externa temporária do OSS. Para mais detalhes, consulte Visão geral das tabelas externas do OSS.

Exemplos

Exportar para CSV

Crie a tabela de origem e insira dados de teste.

CREATE TABLE local_t (a int, b float8, c text);
INSERT INTO local_t
SELECT r, random() * 1000, md5(random()::text)
FROM generate_series(1, 1000) r;

Verifique os dados de origem.

SELECT * FROM local_t LIMIT 5;

Saída esperada:

 a  |        b         |                c
----+------------------+----------------------------------
   5 |  550.81393988803 | 8009fa725372e996786849213a695ce0
   6 | 95.8335199393332 | ce7952c6728cdffdee06cc5b502d6457
   9 | 421.379795763642 | d3260ccbf6b9c03f3658d96bb7678b4d
  10 | 362.347379792482 | 2bbbf89d23a2f83b089b589f55b5c4fc
  11 | 800.203878898174 | a52994c5573e6b36d8a1c357bf800ce5
(5 rows)

Exporte as colunas a e c para o OSS no formato CSV.

UNLOAD ('SELECT a, c FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Saída esperada:

NOTICE:  OSS output prefix: "local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618".
UNLOAD

Confirme se os arquivos foram gravados no OSS.

ossutil --config hangzhou-zmf.config ls oss://adbpg-regress/local_t/

Saída esperada (um arquivo por nó de computação):

LastModifiedTime                   Size(B)  StorageClass   ETAG                                  ObjectName
2020-09-07 16:48:01 +0800 CST        12023      Standard   9F38B5407142C044C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg0_0.csv
2020-09-07 16:48:01 +0800 CST        12469      Standard   807BA680A0DED49BC1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg1_0.csv
2020-09-07 16:48:01 +0800 CST        12401      Standard   3524F68F628CEB64C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
Object Number is: 3

Inspecione o conteúdo do CSV.

head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv

Saída esperada:

7,1225341d0d367a69b1b345536b21ef73
19,424a7a5c36066842f4de8c8a8341fc89
27,c214432e9928e4a6f7bef7bd815424c0
29,ade5d636e2b5d2a606a02e79255da4bd
37,85660e60ede47b68493f6295620db568

Exportar com um arquivo de manifesto

Todos os três exemplos abaixo usam a mesma tabela de origem (local_t) criada na seção anterior.

Cenário 1: Gerar um arquivo de manifesto junto com os arquivos de dados

UNLOAD ('SELECT * FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Liste os arquivos exportados:

ossutil ls -s oss://adbpg-regress/local_t/

Saída esperada — três arquivos de dados e um arquivo de manifesto compartilham o mesmo prefixo de caminho:

oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest
Object Number is: 4

Visualize o conteúdo do manifesto:

ossutil cat oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv"}
  ]
}

Cenário 2: Gravar o arquivo de manifesto em um bucket diferente

ALLOWOVERWRITE 'true' sobrescreve apenas o arquivo de manifesto existente. Os arquivos de dados não são sobrescritos e devem ser excluídos manualmente, se necessário.

UNLOAD ('SELECT * FROM local_t')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI****************'
SECRET_ACCESS_KEY 'TNPP*************************'
FORMAT AS CSV
MANIFEST 'oss://adbpg-regress-2/unload_manifest/t_manifest'
ALLOWOVERWRITE 'true'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

Verifique se os arquivos de dados estão no bucket original e o manifesto está no bucket separado:

# Data files
ossutil ls -s oss://adbpg-regress/local_t/

oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv
Object Number is: 3

# Manifest file in a different bucket
ossutil cat oss://adbpg-regress-2/unload_manifest/t_manifest

{
  "entries": [
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv"},
    {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv"}
  ]
}

Perguntas frequentes

Por que a exportação gerou vários arquivos CSV?

O UNLOAD grava um arquivo de saída por nó de computação por padrão (PARALLEL ON). Se sua instância tiver quatro nós de computação, você obterá quatro arquivos. Para exportar para um único arquivo, defina PARALLEL OFF — mas somente se o tamanho total dos dados for de 8 GB ou menos.

AnalyticDB:Importar ou exportar dados para o OSS usando o comando COPY ou UNLOAD

Pré-requisitos

COPY

Sintaxe

Parâmetros

Opções

Formato de arquivo MANIFEST

Tolerância a falhas

Exemplos

Importar colunas selecionadas de CSV

Importar a partir de um arquivo de manifesto

Tratar erros de importação com tolerância a falhas

UNLOAD

Notas de uso

Sintaxe

Parâmetros

Opções

Exemplos

Exportar para CSV

Exportar com um arquivo de manifesto

Perguntas frequentes

Próximos passos