背景
外部表是数据仓库非常重要的一个功能特性，包括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的高效数据交换》文章中提到的，这将大幅降低外部表功能组件安装部署与运维监控的复杂度，同时缩短数据周转与转换环节（没有额外的进程间序列化/反序列化开销），提升端到端的查询性能。
功能描述
根据我们的经验，目前外部表主要用在如下三个场景：
- 临时查询归档数据（或者其它不活跃数据）；
- ETL，将数据从外部存储系统加载到数据仓库；
- 将数据从数据仓库卸载到外部存储系统；
对于前面两种场景，我们可以通过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语句即可完成包括数据加载、数据备份、归档数据临时查询等数据工程任务。
随着逻辑数仓以及数据湖需求的日益增长，除了继续实现更多的协议类型以及文件格式来对接更多的数据源外，如何提升外部表的扫描效率（尽可能地接近内部表）是我们未来研发的重点：潜在的技术方向包括像内部表一样提供本地缓存，以及通过数据映射物化在本地缓存中，将原始的文件格式转化为更加高效的存储格式。
扫一扫
1508
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
