Federated analysis of Hadoop external data sources using external tables - AnalyticDB: Central de Documentação do Alibaba Cloud

O AnalyticDB for PostgreSQL permite consultar dados do Hadoop — incluindo arquivos do Hadoop Distributed File System (HDFS) e tabelas Hive — diretamente via SQL usando tabelas externas baseadas no protocolo PXF. Esse recurso viabiliza análises federadas em clusters Hadoop existentes sem mover dados.

Limitações

Restrição	Detalhe
Modo da instância	Apenas modo de armazenamento elástico
Rede	A instância do AnalyticDB for PostgreSQL e o cluster Hadoop devem estar na mesma Virtual Private Cloud (VPC)
Idade da instância	Instâncias no modo de armazenamento elástico criadas antes de 6 de setembro de 2020 não se conectam a clusters Hadoop externos devido à incompatibilidade da arquitetura de rede. Entre em contato com o suporte técnico da Alibaba Cloud para provisionar uma nova instância e migrar seus dados.

Pré-requisitos

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

Uma instância do AnalyticDB for PostgreSQL no modo de armazenamento elástico, criada em ou após 6 de setembro de 2020
Um cluster Hadoop em execução na mesma VPC da instância
Um servidor PXF configurado pelo suporte técnico da Alibaba Cloud

Configure um servidor

A configuração do servidor requer assistência do suporte técnico da Alibaba Cloud. Envie um ticket e forneça os seguintes arquivos de configuração.

Fonte de dados externa	Arquivos necessários
Hadoop (HDFS, Hive e HBase)	`core-site.xml`, `hdfs-site.xml`, `mapred-site.xml`, `yarn-site.xml`, `hive-site.xml`

Para clusters com autenticação Kerberos, forneça também keytab e krb5.conf .

O suporte técnico retorna um valor SERVER (por exemplo, hdp3) que identifica o diretório de configuração do servidor PXF (PXF_SERVER/hdp3/). Use esse valor em todas as cláusulas LOCATION.

Sintaxe

Ative a extensão PXF uma vez por banco de dados:

CREATE EXTENSION pxf;

Crie uma tabela externa:

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION('pxf://<path-to-data>?PROFILE=<profile>[&<custom-option>=<value>[...]][&SERVER=<server_name>]')
FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);

Para obter a sintaxe completa de CREATE EXTERNAL TABLE, consulte Sintaxe SQL.

Parâmetros da cláusula LOCATION

Parâmetro	Descrição
`pxf://`	Prefixo do protocolo PXF. Não modifique.
`<path-to-data>`	Para HDFS: caminho absoluto do arquivo ou diretório. Para Hive: `<database>.<table>` (por exemplo, `default.sales_info`).
`PROFILE=<profile>`	Perfil PXF correspondente à fonte de dados e ao formato. Consulte Perfis HDFS suportados e Perfis Hive suportados.
`SERVER=<server_name>`	Identificador da configuração do servidor PXF. Fornecido pelo suporte técnico da Alibaba Cloud.

Consulte dados do HDFS

Perfis HDFS suportados

Formato	Perfil
Texto	`hdfs:text`
CSV	`hdfs:text:multi`, `hdfs:text`
Avro	`hdfs:avro`
JSON	`hdfs:json`
Parquet	`hdfs:parquet`
AvroSequenceFile	`hdfs:AvroSequenceFile`
SequenceFile	`hdfs:SequenceFile`

Exemplo: Leitura de um arquivo de texto HDFS

Crie um arquivo de teste no HDFS.

echo 'Prague,Jan,101,4875.33
Rome,Mar,87,1557.39
Bangalore,May,317,8936.99
Beijing,Jul,411,11600.67' > /tmp/pxf_hdfs_simple.txt

# Create the target directory
hdfs dfs -mkdir -p /data/pxf_examples

# Upload the file
hdfs dfs -put /tmp/pxf_hdfs_simple.txt /data/pxf_examples/

# Verify the upload
hdfs dfs -cat /data/pxf_examples/pxf_hdfs_simple.txt

No AnalyticDB for PostgreSQL, crie uma tabela externa que aponte para esse arquivo.

