title: Register a Spark UDAF in SQL
body: <p>There is plenty of documentation online for how to define a UDAF and register it, but I've not seen any example using SQL. For example, let's say I've defined a custom UDAF:</p>
  
  <pre><code>import org.apache.spark.sql.expressions.UserDefinedAggregateFunction    
  
  class MyUDAF extends UserDefinedAggregateFunction {
    ...
  }
  </code></pre>
  
  <p>I understand that you can compile the jar, add it to the Spark classpath, and register and use it with the scala API:</p>
  
  <pre><code>$SPARK_HOME/bin/spark-shell  --jars my-udaf.jar
  &gt; spark.udf.register("myUDAF", new MyUDAF)
  &gt; spark.sql("select myUDAF(column) from ...")
  </code></pre>
  
  <p>In the SQL API, there is <a href="https://docs.databricks.com/spark/latest/spark-sql/language-manual/create-function.html" rel="nofollow noreferrer"><code>CREATE FUNCTION</code></a>, but according to the docs that expects a Hive UDF/UDAF, not a Spark one. That is, the UDAF should subclass <code>org.apache.hadoop.hive.ql.exec.hive.UDAF</code>, not Spark's <code>UserDefinedAggregateFunction</code>. Indeed, if I try, then I get the following error:</p>
  
  <pre><code>spark-sql&gt; CREATE FUNCTION myUDAF AS 'MyUDAF';
  spark-sql&gt; select myUDAF(column) from blah;
  Error in query: No handler for Hive UDF 'MyUDAF'; line 1 pos 7
  </code></pre>
  
  <p>It seems strange that the SQL and scala API's would treat UDAF registration differently. <strong>Is there a way to register a Spark UDAF using the SQL API?</strong> <code>CREATE FUNCTION</code> seems not to work.</p>
  
  <p>Ultimately, what I'm trying to do is register some UDAF's with a Spark thrift server and register them automatically when the server starts. There may be a different approach. For example, <a href="https://medium.com/@anicolaspp/apache-spark-as-a-distributed-sql-engine-4373e254e0f9" rel="nofollow noreferrer">this article</a> starts the thrift server using a Scala class instead of <code>start-thriftserver.sh</code>.</p>