Q:

Defined Spark Permanent UDF which can see in metastore but can not use in hive SQL on Spark

CREATE FUNCTION hello AS 'com.dtstack.main.udf.HelloUDF' USING JAR 'hdfs:///172.16.1.151:9000/user/spark/sparkUDF.jar'

and used
select hello(xcval) from xctable

error: org.apache.spark.sql.AnalysisException: Undefined function:
  'hello'. This function is neither a registered temporary function nor
  a permanent function registered in the database 'default'.; line 1 pos
  7

Can anyone help me?

A:

For creating a permanent function in hive, you need to have the jar placed on the hive.auxiliary.path.
hive.auxiliary.path is the default location for hive to read udf, if your jar file not available on the location you won't be able to access it.
because when you create a function, hive know's the location of your jar as "hdfs:///172.16.1.151:9000/user/spark/sparkUDF.jar" but to make it available for spark you would have to deploy it on the auxiliary path because once your hive session closes, hive only stores the definition of your function but not the location and for location it would go to auxiliary path. 
For more information around udf deployment please have a look at https://www.cloudera.com/documentation/enterprise/5-4-x/topics/cm_mc_hive_udf.html

