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，示例数据如下所示：

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）执行以下命令切换用户

sudo su - omm

• （c）执行以下命令切换到客户端目录

cd /opt/client

• （d）执行以下命令配置环境变量

source bigdata_env

• （e）如果当前集群已启用 Kerberos 认证，执行以下命令认证当前用户，当前用户需要具有创建 Hive 表的权限。配置拥有对应权限的角色。为用户绑定对应角色。如果当前集群未启用 Kerberos 认证，则无需执行此命令，例如，kinit hiveuser。

kinit MRS集群用户

• （f）执行以下命令启动 Hive 客户端

beeline

（3）在 Hive 中创建数据库 demo，执行以下命令创建数据库：

CREATE DATABASE demo

（4）在数据库 demo 中新建了一个 Hive TEXTFILE 类型的表 product_info，并将数据文件（product_info.txt）导入到该表对应的 HDFS 路径中，执行以下命令切换到 demo 数据库：

USE demo;

执行以下命令，创建表 product_info，表字段按照数据文件中的数据进行定义：

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 完全一致：

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 中。

insert into product_info_orc select * from product_info;

查询表 product_info_orc

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 客户端的用户通过如下语句连接数据库，根据界面提示输入密码。

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

（2）新建一个普通用户，并用它创建一个数据库。新建一个具有创建数据库权限的用户 dbuser：

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

• 切换为新建的用户

SET ROLE dbuser PASSWORD "Bigdata@123";

• 执行以下命令创建数据库：

CREATE DATABASE mydatabase;

• 查询数据库：

SELECT * FROM pg_database;

• 返回结果中有 mydatabase 的信息表示创建成功。

（3）使用管理员用户给普通用户赋予创建外部服务器的权限和使用外表的权限。使用数据库管理员用户通过数据库客户端连接新建的数据库。

• 例如，使用 gsql 客户端的用户可以直接使用如下语句切换为管理员用户去连接新建的数据库，根据提示输入用户密码。

\c mydatabase dbadmin;

• 说明：注意，必须先使用管理员用户连接到将要创建外部服务器和使用外表的数据库，再对普通用户进行授权。
• 默认只有系统管理员才可以创建外部服务器，普通用户需要授权才可以创建，执行以下命令授权：

GRANT ALL ON FOREIGN DATA WRAPPER hdfs_fdw TO dbuser;

• 其中 FOREIGN DATA WRAPPER 的名字只能是 hdfs_fdw，dbuser 为创建 SERVER 的用户名。执行以下命令赋予用户使用外表的权限。

ALTER USER dbuser USEFT;

• 查看用户，返回结果中，dbuser 的信息中包含了 UseFT 权限，表示授权成功：

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服务器上，感兴趣的伙伴可以部署自己尝试下。）连接默认数据库 postgres。例如：通过 gsql 客户端登录数据库的用户可以使用以下两种方法中的一种进行连接：

• （a）如果已经登录了 gsql 客户端，可以执行以下命令切换数据库和用户，根据提示输入密码：

\c postgres dbadmin;

• （b）如果尚未登录 gsql 客户端，或者已经登录了 gsql 客户端执行 \q 退出 gsql 后，执行以下命令重新进行连接，根据提示输入密码：

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

（2）执行以下命令查询自动创建的外部服务器的信息。

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 。

\c mydatabase dbuser;

（4）创建外部服务器，创建外部服务器的详细语法，请参见 CREATE SERVER。示例如下：

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） 查看外部服务器，返回结果如下所示，表示已经创建成功：

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 参数中的数据存储路径。

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 客户端连接至数据库，可以直接执行如下命令进行用户和数据库切换，根据界面提示输入密码：

\c mydatabase dbuser;

场景二：否

