HashData 外部表的实现与应用

背景

外部表是数据仓库非常重要的一个功能特性，包括 AWS Redshift、Azure Synapse、Google BigQuery 和 Snowflake 等云数仓产品都有着完善的支持。顾名思义，外部表与内部表（或者内置表）对应，后者指的是正常的数据库表，无论是表的元数据还是物理数据文件均由数据库自身管理，而前者是将数据库之外（外部）的数据（可以是静态的数据文件，也可以是动态的数据流）映射成数据库的表对象，其中只有元数据是由数据库管理，物理数据文件或者数据流则是由外部的存储系统管理。例如，在《HashData 与 HDFS 的高效数据交换》中重点介绍的 GPHDFS 外部表，其元数据由 HashData 数据仓库保存管理，数据文件则由 HDFS 保存管理。当我们删除一张内部表时，除了表定义外，这张表对应的数据文件也会被删除，而当删除的是一张外部表时，只有表定义会被删除，数据文件保持不动。

除 GPHDFS 外，HashData 还支持其它多种外部表协议与数据格式，其中一部分是从 PostgreSQL 和 Greenplum Database 继承过来的，其它是我们针对客户需求优化和添加的。今天，我们系统性地介绍一下 HashData 的外部表功能。

外部表架构

如下为 HashData 外部表架构示意图。

本质上，外部表是用于 HashData 集群与外部存储系统进行数据交换的。类比 FTP，我们可以认为 HashData 集群是 FTP 的客户端，而外部存储系统是 FTP 服务器。FTP 客户端与服务器端交互会涉及两类信息：控制消息和数据流。其中，控制消息在外部表的框架下对应的是协议，数据流对应的是数据格式。理论上，协议和数据格式是可以完全解耦的，就像无论是本地文件系统（file 协议）、文件服务器（gpfdist 协议）、HDFS（gphdfs 协议）还是对象存储（oss 协议），都能够存储 TEXT、CSV、ORC 和 NETCDF 等格式的文件。但实际上，因为各种历史原因和客户需求侧重点不一样（外部表功能更多是客户场景驱动，不是产品设计驱动），目前 HashData 常用外部表实现中，协议和文件格式是紧耦合的（我们希望在基于 PostgreSQL Foreign Data Wrapper 实现新外部表框架时来解决这个问题），某个协议只能支持某些文件格式：

TEXT、CSV 和 ORC 是比较常用的数据格式，这里简单说明一下另外两种格式：SHAPEFILE 和 NETCDF。GIS 数据主要分两类：矢量数据和栅格数据。我们常说的点、线、面是矢量数据，一般保存成 SHAPEFILE 格式。栅格数据则是行列组成的像素矩阵，每个像素（矩阵元素）代表一块区域，同时保存与这块区域相关的信息，如温度、高度等。栅格数据可以是航拍图片、卫星图像和数字地图。栅格数据有多种格式，包括 TIFF 和 NETCDF，我们支持的是 NETCDF。

上述 HashData 支持的所有协议和数据格式，均为 C/C++原生实现，让数据库进程直接与目标存储系统交互，只有一次序列化/反序列化开销，没有任何中转进程。如在《HashData 与 HDFS 的高效数据交换》文章中提到的，这将大幅降低外部表功能组件安装部署与运维监控的复杂度，同时缩短数据周转与转换环节（没有额外的进程间序列化/反序列化开销），提升端到端的查询性能。

功能描述

根据我们的经验，目前外部表主要用在如下三个场景：

1. 临时查询归档数据（或者其它不活跃数据）；

2. ETL，将数据从外部存储系统加载到数据仓库；

3. 将数据从数据仓库卸载到外部存储系统；

对于前面两种场景，我们可以通过 CREATE READABLE EXTERNAL TABLE 创建可读外部表，实现对外部数据的读访问。定义可读外部表后，即可以使用 SQL 命令直接查询其映射的数据集。例如，用户可以对可读外部表进行 SELECT, JOIN, SORT 等查询操作，并支持基于外部表创建视图。DML 操作（UPDATE, INSERT, DELETE, TRUNCATE） 在可读外部表上是不允许的，用户也无法为其创建索引。

