Create auto increment field in Spark SQL temporary table

Question

I'm new to the Spark environment. I use Spark SQL in my project. I want to create auto increment field in a Spark SQL temporary table. I created UDF, but it didn't work properly. I tried various examples on the internet. This is my Java POJO class:

public class AutoIcrementId  {
    int lastValue;
    public int evaluate() {
        lastValue++;
        return lastValue;
    }
}

mrsrinivas mrsrinivas · Accepted Answer · 2016-12-01T16:10:50

We can use Hive stateful UDF for autoincrement values. Code will goes like this.

package org.apache.hadoop.hive.contrib.udf;

import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;
import org.apache.hadoop.io.LongWritable;

/**
 * UDFRowSequence.
 */
@Description(name = "row_sequence",
    value = "_FUNC_() - Returns a generated row sequence number starting from 1")
@UDFType(deterministic = false, stateful = true)
public class UDFRowSequence extends UDF
{
  private LongWritable result = new LongWritable();

  public UDFRowSequence() {
    result.set(0);
  }

  public LongWritable evaluate() {
    result.set(result.get() + 1);
    return result;
  }
}

// End UDFRowSequence.java

Register UDF:

CREATE TEMPORARY FUNCTION auto_increment_id AS 
   'org.apache.hadoop.hive.contrib.udf.UDFRowSequence'

Usage:

SELECT auto_increment_id() as id, col1, col2 FROM table_name

Similar question was answered here (How to implement auto increment in spark SQL)

Create auto increment field in Spark SQL temporary table

1 Answers