文档

异构数据源访问

更新时间:

若您需要通过AnalyticDB PostgreSQL版访问外部异构数据源(HDFS、Hive和JDBC)时,可以使用异构数据源访问功能将外部数据转换为AnalyticDB PostgreSQL版数据库优化后的格式进行查询和分析。

功能说明

外部数据源管理提供高性能的结构化和非结构化数据并行传输服务。其中,异构数据源访问支持通过AnalyticDB PostgreSQL版数据库以统一的SQL接口查询和分析存储在外部系统中的数据。提供以下功能:

  • 多种数据存储访问:支持多种数据存储系统,允许AnalyticDB PostgreSQL版数据库直接访问存储在HDFS、Hive、MySQL,PostgreSQL,PolarDB MySQL等多种外部系统中的数据。

  • 数据格式透明:支持常见的数据格式,如CSV、ORC、Parquet、JSON、Avro等。

  • 高效数据处理:可利用AnalyticDB PostgreSQL版数据库的并行处理能力,实现高效的数据读写操作。

  • 简化数据集成:可通过编写SQL语句来访问外部数据源,不必担心不同数据源的连接细节。

版本限制

  • AnalyticDB PostgreSQL 6.0版实例需为v6.6.0.0及以上版本。

  • AnalyticDB PostgreSQL 7.0版实例需为v7.0.2.3及以上版本。

  • AnalyticDB PostgreSQL版Serverless模式实例需为v2.1.1.3及以上版本。

使用流程

  1. 步骤一:开启外部数据服务

  2. 步骤二:配置数据源

  3. 步骤三:读写外表

步骤一:开启外部数据服务

  1. 登录云原生数据仓库AnalyticDB PostgreSQL版控制台
  2. 在控制台左上角,选择实例所在地域。
  3. 找到目标实例,单击实例ID。
  4. 在左侧导航栏,单击外部数据源管理

  5. 异构数据源访问页签,单击开启外部数据服务

  6. 在弹窗中配置以下参数,并单击确定

    参数

    说明

    名称

    外部数据服务名称,支持任意字符。

    服务描述

    描述服务信息。

步骤二:配置数据源

异构数据源访问支持配置HDFS、Hive和JDBC数据源。

配置HDFS数据源

前提条件

  • 自建Hadoop集群或具有Hadoop-Common、HDFS和YARN服务的E-MapReduce集群需与AnalyticDB PostgreSQL版实例在同一VPC。

  • 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至Hadoop集群所在的安全组。为确保AnalyticDB PostgreSQL版与Hadoop间的网络端口畅通,需将以下异构数据源访问功能会用到的Hadoop服务默认的端口全部加入安全组。具体操作,请参见添加安全组规则

    服务

    端口号

    Namenode IPC

    • Hadoop2.x版本:8020

    • Hadoop3.x版本:9820

    • E-MapReduce集群:9000

    具体端口号请参见配置文件hdfs-site.xml -> dfs.namenode.rpc-address中的端口号。

    DataNode IPC

    • Hadoop2.x版本:50020

    • Hadoop3.x版本:9867

    具体端口号请参见配置文件hdfs-site.xml -> dfs.datanode.ipc.address中的端口号。

    DataNode

    • Hadoop2.x版本:50010

    • Hadoop3.x版本:9866

    具体端口号请参见配置文件hdfs-site.xml -> dfs.datanode.address中的端口号。

    Hive metastore

    9083

    具体端口号请参见配置文件hive-site.xml -> hive.metastore.uris中的端口号。

    Hive HiveServer

    10000

    具体端口号请参见配置文件hive-site.xml -> hive.server2.thrift.port中的端口号。

    Zookeeper

    2181

    具体端口号请参见配置文件zoo.cfg ->

    clientPort=xxxx中的端口号。

    Kerberos Server

    88(传输加密和认证) 和749(kadmin)

    具体端口号请参见配置文kdc.conf ->

    kdc_ports和kdc.conf -> kadmind_port中的端口号。

    JDBC(MySQL或PostgreSQL)

    3306、1521、5432等

    ICMP协议

    用于网络连通性校验,在安全组规则的入方向与出方向放行ICMP协议。

    说明

    AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

