StarRocks 2.5.0配置Hive外部表后查询报错，无法读取HDFS数据

通过Spark ThriftServer启动Hive并将HDFS设为存储介质，复制了core-site.xml和hdfs-site.xml配置文件后，在StarRocks 2.5.0中创建Hive资源和外部表，查询时出现错误——StarRocks尝试访问本地文件路径而非HDFS路径。

配置文件内容 #

core-site.xml #

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License. See accompanying LICENSE file.
-->

<!-- Put site-specific property overrides in this file. -->

<configuration>
    <property>
            <name>fs.defaultFS</name>
            <value>hdfs://x.x.x.x:9000</value>
    </property>
</configuration>

hdfs-site.xml #

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License. See accompanying LICENSE file.
-->

<!-- Put site-specific property overrides in this file. -->

<configuration>
    <property>
        <name>dfs.replication</name>
            <value>1</value>
    </property>
</configuration>

StarRocks操作步骤 #

1. 创建Hive资源：

+-------+--------------+---------------------+---------------------------+
| Name  | ResourceType | Key                 | Value                     |
+-------+--------------+---------------------+---------------------------+
| hive0 | hive         | hive.metastore.uris | thrift://x.x.x.x:9083 |
+-------+--------------+---------------------+---------------------------+

2. 创建Hive外部表：

CREATE EXTERNAL TABLE `hive_table` (
  `id` int(11) NULL COMMENT ""
) ENGINE=HIVE 
COMMENT "HIVE"
PROPERTIES (
"database" = "rowdata",
"table" = "ht",
"resource" = "hive0",
"hive.metastore.uris"  =  "thrift://x.x.x.x:9083"
);

错误信息 #

ERROR 1064 (HY000): Failed to get remote files, msg: com.starrocks.connector.exception.StarRocksConnectorException: Failed to get hive remote file's metadata on path: RemotePathKey{path='file:/opt/zw/spark-3.3.1-bin-hadoop3/sbin/spark-warehouse/rowdata.db/ht', isRecursive=false}. msg: File /opt/zw/spark-3.3.1-bin-hadoop3/sbin/spark-warehouse/rowdata.db/ht does not exist

1. Hive元数据中的表路径为本地路径
   虽然配置了HDFS作为存储介质，但Spark ThriftServer启动的Hive未正确加载HDFS配置，导致创建rowdata.ht表时，元数据中记录的存储路径是本地文件系统路径，而非HDFS路径。StarRocks通过Hive Metastore获取到该路径后，自然会尝试访问本地文件。

2. StarRocks未获取到HDFS配置信息
   StarRocks 2.5.0访问Hive外部表时，需要HDFS相关配置来识别HDFS路径。仅复制HDFS配置文件但未将其添加到StarRocks的Hive资源中，也未放置到StarRocks节点的指定目录（如$STARROCKS_HOME/conf/hadoop_conf/），导致StarRocks无法解析HDFS路径，或无法确认访问HDFS集群的方式。

3. Spark ThriftServer的Hive配置未生效
   Spark ThriftServer启动时未指定正确的Hadoop配置目录，导致未加载core-site.xml和hdfs-site.xml，Hive仍使用默认的本地存储路径（spark-warehouse目录）创建表。

• 修正Hive表的存储路径
  在Hive客户端执行DESCRIBE FORMATTED rowdata.ht;查看Location字段，确认是本地路径后，迁移表数据到HDFS并修改表路径：

ALTER TABLE rowdata.ht SET LOCATION 'hdfs://x.x.x.x:9000/path/to/ht';

• 向StarRocks提供HDFS配置
  • 方式一：将core-site.xml和hdfs-site.xml复制到所有StarRocks FE和BE节点的$STARROCKS_HOME/conf/hadoop_conf/目录，然后重启StarRocks服务。
  • 方式二：创建Hive资源时直接添加HDFS配置参数：

CREATE EXTERNAL RESOURCE `hive0`
PROPERTIES (
    "type" = "hive",
    "hive.metastore.uris" = "thrift://x.x.x.x:9083",
    "fs.defaultFS" = "hdfs://x.x.x.x:9000",
    "dfs.replication" = "1"
);

• 确保Spark ThriftServer加载正确配置
  启动Spark ThriftServer时通过--hadoop-conf-dir指定Hadoop配置目录：

start-thriftserver.sh --hadoop-conf-dir /path/to/hadoop/conf

内容的提问来源于stack exchange，提问作者Angle Tom