文档

Database外表联邦分析

更新时间:

云原生数据仓库AnalyticDB PostgreSQL(简称ADB PG)支持通过JDBC的方式访问Oracle、PostgreSQL、MySQL外部数据源。

说明
  • 本特性只支持存储弹性模式实例,且需要云原生数据仓库 AnalyticDB PostgreSQL 版实例和目标访问的外部数据源处于同一个VPC网络。

  • 2020年9月6日前申请的存量存储弹性模式实例,由于网络架构不同,无法与外部数据库网络打通,无法使用该特性。如需使用,请联系后台技术人员,重新申请实例,迁移数据。

Database外表联邦分析

前提条件:配置SEVER端

由于不同用户的需求配置不尽相同。如果您需要通过JDBC的方式访问外部数据源进行联邦分析,请提交工单云原生数据仓库 AnalyticDB PostgreSQL 版后台技术人员进行配置。以下为提交工单时需要提交的对应文件。

连接对象

提交工单内容

SQL Database(PostgreSQL, Mysql, Oracle)

JDBC的连接串、目标访问外部数据源的用户名和密码。

使用Database外表联邦分析

  • 创建扩展

    • 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

      外表访问表名,例如 public.test_a

      PROFILE [&<custom-option>=<value>[...]]

      访问外部数据的配置。

      使用JDBC方式访问外部数据库时使用PROFILE=Jdbc

      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数据库

云原生数据仓库 AnalyticDB PostgreSQL 版后台技术人员配置完成后,您可以在云原生数据仓库 AnalyticDB PostgreSQL 版数据库中采用以下SQL语句创建外表并查询。

postgres=# CREATE EXTERNAL TABLE pxf_ext_pg(a int, b int)
  LOCATION ('pxf://public.t?PROFILE=Jdbc&SERVER=postgresql')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';

postgres=# select * from pxf_ext_pg;
   a   |   b
-------+-------
     1 |     2
     2 |     4
     3 |     6
     4 |     8
     5 |    10
     6 |    12
     7 |    14
--more--      

LOCATION各字段含义说明:

  • pxf:// :pxf 协议,固定值。

  • public.t:代表数据库public下的表名为t的表。

  • PROFILE=Jdbc: 代表使用JDBC访问外部数据源

  • SERVER=postgresql云原生数据仓库 AnalyticDB PostgreSQL 版的后台技术人员将提供该选项。后台人员会根据您提交的工单要求进行相对应配置。此例中 SERVER=postgresql 代表使用PXF_SERVER/postgresql/下的配置文件来支持连接。

  • FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'):外部数据源格式配置项。查询外部数据源table时使用CUSTOM,并与FORMATTER='pxfwritable_import'搭配。

CREATE EXTERNAL TABLE pxf_ext_test_a( id int,name varchar)
  LOCATION ('pxf://public.test_a?PROFILE=Jdbc&server=postgresql')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';

支持的数据类型

  • INTEGER, BIGINT, SMALLINT

  • REAL, FLOAT8

  • NUMERIC

  • BOOLEAN

  • VARCHAR, BPCHAR, TEXT

  • DATE

  • TIMESTAMP

  • BYTEA

  • 本页导读 (1)
文档反馈