Integration with Hive UDFs/UDAFs/UDTFs

Description

Spark SQL supports integration of Hive UDFs, UDAFs and UDTFs. Similar to Spark UDFs and UDAFs, Hive UDFs work on a single row as input and generate a single row as output, while Hive UDAFs operate on multiple rows and return a single aggregated row as a result. In addition, Hive also supports UDTFs (User Defined Tabular Functions) that act on one row as input and return multiple rows as output. To use Hive UDFs/UDAFs/UTFs, the user should register them in Spark, and then use them in Spark SQL queries.

Examples

Hive has two UDF interfaces: UDF and GenericUDF. An example below uses GenericUDFAbs derived from GenericUDF.

  1. -- Register `GenericUDFAbs` and use it in Spark SQL.
  2. -- Note that, if you use your own programmed one, you need to add a JAR containing it
  3. -- into a classpath,
  4. -- e.g., ADD JAR yourHiveUDF.jar;
  5. CREATE TEMPORARY FUNCTION testUDF AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs';
  7. SELECT * FROM t;
  8. +-----+
  9. |value|
  10. +-----+
  11. | -1.0|
  12. |  2.0|
  13. | -3.0|
  14. +-----+
  16. SELECT testUDF(value) FROM t;
  17. +--------------+
  18. |testUDF(value)|
  19. +--------------+
  20. |           1.0|
  21. |           2.0|
  22. |           3.0|
  23. +--------------+
  25. -- Register `UDFSubstr` and use it in Spark SQL.
  26. -- Note that, it can achieve better performance if the return types and method parameters use Java primitives.
  27. -- e.g., UDFSubstr. The data processing method is UTF8String <-> Text <-> String. we can avoid UTF8String <-> Text. 
  28. CREATE TEMPORARY FUNCTION hive_substr AS 'org.apache.hadoop.hive.ql.udf.UDFSubstr';
  30. select hive_substr('Spark SQL', 1, 5) as value;
  31. +-----+
  32. |value|
  33. +-----+
  34. |Spark|
  35. +-----+

An example below uses GenericUDTFExplode derived from GenericUDTF.

  1. -- Register `GenericUDTFExplode` and use it in Spark SQL
  2. CREATE TEMPORARY FUNCTION hiveUDTF
  3.     AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode';
  5. SELECT * FROM t;
  6. +------+
  7. | value|
  8. +------+
  9. |[1, 2]|
  10. |[3, 4]|
  11. +------+
  13. SELECT hiveUDTF(value) FROM t;
  14. +---+
  15. |col|
  16. +---+
  17. |  1|
  18. |  2|
  19. |  3|
  20. |  4|
  21. +---+

Hive has two UDAF interfaces: UDAF and GenericUDAFResolver. An example below uses GenericUDAFSum derived from GenericUDAFResolver.

  1. -- Register `GenericUDAFSum` and use it in Spark SQL
  2. CREATE TEMPORARY FUNCTION hiveUDAF
  3.     AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFSum';
  5. SELECT * FROM t;
  6. +---+-----+
  7. |key|value|
  8. +---+-----+
  9. |  a|    1|
  10. |  a|    2|
  11. |  b|    3|
  12. +---+-----+
  14. SELECT key, hiveUDAF(value) FROM t GROUP BY key;
  15. +---+---------------+
  16. |key|hiveUDAF(value)|
  17. +---+---------------+
  18. |  b|              3|
  19. |  a|              3|
  20. +---+---------------+