创建外表

前提条件

• 已创建MRS集群，并将数据导入Hive/Spark数据库中的ORC表。

  请参见MRS集群上的数据准备。

• GaussDB(DWS)集群已创建MRS数据源连接。

  具体操作请参见《数据仓库服务管理指南》的创建MRS数据源连接。

获取MRS数据源的HDFS路径

有两种方法可以查看：

• 方法一：

  对于Hive数据，可以登录MRS的Hive客户端（参见2），执行以下命令查看表的详细信息，并记录下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路径。

  1. 登录MRS管理控制台。
  2. 选择“集群列表 > 现有集群”，单击要查看的集群名称，进入集群基本信息页面。
  3. 单击“文件管理”，选择“HDFS文件列表”。
  4. 进入您要导入到GaussDB(DWS)集群的数据的存储目录，并记录其路径。
     图1 在MRS上查看数据存储路径
     

获取MRS数据源连接的外部服务器信息

1. 使用创建外部服务器的用户去连接其对应的数据库。

   是否使用普通用户在自定义数据库中创建外表，请根据需求进行选择：

   • 是
     1. 请先确保，您已按照手动创建外部服务器章节中的步骤，创建了普通用户dbuser和它的数据库mydatabase，并在mydatabase中手动创建了一个外部服务器。
     2. 使用用户dbuser通过GaussDB(DWS)提供的数据库客户端连接数据库mydatabase。
        如果已经使用gsql客户端连接至数据库，可以直接执行如下命令进行用户和数据库切换：

        1	\c mydatabase dbuser;

        根据界面提示输入密码。

   • 否

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

     1	gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -W password -r

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

   1	SELECT * FROM pg_foreign_server;

   

   也可以执行\desc+命令查看外部服务器信息。

   返回结果如：

   1 2 3 4 5 6

   srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- gsmpp_server | 10 | 13673 | | | | gsmpp_errorinfo_server | 10 | 13678 | | | | hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | | | | {"address=192.168.1.245:25000,192.168.1.218:25000",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs} (3 rows)

   查询结果中，每一行代表一个外部服务器的信息。与MRS数据源连接相关联的外部服务器包含以下信息：

   • srvname值包含“hdfs_server”字样以及MRS集群的ID，此ID与MRS管理控制台的集群列表MRS ID相同。
   • srvoptions字段中的address参数为MRS集群的主备节点的IP地址及端口。

   您可以根据上述信息找到您所要的外部服务器，并记录下它的srvname和srvoptions的值。

创建外表

当完成获取MRS数据源连接的外部服务器信息和获取MRS数据源的HDFS路径后，就可以创建一个外表，用于读取MRS数据源数据。

创建外表的语法格式如下，详细的描述请参见（CREATE FOREIGN TABLE (SQL on Hadoop or OBS)）。

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

例如，创建一个名为"foreign_product_info"的外表，对语法中的参数按如下描述进行设置：

• table_name

  必选。外表的表名。

• 表字段定义
  • column_name：外表中的字段名。
  • type_name：字段的数据类型。

  多个字段用“,”隔开。

  外表的字段个数和字段类型，需要与MRS上保存的数据完全一致。定义字段的数据类型之前，您必须先了解数据类型转换说明。

• SERVER dfs_server

  外表的外部服务器名称，这个server必须存在。外表通过设置外部服务器，从而关联MRS数据源连接并从MRS集群读取数据。

  此处应填写为通过获取MRS数据源连接的外部服务器信息查询到的“srvname”字段的值。

• OPTIONS参数

  用于指定外表数据的各类参数，关键参数如下所示。

  • format：必选参数。取值只支持“orc”。表示数据源文件的格式，只支持Hive的ORC数据文件。
  • foldername：必选参数。表示数据在HDFS的存储目录或数据文件路径。

    如果是启用了Kerberos认证的MRS分析集群，请确保MRS数据源连接的MRS用户，拥有此目录的读取权限。

    请按照获取MRS数据源的HDFS路径中的步骤获取HDFS路径，该路径作为foldername的参数值。

  • encoding：可选参数。外表中数据源文件的编码格式名称，缺省为utf8。
  • DISTRIBUTE BY
    表示外表的数据读取方式。有以下两种方式供选择，在本例中选择ROUNDROBIN。

    • ROUNDROBIN：表示外表在从数据源读取数据时，GaussDB(DWS)集群每一个节点读取随机一部分数据，并组成完整数据。
    • REPLICATION：表示外表在从数据源读取数据时，GaussDB(DWS)集群每一个节点都读取一份完整数据。
  • 语法中的其他参数

    其他参数均为可选参数，用户可以根据自己的需求进行设置，在本例中不需要设置。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DROP FOREIGN TABLE IF EXISTS foreign_product_info;

CREATE FOREIGN TABLE foreign_product_info
(
    product_price                integer        ,
    product_id                   char(30)       ,
    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;

数据类型转换说明

当前用户导入到Hive/Spark的数据在HDFS存储为ORC文件格式，GaussDB(DWS)实际读取HDFS中的ORC文件，并对文件内的数据进行查询分析。

由于Hive/Spark支持的数据类型与GaussDB(DWS)自身支持的数据类型存在差异，在创建外表定义表字段时，您需要了解这两者之间数据类型的对应关系，具体如表1所示：