Hive "insert into" doesnt add values

Question

Im new to hadoop etc. Connect via beeline to hiveserver2. Then I create table:

create table test02(id int, name string);

Table creates and I try to insert values:

insert into test02(id, name) values (1, "user1");

And nothing happens. table02 and values__tmp__table__1 are created but they are both empty. Hadoop directory "/user/$username/warehouse/test01" is empty to.

0: jdbc:hive2://localhost:10000> insert into test02 values (1,"user1");  
No rows affected (2.284 seconds)
0: jdbc:hive2://localhost:10000> select * from test02;
+------------+--------------+
| test02.id  | test02.name  |
+------------+--------------+
+------------+--------------+
No rows selected (0.326 seconds)
0: jdbc:hive2://localhost:10000> show tables;
+------------------------+
|        tab_name        |
+------------------------+
| test02                 |
| values__tmp__table__1  |
+------------------------+
2 rows selected (0.137 seconds)

This smells like a permission issue. Who has permssion to write to the hive data warehouse? — Matt Andruff, Nov 18 '21 at 18:58

Rohit Dhiman · Answer 1 · 2021-11-20T10:09:13.090

Temp tables like these are created when hive needs to manage intermediate data during an operation. Hive automatically deletes all temporary tables at the end of the Hive session in which they are created. If you close the session and open it again, you won't find the temp table.

https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.5.0/bk_data-access/content/temp-tables.html

Insert data like this ->

insert into test02 values (999, "user_new");

Data would be inserted into test02 and a temp table like values__tmp__table__1 (temp table will gone after the hive session).

score 0 · Answer 2 · edited Nov 26 '21 at 00:40

I found a solution. I'm new to Hadoop&co, so the answer was not obvious to me.

First, I turned Hive logging to level ERROR to see the problem:

Find hive-exec-log4j2.properties ({your hive directory}/conf/)
Find property.hive.log.level and set the value to ERROR (..log.level = ERROR)

Then, while executing the command insert into via Beeline, I saw all of the errors. The main error was:

There are 0 datanode(s) running and no node(s) are excluded in this operation

I found the same question elsewhere. The top answer helped me, which was to delete all /tmp/* files (which stored all of my local HDFS data).

Then, like the first time, I initialized namenode (-format) and Hive (ran my metahive script).

The problem was solved—though it did expose another issue, which I'll need to look into: the insert into executes in 25+ seconds.

Hive "insert into" doesnt add values

2 Answers2