准备测试数据集

准备测试数据集,并执行hadoop fs -put <本地文件名称> <文件保存在Hadoop集群的路径>命令将数据集保存在Hadoop集群路径。

例如,将本地文件/home/user/file.txt上传到HDFS的/tmp目录下,执行hadoop fs -put /home/user/file.txt /tmp/file.tx即可。测试数据集内容如下:

1,test1,1,100
2,test2,2,90

操作步骤

  1. 异构数据源访问页签,单击新增数据源的下拉菜单,选择Hadoop数据源

  2. 新增Hadoop数据源页面页面依次配置网络&安全组配置文件初始化

    1. 配置网络&安全组

      参数

      说明

      数据源名称

      • 可包含英文大小写字母、数字、中划线(-)、下划线(_)。

      • 以数字或者英文字母开始和结尾。

      • 长度不超过50字符。

      数据源描述

      数据源描述。

      数据源类型

      • 当目标数据源为E-MapReduce集群,选择阿里云E-MapReduce

      • 当目标数据源为自建Hadoop集群,选择自建Hadoop

      数据库类型

      选择HDFS

      数据来源

      E-MapReduce集群ID。

      说明

      自建Hadoop集群无此参数。

      网络校验

      单击网络校验,提示网络连通。

    2. 单击下一步,填写配置文件

      参数

      说明

      获取方式

      参数配置

      • 若目标数据源为E-MapReduce集群,支持自动配置获取

      • 若目标数据源为自建Hadoop集群需要自行粘贴配置文件信息。

      hdfs-site.xml

      HDFS配置项,如副本因子和块大小等。

      自建Hadoop集群xml文件一般位于以下两个位置之一:

      • conf目录:在早期版本的Hadoop中,这些配置文件通常放在Hadoop安装目录下的conf子目录中。

      • etc/hadoop目录:在较新版本的Hadoop中,配置文件通常放在 etc/hadoop目录下。这个目录位于 Hadoop的主安装目录下。

      core-site.xml

      Hadoop核心配置项,如I/O设置和文件系统的配置等。

      yarn-site.xml

      YARN配置项,负责集群资源管理和作业调度等。

      mapred-site.xml

      用于配置MapReduce作业的参数,如作业执行器和任务优化参数等。

      /etc/host文件

      解析Hadoop集群中每个节点的主机名到IP。

      有以下两种获取方式:

      • 方式一:依次登录Hadoop集群的每台机器,获取/etc/hosts文件中的本节点IP和主机名的映射关系并填写。

      • 方式二:登录Hadoop namenode所在的机器,运行#!/bin/bash脚本,填写返回结果。返回结果如下:

        echo $(hostname -i) $(hostname -f)
        hdfs dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'
    3. 单击保存,并单击左下角完成

      等待3~5分钟,数据源状态为运行中时,HDFS数据源可用。

  3. 单击目标数据源操作列的校验

  4. 服务配置校验窗口中,填写数据集所在Hadoop集群路径(例如:/tmp/file.txt),并单击校验

    提示配置校验成功后,您可以继续操作读写HDFS外表。

配置Hive数据源

前提条件

  • 自建Hive集群或具有Hadoop-Common、HDFS、Hive和YARN服务的E-MapReduce集群需与AnalyticDB PostgreSQL版实例在同一VPC。

  • 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至Hive集群所在的安全组。具体操作,请参见添加安全组规则

    说明

    AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

准备测试数据集

准备测试数据集,并执行hadoop fs -put <本地文件名称> <文件保存在Hadoop集群的路径>命令将数据集保存在Hadoop集群路径。

例如,将本地文件/home/user/file.txt上传到Hive的/tmp目录下,执行hadoop fs -put /home/user/file.txt /tmp/file.tx即可。测试数据集内容如下:

1,test1,1,100
2,test2,2,90

