AnalyticDB:Analyze data lakes with OSS foreign tables

Limitações

A instância do AnalyticDB for PostgreSQL e o bucket do OSS devem estar na mesma região.

Recursos

O OSS FDW baseia-se na estrutura PostgreSQL Foreign Data Wrapper (FDW) e permite:

• Importar dados do OSS para tabelas locais orientadas a linhas ou colunas, acelerando a análise.

• Consultar e analisar diretamente grandes volumes de dados no OSS.

• Combinar tabelas externas do OSS com tabelas locais para análise.

O OSS FDW oferece suporte a vários formatos de arquivo de dados, incluindo:

• Arquivos de texto não compactados nos formatos CSV, TEXT, JSON e JSON Lines.

• Arquivos de texto compactados com GZIP e Snappy nos formatos CSV e TEXT.

• Arquivos de texto compactados com GZIP nos formatos JSON e JSON Lines.

• Arquivos binários ORC. Para mapeamentos de tipos de dados entre ORC e AnalyticDB for PostgreSQL, consulte Mapeamentos de tipos de dados para arquivos ORC.

• Arquivos binários Parquet. Para mapeamentos de tipos de dados entre Parquet e AnalyticDB for PostgreSQL, consulte Mapeamentos de tipos de dados para arquivos Parquet.

• Arquivos binários Avro. Para mapeamentos de tipos de dados entre Avro e AnalyticDB for PostgreSQL, consulte Mapeamentos de tipos de dados para arquivos Avro.

Antes de começar

Prepare os dados do OSS

Prepare o arquivo de exemplo example.csv.

Encontre as informações do bucket do OSS

Esta seção descreve como localizar os campos Buckets, Bucket Name, Port e Bucket Domain Name.

1. Faça login no console do OSS.

2. No painel de navegação à esquerda, clique em Access from ECS over the VPC (internal network).

3. Na página Endpoint, clique no bucket desejado.

   Na página Object Management, localize o nome do bucket.

4. Na página Object Management, localize o caminho do objeto.

5. No painel de navegação à esquerda, clique em Overview.

6. Na seção Port da página Overview, localize o endpoint e o nome de domínio do bucket.

   Recomendamos usar o endpoint para Access from ECS over the VPC (internal network).

Obtenha um AccessKey ID e AccessKey Secret

Para obter um AccessKey ID e AccessKey Secret, consulte Criar um AccessKey.

Crie um servidor OSS

Use a instrução CREATE SERVER para criar um servidor OSS. Esse servidor define a conexão com o serviço OSS que você deseja acessar. Para mais informações sobre CREATE SERVER, consulte CREATE SERVER.

Sintaxe

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parâmetros

A tabela a seguir descreve os parâmetros da cláusula OPTIONS.

Exemplos

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

Também é possível usar a instrução ALTER SERVER para modificar a configuração do servidor OSS. Para mais informações, consulte ALTER SERVER.

Exemplos:

• Modifique uma opção do servidor OSS.

  ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');

• Adicione uma opção ao servidor OSS.

  ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');

• Remova uma opção do servidor OSS.

  ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);

Para excluir o servidor OSS, use a instrução DROP SERVER. Para mais informações, consulte DROP SERVER.

Crie um mapeamento de usuário do OSS

Após criar um servidor OSS, crie também um mapeamento de usuário. A instrução CREATE USER MAPPING define um mapeamento entre um usuário no banco de dados AnalyticDB for PostgreSQL e as credenciais para acessar o servidor OSS. Para mais informações, consulte CREATE USER MAPPING.

Sintaxe

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER <server_name>
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parâmetros

A tabela a seguir descreve os parâmetros da cláusula OPTIONS.

Exemplo

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI****************',
        key 'yourAccessKeySecret'
    );

Também é possível usar a instrução DROP USER MAPPING para excluir um mapeamento de usuário. Para mais informações, consulte DROP USER MAPPING.

Crie uma tabela externa do OSS

Depois de configurar um servidor OSS e um usuário com acesso a ele, use a instrução CREATE FOREIGN TABLE para criar uma tabela externa do OSS. Para mais informações, consulte CREATE FOREIGN TABLE.

Sintaxe

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

Parâmetros

A tabela a seguir lista os parâmetros da cláusula OPTIONS.

Exemplo

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

Também é possível usar a instrução DROP FOREIGN TABLE para remover a tabela externa do OSS. Para mais informações, consulte DROP FOREIGN TABLE.

Consulte e analise dados do OSS

Consulte uma tabela externa do OSS da mesma forma que consulta uma tabela local. Os exemplos a seguir mostram consultas comuns.

• Consulte dados usando um filtro de chave-valor.

  SELECT * FROM ossexample WHERE volume = 5;

• Consulte dados usando uma função de agregação.

  SELECT count(*) FROM ossexample WHERE volume = 5;

• Consulte dados usando as cláusulas GROUP BY e LIMIT.

  SELECT low, sum(volume)
    FROM ossexample
   GROUP BY low
   ORDER BY low
   limit 5;

Análise de junção com tabelas externas do OSS

1. Crie uma tabela local chamada example e insira dados de teste.

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. Execute uma consulta de junção nas tabelas example e ossexample.

   SELECT example.volume, min(high), max(low)
   FROM
   ossexample,
   example
   WHERE ossexample.volume = example.volume
   GROUP BY(example.volume)
   ORDER BY example.volume;

Tolerância a falhas

O OSS FDW fornece tolerância a falhas usando os parâmetros log_errors e segment_reject_limit. Isso evita que erros nos dados brutos interrompam as varreduras de uma tabela externa do OSS.

Para mais informações sobre os parâmetros log_errors e segment_reject_limit, consulte Criar uma tabela externa do OSS.

• Crie uma tabela externa do OSS com tolerância a falhas.

  CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
      SERVER oss_serv
      OPTIONS (log_errors 'true', -- Enable error logging.
               segment_reject_limit '10', -- Set the reject limit to 10 rows. The scan is aborted if this limit is exceeded.
               dir 'error_sales/', -- Specify the OSS object directory for the foreign table.
               format 'csv', -- Specify the object format as CSV.
               encoding 'utf8'); -- Specify the encoding. 

• Consulte o log de erros.

  SELECT * FROM gp_read_error_log('oss_error_sales');

• Limpe o log de erros.

  SELECT gp_truncate_error_log('oss_error_sales');

Perguntas frequentes

P: Excluir dados de uma tabela externa do OSS também exclui os dados subjacentes no OSS?

R: Não. Excluir dados de uma tabela externa do OSS não exclui os dados subjacentes no OSS.

Documentação relacionada

• Usando tabelas externas particionadas do OSS

• Importando e exportando dados com tabelas externas do OSS

• Melhores práticas para tabelas externas do OSS

• Mapeamentos de tipos de dados para tabelas externas do OSS