HashData外部表的实现与应用

2022-02-16 10:57:27

背景

外部表是数据仓库非常重要的一个功能特性，包括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实现新外部表框架时来解决这个问题），某个协议只能支持某些文件格式：

协议	支持数据格式
file	TEXT、CSV
gpfdist	TEXT、CSV
gphdfs	TEXT、CSV、ORC
oss	TEXT、CSV、ORC、SHAPEFILE、NETCDF

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构建的私有云对象存储产品。

4.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

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

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

4.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.4 查看可读外部表数据

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

4.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)