huaxingao commented on a change in pull request #28104: URL: https://github.com/apache/spark/pull/28104#discussion_r411125464
########## File path: docs/sql-ref-functions-udf-hive.md ########## @@ -19,4 +19,90 @@ license: | limitations under the License. --- -Integration with Hive UDFs/UDAFs/UDTFs \ No newline at end of file +### 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](https://github.com/apache/hive/blob/master/udf/src/java/org/apache/hadoop/hive/ql/exec/UDF.java) and [GenericUDF](https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDF.java). +An example below uses [GenericUDFAbs](https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFAbs.java) derived from `GenericUDF`. + +{% highlight sql %} +-- Register `GenericUDFAbs` and use it in Spark SQL. +-- Note that, if you use your own programmed one, you need to add a JAR containig it +-- into a classpath, +-- e.g., ADD JAR yourHiveUDF.jar; +CREATE TEMPORARY FUNCTION testUDF AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs'; + +SELECT * FROM t; + +-----+ + |value| + +-----+ + | -1.0| + | 2.0| + | -3.0| + +-----+ + +SELECT testUDF(value) FROM t; + +--------------+ + |testUDF(value)| + +--------------+ + | 1.0| + | 2.0| + | 3.0| + +--------------+ +{% endhighlight %} + + +An example below uses [GenericUDTFExplode](https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDTFExplode.java) derived from [GenericUDTF](https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDF.java). + +{% highlight sql %} +-- Register `GenericUDTFExplode` and use it in Spark SQL +CREATE TEMPORARY FUNCTION hiveUDTF + AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode'; + +SELECT * FROM t; + +------+ Review comment: > Also, seems like we should comment these output out. Not sure to comment out the output or not. In SQL syntax section, we didn't comment out any of the output. But in the UDAF SQL example, I commented out the output to be consistent with the scala and java examples. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
