解密数仓的 SQL ON ANYWHERE 技术

本文分享自华为云社区《GaussDB DWS的SQL ON ANYWHERE技术解密》，作者：tooooooooooomy。

1. 前言

• 适用版本：【8.1.1（及以上）】

查询分析是大数据要解决的核心问题之一，虽然大数据相关的处理引擎组件种类繁多，并提供了丰富的接口供用户使用，但相对传统数据库用户来说，SQL 语言依然是使用最简单、最广泛和方便的一种接口。如果能在一个客户端中使用 SQL 语句操作不同的大数据组件，将极大提升使用各种大数据组件的效率。

2. 什么是 SQL On Anywhere

GaussDB(DWS)的 SQL On Anywhere，主要指对大数据的文件系统和与其他异构数据库的访问和交互，构筑起统一的大数据计算平台。大数据文件系统主要包括 HDFS 和 OBS，其他异构数据库主要包括 Oracle、Spark 和 Other GaussDB(DWS)。

3. GaussDB(DWS)SQL On Anywhere 的作用及其应用场景

通过 SQL On Anywhere 特性可以实现与其他大数据组件和数据库互联互通访问，可以直接同时处理本地和 HDFS/OBS 上的数据集，甚至其他异构数据库的数据，而无需导入导出数据，将其分析能力从本地存储扩展到数据湖中，扩大 GaussDB DWS 的大数据分析的应用场景；通过该特性可以帮助客户实现冷热数据分离，将使用频度更高的热数据存储在本地，而使用频度更低的冷数据存储在成本更低廉的共享存储 HDFS 或者 DWS 上，降低用户成本。

从应用场景来看，可以满足如下业务需求：

• 针对多数据源需要构建虚拟的统一数据仓库，实现多数据源联邦查询，跨数据仓库热数据和 HDFS/OBS 冷数据的复杂混合查询，需要提供一致的、熟悉的数据仓库操作体验。

• 满足低频的业务全数据的低成本低延迟即席查询。

4. GaussDB(DWS)SQL On Anywhere 的实现方式

GaussDB(DWS)SQL On Anywhere 针对大数据的文件系统的访问主要通过 FDW 或 ELK 机制（已停止演进）实现的，而跨数据库的访问主要通过 EC+ODBC 的方式实现的。

3.1 利用 FDW 访问 HDFS/OBS 数据

GaussDB(DWS)对存储在 HDFS 上的 Hadoop 或者 OBS 原生数据的访问，采用 FDW（Foreign Data Wrapper）机制，也称外表机制。首先通过创建 Foreign Data Server 来定义对 HDFS 数据源或同构其他集群的连接信息；之后创建 Foreign Table，用于在 GaussDB A 数据库内部系统表中，定义对应的 HDFS 数据源上 Hadoop 原生结构化数据表的结构或对应同构其他集群结构化数据表的结构。

例如读取 hdfs 上的数据，其流程如下：

​ 1）建立一个 hdfs_server，其中 hdfs_fdw 为数据库中存在的 foreign data wrapper。

--创建hdfs_server。postgres=# CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS    (address '10.146.187.231:8000,10.180.157.130:8000' ,    hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',     type 'HDFS') ;

​

2）创建一个 hdfs 外表读取 hdfs 上的数据

CREATE FOREIGN TABLE region (   R_REGIONKEY INT4,   R_NAME TEXT,   R_COMMENT TEXT )SERVER  hdfs_serverOPTIONS(    FORMAT 'orc',    FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/')DISTRIBUTE BY roundrobin;

​

3）查询 HDFS 外表，例如：

select * from region limit 10;

目前外表支持与普通表进行关联查询，并支持多种文件存储格式，其支持的文件格式如下（不同版本能力可能存在差异，以官方文档为准）：

3.2 通过 ELK 访问 HDFS（已停止演进，不推荐）

ELK 的方式类似于 HAWQ，它是通过建立表空间为 HDFS 表空间，直接将数据存储和访问 HDFS 文件系统，目前只支持访问 HDFS 文件系统，而不支持访问 OBS 上的数据。首先通过创建 HDFS 表空间，然后会创建一个 HDFS 表，在创建时指定表空间为 HDFS 表空间，最后对 HDFS 表的操作如同普通表的操作，可进行插入修改删除数据。

以 GaussDB 数据库数据推到 HDFS 中

​ 1）在数据库中创建 HDFS 表空间

CREATE TABLESPACE hdfs_table RELATIVE LOCATION ‘tmp/hdtest’With (filesystem=’hdfs’,	address=’28.4.136.221:9000’,	cfgpath=’/opt/Huawei/bigdata/mppdb/hdfs_conf/zhndnrop/omm@HADOOP.COM/’,	storepath=’/tmp/test’);

​

2）数据库中创建 HDFS 表

CREATE TABLE abc(	zjxxlh char(20),	nbbsh char(20),	khwybh char(20),	zjlx char(20))WITH (orientation=orc) TABLESPACE tables_hdfs;

​

3）向表中插入数据

insert into abc select * from region10;

3.3 基于 EC+ODBC 的跨集群访问数据

GaussDB(DWS)支持通过 EC(全称 Extension Connector)+ODBC 统一访问其它大数据组件——将 SQL 发给其它大数据组件并接收执行结果，实现跨集群访问数据。目前 EC+ODBC 为用户提供了三种功能： SQL on Oracle、SQL on Spark 和 SQL on other GaussDB，分别用于连接 Oracle 数据库、Spark 集群和其他 GaussDB 集群。

EC+ODBC 的基本工作原理是：用户首先构建 Data Source 对象（其中包含目标库的一些连接信息和字符编码方式），然后用户获取该 Data Source 的使用权限，最后通过标准 ODBC API 连接目标库，发送 SQL 语句并获取执行结果。

为了方便使用，EC+ODBC 为用户提供了统一的连接函数 exec_on_extension(text, text)。其中，第一个参数为 Data Source 名称，第二个参数为发送的 SQL 语句，例如：

postgres=# SELECT * FROM exec_on_extension('ds_spark', 'select * from a;') AS (c1 int);

5. GaussDB(DWS) SQL On Anywhere 的实现方式优缺点对比

| ELK | HDFS | 1. 支持多 DN 并发查询 2. 支持和本地多表 join 查询和写入 3. 支持 analyze 收集统计信息 4. 节点本地化效率相对比较高 5. 支持增量写，支持 update 和 delete | 1. HDFS 表空间方式要求 HDFS 集群与 MPPDB 集群有强依赖关系，不易于扩展 2. 格式支持有限，目前只支持 ORC 格式，并且只支持访问 HDFS 文件系统 3. 有可能会产生大量小文件 |

点击关注，第一时间了解华为云新鲜技术~