# 5 步教你將 MRS 資料匯入 DWS

MapReduce 服務（MapReduce Service，簡稱 MRS）是一個基於開源 Hadoop 生態環境而執行的大資料叢集，對外提供大容量資料的儲存和分析能力，可解決使用者的資料儲存和處理需求。使用者可以將海量業務資料，儲存在 MRS 的分析叢集，即使用 Hive/Spark 元件儲存。Hive/Spark 的資料檔案則儲存在 HDFS 中。GaussDB (DWS) 支援在相同網路中，配置一個 GaussDB (DWS) 叢集連線到一個 MRS 叢集，然後將資料從 HDFS 中的檔案讀取到 GaussDB (DWS)。從 MRS 匯入資料到叢集的流程，大致可以分為 5 個步驟：

- 第一步： MRS 叢集上的資料準備
- 第二步：手動建立外部伺服器
- 第三步：建立外表
- 第四步：執行資料匯入
- 第五步：清除資源

## 1 MRS 叢集上的資料準備

從 MRS 匯入資料到 GaussDB (DWS) 叢集之前，假設您已經完成了以下準備工作：

（1）已建立 MRS 叢集。

（2）在 MRS 叢集上建立了 Hive/Spark ORC 表，且表資料已經儲存到該表對應的 HDFS 路徑上。

如果您已經完成上述準備，則可以跳過本章節。

為方便起見，我們將以在 MRS 叢集上建立 Hive ORC 表作為示例，完成上述準備工作。在 MRS 叢集上建立 Spark ORC 表的大致流程和 SQL 語法，同 Hive 類似，在本文中不再展開描述。

### **1.1 資料檔案**

假設有資料檔案 product_info.txt，示例資料如下所示：

```text
100,XHDK-A-1293-#fJ3,2017-09-01,A,2017 Autumn New Shirt Women,red,M,328,2017-09-04,715,good
205,KDKE-B-9947-#kL5,2017-09-01,A,2017 Autumn New Knitwear Women,pink,L,584,2017-09-05,406,very good!
300,JODL-X-1937-#pV7,2017-09-01,A,2017 autumn new T-shirt men,red,XL,1245,2017-09-03,502,Bad.
310,QQPX-R-3956-#aD8,2017-09-02,B,2017 autumn new jacket women,red,L,411,2017-09-05,436,It's really super nice
150,ABEF-C-1820-#mC6,2017-09-03,B,2017 Autumn New Jeans Women,blue,M,1223,2017-09-06,1200,The seller's packaging is exquisite
200,BCQP-E-2365-#qE4,2017-09-04,B,2017 autumn new casual pants men,black,L,997,2017-09-10,301,The clothes are of good quality.
250,EABE-D-1476-#oB1,2017-09-10,A,2017 autumn new dress women,black,S,841,2017-09-15,299,Follow the store for a long time.
108,CDXK-F-1527-#pL2,2017-09-11,A,2017 autumn new dress women,red,M,85,2017-09-14,22,It's really amazing to buy
450,MMCE-H-4728-#nP9,2017-09-11,A,2017 autumn new jacket women,white,M,114,2017-09-14,22,Open the package and the clothes have no odor
260,OCDA-G-2817-#bD3,2017-09-12,B,2017 autumn new woolen coat women,red,L,2004,2017-09-15,826,Very favorite clothes
980,ZKDS-J-5490-#cW4,2017-09-13,B,2017 Autumn New Women's Cotton Clothing,red,M,112,2017-09-16,219,The clothes are small
98,FKQB-I-2564-#dA5,2017-09-15,B,2017 autumn new shoes men,green,M,4345,2017-09-18,5473,The clothes are thick and it's better this winter.
150,DMQY-K-6579-#eS6,2017-09-21,A,2017 autumn new underwear men,yellow,37,2840,2017-09-25,5831,This price is very cost effective
200,GKLW-l-2897-#wQ7,2017-09-22,A,2017 Autumn New Jeans Men,blue,39,5879,2017-09-25,7200,The clothes are very comfortable to wear
300,HWEC-L-2531-#xP8,2017-09-23,A,2017 autumn new shoes women,brown,M,403,2017-09-26,607,good
100,IQPD-M-3214-#yQ1,2017-09-24,B,2017 Autumn New Wide Leg Pants Women,black,M,3045,2017-09-27,5021,very good.
350,LPEC-N-4572-#zX2,2017-09-25,B,2017 Autumn New Underwear Women,red,M,239,2017-09-28,407,The seller's service is very good
110,NQAB-O-3768-#sM3,2017-09-26,B,2017 autumn new underwear women,red,S,6089,2017-09-29,7021,The color is very good 
210,HWNB-P-7879-#tN4,2017-09-27,B,2017 autumn new underwear women,red,L,3201,2017-09-30,4059,I like it very much and the quality is good.
230,JKHU-Q-8865-#uO5,2017-09-29,C,2017 Autumn New Clothes with Chiffon Shirt,black,M,2056,2017-10-02,3842,very good
```