CREATE EXTERNAL TABLE pxf_hdfs_textsimple (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://data/pxf_examples/pxf_hdfs_simple.txt?PROFILE=hdfs:text&SERVER=hdp3')
FORMAT 'TEXT' (delimiter=E',');

Consulte a tabela.

SELECT * FROM pxf_hdfs_textsimple;

Saída esperada:

 location  | month | num_orders |    total_sales
-----------+-------+------------+--------------------
 Prague    | Jan   |        101 | 4875.3299999999999
 Rome      | Mar   |         87 | 1557.3900000000001
 Bangalore | May   |        317 | 8936.9899999999998
 Beijing   | Jul   |        411 |           11600.67
(4 rows)

Exemplo: Gravação de dados no HDFS

Para gravar dados do AnalyticDB for PostgreSQL no HDFS, crie uma tabela externa gravável.

Crie o diretório de destino no HDFS.

Você precisa ter permissões de gravação neste diretório para executar instruções INSERT a partir do AnalyticDB for PostgreSQL.
```
hdfs dfs -mkdir -p /data/pxf_examples/pxfwritable_hdfs_textsimple1
```

Crie uma tabela externa gravável e insira dados.

CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1 (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://data/pxf_examples/pxfwritable_hdfs_textsimple1?PROFILE=hdfs:text&SERVER=hdp3')
FORMAT 'TEXT' (delimiter=',');

INSERT INTO pxf_hdfs_writabletbl_1 VALUES ('Frankfurt', 'Mar', 777, 3956.98);
INSERT INTO pxf_hdfs_writabletbl_1 VALUES ('Cleveland', 'Oct', 3812, 96645.37);

Verifique se os dados foram gravados no HDFS.

# List files in the directory
hdfs dfs -ls /data/pxf_examples/pxfwritable_hdfs_textsimple1

# Print the file contents
hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/*

Saída esperada:

Frankfurt,Mar,777,3956.98
Cleveland,Oct,3812,96645.37

Consulte dados do Hive

Perfis Hive suportados

Formato	Perfis suportados
TextFile	`Hive`, `HiveText`
SequenceFile	`Hive`
RCFile	`Hive`, `HiveRC`
ORC	`Hive`, `HiveORC`, `HiveVectorizedORC`
Parquet	`Hive`

O perfil Hive suporta todos os formatos de armazenamento listados acima. Use um subperfil específico (HiveText, HiveRC, HiveORC, HiveVectorizedORC) quando precisar de capacidades específicas.

Escolha entre HiveORC e HiveVectorizedORC

Ambos os perfis leem tabelas Hive no formato ORC. Escolha com base nos requisitos da consulta:

Capacidade	HiveORC	HiveVectorizedORC
Linhas lidas por lote	1	Até 1.024
Projeção de colunas	Sim	Não
Tipos complexos (`array`, `map`, `struct`, `union`)	Sim	Não
Tipo de dados `timestamp`	Sim	Não

Exemplo: Uso do perfil Hive

O perfil Hive funciona com todos os formatos de armazenamento do Hive.

Gere dados de amostra e carregue-os no Hive.

echo 'Prague,Jan,101,4875.33
Rome,Mar,87,1557.39
Bangalore,May,317,8936.99
Beijing,Jul,411,11600.67
San Francisco,Sept,156,6846.34
Paris,Nov,159,7134.56
San Francisco,Jan,113,5397.89
Prague,Dec,333,9894.77
Bangalore,Jul,271,8320.55
Beijing,Dec,100,4248.41' > /tmp/pxf_hive_datafile.txt

-- In Hive
CREATE TABLE sales_info (
    location         string,
    month            string,
    number_of_orders int,
    total_sales      double
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS textfile;

LOAD DATA LOCAL INPATH '/tmp/pxf_hive_datafile.txt' INTO TABLE sales_info;
SELECT * FROM sales_info;

No AnalyticDB for PostgreSQL, ative a extensão PXF e crie uma tabela externa.

CREATE EXTENSION pxf;

CREATE EXTERNAL TABLE salesinfo_hiveprofile (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info?PROFILE=Hive&SERVER=hdp3')
FORMAT 'custom' (formatter='pxfwritable_import');

Consulte a tabela.

SELECT * FROM salesinfo_hiveprofile;

Saída esperada:

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 Beijing       | Jul   |        411 |    11600.67
 San Francisco | Sept  |        156 |     6846.34
 Paris         | Nov   |        159 |     7134.56
......

Exemplo: Uso do perfil HiveText

O perfil HiveText lê tabelas Hive no formato TextFile e usa um delimitador de texto em vez do formatador pxfwritable_import.

CREATE EXTERNAL TABLE salesinfo_hivetextprofile (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info?PROFILE=HiveText&SERVER=hdp3')
FORMAT 'TEXT' (delimiter=E',');

SELECT * FROM salesinfo_hivetextprofile;

Saída esperada:

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 ......

Exemplo: Uso do perfil HiveRC

Crie uma tabela Hive no formato RCFile.

-- In Hive
CREATE TABLE sales_info_rcfile (
    location         string,
    month            string,
    number_of_orders int,
    total_sales      double
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile;

-- Import data from the existing table
INSERT INTO TABLE sales_info_rcfile SELECT * FROM sales_info;

-- Verify the data
SELECT * FROM sales_info_rcfile;

Crie uma tabela externa no AnalyticDB for PostgreSQL e consulte-a.

CREATE EXTERNAL TABLE salesinfo_hivercprofile (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info_rcfile?PROFILE=HiveRC&SERVER=hdp3')
FORMAT 'TEXT' (delimiter=E',');

SELECT location, total_sales FROM salesinfo_hivercprofile;

Saída esperada:

   location    | total_sales
---------------+-------------
 Prague        |     4875.33
 Rome          |     1557.39
 Bangalore     |     8936.99
 ......

Exemplo: Uso do perfil HiveORC

Use HiveORC quando precisar de projeção de colunas ou consultar tabelas com tipos complexos (array, map, struct, union).

Crie uma tabela Hive no formato ORC.

-- In Hive
CREATE TABLE sales_info_ORC (
    location         string,
    month            string,
    number_of_orders int,
    total_sales      double
)
STORED AS ORC;

INSERT INTO TABLE sales_info_ORC SELECT * FROM sales_info;

-- Verify the data
SELECT * FROM sales_info_ORC;

Crie uma tabela externa no AnalyticDB for PostgreSQL e consulte-a.

CREATE EXTERNAL TABLE salesinfo_hiveORCprofile (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveORC&SERVER=hdp3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM salesinfo_hiveORCprofile;

Saída esperada:

 ......
 Prague        | Dec   |        333 |     9894.77
 Bangalore     | Jul   |        271 |     8320.55
 Beijing       | Dec   |        100 |     4248.41
(60 rows)
Time: 420.920 ms

Exemplo: Uso do perfil HiveVectorizedORC

Use HiveVectorizedORC para consultas simples em grandes tabelas ORC quando não houver necessidade de projeção de colunas, suporte a tipos complexos ou ao tipo de dados timestamp.

CREATE EXTERNAL TABLE salesinfo_hiveVectORC (
    location    text,
    month       text,
    num_orders  int,
    total_sales float8
)
LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveVectorizedORC&SERVER=hdp3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM salesinfo_hiveVectORC;

Saída esperada:

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 Beijing       | Jul   |        411 |    11600.67
 San Francisco | Sept  |        156 |     6846.34
 ......

Exemplo: Consulta a uma tabela Hive no formato Parquet

Crie uma tabela Hive no formato Parquet.

-- In Hive
CREATE TABLE hive_parquet_table (
    location         string,
    month            string,
    number_of_orders int,
    total_sales      double
)
STORED AS parquet;

INSERT INTO TABLE hive_parquet_table SELECT * FROM sales_info;

SELECT * FROM hive_parquet_table;

Crie uma tabela externa no AnalyticDB for PostgreSQL e consulte-a.

CREATE EXTERNAL TABLE pxf_parquet_table (
    location         text,
    month            text,
    number_of_orders int,
    total_sales      double precision
)
LOCATION ('pxf://default.hive_parquet_table?profile=Hive&SERVER=hdp3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT month, number_of_orders FROM pxf_parquet_table;

Saída esperada:

 month | number_of_orders
-------+------------------
 Jan   |              101
 Mar   |               87
 May   |              317
 Jul   |              411
 Sept  |              156
 Nov   |              159
 ......