CODEWITHSUNDEEP
hivehiveql
Noy
Noy

Reputation: 65

Insert row number into table

I am trying to insert a row number into a table. The row_number() function works when performing a select query but the query doesn't work when I use it as part of an INSERT INTO TABLE query. I have also tried via Create Table As Select but I get the same seemingly generic error.

FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask (state=08S01,code=2)

Example: This does not work.

INSERT INTO TABLE tablea
SELECT
column1,
column2,
row_number() over (order by column2 desc)
FROM
tableb;

Example: This does work

SELECT
column1,
column2,
row_number() over (order by column2 desc)
FROM
tableb;

Any pointers? Thanks!

EDIT: I'm using Hive 1.1.0 as part of CDH 5.4.8.

Upvotes: 2

Views: 4802

Answers (2)

Noy
Noy

Reputation: 65

OK it looks like this is because the storage format was ORC. Setting the table to TEXTFILE and the problem goes away.

Upvotes: 0

Sandesh Jain
Sandesh Jain

Reputation: 742

I have tried the thing you wanted to do and it is working. here is my HQL statements:

create table tablea (id int, string name);

insert into tablea values (1, 'test1');
insert into tablea values (2, 'test2');

create table tableb (id int, name string, row_num int);

insert into tableb select id, name, row_number() over ( order by name desc) from tablea;
select * from tableb;

outcome

+------------+--------------+-----------------+--+
| tableb.id  | tableb.name  | tableb.row_num  |
+------------+--------------+-----------------+--+
| 2          | test2        | 1               |
| 1          | test1        | 2               |
+------------+--------------+-----------------+--+

Upvotes: 3

Related Questions