### **1.2 在 MRS 叢集上建立 Hive ORC 表**

（1）建立了 MRS 叢集。

（2）登入 MRS 叢集的 Hive 客戶端。

- （a）登入 Master 節點
- （b）執行以下命令切換使用者

```text
sudo su - omm
```

- （c）執行以下命令切換到客戶端目錄

```text
cd /opt/client
```

- （d）執行以下命令配置環境變數

```text
source bigdata_env
```

- （e）如果當前叢集已啟用 Kerberos 認證，執行以下命令認證當前使用者，當前使用者需要具有建立 Hive 表的許可權。配置擁有對應許可權的角色。為使用者繫結對應角色。如果當前叢集未啟用 Kerberos 認證，則無需執行此命令，例如，kinit hiveuser。

```text
kinit MRS叢集使用者
```

- （f）執行以下命令啟動 Hive 客戶端

```text
beeline
```

（3）在 Hive 中建立資料庫 demo，執行以下命令建立資料庫：

```text
CREATE DATABASE demo
```

（4）在資料庫 demo 中新建了一個 Hive TEXTFILE 型別的表 product_info，並將資料檔案（product_info.txt）匯入到該表對應的 HDFS 路徑中，執行以下命令切換到 demo 資料庫：

```text
USE demo;
```

執行以下命令，建立表 product_info，表欄位按照資料檔案中的資料進行定義：

```text
DROP TABLE product_info;
CREATE TABLE product_info (    
    product_price                int            not null,
    product_id                   char(30)       not null,
    product_time                 date,
    product_level                char(10),
    product_name                 varchar(200),
    product_type1                varchar(20),
    product_type2                char(10),
    product_monthly_sales_cnt    int,
    product_comment_time         date,
    product_comment_num          int,
    product_comment_content      varchar(200)                   
) 
row format delimited fields terminated by ',' stored as TEXTFILE;
```

（5）在資料庫 demo 中建立了一個 Hive ORC 表 product_info_orc，執行以下命令，建立 Hive ORC 表 product_info_orc，表欄位與上一步建立的表 product_info 完全一致：

```text
DROP TABLE product_info_orc;
CREATE TABLE product_info_orc(    
    product_price                int            not null,
    product_id                   char(30)       not null,
    product_time                 date,
    product_level                char(10),
    product_name                 varchar(200),
    product_type1                varchar(20),
    product_type2                char(10),
    product_monthly_sales_cnt    int,
    product_comment_time         date,
    product_comment_num          int,
    product_comment_content      varchar(200)                   
) 
row format delimited fields terminated by ',' stored as orc;
```

（6）將 product_info 表的資料插入到 Hive ORC 表 product_info_orc 中。

```text
insert into product_info_orc select * from product_info;
```

查詢表 product_info_orc

```text
select * from product_info_orc;
```

## 2 手動建立外部伺服器

建立外表語法（CREATE FOREIGN TABLE (SQL on Hadoop or OBS)）中，需指定一個與 MRS 資料來源連線相關聯的外部伺服器。

當您透過 GaussDB (DWS) 管理控制檯建立 MRS 資料來源連線時，資料庫管理員 dbadmin 會在預設資料庫 postgres 中自動建立一個外部伺服器。因此，如果您希望在預設資料庫 postgres 中建立外表讀取 MRS 資料，可以跳過本章節。