当您通过 GaussDB (DWS) 管理控制台创建 MRS 数据源连接时，数据库管理员 dbadmin 会在默认数据库 postgres 中自动创建一个外部服务器。因此，如果使用数据库管理员 dbadmin 在默认数据库 postgres 中创建外表，需要通过 GaussDB (DWS) 提供的数据库客户端工具连接数据库。例如，使用 gsql 客户端的用户通过如下命令连接数据库，根据界面提示输入密码：

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

• （2）执行以下命令，查看已创建的 MRS 数据源连接的外部服务器信息。

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 数据源数据。

创建外表的语法格式如下。

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 ] ] ;

根据以上信息，创建外表命令如下所示：

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 数据源的数据，执行以下命令，则可以从外表查询数据。

SELECT * FROM foreign_product_info;

查询结果显示如数据文件中所示的数据，表示导入成功。查询结果的结尾将显示以下信息：(20 rows)，通过外表查询到数据后，用户可以将数据插入数据库的普通表。

4.2 导入数据后查询数据

• （1）在 GaussDB (DWS) 数据库中，创建导入数据的目标表，用于存储导入的数据。该表的表结构必须与创建外表中创建的外表的表结构保持一致，即字段个数、字段类型要完全一致。

例如，创建一个名为 product_info 的表，示例如下：

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 ..” 命令从外表导入数据到目标表，示例：

INSERT INTO product_info SELECT * FROM foreign_product_info;

若出现以下类似信息，说明数据导入成功。

INSERT 0 20

• （3）执行 SELECT 命令，查看从 MRS 导入到 GaussDB (DWS) 中的数据。

SELECT * FROM product_info;

查询结果显示如数据文件中所示的数据，表示导入成功。查询结果的结尾将显示以下信息：

(20 rows)

5 清除资源

当完成本教程的示例后，如果您不再需要使用本示例中创建的资源，您可以删除这些资源，以免资源浪费或占用您的配额。

5.1 删除外表和目标表

（1）（可选）如果执行了导入数据后查询数据，请执行以下命令，删除目标表。

DROP TABLE product_info;

（2）执行以下命令，删除外表。

DROP FOREIGN TABLE foreign_product_info;

5.2 删除手动创建的外部服务器

如果执行了手动创建外部服务器，请按照以下步骤删除外部服务器、数据库和用户。

（1）使用创建外部服务器的用户通过 GaussDB (DWS) 提供的数据库客户端连接到外部服务器所在的数据库。例如，使用 gsql 客户端的用户可以通过以下两种方法中的一种进行连接：

（a）如果已经登录了 gsql 客户端，可以执行以下命令进行切换：

\c mydatabase dbuser;

（b）如果已经登录了 gsql 客户端，您也可以执行 \q 退出 gsql 后，再执行以下命令重新进行连接：

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

（2）删除手动创建的外部服务器。执行以下命令进行删除，详细语法请参见 DROP SERVER：

DROP SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca;

返回以下信息表示删除成功：

DROP SERVER

查看外部服务器：

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';

返回结果如下所示，表示已经删除成功：

srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+--------+------------
(0 rows)

5.3 删除自定义数据库。

通过 GaussDB (DWS) 提供的数据库客户端连接默认数据库 postgres。如果已经登录了 gsql 客户端，可以直接执行如下命令进行切换：

\c postgres

执行以下命令，删除自定义数据库：

DROP DATABASE mydatabase;

返回以下信息表示删除成功：

DROP DATABASE

5.4 使用管理员用户，删除本示例中创建的普通用户。

使用数据库管理员用户通过 GaussDB (DWS) 提供的数据库客户端连接数据库。如果已经登录了 gsql 客户端，可以直接执行如下命令进行切换：

\c postgres dbadmin

执行以下命令回收创建外部服务器的权限：

REVOKE ALL ON FOREIGN DATA WRAPPER hdfs_fdw FROM dbuser;

其中 FOREIGN DATA WRAPPER 的名字只能是 hdfs_fdw，dbuser 为创建 SERVER 的用户名，执行以下命令删除用户：

DROP USER dbuser;

可使用 \du 命令查询用户，确认用户是否已经删除。