While Spark SQL doesn't support a range of functions as wide as ANSI SQL does, it has an easy and powerful mechanism for registering a normal Scala function and using it inside the SQL context.
Let's say we would like to find out how many profiles fall under each age group. We have a simple function called ageGroup. Given an age, it returns a string representing the age group:
def fnGroupAge(age: Int, bucket:Int=10) = {
val buckets = Array("0-10", "11-20", "20-30", "31-40", "41-50", "51-60", "61-70", "71-80", "81-90", "91-100", ">100")
val bucket = buckets((age-1)/10)
bucket
}
Now, in order to register this function to be used inside Spark SQL, all that we need to do is give it a name and call the register method of the SQLContext's user-defined function object:
sqlc.udf.register("fnGroupAge", (age:Long)=>ageGroup(age.toInt))
Let's fire our query and see the use of the function in action:
%sql select fnGroupAge(age) as ageGroup...