如果您希望使用普通使用者在自定義資料庫中建立外表讀取 MRS 資料，必須先在自定義資料庫中手動建立一個外部伺服器。本章節將為您介紹，如何使用普通使用者在自定義資料庫中建立外部伺服器。步驟如下：

第一步：請確保 GaussDB (DWS) 叢集已建立 MRS 資料來源連線。

第二步：建立使用者和資料庫並授予外表許可權

第三步：手動建立外部伺服器

說明：需要注意的是，當您不再需要從該 MRS 資料來源讀取資料時，透過 GaussDB (DWS) 管理控制檯刪除 MRS 資料來源，僅會刪除在預設資料庫 postgres 中自動建立的外部伺服器，手動建立的外部伺服器需要您手動刪除。

### **2.1 建立使用者和資料庫並授予外表許可權**

以下示例，是新建一個普通使用者 dbuser 並建立一個資料庫 mydatabase，然後使用管理員使用者授予 dbuser 外表許可權。

（1）使用資料庫管理員透過 GaussDB (DWS) 提供的資料庫客戶端連線預設資料庫 postgres，例如，使用 gsql 客戶端的使用者透過如下語句連線資料庫，根據介面提示輸入密碼。

```text
gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -r
```

（2）新建一個普通使用者，並用它建立一個資料庫。新建一個具有建立資料庫許可權的使用者 dbuser：

```text
CREATE USER dbuser WITH CREATEDB PASSWORD "Bigdata@123";
```

- 切換為新建的使用者

```text
SET ROLE dbuser PASSWORD "Bigdata@123";
```

- 執行以下命令建立資料庫：

```text
CREATE DATABASE mydatabase;
```

- 查詢資料庫：

```text
SELECT * FROM pg_database;
```

- 返回結果中有 mydatabase 的資訊表示建立成功。

（3）使用管理員使用者給普通使用者賦予建立外部伺服器的許可權和使用外表的許可權。使用資料庫管理員使用者透過資料庫客戶端連線新建的資料庫。

- 例如，使用 gsql 客戶端的使用者可以直接使用如下語句切換為管理員使用者去連線新建的資料庫，根據提示輸入使用者密碼。

```text
\c mydatabase dbadmin;
```

- 說明：注意，必須先使用管理員使用者連線到將要建立外部伺服器和使用外表的資料庫，再對普通使用者進行授權。
- 預設只有系統管理員才可以建立外部伺服器，普通使用者需要授權才可以建立，執行以下命令授權：

```text
GRANT ALL ON FOREIGN DATA WRAPPER hdfs_fdw TO dbuser;
```

- 其中 FOREIGN DATA WRAPPER 的名字只能是 hdfs_fdw，dbuser 為建立 SERVER 的使用者名稱。執行以下命令賦予使用者使用外表的許可權。

```text
ALTER USER dbuser USEFT;
```

- 檢視使用者，返回結果中，dbuser 的資訊中包含了 UseFT 許可權，表示授權成功：

```text
select
	r.rolname,
	r.rolsuper,
	r.rolinherit,
	r.rolcreaterole,
	r.rolcreatedb,
	r.rolcanlogin,
	r.rolconnlimit,
	r.rolvalidbegin,
	r.rolvaliduntil,
	array(
		select
			b.rolname
		from pg_catalog.pg_auth_members m
		join pg_catalog.pg_roles b 
		on (m.roleid = b.oid)
		where m.member = r.oid
	) as memberof,
	r.rolreplication,
	r.rolauditadmin,
	r.rolsystemadmin,
	r.roluseft
from pg_catalog.pg_roles r
order by 1;
```

### **2.2 手動建立外部伺服器**

