# 创建外表 在DWS数据库中创建一个Hadoop外表，用来访问存储在MRS HDFS文件系统上的Hadoop结构化数据。Hadoop外表是只读的，只能用于查询操作，可直接使用SELECT查询其数据。 #### 前提条件 - 已创建MRS集群，并将数据导入Hive/Spark数据库中的ORC表。 请参见[MRS集群上的数据准备](https://support.huaweicloud.com/migration-dws/dws_15_0034.html#ZH-CN_TOPIC_0000002219983996)。 - DWS集群已创建MRS数据源连接。 具体操作请参见《数据仓库服务管理指南》的[创建MRS数据源连接](https://support.huaweicloud.com/mgtg-dws/dws_01_0057.html)。 #### 获取MRS数据源的HDFS路径 有两种方法可以查看： - **方法一：** 对于Hive数据，可以登录MRS的Hive客户端（参见[2](https://support.huaweicloud.com/migration-dws/dws_15_0034.html#ZH-CN_TOPIC_0000002219983996__zh-cn_topic_0000001764492076_zh-cn_topic_0000001188642198_zh-cn_topic_0000001082830951_zh-cn_topic_0109259515_zh-cn_topic_0101477888_li14725131112614)），执行以下命令查看表的详细信息，并记录下location参数中的数据存储路径。 ``` use ; desc formatted ; ``` 例如，返回结果中location参数值为"hdfs://hacluster/user/hive/warehouse/demo.db/product_info_orc/"，则记录HDFS路径为"/user/hive/warehouse/demo.db/product_info_orc/"。 - **方法二：** 按以下步骤获取HDFS路径。 1. 登录[MRS控制台](https://console.huaweicloud.com/mrs/#/clusterList/existing)。 2. 选择"集群列表 \> 现有集群"，单击要查看的集群名称，进入集群基本信息页面。 3. 单击"文件管理"，选择"HDFS文件列表"。 4. 进入您要导入到DWS集群的数据的存储目录，并记录其路径。 #### 获取MRS数据源连接的外部服务器信息 1. 使用创建外部服务器的用户去连接其对应的数据库。 是否使用普通用户在自定义数据库中创建外表，请根据需求进行选择： - **是** 1. 请先确保，您已按照[手动创建外部服务器](https://support.huaweicloud.com/migration-dws/dws_15_0035.html#ZH-CN_TOPIC_0000002254983697)章节中的步骤，创建了普通用户dbuser和它的数据库mydatabase，并在mydatabase中手动创建了一个外部服务器。 2. 使用用户dbuser通过DWS提供的数据库客户端连接数据库mydatabase。 如果已经使用gsql客户端连接至数据库，可以直接执行如下命令进行用户和数据库切换： ``` \c mydatabase dbuser; ``` 根据界面提示输入密码。 - **否** 当您通过DWS管理控制台创建MRS数据源连接时，数据库管理员dbadmin会在默认数据库postgres中自动创建一个外部服务器。因此，如果使用数据库管理员dbadmin在默认数据库postgres中创建外表，需要通过DWS提供的数据库客户端工具连接数据库。例如，使用gsql客户端的用户通过如下命令连接数据库： ``` gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -W password -r ``` 2. 执行以下命令，查看已创建的MRS数据源连接的外部服务器信息。 ``` SELECT * FROM pg_foreign_server; ``` ![](https://support.huaweicloud.com/migration-dws/public_sys-resources/note_3.0-zh-cn.png) 也可以执行\desc+命令查看外部服务器信息。 返回结果如下： ``` srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- gsmpp_server | 10 | 13673 | | | | 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} (2 rows) ``` 查询结果中，每一行代表一个外部服务器的信息。与MRS数据源连接相关联的外部服务器包含以下信息： - srvname值包含"hdfs_server"字样以及MRS集群的ID，此ID与MRS管理控制台的集群列表MRS ID相同。 - srvoptions字段中的address参数为MRS集群的主备节点的IP地址及端口。 您可以根据上述信息找到您所要的外部服务器，并记录下它的srvname和srvoptions的值。 #### 创建外表 当完成[获取MRS数据源连接的外部服务器信息]和[获取MRS数据源的HDFS路径]后，就可以创建一个外表，用于读取MRS数据源数据。 创建外表的语法格式如下，详细的描述请参见《数据仓库服务SQL语法参考》中"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**：表示外表在从数据源读取数据时，DWS集群每一个节点读取随机一部分数据，并组成完整数据。 - **REPLICATION**：表示外表在从数据源读取数据时，DWS集群每一个节点都读取一份完整数据。 - **语法中的其他参数** 其他参数均为可选参数，用户可以根据自己的需求进行设置，在本例中不需要设置。 根据以上信息，创建外表命令如下所示： ``` 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文件格式，DWS实际读取HDFS中的ORC文件，并对文件内的数据进行查询分析。 由于Hive/Spark支持的数据类型与DWS自身支持的数据类型存在差异，在创建外表定义表字段时，您需要了解这两者之间数据类型的对应关系，具体如[表1]所示： 表1数据类型匹配表 | 类型名称 | DWS的HDFS/OBS外表支持的字段类型 | Hive表字段类型 | Spark表字段类型 | |:---|:---|:---|:---| | 2字节整数 | SMALLINT | SMALLINT | SMALLINT | | 4字节整数 | INTEGER | INT | INT | | 8字节整数 | BIGINT | BIGINT | BIGINT | | 单精度浮点数 | FLOAT4 (REAL) | FLOAT | FLOAT | | 双精度浮点型 | FLOAT8(DOUBLE PRECISION) | DOUBLE | FLOAT | | 科学数据类型 | DECIMAL\[p (,s)\] 最大支持38位精度 | DECIMAL 最大支持38位（Hive 0.11） | DECIMAL | | 日期类型 | DATE | DATE | DATE | | 时间类型 | TIMESTAMP | TIMESTAMP | TIMESTAMP | | Boolean类型 | BOOLEAN | BOOLEAN | BOOLEAN | | Char类型 | CHAR(n) | CHAR (n) | STRING | | VarChar类型 | VARCHAR(n) | VARCHAR (n) | VARCHAR (n) | | 字符串 | TEXT(CLOB) | STRING | STRING |