hive数据库不能插入

Question

hive数据库不能插入

错误

hive> insert into stu values(1,"ss");
Query ID = root_20240723153602_5ddf2ba6-e1e0-44fe-a679-007a09d9bd6b
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1721718496900_0003, Tracking URL = http://node1:8088/proxy/application_1721718496900_0003/
Kill Command = /export/servers/hadoop/bin/mapred job  -kill job_1721718496900_0003
Hadoop job information for Stage-1: number of mappers: 0; number of reducers: 0
2024-07-23 15:37:08,157 Stage-1 map = 0%,  reduce = 0%
Ended Job = job_1721718496900_0003 with errors
Error during job, obtaining debugging information...
FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 0 msec

yarn-site。xml

<?xml version="1.0"?>
<!--
  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.
-->
<configuration>

<!-- Site specific YARN configuration properties -->
 <property>
                <name>yarn.resourcemanager.hostname</name>
                <value>node1</value>
                <description>ResourceManager设置在node1节点</description>
        </property>

        <property>
                <name>yarn.nodemanager.local-dirs</name>a
                <value>/data/nm-local</value>
                <description>NodeManager中间数据本地存储路径</description>
        </property>

        <property>
                <name>yarn.nodemanager.log-dirs</name>
                <value>/data/nm-log</value>
                <description>NodeManager数据日志本地存储路径</description>
        </property>

        <property>
                <name>yarn.nodemanager.aux-services</name>
                <value>mapreduce_shuffle</value>
                <description>为MapReduce程序开启shuffle服务</description>
        </property>

        <property>
                <name>yarn.log.server.url</name>
                <value>http://node1:19888/jobhistory/logs</value>
                <description>历史服务器URL</description>
        </property>

        <property>
                <name>yarn.web-proxy.address</name>
                <value>node1:8088</value>
                <description>代理服务器主机和端口</description>
        </property>

        <property>
                <name>yarn.log-aggregation-enable</name>
                <value>true</value>
                <description>开启日志聚合</description>
        </property>

        <property>
                <name>yarn.nodemanager.remote-app-log-dir</name>
                <value>/tmp/logs</value>
                <description>程序日志HDSF的存储路径</description>
        </property>

        <property>
                <name>yarn.resourcemanager.scheduler.calss</name>
                <value>org.apache.hadoop.yarn.server.resourcemanager.scheduler.fair.FairScheduler</value>
                <description>选择公平调度器</description>
        </property>
<property>
<name>yarn.application.classpath</name>

<value>
/opt/module/hadoop-3.1.3/etc/hadoop,
/opt/module/hadoop-3.1.3/share/hadoop/common/*,
/opt/module/hadoop-3.1.3/share/hadoop/common/lib/*,
/opt/module/hadoop-3.1.3/share/hadoop/hdfs/*,
/opt/module/hadoop-3.1.3/share/hadoop/hdfs/lib/*,
/opt/module/hadoop-3.1.3/share/hadoop/mapreduce/*,
/opt/module/hadoop-3.1.3/share/hadoop/mapreduce/lib/*,
/opt/module/hadoop-3.1.3/share/hadoop/yarn/*,
/opt/module/hadoop-3.1.3/share/hadoop/yarn/lib/*
</value>
</property>
<property>
   <name>yarn.scheduler.minimum-allocation-mb</name>
   <value>2048</value>
   <description>default value is 1024</description>
</property>

</configuration>

hive-site。xml

<configuration>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>                        #此处为MySQL用户
  </property>

  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</value>         #此处为MySQL自己设置的密码
  </property>

  <property>
    <name>hive.server2.thrift.bind.host</name>
    <value>node1</value>
  </property>

  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://node1:9083</value>
  </property>

  <property>
    <name>hive.metastore.event.db.notification.api.auth</name>
    <value>false</value>
  </property>

</configuration>

Answer 1

该回答引用自GPT-3.5, 由博主 GISer Liu 编写：

问题分析

根据用户提供的错误信息和配置文件，Hive插入操作失败，返回代码为2。主要错误信息如下：

FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

关键点分析

1. MapReduce任务失败：错误信息显示MapReduce任务在执行过程中失败。
2. 配置文件检查：用户提供了yarn-site.xml和hive-site.xml的配置，需要检查这些配置是否正确。
3. 日志信息：需要进一步查看详细的日志信息来定位具体问题。

解决方案

1. 检查配置文件

首先，检查yarn-site.xml和hive-site.xml的配置是否正确。特别是以下几个关键配置：

• yarn.resourcemanager.hostname
• yarn.nodemanager.local-dirs
• yarn.nodemanager.log-dirs
• hive.metastore.uris

2. 检查HDFS路径

确保HDFS路径存在并且有正确的权限：

hdfs dfs -mkdir -p /tmp/logs
hdfs dfs -chmod 777 /tmp/logs

3. 检查Hive表结构

确保表stu存在并且结构正确：

CREATE TABLE IF NOT EXISTS stu (id INT, name STRING);

4. 查看详细日志

查看详细的MapReduce任务日志，定位具体错误：

yarn logs -applicationId <application_id>

5. 检查Hadoop和Hive服务状态

确保Hadoop和Hive的所有服务都正常运行：

# 检查Hadoop服务
jps

# 检查Hive服务
hive --service metastore &
hive --service hiveserver2 &

6. 修改配置文件

根据错误信息和日志，可能需要调整一些配置参数。例如，增加MapReduce的内存配置：

<property>
  <name>mapreduce.map.memory.mb</name>
  <value>2048</value>
</property>
<property>
  <name>mapreduce.reduce.memory.mb</name>
  <value>2048</value>
</property>

具体操作步骤

1. 检查并修正配置文件：

   • 确保yarn-site.xml和hive-site.xml中的配置正确无误。
   • 修正yarn.nodemanager.local-dirs和yarn.nodemanager.log-dirs的路径。

2. 创建并检查HDFS路径：

   • 确保/tmp/logs路径存在并有权限。

3. 检查Hive表结构：

   • 确保表stu存在并且结构正确。

4. 查看详细日志：

   • 使用yarn logs -applicationId <application_id>查看详细日志，定位具体错误。

5. 检查服务状态：

   • 确保Hadoop和Hive的所有服务都正常运行。

6. 调整配置参数：

   • 根据需要调整MapReduce的内存配置。

结论

通过以上步骤，可以逐步排查并解决Hive插入操作失败的问题。关键在于检查配置文件、HDFS路径、Hive表结构以及查看详细日志，确保所有服务正常运行，并根据需要调整配置参数。

如果该回答解决了您的问题，请采纳！如果没有，请私信联系或评论您的疑惑