跳转至

使用External Table 访问数据

定义External Table

External Table允许把外部文件当作常规数据库表来访问。它们常常被用来把数据移进或者移出SeaboxMPP数据库。

要创建一个外部表定义，用户需要指定输入文件的格式以及外部数据源的位置。有关输入文件格式的信息, 请参考 格式化数据文件。

使用下列协议之一来访问外部表数据源。用户不能在CREATE EXTERNAL TABLE语句中混用协议：

  • file:// 访问executor主机上SeaboxMPP数据超级用户 (seabox)能访问的外部数据文件。 见file://协议。

  • scfs:// 指向一个位于文件主机上的目录并且向SeaboxMPP数据库所有的executor并行提供外部数据文件。见scfs://协议。

  • scfss:// 是scfs的一个安全版本。见 scfss://协议。

  • s3://访问Amazon S3桶中的文件。见s3://协议。

  • pxf://协议使用SeaboxMPP平台扩展框架（PXF）访问外部HDFS文件和HBase和Hive表。见pxf://协议。

注意:

s3:// 和 pxf:// 协议是自定义数据访问协议，file://, scfs://, 和scfss://协议是实现在SeaboxMPP数据库内部的。自定义和内部协议有以下不同：

  • 自定义协议必须使用CREATE PROTOCOL 命令注册。 在数据库中注册PXF扩展会创建 pxf:// 协议。(见使用PXF访问外部数据。)您可以选择性地注册s3:// 协议。(见配置和使用S3外部表。)

    内部协议始终存在且无法取消注册。

  • 注册自定义协议时，会在pg_extprotocol catalog表中添加一行，以指定实现该协议的处理函数。 协议的共享库必须安装在所有SeaboxMPP数据库主机上。 内部协议在pg_extprotocol表中没有记录，并且没有其他库需要安装。

  • 要授予用户对自定义协议的权限，请使用GRANT [SELECT | INSERT | ALL] ON PROTOCOL。要允许（或拒绝）内部协议的用户权限，需要使用CREATE ROLE 或ALTER ROLE将CREATEEXTTABLE（或NOCREATEEXTTABLE）属性添加到每个用户的角色。

外部表从数据库内访问外部文件，就好像它们是常规的数据库表一样。用 scfs/scfss, pxf, 和s3协议定义的外部表通过使用所有SeaboxMPP数据库的executor资源导入或导出数据来利用SeaboxMPP的并行机制。pxf协议利用Hadoop分布式文件系统的并行体系结构来访问该系统上的文件。 s3协议利用了Amazon Web服务（AWS）的功能。

用户可以使用SELECT、JOIN或SORT EXTERNAL TABLE DATA等SQL命令直接并行地查询外部表数据，并且用户可以为外部表创建视图。

使用外部表的步骤是：

  1. 定义外部表。

    使用s3 协议， 用户还必须配置SeaboxMPP数据库并且启用该协议。见 s3://协议。

  2. 做下面的事情之一：

    • 使用scfs或者scfss协议时，启动SeaboxMPP数据库文件服务器。

    • 验证为s3协议做好了所需的一次性配置。

  3. 把数据文件放置在正确位置。

  4. 用SQL命令查询外部表。

SeaboxMPP数据库提供可读和可写的外部表：

  • 用于数据加载的可读外部表。 可读外部表支持：

    • 数据仓库中常见的基本提取，转换和加载（ETL）任务。
    • 从多个SeaboxMPP数据库段实例并行读取外部表数据，以优化大型加载操作。

    • 过滤下推。 如果查询包含WHERE子句，则可以将其传递给外部数据源。

      有关更多信息，请参考sc_external_enable_filter_pushdown服务器配置参数讨论。

      请注意，此功能目前仅支持pxf协议(参阅 pxf://协议)。

可读外部表仅允许SELECT操作。

  • 用于数据导出的可写外部表。 可写外部表支持：

    • 从数据库表中选择数据以插入可写外部表。
    • 将数据以数据流形式发送到应用程序。 例如，从SeaboxMPP数据库导出数据并将其发送到连接到另一个数据库或ETL工具的应用程序以在其他地方加载数据。
    • 从SeaboxMPP并行MapReduce计算接收输出。

    可写外部表仅允许INSERT操作。

外部表可以是基于文件的或者是基于Web的。使用file://协议的外部表示只读表。

  • 普通（基于文件的）外部表访问静态平面文件。普通外部表是可以重新扫描的：在查询运行时数据是静态的。
  • Web（基于Web的）外部表访问动态数据源， 或者在一个用http://协议的Web服务器上，或者通过执行OS命令或脚本得到。外部Web表不是可以重新扫描的：在查询运行时数据可以改变。

转储和操作只针对外部表和外部Web表的定义而非数据源。

  • file://协议 file://协议被用在一个指定操作系统文件的URI中。

  • scfs://协议 scfs://协议被用在一个URI中引用一个正在运行的 scfs实例。

  • scfss:// 协议 scfss:// 协议是scfs:// 协议的一个安全版本。

  • pxf:// 协议 您可以使用SeaboxMPP平台扩展框架（PXF）pxf://协议访问驻留在外部Hadoop系统（HDFS，Hive，HBase），对象存储系统（Azure，Google云端存储，Minio，S3）和SQL数据库上的数据。

  • s3:// 协议 s3协议使用一个URL指定Amazon S3桶的位置和一个用来在桶里读写文件的前缀。

  • 使用自定义协议 一种自定义协议允许用户连接到一个不能用file://, scfs://，或者 pxf://协议的SeaboxMPP数据库。

  • 处理外部表数据中的错误 默认情况下，如果外部表数据中包含有一个错误，命令就会失败并且不会有数据被载入到目标数据库表中。

  • 创建和使用外部Web表 外部Web表允许SeaboxMPP数据库像处理常规数据表一样处理动态数据源。由于Web表数据可以在查询运行时更改，因此数据无法重新扫描。

  • 外部表和数据加载示例