操作步骤

  1. 异构数据源访问页签,单击新增数据源的下拉菜单,选择Hadoop数据源

  2. 新增Hadoop数据源页面页面依次配置网络&安全组配置文件初始化

    1. 配置网络&安全组

      参数

      说明

      数据源名称

      • 可包含英文大小写字母、数字、中划线(-)、下划线(_)。

      • 以数字或者英文字母开始和结尾。

      • 长度不超过50字符。

      数据源描述

      数据源描述。

      数据源类型

      • 当目标数据源为E-MapReduce集群,选择阿里云E-MapReduce

      • 当目标数据源为自建Hive集群,选择自建Hadoop

      数据库类型

      选择Hive

      实例名称

      E-MapReduce集群ID。

      说明

      自建Hive集群无此参数。

      网络校验

      单击网络校验,提示网络连通。

    2. 单击下一步,填写配置文件

      参数

      说明

      获取方式

      参数配置

      • 若目标数据源为E-MapReduce集群,支持自动配置获取

      • 若目标数据源为自建Hive集群需要自行粘贴配置文件信息。

      hdfs-site.xml

      HDFS配置项,如副本因子和块大小。

      自建Hive集群xml文件一般位于以下两个位置之一:

      • conf目录:在标准安装中,这些配置文件通常放在Hive安装目录下的conf子目录中。

      • etc/hadoop目录:在集成了Hadoop和Hive的安装中,也可能放在Hadoop的配置目录中,以确保Hive能够正确地与Hadoop集群进行交互。

      core-site.xml

      Hadoop核心配置项,如I/O设置和文件系统的配置等。

      yarn-site.xml

      YARN配置项,负责集群资源管理和作业调度等。

      mapred-site.xml

      用于配置MapReduce作业的参数,如作业执行器和任务优化参数等。

      hive-site.xml

      Hive配置项,如元数据存储的连接信息、Hive运行时的内存和性能设置等。

      /etc/host文件

      解析Hive集群中每个节点的主机名到IP。

      有以下两种获取方式:

      • 方式一:依次登录Hive集群的每台机器,获取/etc/hosts文件中的本节点IP和主机名的映射关系并填写。

      • 方式二:登录Hive namenode所在的机器,运行#!/bin/bash脚本,填写返回结果。返回结果如下:

        echo $(hostname -i) $(hostname -f)
        hive dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'
    3. 单击保存,并单击左下角完成

      等待3~5分钟,数据源状态为运行中时,Hive数据源可用。

  3. 单击目标数据源操作列的校验

  4. 服务配置校验窗口中,填写数据集所在Hadoop集群路径(例如:/tmp/file.txt),并单击校验

    提示配置校验成功后,您可以继续操作读写Hive外表。

配置JDBC数据源

JDBC数据源指兼容MySQL和PostgreSQL的数据源,如RDS MySQL、RDS PostgreSQL、PolarDB MySQL、PolarDB PostgreSQL等。

本文以配置RDS MySQL数据源为例。

前提条件

  • 数据源所在实例(RDS MySQL实例)需与AnalyticDB PostgreSQL版实例在同一VPC。

  • 已在RDS MySQL实例中创建数据库和账号。具体操作,请参见创建数据库和账号

  • 已将AnalyticDB PostgreSQL版实例的专有网络交换机IP添加至RDS MySQL实例的白名单中。具体操作,请参见设置IP白名单

    说明

    AnalyticDB PostgreSQL版实例的专有网络交换机IP可在外部数据源管理页面获取。

