hive直连数据库模式插入mysql表数据失败（相关搜索：数据库）

Question

#hive直连数据库模式插入mysql表数据失败
Hadoop3.1.3，hive3.1.2

hive> create table tttt(id int);
OK
Time taken: 0.723 seconds
hive> show tables;
OK
tb_test
test
tttt
Time taken: 0.058 seconds, Fetched: 3 row(s)
hive> insert into test values(1,1);
Query ID = root_20240901203126_01ff4ce2-2d18-4d99-bc91-9059fbb8ea4b
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_1725193171848_0003, Tracking URL = http://node4:8088/proxy/application_1725193171848_0003/
Kill Command = /opt/hadoop-3.1.3/bin/mapred job  -kill job_1725193171848_0003
Hadoop job information for Stage-1: number of mappers: 0; number of reducers: 0
2024-09-01 20:31:34,824 Stage-1 map = 0%,  reduce = 0%
Ended Job = job_1725193171848_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
hive> insert into test values(1,1);
Query ID = root_20240901203520_8bbcfb12-ba9f-4532-87b0-ce022587a055
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_1725193171848_0004, Tracking URL = http://node4:8088/proxy/application_1725193171848_0004/
Kill Command = /opt/hadoop-3.1.3/bin/mapred job  -kill job_1725193171848_0004
Hadoop job information for Stage-1: number of mappers: 0; number of reducers: 0
2024-09-01 20:35:27,642 Stage-1 map = 0%,  reduce = 0%
Ended Job = job_1725193171848_0004 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

hadoop报错如下：

Application application_1725193171848_0001 failed 2 times due to AM Container for appattempt_1725193171848_0001_000002 exited with exitCode: 1
Failing this attempt.Diagnostics: [2024-09-01 20:21:05.357]Exception from container-launch.
Container id: container_e04_1725193171848_0001_02_000001
Exit code: 1
[2024-09-01 20:21:05.379]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/yarn/exceptions/YarnRuntimeException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
at java.lang.Class.getMethod0(Class.java:3018)
at java.lang.Class.getMethod(Class.java:1784)
at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.yarn.exceptions.YarnRuntimeException
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 7 more
[2024-09-01 20:21:05.380]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/yarn/exceptions/YarnRuntimeException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
at java.lang.Class.getMethod0(Class.java:3018)
at java.lang.Class.getMethod(Class.java:1784)
at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.yarn.exceptions.YarnRuntimeException
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 7 more
For more detailed output, check the application tracking page: http://node4:8088/cluster/app/application_1725193171848_0001 Then click on links to logs of each attempt.
. Failing the application.
![img](https://img-mid.csdnimg.cn/release/static/image/mid/ask/9c66eb09c895490496c93cfd44b97fc9.png "#left")




```bash




[root@node2 ~]# echo $HADOOP_CLASSPATH
/opt/hadoop-3.1.3/share/hadoop/common/:/opt/hadoop-3.1.3/share/hadoop/common/lib/:/opt/hadoop-3.1.3/share/hadoop/hdfs/:/opt/hadoop-3.1.3/share/hadoop/hdfs/lib/:/opt/hadoop-3.1.3/share/hadoop/mapreduce/:/opt/hadoop-3.1.3/share/hadoop/mapreduce/lib/:/opt/hadoop-3.1.3/share/hadoop/yarn/:/opt/hadoop-3.1.3/share/hadoop/yarn/lib/
[root@node2 ~]# echo $HIVE_HOME
/opt/hive-3.1.2

能创建表，但是插入失败，hdfs上也是存在文件的

Application application_1725364422310_0001 failed 2 times due to AM Container for appattempt_1725364422310_0001_000002 exited with exitCode: 1
Failing this attempt.Diagnostics: [2024-09-03 19:54:25.701]Exception from container-launch.
Container id: container_e17_1725364422310_0001_02_000001
Exit code: 1
[2024-09-03 19:54:25.702]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/yarn/exceptions/YarnRuntimeException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
at java.lang.Class.getMethod0(Class.java:3018)
at java.lang.Class.getMethod(Class.java:1784)
at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.yarn.exceptions.YarnRuntimeException
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 7 more
[2024-09-03 19:54:25.703]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/yarn/exceptions/YarnRuntimeException
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
at java.lang.Class.getMethod0(Class.java:3018)
at java.lang.Class.getMethod(Class.java:1784)
at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.yarn.exceptions.YarnRuntimeException
at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 7 more
For more detailed output, check the application tracking page: http://node4:8088/cluster/app/application_1725364422310_0001 Then click on links to logs of each attempt.
. Failing the application.

Answer 1

以下回复引自免费微信小程序：皆我百晓生。 如问题解决，还请采纳。如仍有疑问，请回复留言或私信博主，以便获得更精准的帮助。

这个问题似乎在尝试将一个SQL语句插入到MYSQL的表中，并且出现了一个错误。这个问题可能是因为你的MYSQL连接池已经满或者你的SQL语句是错误的。以下是一些可能的解决方案和示例代码：

解决方案

1. 确保MYSQL连接已开启并正确配置

确保MYSQL连接池已经被正确打开并且设置为可用状态。

CREATE DATABASE TESTDB;
USE TESTDB;

2. 检查SQL语句是否符合要求

检查你正在执行的SQL语句是否包含正确的字段名、列名以及类型。例如：

CREATE TABLE TTTT (
    ID INT,
    NAME VARCHAR(50)
);

3. 验证MYSQL服务器的版本

确保你的MYSQL服务器支持最新的HIVE版本，特别是HIVE 3.1.X。如果没有，你可以考虑升级到HIVE 4.X或更高版本。

4. 清除数据库中的旧记录

如果你使用的是HIVE 3.1.X，可以使用DROP TABLE命令来删除旧的数据：

DROP TABLE IF EXISTS TTTT;

5. 使用--NO-REPL参数

如果你使用的是HIVE 3.1.X，可以使用--NO-REPL参数来避免重新连接到数据库。

CREATE DATABASE TESTDB;
CREATE TABLE TTTT (
    ID INT,
    NAME VARCHAR(50),
    -- 重置为默认值
    DATA AS 'VALUE'
) WITH (NO_REPL = TRUE);

6. 更新HIVE数据库配置

如果你的HIVE数据库配置文件存在问题，可以尝试更新其配置以解决这个问题。

CAT /ETC/HIVE/CONF/SCHEMA.SQL | GREP -Q "INSERT INTO TTTT VALUES(" &&

7. 安装HIVE库

如果上述方法都无法解决问题，你可能需要安装HIVE库。可以通过YUM INSTALL HIVE-SERVER等命令进行安装。

SUDO YUM INSTALL -Y HIVE-SERVER

这些步骤可以帮助你解决这个问题。如果你遇到其他问题，也欢迎随时提问！