Federated analysis of Hadoop external data sources using external tables - AnalyticDB

AnalyticDB for PostgreSQL le permite consultar datos de Hadoop directamente desde SQL mediante tablas externas basadas en el protocolo PXF. Esto incluye archivos de Hadoop Distributed File System (HDFS) y tablas de Hive. De esta forma, puede ejecutar análisis federados sobre clústeres de Hadoop existentes sin necesidad de mover los datos.

Limitaciones

Restricción	Detalle
Modo de instancia	Solo modo de almacenamiento elástico
Red	La instancia de AnalyticDB for PostgreSQL y el clúster de Hadoop deben estar en la misma virtual private cloud (VPC)
Antigüedad de la instancia	Las instancias en modo de almacenamiento elástico creadas antes del 6 de septiembre de 2020 no pueden conectarse a clústeres de Hadoop externos debido a incompatibilidades en la arquitectura de red. Póngase en contacto con el soporte técnico de Alibaba Cloud para aprovisionar una nueva instancia y migrar sus datos.

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Una instancia de AnalyticDB for PostgreSQL en modo de almacenamiento elástico, creada el 6 de septiembre de 2020 o después.
Un clúster de Hadoop en ejecución dentro de la misma VPC que la instancia.
Un servidor PXF configurado por el soporte técnico de Alibaba Cloud.

Configuración de un servidor

La configuración del servidor requiere asistencia del soporte técnico de Alibaba Cloud. Envíe un ticket y proporcione a continuación los archivos de configuración correspondientes.

Origen de datos externo	Archivos obligatorios
Hadoop (HDFS, Hive y HBase)	`core-site.xml`, `hdfs-site.xml`, `mapred-site.xml`, `yarn-site.xml`, `hive-site.xml`

Para los clústeres con autenticación Kerberos, proporcione también keytab y krb5.conf .

El soporte técnico le devolverá un valor de SERVER (por ejemplo, hdp3) que identifica el directorio de configuración del servidor PXF (PXF_SERVER/hdp3/). Utilice este valor en todas las cláusulas LOCATION.

Sintaxis

Habilite la extensión PXF una vez por cada base de datos:

CREATE EXTENSION pxf;

Cree una tabla 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 consultar la sintaxis completa de CREATE EXTERNAL TABLE, consulte SQL syntax.

Parámetros de la cláusula LOCATION

Parámetro	Descripción
`pxf://`	Prefijo del protocolo PXF. No lo modifique.
`<path-to-data>`	Para HDFS: la ruta absoluta al archivo o directorio. Para Hive: `<database>.<table>` (por ejemplo, `default.sales_info`).
`PROFILE=<profile>`	El perfil de PXF que coincide con su origen de datos y formato. Consulte Perfiles de HDFS compatibles y Perfiles de Hive compatibles.
`SERVER=<server_name>`	Identificador de la configuración del servidor PXF. Lo proporciona el soporte técnico de Alibaba Cloud.

Consulta de datos de HDFS

Perfiles de HDFS compatibles

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

Ejemplo: lectura de un archivo de texto de HDFS

Cree un archivo de prueba en 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

En AnalyticDB for PostgreSQL, cree una tabla externa que apunte a ese archivo.

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 la tabla.

SELECT * FROM pxf_hdfs_textsimple;

Resultado esperado:

 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)

Ejemplo: escritura de datos en HDFS

Para escribir datos de AnalyticDB for PostgreSQL en HDFS, cree una tabla externa con permisos de escritura.

Cree el directorio de destino en HDFS.

Debe tener permisos de escritura en este directorio para ejecutar instrucciones INSERT desde AnalyticDB for PostgreSQL.
```
hdfs dfs -mkdir -p /data/pxf_examples/pxfwritable_hdfs_textsimple1
```

Cree una tabla externa con permisos de escritura e inserte los datos.

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);

Compruebe que los datos se escribieron correctamente en 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/*

Resultado esperado:

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

Consulta de datos de Hive

Perfiles de Hive compatibles

Formato	Perfiles compatibles
TextFile	`Hive`, `HiveText`
SequenceFile	`Hive`
RCFile	`Hive`, `HiveRC`
ORC	`Hive`, `HiveORC`, `HiveVectorizedORC`
Parquet	`Hive`

El perfil Hive es compatible con todos los formatos de almacenamiento mencionados. Utilice un subperfil específico (HiveText, HiveRC, HiveORC, HiveVectorizedORC) cuando necesite sus capacidades particulares.

Elección entre HiveORC y HiveVectorizedORC

Ambos perfiles leen tablas de Hive en formato ORC. Elija uno según los requisitos de su consulta:

Capacidad	HiveORC	HiveVectorizedORC
Filas leídas por lote	1	Hasta 1,024
Proyección de columnas	Sí	No
Tipos complejos (`array`, `map`, `struct`, `union`)	Sí	No
Tipo de dato `timestamp`	Sí	No

Ejemplo: uso del perfil Hive

El perfil Hive funciona con todos los formatos de almacenamiento de Hive.

Genere datos de muestra y cárguelos en 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;

En AnalyticDB for PostgreSQL, habilite la extensión PXF y cree una tabla 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 la tabla.

SELECT * FROM salesinfo_hiveprofile;

Resultado esperado:

   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
......

Ejemplo: uso del perfil HiveText

HiveText lee tablas de Hive en formato TextFile y utiliza un delimitador de texto en lugar del formateador 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;

Resultado esperado:

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

Ejemplo: uso del perfil HiveRC

Cree una tabla de Hive en 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;

Cree una tabla externa en AnalyticDB for PostgreSQL y consúltela.

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;

Resultado esperado:

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

Ejemplo: uso del perfil HiveORC

Utilice HiveORC cuando necesite proyección de columnas o consultar tablas con tipos complejos (array, map, struct, union).

Cree una tabla de Hive en 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;

Cree una tabla externa en AnalyticDB for PostgreSQL y consúltela.

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;

Resultado esperado:

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

Ejemplo: uso del perfil HiveVectorizedORC

Utilice HiveVectorizedORC para consultas sencillas sobre tablas ORC grandes donde no requiera proyección de columnas, compatibilidad con tipos complejos ni el tipo de dato 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;

Resultado esperado:

   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
 ......

Ejemplo: consulta de una tabla de Hive en formato Parquet

Cree una tabla de Hive en 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;

Cree una tabla externa en AnalyticDB for PostgreSQL y consúltela.

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;

Resultado esperado:

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