CODEWITHSUNDEEP
hadoophiveapache-pig
Breakinen
Breakinen

Reputation: 619

How can I add row numbers for rows in PIG or HIVE?

I have a problem when adding row numbers using Apache Pig. The problem is that I have a STR_ID column and I want to add a ROW_NUM column for the data in STR_ID, which is the row number of the STR_ID.

For example, here is the input:

STR_ID
------------
3D64B18BC842
BAECEFA8EFB6
346B13E4E240
6D8A9D0249B4
9FD024AA52BA

How do I get the output like:

   STR_ID    |   ROW_NUM
----------------------------
3D64B18BC842 |     1
BAECEFA8EFB6 |     2
346B13E4E240 |     3
6D8A9D0249B4 |     4
9FD024AA52BA |     5

Answers using Pig or Hive are acceptable. Thank you.

Upvotes: 5

Views: 30709

Answers (8)

Keshav Pradeep Ramanath
Keshav Pradeep Ramanath

Reputation: 1687

In Hive:

Query

select str_id,row_number() over() from tabledata;

Output

3D64B18BC842      1
BAECEFA8EFB6      2
346B13E4E240      3
6D8A9D0249B4      4
9FD024AA52BA      5

Upvotes: 5

aprodan
aprodan

Reputation: 569

In Hive:

select
str_id, ROW_NUMBER() OVER() as row_num 
from myTable;

Upvotes: 1

KalEl
KalEl

Reputation: 9136

Hive solution -

select *
  ,rank() over (rand()) as row_num
  from table

Or, if you want to have rows ascending by STR_ID -

select *
  ,rank() over (STR_ID,rank()) as row_num
  from table

Upvotes: 1

Harikrishnan Ck
Harikrishnan Ck

Reputation: 930

From version 0.11, hive supports analytic functions like lead,lag and also row number

https://issues.apache.org/jira/browse/HIVE-896

Upvotes: 1

cabad
cabad

Reputation: 4575

Pig 0.11 introduced a RANK operator that can be used for this purpose.

Upvotes: 2

Trong Tran
Trong Tran

Reputation: 686

This is good answer for you on my example

Step 1. Define row_sequence() function to process for auto increase ID

add jar /Users/trongtran/research/hadoop/dev/hive-0.9.0-bin/lib/hive-contrib-0.9.0.jar;
drop temporary function row_sequence;
create temporary function row_sequence as 'org.apache.hadoop.hive.contrib.udf.UDFRowSequence';

Step 2. Insert unique id & STR

INSERT OVERWRITE TABLE new_table
SELECT 
    row_sequence(),
    STR_ID
FROM old_table;

Upvotes: 1

srini
srini

Reputation: 452

For folks wondering about Pig, I found the best way (currently) is to write your own UDF. I wanted to add row numbers for tuples in a bag. This is the code for that:

import java.io.IOException;
import java.util.Iterator;
import org.apache.pig.EvalFunc;
import org.apache.pig.backend.executionengine.ExecException;
import org.apache.pig.data.BagFactory;
import org.apache.pig.data.DataBag;
import org.apache.pig.data.Tuple;
import org.apache.pig.data.TupleFactory;
import org.apache.pig.impl.logicalLayer.schema.Schema;
import org.apache.pig.data.DataType;

public class RowCounter extends EvalFunc<DataBag> {
TupleFactory mTupleFactory = TupleFactory.getInstance();
BagFactory mBagFactory = BagFactory.getInstance();
public DataBag exec(Tuple input) throws IOException {
    try {
        DataBag output = mBagFactory.newDefaultBag();
        DataBag bg = (DataBag)input.get(0);
        Iterator it = bg.iterator();
        Integer count = new Integer(1);
        while(it.hasNext())
            { Tuple t = (Tuple)it.next();
              t.append(count);
              output.add(t);
              count = count + 1;
            }

        return output;
    } catch (ExecException ee) {
        // error handling goes here
        throw ee;
    }
}
public Schema outputSchema(Schema input) {
     try{
         Schema bagSchema = new Schema();
         bagSchema.add(new Schema.FieldSchema(null, DataType.BAG));

         return new Schema(new Schema.FieldSchema(getSchemaName(this.getClass().getName().toLowerCase(), input),
                                                bagSchema, DataType.BAG));
     }catch (Exception e){
        return null;
     }
    }
}

This code is for reference only. Might not be error-proof.

Upvotes: 1

Steve Severance
Steve Severance

Reputation: 6646

Facebook posted a number of hive UDFs including NumberRows. Depending on your hive version (I believe 0.8) you may need to add an attribute to the class (stateful=true).

Upvotes: 3

Related Questions