解密數倉的SQL ON ANYWHERE技術

华为云开发者联盟發表於2024-04-03
解密SQL

本文分享自華為雲社群《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上，降低使用者成本。

img

從應用場景來看，可以滿足如下業務需求：

  • 針對多資料來源需要構建虛擬的統一資料倉儲，實現多資料來源聯邦查詢，跨資料倉儲熱資料和HDFS/OBS冷資料的複雜混合查詢，需要提供一致的、熟悉的資料倉儲操作體驗。
  • 滿足低頻的業務全資料的低成本低延遲即席查詢。

4. GaussDB(DWS)SQL On Anywhere的實現方式

GaussDB(DWS)SQL On Anywhere針對大資料的檔案系統的訪問主要透過FDW或ELK機制（已停止演進）實現的，而跨資料庫的訪問主要透過EC+ODBC的方式實現的。

img

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_server
OPTIONS(
    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的實現方式優缺點對比

解密數倉的SQL ON ANYWHERE技術

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章