云原生数据仓库 AnalyticDB PostgreSQL (简称 ADB PG)支持访问 Hadoop 生态的外部数据源。
- 本特性只支持存储弹性模式实例,且需要ADB PG实例和目标访问的外部数据源处于同一个VPC网络。
- 2020年9月6日前申请的存量存储弹性模式实例,由于网络架构不同,无法与外部 Hadoop 生态的数据源网络打通,无法使用该特性。如需使用,请联系后台技术人员,重新申请实例,迁移数据。

前提条件:配置SERVER端
由于不同用户的配置需求不尽相同,如果您需要访问 Hadoop 生态的外部数据源进行联邦分析,请提交工单由ADB PG后台技术人员进行配置。以下为提交工单时需要提交的对应文件。
连接对象 | 提交工单内容 |
---|---|
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。
参数 说明 path-to-data 外表访问的目录,文件名,表名 例如 //data/pxf_examples/pxf_hdfs_simple.txt PROFILE [&<custom-option>=<value>[...]] PXF访问外部数据的配置。 支持格式包括:Jdbc | hdfs:text | hdfs:text:multi | hdfs:avro | hdfs:json | hdfs:parquet | hdfs:AvroSequenceFile | hdfs:SequenceFile | HiveText | HiveRC | HiveORC | HiveVectorizedORC | HBase
FORMAT '[TEXT|CSV|CUSTOM]' 读取数据的格式。取值范围: formatting-properties 与特定文件数据对应的格式化选项: formatter 或者 delimiter(分割符) - 与CUSTOM搭配
- formatter='pxfwritable_import'
- formatter='pxfwritable_export'
- 与TEXT|CSV搭配
- delimiter=E'\t'
- delimiter ':'
说明 escape 时需要加上 E
SERVER 配置服务端文件的位置,该部分由后台技术人员操作后反馈给用户。 - postgresql
- hdp3
- 与CUSTOM搭配
访问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.txt
ADB PG实例创建外表并访问
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
说明 在ADB PG执行insert的用户需要有HDFS该目录的写入权限。- ADB PG实例上创建可写外表。
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
- ADB PG实例上创建可写外表。
访问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.txt
- Hive 创建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;
- ADB PG实例访问数据
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;
- ADB PG 实例访问数据。
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 ......
- Hive创建table。
- 示例 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;
- ADB PG 实例访问数据。
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行数据。
- 不支持列投影。
- 不支持复杂类型或时间戳数据类型。
ADB PG 实例访问数据
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;
- ADB PG 实例访问数据。
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 ......
- Hive创建table。
在文档使用中是否遇到以下问题
更多建议
匿名提交