5步教你将MRS数据导入DWS

摘要：GaussDB(DWS)支持在相同网络中，配置一个GaussDB(DWS)集群连接到一个MRS集群，然后将数据从HDFS中的文件读取到GaussDB(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

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)提供的数据库客户端连接默认数据库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;