Analisis federasi sumber data eksternal Hadoop menggunakan tabel eksternal - AnalyticDB

AnalyticDB for PostgreSQL memungkinkan Anda menjalankan kueri terhadap data Hadoop—termasuk file Hadoop Distributed File System (HDFS) dan tabel Hive—langsung dari SQL menggunakan tabel eksternal yang didukung protokol PXF, sehingga memungkinkan analitik federasi terhadap kluster Hadoop yang sudah ada tanpa perlu memindahkan data.

Batasan

Kendala	Detail
Mode instans	Hanya mode elastic storage
Jaringan	Instans AnalyticDB for PostgreSQL dan kluster Hadoop harus berada dalam virtual private cloud (VPC) yang sama
Usia instans	Instans mode elastic storage yang dibuat sebelum 6 September 2020 tidak dapat terhubung ke kluster Hadoop eksternal karena arsitektur jaringan yang tidak kompatibel. Hubungi dukungan teknis Alibaba Cloud untuk menyediakan instans baru dan migrasikan data Anda.

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Instans AnalyticDB for PostgreSQL dalam mode elastic storage yang dibuat pada atau setelah 6 September 2020
Kluster Hadoop yang sedang berjalan dalam VPC yang sama dengan instans tersebut
Server PXF yang dikonfigurasi oleh dukungan teknis Alibaba Cloud

Konfigurasi server

Konfigurasi server memerlukan bantuan dari dukungan teknis Alibaba Cloud. Kirim tiket dan lampirkan file konfigurasi berikut.

Sumber data eksternal	File yang diperlukan
Hadoop (HDFS, Hive, dan HBase)	`core-site.xml`, `hdfs-site.xml`, `mapred-site.xml`, `yarn-site.xml`, `hive-site.xml`

Untuk kluster dengan otentikasi Kerberos, sertakan juga keytab dan krb5.conf.

Dukungan teknis akan mengembalikan nilai SERVER (misalnya, hdp3) yang mengidentifikasi direktori konfigurasi server PXF (PXF_SERVER/hdp3/). Gunakan nilai ini dalam semua klausa LOCATION.

Sintaksis

Aktifkan ekstensi PXF satu kali per database:

CREATE EXTENSION pxf;

Buat tabel eksternal:

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

Untuk sintaksis lengkap CREATE EXTERNAL TABLE, lihat Sintaksis SQL.

Parameter klausa LOCATION

Parameter	Deskripsi
`pxf://`	Awalan protokol PXF. Jangan ubah.
`<path-to-data>`	Untuk HDFS: jalur mutlak ke file atau direktori. Untuk Hive: `<database>.<table>` (misalnya, `default.sales_info`).
`PROFILE=<profile>`	Profil PXF yang sesuai dengan sumber data dan format Anda. Lihat Profil HDFS yang didukung dan Profil Hive yang didukung.
`SERVER=<server_name>`	Identifier konfigurasi server PXF. Disediakan oleh dukungan teknis Alibaba Cloud.

Kueri data HDFS

Profil HDFS yang didukung

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

Contoh: Baca file teks HDFS

Buat file uji di 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

# Buat direktori target
hdfs dfs -mkdir -p /data/pxf_examples

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

# Verifikasi unggahan
hdfs dfs -cat /data/pxf_examples/pxf_hdfs_simple.txt

Di AnalyticDB for PostgreSQL, buat tabel eksternal yang mengarah ke file tersebut.

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

Jalankan kueri terhadap tabel tersebut.

SELECT * FROM pxf_hdfs_textsimple;

Output yang diharapkan:

 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)

Contoh: Tulis data ke HDFS

Untuk menulis data dari AnalyticDB for PostgreSQL ke HDFS, buat tabel eksternal yang dapat ditulis.

Buat direktori target di HDFS.
Anda harus memiliki izin menulis pada direktori ini untuk menjalankan pernyataan INSERT dari AnalyticDB for PostgreSQL.
```
hdfs dfs -mkdir -p /data/pxf_examples/pxfwritable_hdfs_textsimple1
```

Buat tabel eksternal yang dapat ditulis dan masukkan data.

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

Verifikasi bahwa data telah ditulis ke HDFS.

# Daftar file dalam direktori
hdfs dfs -ls /data/pxf_examples/pxfwritable_hdfs_textsimple1

# Cetak isi file
hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/*

Output yang diharapkan:

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

Kueri data Hive

Profil Hive yang didukung

Format	Profil yang didukung
TextFile	`Hive`, `HiveText`
SequenceFile	`Hive`
RCFile	`Hive`, `HiveRC`
ORC	`Hive`, `HiveORC`, `HiveVectorizedORC`
Parquet	`Hive`

Profil Hive mendukung semua format penyimpanan yang tercantum di atas. Gunakan sub-profil spesifik (HiveText, HiveRC, HiveORC, HiveVectorizedORC) sesuai kebutuhan fungsionalitas tertentu.

Pilih antara HiveORC dan HiveVectorizedORC

Kedua profil membaca tabel Hive berformat ORC. Pilih berdasarkan kebutuhan kueri Anda:

Kemampuan	HiveORC	HiveVectorizedORC
Baris yang dibaca per batch	1	Hingga 1.024
Proyeksi kolom	Ya	Tidak
Tipe kompleks (`array`, `map`, `struct`, `union`)	Ya	Tidak
`timestamp` tipe data	Ya	Tidak

Contoh: Gunakan profil Hive

Profil Hive kompatibel dengan semua format penyimpanan Hive.

Hasilkan data sampel dan muat ke 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

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

Di AnalyticDB for PostgreSQL, aktifkan ekstensi PXF dan buat tabel eksternal.

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

Jalankan kueri terhadap tabel tersebut.

SELECT * FROM salesinfo_hiveprofile;

Output yang diharapkan:

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

Contoh: Gunakan profil HiveText

HiveText membaca tabel Hive berformat TextFile dan menggunakan delimiter teks alih-alih formatter 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;

Output yang diharapkan:

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

Contoh: Gunakan profil HiveRC

Buat tabel Hive berformat RCFile.

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

-- Impor data dari tabel yang sudah ada
INSERT INTO TABLE sales_info_rcfile SELECT * FROM sales_info;

-- Verifikasi data
SELECT * FROM sales_info_rcfile;

Buat tabel eksternal di AnalyticDB for PostgreSQL dan jalankan kueri terhadapnya.

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;

Output yang diharapkan:

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

Contoh: Gunakan profil HiveORC

Gunakan HiveORC ketika Anda memerlukan proyeksi kolom atau perlu menjalankan kueri terhadap tabel yang memiliki tipe kompleks (array, map, struct, union).

Buat tabel Hive berformat ORC.

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

-- Verifikasi data
SELECT * FROM sales_info_ORC;

Buat tabel eksternal di AnalyticDB for PostgreSQL dan jalankan kueri terhadapnya.

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;

Output yang diharapkan:

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

Contoh: Gunakan profil HiveVectorizedORC

Gunakan HiveVectorizedORC untuk kueri sederhana pada tabel ORC besar yang tidak memerlukan proyeksi kolom, dukungan tipe kompleks, atau tipe data 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;

Output yang diharapkan:

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

Contoh: Jalankan kueri terhadap tabel Hive berformat Parquet

Buat tabel Hive berformat Parquet.

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

Buat tabel eksternal di AnalyticDB for PostgreSQL dan jalankan kueri terhadapnya.

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;

Output yang diharapkan:

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