（1）使用資料庫管理員透過 GaussDB (DWS) 提供的資料庫客戶端（以上的環境博主都是搭建在[cnaaa伺服器](https://www.cnaaa.com/)上，感興趣的夥伴可以部署自己嘗試下。）連線預設資料庫 postgres。例如：透過 gsql 客戶端登入資料庫的使用者可以使用以下兩種方法中的一種進行連線：

- （a）如果已經登入了 gsql 客戶端，可以執行以下命令切換資料庫和使用者，根據提示輸入密碼：

```text
\c postgres dbadmin;
```

- （b）如果尚未登入 gsql 客戶端，或者已經登入了 gsql 客戶端執行 \q 退出 gsql 後，執行以下命令重新進行連線，根據提示輸入密碼：

```text
gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -r
```

（2）執行以下命令查詢自動建立的外部伺服器的資訊。

```text
SELECT * FROM pg_foreign_server;
```

查詢結果中，每一行代表一個外部伺服器的資訊。與 MRS 資料來源連線相關聯的外部伺服器包含以下資訊：

- （a）srvname 值包含 “hdfs_server” 字樣以及 MRS 叢集的 ID，此 ID 與 MRS 管理控制檯的叢集列表 MRS ID 相同。
- （b）srvoptions 欄位中的 address 引數為 MRS 叢集的主備節點的 IP 地址及埠。

您可以根據上述資訊找到您所要的外部伺服器，並記錄下它的 srvname 和 srvoptions 的值。

（3）切換為即將建立外部伺服器的使用者去連線其對應的資料庫。在本示例中，執行以下命令，使用建立使用者和資料庫並授予外表許可權中建立的普通使用者 dbuser 連線其建立的資料庫 mydatabase 。

```text
\c mydatabase dbuser;
```

（4）建立外部伺服器，建立外部伺服器的詳細語法，請參見 CREATE SERVER。示例如下：

```text
CREATE SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca FOREIGN DATA WRAPPER HDFS_FDW 
OPTIONS (
	address '192.168.1.245:25000,192.168.1.218:25000', 
	hdfscfgpath '/MRS/8f79ada0-d998-4026-9020-80d6de2692ca',
	type 'hdfs'
);
```

（5） 檢視外部伺服器，返回結果如下所示，表示已經建立成功：

```text
SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';
```

## 3 建立外表

在 GaussDB (DWS) 資料庫中建立一個 Hadoop 外表，用來訪問儲存在 MRS HDFS 檔案系統上的 Hadoop 結構化資料。Hadoop 外表是隻讀的，只能用於查詢操作，可直接使用 SELECT 查詢其資料，您可以按照以下步驟建立外表：

第一步： 請確保您已經完成前提條件

第二步：根據建立外表（CREATE FOREIGN TABLE (SQL on Hadoop or OBS)）的語法描述，需要先獲取以下資訊：

第三步：獲取 MRS 資料來源的 HDFS 路徑

第四步：獲取 MRS 資料來源連線的外部伺服器資訊

第五步：建立外表

### **3.1 前提條件**

- 已建立 MRS 叢集，並將資料匯入 Hive/Spark 資料庫中的 ORC 表。
- GaussDB (DWS) 叢集已建立 MRS 資料來源連線。

### **3.2 獲取 MRS 資料來源的 HDFS 路徑**

有兩種方法可以檢視：

方法一：

- 對於 Hive 資料，可以登入 MRS 的 Hive 客戶端，執行以下命令檢視錶的詳細資訊，並記錄下 location 引數中的資料儲存路徑。

```text
use <database_name>;
desc formatted <table_name>;
```

- 例如，返回結果中 location 引數值為 “hdfs://hacluster/user/hive/warehouse/demo.db/product_info_orc/”，則記錄 HDFS 路徑為 “/user/hive/warehouse/demo.db/product_info_orc/”。

方法二：

按以下步驟獲取 HDFS 路徑。

- （a）登入 MRS 管理控制檯。
- （b）選擇 “叢集列表> 現有叢集”，單擊要檢視的叢集名稱，進入叢集基本資訊頁面。
- （c）單擊 “檔案管理”，選擇 “HDFS 檔案列表”。
- （d）進入您要匯入到 GaussDB (DWS) 叢集的資料的儲存目錄，並記錄其路徑。

### **3.3 獲取 MRS 資料來源連線的外部伺服器資訊**

- （1）使用建立外部伺服器的使用者去連線其對應的資料庫。

是否使用普通使用者在自定義資料庫中建立外表，請根據需求進行選擇：

**場景一：是**

（a）請先確保，您已按照手動建立外部伺服器章節中的步驟，建立了普通使用者 dbuser 和它的資料庫 mydatabase，並在 mydatabase 中手動建立了一個外部伺服器。

（b）使用使用者 dbuser 透過 GaussDB (DWS) 提供的資料庫客戶端連線資料庫 mydatabase。

（c）如果已經使用 gsql 客戶端連線至資料庫，可以直接執行如下命令進行使用者和資料庫切換，根據介面提示輸入密碼：

```text
\c mydatabase dbuser;
```

**場景二：否**

當您透過 GaussDB (DWS) 管理控制檯建立 MRS 資料來源連線時，資料庫管理員 dbadmin 會在預設資料庫 postgres 中自動建立一個外部伺服器。因此，如果使用資料庫管理員 dbadmin 在預設資料庫 postgres 中建立外表，需要透過 GaussDB (DWS) 提供的資料庫客戶端工具連線資料庫。例如，使用 gsql 客戶端的使用者透過如下命令連線資料庫，根據介面提示輸入密碼：

```text
gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -r
```

- （2）執行以下命令，檢視已建立的 MRS 資料來源連線的外部伺服器資訊。

```text
SELECT * FROM pg_foreign_server;
```

查詢結果中，每一行代表一個外部伺服器的資訊。與 MRS 資料來源連線相關聯的外部伺服器包含以下資訊：

- o srvname 值包含 “hdfs_server” 字樣以及 MRS 叢集的 ID，此 ID 與 MRS 管理控制檯的叢集列表 MRS ID 相同。
- o srvoptions 欄位中的 address 引數為 MRS 叢集的主備節點的 IP 地址及埠。

您可以根據上述資訊找到您所要的外部伺服器，並記錄下它的 srvname 和 srvoptions 的值。

### **3.4 建立外表**

當完成獲取 MRS 資料來源連線的外部伺服器資訊和獲取 MRS 資料來源的 HDFS 路徑後，就可以建立一個外表，用於讀取 MRS 資料來源資料。

建立外表的語法格式如下。

```text
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name 
( [ { column_name type_name 
    [ { [CONSTRAINT constraint_name] NULL |
    [CONSTRAINT constraint_name] NOT NULL |
      column_constraint [...]} ] |
      table_constraint [, ...]} [, ...] ] ) 
    SERVER dfs_server 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;
```

根據以上資訊，建立外表命令如下所示：

```text
DROP FOREIGN TABLE IF EXISTS foreign_product_info;
CREATE FOREIGN TABLE foreign_product_info
(
    product_price                integer        not null,
    product_id                   char(30)       not null,
    product_time                 date,
    product_level                char(10),
    product_name                 varchar(200),
    product_type1                varchar(20),
    product_type2                char(10),
    product_monthly_sales_cnt    integer,
    product_comment_time         date,
    product_comment_num          integer,
    product_comment_content      varchar(200)                      
) SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca 
OPTIONS (
format 'orc', 
encoding 'utf8',
foldername '/user/hive/warehouse/demo.db/product_info_orc/'
) 
DISTRIBUTE BY ROUNDROBIN;
```

## 4 執行資料匯入

### **4.1 直接查詢外表檢視 MRS 資料來源的資料**

如果資料量較少，可直接使用 SELECT 查詢外表，即可檢視到 MRS 資料來源的資料，執行以下命令，則可以從外表查詢資料。

```text
SELECT * FROM foreign_product_info;
```

查詢結果顯示如資料檔案中所示的資料，表示匯入成功。查詢結果的結尾將顯示以下資訊：(20 rows)，透過外表查詢到資料後，使用者可以將資料插入資料庫的普通表。

### **4.2 匯入資料後查詢資料**

- （1）在 GaussDB (DWS) 資料庫中，建立匯入資料的目標表，用於儲存匯入的資料。該表的表結構必須與建立外表中建立的外表的表結構保持一致，即欄位個數、欄位型別要完全一致。

例如，建立一個名為 product_info 的表，示例如下：

```text
DROP TABLE IF EXISTS product_info;
CREATE TABLE product_info
(
    product_price                integer        not null,
    product_id                   char(30)       not null,
    product_time                 date,
    product_level                char(10),
    product_name                 varchar(200),
    product_type1                varchar(20),
    product_type2                char(10),
    product_monthly_sales_cnt    integer,
    product_comment_time         date,
    product_comment_num          integer,
    product_comment_content      varchar(200)                   
) 
with (
orientation = column,
compression=middle
) 
DISTRIBUTE BY HASH (product_id);
```

- （2）執行 “INSERT INTO .. SELECT ..” 命令從外表匯入資料到目標表，示例：

```text
INSERT INTO product_info SELECT * FROM foreign_product_info;
```

若出現以下類似資訊，說明資料匯入成功。

```text
INSERT 0 20
```

- （3）執行 SELECT 命令，檢視從 MRS 匯入到 GaussDB (DWS) 中的資料。

```text
SELECT * FROM product_info;
```

查詢結果顯示如資料檔案中所示的資料，表示匯入成功。查詢結果的結尾將顯示以下資訊：

```text
(20 rows)
```

## 5 清除資源

當完成本教程的示例後，如果您不再需要使用本示例中建立的資源，您可以刪除這些資源，以免資源浪費或佔用您的配額。

### **5.1 刪除外表和目標表**

（1）（可選）如果執行了匯入資料後查詢資料，請執行以下命令，刪除目標表。

```text
DROP TABLE product_info;
```

（2）執行以下命令，刪除外表。

```text
DROP FOREIGN TABLE foreign_product_info;
```

### **5.2 刪除手動建立的外部伺服器**

如果執行了手動建立外部伺服器，請按照以下步驟刪除外部伺服器、資料庫和使用者。

（1）使用建立外部伺服器的使用者透過 GaussDB (DWS) 提供的資料庫客戶端連線到外部伺服器所在的資料庫。例如，使用 gsql 客戶端的使用者可以透過以下兩種方法中的一種進行連線：

（a）如果已經登入了 gsql 客戶端，可以執行以下命令進行切換：

```text
\c mydatabase dbuser;
```

（b）如果已經登入了 gsql 客戶端，您也可以執行 \q 退出 gsql 後，再執行以下命令重新進行連線：

```text
gsql -d mydatabase -h 192.168.2.30 -U dbuser -p 8000 -r
```

（2）刪除手動建立的外部伺服器。執行以下命令進行刪除，詳細語法請參見 DROP SERVER：

```text
DROP SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca;
```

返回以下資訊表示刪除成功：

```text
DROP SERVER
```

檢視外部伺服器：

```text
SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';
```

返回結果如下所示，表示已經刪除成功：

### **5.3 刪除自定義資料庫。**

透過 GaussDB (DWS) 提供的資料庫客戶端連線預設資料庫 postgres。如果已經登入了 gsql 客戶端，可以直接執行如下命令進行切換：

```text
\c postgres
```

執行以下命令，刪除自定義資料庫：

```text
DROP DATABASE mydatabase;
```

返回以下資訊表示刪除成功：

```text
DROP DATABASE
```

### **5.4 使用管理員使用者，刪除本示例中建立的普通使用者。**

使用資料庫管理員使用者透過 GaussDB (DWS) 提供的資料庫客戶端連線資料庫。如果已經登入了 gsql 客戶端，可以直接執行如下命令進行切換：

```text
\c postgres dbadmin
```

執行以下命令回收建立外部伺服器的許可權：

```text
REVOKE ALL ON FOREIGN DATA WRAPPER hdfs_fdw FROM dbuser;
```

其中 FOREIGN DATA WRAPPER 的名字只能是 hdfs_fdw，dbuser 為建立 SERVER 的使用者名稱，執行以下命令刪除使用者：

```text
DROP USER dbuser;
```

可使用 \du 命令查詢使用者，確認使用者是否已經刪除。</table_name></database_name>

5 步教你將 MRS 資料匯入 DWS

相關文章