雲原生資料倉儲AnalyticDB PostgreSQL版支援通過外表訪問Hadoop生態的外部資料源（包括HDFS與Hive）。
注意事項
本特性只支援儲存彈性模式執行個體，且需要AnalyticDB PostgreSQL版執行個體和目標訪問的外部資料源處於同一個VPC網路。
2020年9月6日前申請的存量儲存彈性模式執行個體，由於網路架構不同，無法與外部Hadoop生態的資料來源網路打通，無法使用該特性。如需使用，請聯絡後台技術人員，重新申請執行個體，遷移資料。
前提條件：配置SERVER端
由於不同使用者的配置需求不盡相同，如果您需要訪問Hadoop生態的外部資料源進行聯邦分析，請提交工單由AnalyticDB PostgreSQL版後台技術人員進行配置。以下為提交工單時需要提交的對應檔案。
連線物件 | 提交工單內容 |
Hadoop(HDFS, HIVE, HBase) | core-site.xml 、hdfs-site.xml 、mapred-site.xml 、yarn-site.xml、hive-site.xml 說明 Kerberos認證時還需提供 keytab、krb5.conf等設定檔 |
基本文法
建立擴充
CREATE extension pxf;建立外表
CREATE EXTERNAL TABLE <table_name> ( <column_name> <data_type> [, ...] | LIKE <other_table> ) LOCATION('pxf://<path-to-data>?PROFILE[&<custom-option>=<value>[...]]&[SERVER=value]') FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);
建立EXTERNAL TABLE文法請參見CREATE EXTERNAL TABLE。
訪問HDFS資料
支援格式：
資料格式 | PROFILE |
text | hdfs:text |
csv | hdfs:text:multi 、hdfs:text |
Avro | hdfs:avro |
JSON | hdfs:json |
Parquet | hdfs:parquet |
AvroSequenceFile | hdfs:AvroSequenceFile |
SequenceFile | hdfs:SequenceFile |
FORMAT與 formatting-properties的介紹，請參見建立外表部分 。
樣本HDFS訪問檔案
HDFS構建測試資料檔案
pxf_hdfs_simple.txt。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 # 構建目錄 hdfs dfs -mkdir -p /data/pxf_examples # 推檔案至hdfs hdfs dfs -put /tmp/pxf_hdfs_simple.txt /data/pxf_examples/ # 查看檔案 hdfs dfs -cat /data/pxf_examples/pxf_hdfs_simple.txtAnalyticDB PostgreSQL版執行個體建立外表並訪問。
postgres=# 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','); postgres=# select * from pxf_hdfs_textsimple; 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)LOCATION各欄位含義說明：
pxf:// ：pxf 協議，固定值。
data/pxf_examples/pxf_hdfs_simple.txt：代表HDFS中的 pxf_hdfs_simple.txt 檔案。
PROFILE=hdfs:text: 代表使用 PROFILE=hdfs:text 訪問HDFS。
SERVER=hdp3：後台技術人員會提供該選項。代表使用PXF_SERVER/hdp3/下的設定檔來支援PXF訪問 HDFS。
FORMAT 'TEXT' (delimiter=E',') ：外部資料源格式配置項，代表以
,分割符，格式為TEXT。
詳細欄位說明請參見基本文法。
樣本：向HDFS寫入
(TEXT,CSV)前提條件：在HDFS上構建資料目錄/data/pxf_examples/pxfwritable_hdfs_textsimple1。
hdfs dfs -mkdir -p /data/pxf_examples/pxfwritable_hdfs_textsimple1說明在AnalyticDB PostgreSQL版執行insert的使用者需要有HDFS該目錄的寫入許可權。
AnalyticDB PostgreSQL版執行個體上建立可寫外表。
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 );HDFS查看。
#查看檔案 hdfs dfs -ls /data/pxf_examples/pxfwritable_hdfs_textsimple1 #查看資料 hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/* Frankfurt,Mar,777,3956.98 Cleveland,Oct,3812,96645.37
訪問Hive資料
資料格式 | PROFILE |
TextFile | Hive, HiveText |
SequenceFile | Hive |
RCFile | Hive, HiveRC |
ORC | Hive, HiveORC, HiveVectorizedORC |
Parquet | Hive |
FORMAT與formatting-properties的介紹，請參見建立外表部分。
樣本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.txtHive建立table。
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; hive> LOAD DATA LOCAL INPATH '/tmp/pxf_hive_datafile.txt' INTO TABLE sales_info; hive> SELECT * FROM sales_info;AnalyticDB PostgreSQL版執行個體訪問資料。
postgres=# create extension pxf; postgres=# 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'); postgres=# SELECT * FROM salesinfo_hiveprofile; 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 ......LOCATION各欄位含義說明：
pxf://：pxf 協議，固定值。
default.sales_info：代表 Hive中default資料庫下的 sales_info 表。
PROFILE=Hive：代表使用PROFILE=Hive 訪問 Hive。
SERVER=hdp3：後台技術人員會提供該選項,代表使用PXF_SERVER/hdp3/下的設定檔來支援PXF訪問 Hive。
FORMAT 'custom' (formatter='pxfwritable_import')：外部資料源格式配置項，讀取Hive中sales_info表時採用 custom ，並與 formatter='pxfwritable_import' 搭配。
詳細欄位說明請參見基本文法。
樣本HiveText
postgres=# 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','); postgres=# select * from salesinfo_hivetextprofile; location | month | num_orders | total_sales ---------------+-------+------------+------------- Prague | Jan | 101 | 4875.33 Rome | Mar | 87 | 1557.39 Bangalore | May | 317 | 8936.99 ......樣本HiveRC
Hive建立table。
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; OK ##匯入資料 hive> INSERT INTO TABLE sales_info_rcfile SELECT * FROM sales_info; ##查看 hive> SELECT * FROM sales_info_rcfile;AnalyticDB PostgreSQL版執行個體訪問資料。
postgres=# 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','); postgres=# SELECT location, total_sales FROM salesinfo_hivercprofile; location | total_sales ---------------+------------- Prague | 4875.33 Rome | 1557.39 Bangalore | 8936.99 ......
樣本HiveORC
ORC可以有兩種配置HiveORC和HiveVectorizedORC 。
一次讀取一行資料。
支援列投影。
支援複雜類型, 可以訪問由數組、映射、結構和聯合資料類型組成的Hive表。
樣本
Hive建立table。
hive> CREATE TABLE sales_info_ORC (location string, month string, number_of_orders int, total_sales double) STORED AS ORC; hive> INSERT INTO TABLE sales_info_ORC SELECT * FROM sales_info; hive> SELECT * FROM sales_info_ORC;AnalyticDB PostgreSQL版執行個體訪問資料。
postgres=# 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'); postgres=# SELECT * FROM salesinfo_hiveORCprofile; ...... Prague | Dec | 333 | 9894.77 Bangalore | Jul | 271 | 8320.55 Beijing | Dec | 100 | 4248.41 (60 rows) Time: 420.920 ms
樣本HiveVectorizedORC
一次最多讀取1024行資料。
不支援列投影。
不支援複雜類型或時間戳記資料類型。
AnalyticDB PostgreSQL版執行個體訪問資料。
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; 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 ......樣本Parquet
Hive建立table。
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;AnalyticDB PostgreSQL版執行個體訪問資料。
postgres=# 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'); postgres=# SELECT month, number_of_orders FROM pxf_parquet_table; month | number_of_orders -------+------------------ Jan | 101 Mar | 87 May | 317 Jul | 411 Sept | 156 Nov | 159 ......