操作步骤

  1. 异构数据源访问页签,单击新增数据源的下拉菜单,选择JDBC数据源

  2. 新增JDBC数据源页面页面配置如下参数。

    参数

    说明

    数据源名称

    • 可包含英文大小写字母、数字、中划线(-)、下划线(_)。

    • 以数字或者英文字母开始和结尾。

    • 长度不超过50字符。

    数据库类型

    选择MySQL

    数据源描述

    数据源描述。

    JDBC连接串

    连接RDS MySQL实例的JDBC连接串,格式如下:

    jdbc:mysql://<servername>:<port>/<dbName>
    • <servername>:<port>:RDS MySQL实例的连接地址和对应的端口。

      • 若通过内网连接,需输入RDS MySQL实例的内网地址和内网端口。

      • 若使用外网连接,需输入RDS MySQL实例的外网地址和外网端口。

      您可以在RDS MySQL实例的数据库连接页面查看。

      更多信息,请参见查看或修改连接地址和端口

    • <dbName>:RDS MySQL实例的数据库。

    用户名

    RDS MySQL实例的账号。

    登录密码

    账号对应的密码。

  3. 单击测试连接,提示网络连通后,单击左下角确定

    如果网络不通,请检查JDBC连接串地址填写是否正确。

    等待3~5分钟,数据源状态为运行中时,JDBC数据源可用。您可以继续操作读写JDBC外表。

步骤三:读写外表

根据您配置的数据源类型,选择不同外表语法读写数据。

HDFS外表

读写文本数据

语法