对于第三种场景，我们需要的是可写外部表，通过 CREATE WRITABLE EXTERNAL TABLE 来创建，实现对外部存储系统的写操作。可写外部表定义后，便可以在数据库中将查询的结果集插入到可写外部表，从而实现数据的卸载。可写外部表仅允许 INSERT 操作 – SELECT，UPDATE，DELETE 或 TRUNCATE 不被允许。

oss 协议外部表实践数据的导入导出

这里我们通过 oss 协议（对象存储）来演示一下如何通过外部表实现数据的导入导出，如下为简单示意图。

整体的演示过程为，先通过可写外部表将一张表的数据导出到对象存储（这里使用的是青云的 QingStor 对象存储），然后再通过可读外部表将保存在 QingStor 上的数据恢复到原始表。除了 QingStor 外，HashData 还支持其它主流公有云的对象存储服务，包括 AWS 的 S3、Azure 的 Blob Storage、阿里云的 OSS、腾讯云的 COS、金山云的 KS3 和华为云的 OBS，以及基于开源 Ceph 和 Minio 构建的私有云对象存储产品。

1 创建可写外部表

  ---数据库表 zz_hashdata    warehouse=# select * from zz_hashdata;     id    ----      6      6      6      6      6      6      6      6      6      6    (10 rows)    ---创建对应的可写外部表    warehouse=# CREATE WRITABLE EXTERNAL TABLE ext_hashdata ( like zz_hashdata)    warehouse-#    LOCATION ('oss://zz-hashdata.pek3b.qingstor.com/ext-hashdata/zzhashdata.txt access_key_id=<access-key-id> secret_access_key=<secret-access-key> oss_type=qs')    warehouse-#    FORMAT 'TEXT' (DELIMITER '|');    NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table    CREATE EXTERNAL TABLE  

2 将数据库表数据放入可写外部表中

 ---将数据写入可写外部表    warehouse=# insert into ext_hashdata select * from zz_hashdata;    INSERT 0 10 

3 创建可读外部表

 ---创建对应的可读外部表    warehouse=# CREATE READABLE EXTERNAL TABLE ext_hashdata1 ( like zz_hashdata)    warehouse-#    LOCATION ('oss://zz-hashdata.pek3b.qingstor.com/ext-hashdata/zzhashdata.txt access_key_id=<access-key-id> secret_access_key=<secret-access-key> oss_type=qs')    warehouse-#    FORMAT 'TEXT' (DELIMITER '|')    warehouse-#    LOG ERRORS SEGMENT REJECT LIMIT 10 ROWS;    CREATE EXTERNAL TABLE 

4 查看可读外部表数据

  ---查看可读外部表的数据    warehouse=# select * from ext_hashdata1;     id    ----      6      6      6      6      6      6      6      6      6      6    (10 rows)  

5 将原始表数据清空，从可读外部表恢复数据

 ---将原始表数据清空，然后通过可读外部表插入数据    warehouse=# truncate table zz_hashdata;    TRUNCATE TABLE    warehouse=# insert into zz_hashdata select * from ext_hashdata1;    INSERT 0 10    warehouse=# select * from zz_hashdata;     id    ----      6      6      6      6      6      6      6      6      6      6    (10 rows)  

总结与展望

在这篇文章中，我们系统性地介绍了 HashData 支持的外部表功能。作为数据库内置的功能特性，用户可以在不依赖任何第三方工具的情况下，简单地通过 SQL 语句即可完成包括数据加载、数据备份、归档数据临时查询等数据工程任务。

随着逻辑数仓以及数据湖需求的日益增长，除了继续实现更多的协议类型以及文件格式来对接更多的数据源外，如何提升外部表的扫描效率（尽可能地接近内部表）是我们未来研发的重点：潜在的技术方向包括像内部表一样提供本地缓存，以及通过数据映射物化在本地缓存中，将原始的文件格式转化为更加高效的存储格式。