CREATE [WRITABLE | READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-hdfs-dir>
    ?PROFILE=hdfs:text[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

参数

参数

说明

[WRITABLE | READABLE]

  • 创建可写外表时,必须定义WRITABLE关键字。

  • 创建可读外表时,可定义READABLE关键字或省略。

READABLE

可读外表,可选。

path-to-hdfs-dir

HDFS数据存储目录的绝对路径。

PROFILE

指定HDFS外表数据格式,文本数据必须指定为hdfs:text

HDFS外表支持的数据格式,请参见附录1:HDFS外表数据格式

SERVER

外部数据源名称。登录云原生数据仓库AnalyticDB PostgreSQL版控制台,在外部数据源管理 > 异构数据源访问页面查看数据源名称。

如未指定,异构数据源访问将使用Default服务器。

FORMAT

定义文本格式。

  • path-to-hdfs-file引用纯文本分隔数据时,使用FORMAT 'TEXT'

  • path-to-hdfs-file引用逗号分隔数据时,使用FORMAT 'CSV'

delimiter

数据分隔符。

  • 对于FORMAT 'CSV',默认的delim_value为英文逗号(,)。

  • 对于FORMAT 'TEXT',默认的delim_value为竖线(|)。

  • 当分隔符为转义序列时,在delim_value前加上E,例如(delimiter=E'\t'), (delimiter ':')

DISTRIBUTED BY

分布策略。

如果您计划将现有AnalyticDB PostgreSQL版数据库表中的数据加载到外表,建议在外表中使用AnalyticDB PostgreSQL版表相同的分布策略或字段名,可以避免数据加载操作中Segment节点间额外的数据移动。

示例

  • 示例一:创建一个可写外表,在建表语句中指定好HDFS协议、格式及路径,并向表中写入数据。

    CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
    FORMAT 'TEXT' (delimiter='|');
    
    INSERT INTO pxf_hdfs_writable_table 
    SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);
  • 示例二:创建一个可读外表,在建表语句中指定好HDFS协议、格式及路径,并查询外表数据。

    CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
    FORMAT 'TEXT' (delimiter='|');
    
    SELECT * FROM pxf_hdfs_readable_table order by 1;

读写Parquet数据

重要

AnalyticDB PostgreSQL版数据库中读写Parquet基本数据类型,需要将Parquet数据值映射到相同类型的AnalyticDB PostgreSQL版数据库列。映射关系,请参见附录3:Parquet数据类型与AnalyticDB PostgreSQL数据类型映射关系

语法

CREATE [WRITABLE |READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<hdfs_dbName>.<hdfs_tableName>
    ?PROFILE=hdfs:parquet[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

参数

参数

说明

[WRITABLE | READABLE]

  • 创建可写外表时,必须定义WRITABLE关键字。

  • 创建可读外表时,可定义READABLE关键字或省略。

<hdfs_dbName>.<hdfs_tableName>

HDFS数据存储目录的绝对路径。

PROFILE

指定HDFS外表数据格式,Parquet格式数据必须指定为hdfs:parquet

HDFS外表支持的数据格式,请参见附录1:HDFS外表数据格式

SERVER

外部数据源名称。登录云原生数据仓库AnalyticDB PostgreSQL版控制台,在外部数据源管理 > 异构数据源访问页面查看数据源名称。

如未指定,异构数据源访问将使用Default服务器。

FORMAT

定义非文本格式。当数据为Parquet格式固定为CUSTOM。

FORMATTER

定义表为可读外表或可写外表。

  • 可写外表为pxfwritable_export。

  • 可读外表为pxfwritable_import。

DISTRIBUTED BY

分布策略。

如果您计划将现有AnalyticDB PostgreSQL版数据库表中的数据加载到外表,建议在外表中使用AnalyticDB PostgreSQL版表相同的分布策略或字段名,可以避免数据加载操作中Segment节点间额外的数据移动。

hdfs:parquet配置文件支持与编码和压缩有关的写入选项,可以在CREATE WRITABLE EXTERNAL TABLE LOCATION子句中指定以下自定义选项:

写入选项

描述

COMPRESSION_CODEC

压缩编码器别名。 用于写入Parquet数据受支持的压缩编码器包括: 

  • snappy(默认压缩编码器)

  • gzip

  • lzo

  • uncompressed

ROWGROUP_SIZE

行组大小,单位字节(Byte),默认值:8 * 1024 * 1024。

Parquet文件由一个或多个行组组成,将数据逻辑划分为行。

PAGE_SIZE

页面大小,单位字节(Byte),默认值:1024 * 1024。

行组由划分为页面的列块组成。

DICTIONARY_PAGE_SIZE

字典页面大小,单位字节(Byte),默认值:512 * 1024。

当异构数据源访问写入Parquet文件时,默认启用字典编码。每列、每行组只有一个字典页面。

PARQUET_VERSION

Parquet版本,支持:

  • v1(默认版本)

  • v2

示例

  • 示例一:创建一个可写外表,并在建表语句中指定好HDFS协议、Parquet格式及路径,并向表中写入数据。

    CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_parquet_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
    
    INSERT INTO pxf_hdfs_writable_parquet_table 
    SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);
  • 示例二:创建一个可读外表,并在建表语句中指定好HDFS协议、Parquet格式及路径,并查询表中数据。

    CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_parquet_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
    
    SELECT * FROM pxf_hdfs_readable_parquet_table;

Hive外表

若需在AnalyticDB PostgreSQL版数据库中展示Hive数据,请将Hive数据值映射到相同类型的AnalyticDB PostgreSQL版数据库列。映射关系,请参见附录4:Hive数据类型与AnalyticDB PostgreSQL数据类型映射关系

语法

可写Hive外表与可读Hive外表支持的功能有差异,请根据需要选择合适的语法。

  • 可写外表

    由于在新版本中Hive相关数据写入接口进行了较大变更,与早期版本的底层逻辑不同,可能在使用新版本Hive外表进行创建或写入数据时报错,建议使用HDFS方式创建外表进行写入。语法详情请参见HDFS外表

  • 可读外表

    CREATE [READABLE] EXTERNAL TABLE <table_name>
        ( <column_name> <data_type> [, ...] | LIKE <other_table> )
    LOCATION ('pxf://<hive-db-name>.<hive-table-name>
        ?PROFILE=Hive|HiveText|HiveRC|HiveORC|HiveVectorizedORC[&SERVER=<server_name>]'])
    FORMAT 'CUSTOM|TEXT' (FORMATTER='pxfwritable_import' | delimiter='<delim>')

参数

参数

说明

READABLE

创建可读外表时,可定义READABLE关键字或省略。

hive_dbName

Hive数据库的名称。 如果省略,默认为Default的Hive数据库.

hive_tableName

Hive表的名称。

PROFILE

指定Hive外表数据格式,必须指定为HiveHiveTextHiveRCHiveORCHiveVectorizedORC之一。

Hive外表支持的数据格式,请参见附录2:Hive外表数据格式

重要

HiveVectorizedORC配置文件不支持Timestamp数据类型。

SERVER

外部数据源名称。登录云原生数据仓库AnalyticDB PostgreSQL版控制台,在外部数据源管理 > 异构数据源访问页面查看数据源名称。

如未指定,异构数据源访问将使用Default服务器。

FORMAT

  • 当PROFILE为HiveHiveORCHiveVectorizedORC时,FORMAT子句必须指定为'CUSTOM',且CUSTOM需要内置pxfwritable_impor的FORMATTER。

  • 当PROFILE为HiveText和HiveRC时,FORMAT子句必须指定为'TEXT',且需在delimiter ='<delim>'中指定单个ASCII定界符。

示例

  1. 登录Hive集群,创建Hive表。

    hive> CREATE EXTERNAL TABLE IF NOT EXISTS pxf_hive_demo_table
    (
    a int,
    b int,
    c int
    )
    row format delimited fields terminated by '|'
    stored as textfile
    location '/data/demo/pxf_hdfs_demo_table';
  2. 登录AnalyticDB PostgreSQL版数据库,创建可写Hive外表,并向外表插入数据。

    postgres> CREATE WRITABLE EXTERNAL TABLE pxf_hive_writable_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
    FORMAT 'TEXT' (delimiter='|');
    
    INSERT INTO pxf_hive_writable_table 
    SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);
  3. 创建可读Hive外表,并查询外表数据。

    postgres> CREATE EXTERNAL TABLE pxf_hive_readable_table
    ( 
    a int,
    b int,
    c int
    )
    LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
    FORMAT 'TEXT' (delimiter='|');
    
    SELECT * FROM pxf_hive_readable_table;

JDBC外表

支持的数据类型

JDBC连接器支持以下数据类型:

  • INTEGER,BIGINT,SMALLINT

  • REAL,FLOAT8

  • NUMERIC

  • BOOLEAN

  • VARCHAR,BPCHAR,TEXT

  • DATE

  • TIMESTAMP

  • BYTEA

重要

JDBC连接器不支持读取或写入以字节数组(Byte[])存储的Hive数据。

语法

使用以下语法创建外表并使用JDBC连接器读取或写入数据,如需访问远程SQL数据库中的数据,可以创建一个引用该远程数据库表的可读或可写的

AnalyticDB PostgreSQL版

外表。

AnalyticDB PostgreSQL版

外表、远程数据库表和查询结果元组必须具有相同的列名称和列类型。

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<external_tableName>?PROFILE=Jdbc[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');

参数

参数

说明

external_tableName

外部表的全名。取决于外部SQL数据库,可能包括模式名称和表名称。

PROFILE

指定JDBC外表数据格式,必须指定为Jdbc

SERVER

外部数据源名称。登录云原生数据仓库AnalyticDB PostgreSQL版控制台,在外部数据源管理 > 异构数据源访问页面查看数据源名称。

如未指定,异构数据源访问将使用Default服务器。

custom-option

自定义选项,详情请参见JDBC自定义选项

FORMAT

固定为'CUSTOM'。支持以下FORMATTER函数:

  • 用于读取操作的内置'pxfwritable_import'函数。

  • 用于写入操作的内置'pxfwritable_export'函数。

JDBC自定义选项

您可以在LOCATION URI中添加JDBC连接器自定义选项,并在每个选项前加上&符号。JDBC支持的custom-option包括:

选项名称

操作

描述

BATCH_SIZE

Write

标识要批处理到外部SQL数据库的INSERT操作数量。默认开启批处理,默认值:100,需为整数。 异构数据源访问始终会验证BATCH_SIZE选项。

  • 当外部数据库的JDBC驱动程序支持批处理时,批量INSERT操作可能会大大提升性能。如需禁用批处理或修改批处理大小的值,在创建外表时按如下规则指定BATCH_SIZE:

    • BATCH_SIZE=0或BATCH_SIZE=1时,将关闭批处理。

    • BATCH_SIZE=n(n>1)时,将BATCH_SIZE设置为n。

  • 当外部数据库的JDBC驱动程序不支持批处理时,JDBC连接器的行为取决于BATCH_SIZE设置,如下所述:

    • BATCH_SIZE 省略,JDBC连接器插入时不使用批处理。

    • BATCH_SIZE=n(n>1),INSERT操作失败并且连接器返回错误。

FETCH_SIZE

Read

标识从外部SQL数据库读取时要缓冲的行数。读取行批处理默认为启用,默认值:1000,需为整数。

要修改默认的获取值大小,请在创建外表时指定FETCH_SIZE,如FETCH_SIZE=5000

如果外部数据库JDBC驱动程序不支持读取时批处理,则必须通过设置FETCH_SIZE=0来禁用读取行批处理。

QUERY_TIMEOUT

Read或Write

标识JDBC驱动程序等待语句执行的时间。单位:(s)。 默认值:无限的,需为整数。

POOL_SIZE

Write

在INSERT操作中启动线程池,并标识线程池中的线程数。默认线程池为禁用。

建议将批处理(BATCH_SIZE)和线程池一起使用,当一起使用时,每个线程将接收并处理一批完整的数据。如果仅使用线程池而不使用批处理,则线程池中的每个线程都恰好接收一个元组。

当线程池中的任一线程失败时,JDBC连接器返回一个错误。请注意INSERT操作失败,部分数据可能会写入外部数据库表中。

要禁用或启动线程池并设置线程池大小,在创建外表时按如下规则指定POOL_SIZE:

  • POOL_SIZE=n(n<1):线程池大小是系统中的CPU数量。

  • POOL_SIZE=1:关闭线程池。

  • POOL_SIZE=n(n>1):启动线程池并设置线程池大小为n。

PARTITION_BY

Read

启用分区读。 只能指定一个分区列,格式为<column_name>:<column_type>。JDBC连接器支持DateIntEnum<column_type>值。 如果您未指定PARTITION_BY列,则单个JDBC连接器为读取请求提供服务。

在创建JDBC外表时,PARTITION_BY子句的示例如下:

&PARTITION_BY=id:int&RANGE=1:100&INTERVAL=5
&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1
&PARTITION_BY=createdate:date&RANGE=2013-01-01:2016-01-01&INTERVAL=1:month
&PARTITION_BY=color:enum&RANGE=red:yellow:blue

启用分区时,JDBC连接器将SELECT查询拆分为多个子查询,这些子查询是检索数据的子集,每个子集称为一个片段。 JDBC连接器会自动向每个片段添加额外的查询约束(WHERE表达式),以确保从外部数据库中检索每个元组的数据都恰好一次。

例如,当用户查询使用指定&PARTITION_BY=id:int&RANGE=1:5&INTERVAL=2的LOCATION子句创建的外表时,会生成5个片段,根据分区设置两个和最多三个隐式片段生成的碎片。 与每个片段相关的约束如下:

  • 片段1:WHERE (id < 1), 隐式生成的片段,用于RANGE起始区间。

  • 片段2:WHERE (id >= 1) AND (id < 3) ,分区设置指定的片段。

  • 片段3:WHERE (id >= 3) AND (id < 5) ,分区设置指定的片段。

  • 片段4:WHERE (id >= 5),隐式生成的片段,用于RANGE结束区间。

  • 片段5:WHERE (id IS NULL) ,隐式生成的片段。

外部数据源服务会在AnalyticDB PostgreSQL版数据库Segment之间分配片段。在外部数据源实例为服务片段的主机上的每个Segment生成一个线程。 如果片段的数量小于或等于在片段主机上配置的AnalyticDB PostgreSQL版数据库Segment的数量,则单个外部数据源实例可以为所有片段提供服务。每个异构数据源访问服务实例将其结果发送回AnalyticDB PostgreSQL版数据库,在此收集并将其返回给用户。

当您指定PARTITION_BY选项时,根据与目标数据库的最佳JDBC连接数以及跨AnalyticDB PostgreSQL版数据库Segment的最佳外部数据分配,调整INTERVAL值和单位。INTERVAL低边界由AnalyticDB PostgreSQL版数据库Segment的数量驱动,而高边界由与目标数据库的可接受的JDBC连接数量驱动。INTERVAL设置会影响片段的数量,理想情况下不应设置得太高或太低。 使用多个值进行测试可以帮助您选择最佳设置。

RANGE

Read

当指定PARTITION_BY时,则为必填项。

指定查询范围, 用以提示帮助创建分区。 RANGE格式取决于分区列的数据类型。

  • 当分区列为Enum类型时,RANGE必须指定值列表,即<value>:<value>[:<value>[…]],形成自己的片段。

  • 当分区列是IntDate类型时,RANGE必须指定<start-value>:<end-value>,表示从<start-value><end-value>(包含)。

    说明

    分区列是Date类型,请使用yyyy-MM-dd日期格式。

INTERVAL

Read

如果指定了PARTITION_BY且类型为IntDate时,则为必填项。

一个分区片段的间隔,格式为<interval-value>[:<interval-unit>],与RANGE一起使用用以提示创建分区。通过<interval-value>指定片段的大小。

  • 当PARTITION_BY列为Date类型,使用<interval-unit>指定Year,Month或Day。

  • 当PARTITION_BY列为Enum类型,JDBC连接器会忽略INTERVAL。

QUOTE_COLUMNS

Read

控制在构造外部数据库的SQL查询时JDBC连接器是否引用列名。

  • 指定为true:强制JDBC连接器引用所有列名称。

  • 指定任何其他值,JDBC连接器不会引用列名。

如果未指定QUOTE_COLUMNS(默认不指定),当查询中任一字段满足以下条件,JDBC连接器将自动引用所有列名:

  • 包含特殊字符。

  • 混合大小写,且外部数据库不支持未引用的混合大小写标识符。

示例

  1. 在MySQL或PostgreSQL数据库中,创建数据表,并插入数据。

CREATE TABLE test(id int, name VARCHAR(255));
INSERT INTO test(id,name) VALUES(1,'qingzhi');
INSERT INTO test(id,name) VALUES(2,'jianli');
  1. 登录AnalyticDB PostgreSQL版数据库,创建JDBC外表,并查询外表数据。

CREATE EXTERNAL TABLE pxf_jdbc_textsimple(id int, name varchar)
LOCATION ('pxf://$test?PROFILE=Jdbc&SERVER=$test01')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';
SELECT * FROM pxf_jdbc_textsimple;

附录1:HDFS外表数据格式

HDFS外表支持的数据格式与创建HDFS外表配置的PROFILE参数对应关系如下。

数据格式

PROFILE(配置文件名称)

单行的分隔文本

hdfs:text

含有被引号引起来的换行符的分隔文本

hdfs:text:multi

Avro

hdfs:avro

JSON

hdfs:json

Parquet

hdfs:parquet

AvroSequenceFile

hdfs:AvroSequenceFile

SequenceFile

hdfs:SequenceFile

附录2:Hive外表数据格式

Hive外表支持的数据格式与创建Hive外表配置的PROFILE参数对应关系如下。

数据格式

PROFILE(配置文件名称)

TextFile

Hive或HiveText

SequenceFile

Hive

RCFile

Hive或HiveRC

ORC

Hive、HiveORC或HiveVectorizedORC

Parquet

Hive

附录3:Parquet数据类型与AnalyticDB PostgreSQL数据类型映射关系

Parquet数据类型

AnalyticDB PostgreSQL数据类型

Boolean

Boolean

Byte_array

Bytea,Text

Double

Float8

Fixed_len_byte_array

Numeric

Float

Real

Int_8, Int_16

Smallint,Integer

Int64

Bigint

Int96

Timestamp,Timestamptz

写入Parquet时,异构数据源将Timestamp本地化为当前系统时区,并将其转换为通用时间(UTC),最终转换为Int96。在此转换过程中,异构数据源会丢失时区信息。

附录4:Hive数据类型与AnalyticDB PostgreSQL数据类型映射关系

Hive数据类型

AnalyticDB PostgreSQL数据类型

Boolean

Bool

Int

Int4

Smallint

Int2

Tinyint

Int2

Float

Float4

Bigint

Int8

double

Float8

String

Text

Binary

Bytea

Timestamp

Timestamp