使用非原始数据类型创建 UDF 函数并在 Spark-sql 查询中使用:Scala [英] Creting UDF function with NonPrimitive Data Type and using in Spark-sql Query: Scala

问题描述

我正在 scala 中创建一个函数，我想在我的 spark-sql 查询中使用它.我的查询在 hive 中工作正常，或者如果我在 spark sql 中提供相同的查询但我在多个使用相同的查询地方所以我想将它创建为可重用的函数/方法，所以只要需要我就可以调用它.我在 Scala 类中创建了以下函数.

def date_part(date_column:Column) = {val m1: Column = month(to_date(from_unixtime(unix_timestamp(date_column, "dd-MM-yyyy"))))//赋值为01,02...等m1匹配{案例 01 =>concat(concat(year(to_date(from_unixtime(unix_timestamp(date_column,"dd-MM-yyyy"))))-1,'-'),substr(year(to_date(from_unixtime(unix_timestamp(date_column,"dd-MM-))yyyy")))),3,4))//等等..案例_ =>其他一些逻辑"}}

但它显示多个错误.

1. 对于 01:

<块引用>

◾十进制整数文字可能没有前导零.(八进制语法已过时.)

◾类型不匹配；发现:需要 Int(0):org.apache.spark.sql.Column.

2. 对于-":

<块引用>

类型不匹配；找到:需要字符('-'):org.apache.spark.sql.Column.

3. 对于substr":

<块引用>

未找到:值 substr.

另外，如果我创建任何简单的函数，也将类型作为列，我将无法注册它，因为我在列格式中无法获得错误.对于所有原始数据类型(字符串，长，Int)它工作正常.但在我的情况下，类型是列我无法做到这一点.有人可以指导我应该如何做到这一点.截至目前我在堆栈溢出中发现我需要将此函数与 df 一起使用，然后需要将此 df 转换为临时表.有人可以吗请指导我使用任何其他替代方法，以便我无需对现有代码进行太多更改即可使用此功能.

解决方案

尝试下面的代码.

scala>导入 org.joda.time.format._导入 org.joda.time.format._标度>spark.udf.register("datePart",(date:String) => DateTimeFormat.forPattern("MM-dd-yyyy").parseDateTime(date).toString(DateTimeFormat.forPattern("MMyyyy")))res102: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function1>,StringType,Some(List(StringType)))标度>spark.sql("""select datePart("03-01-2019") 作为日期部分""").show+--------+|日期部分|+--------+|032019|+--------+

I am creating one function in scala which i want to use in my spark-sql query.my query is working fine in hive or if i am giving the same query in spark sql but the same query i'm using at multiple places so i want to create it as reusable function/method so whenever its required i can just call it. I have created below function in my scala class.

def date_part(date_column:Column) = {
    val m1: Column = month(to_date(from_unixtime(unix_timestamp(date_column, "dd-MM-yyyy")))) //give  value as 01,02...etc

    m1 match {
        case 01 => concat(concat(year(to_date(from_unixtime(unix_timestamp(date_column, "dd-MM- yyyy"))))-1,'-'),substr(year(to_date(from_unixtime(unix_timestamp(date_column, "dd-MM-yyyy")))),3,4))
        //etc..
        case _ => "some other logic"
    }
}

but its showing multiple error.

1. For 01:

◾Decimal integer literals may not have a leading zero. (Octal syntax is obsolete.)

◾type mismatch; found : Int(0) required: org.apache.spark.sql.Column.

2. For '-':

type mismatch; found : Char('-') required: org.apache.spark.sql.Column.

3. For 'substr':

not found: value substr.

also that if I'm creating any simple function also with type as column I'm not able to register it as I'm getting error not possible in columnar format.and for all primitive data types(String,Long,Int) its working fine.But in my case type is column so I'm not able to do this.Can someone please guide me how should i do this.as of now I found on stack-overflow that i need use this function with df and then need to convert this df as temp table.can someone please guide me any other alternate way so without much changes in my existing code i can use this functionality.

解决方案

Try Below Code.

scala> import org.joda.time.format._
import org.joda.time.format._

scala> spark.udf.register("datePart",(date:String) => DateTimeFormat.forPattern("MM-dd-yyyy").parseDateTime(date).toString(DateTimeFormat.forPattern("MMyyyy")))
res102: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function1>,StringType,Some(List(StringType)))

scala> spark.sql("""select datePart("03-01-2019") as datepart""").show
+--------+
|datepart|
+--------+
|  032019|
+--------+

这篇关于使用非原始数据类型创建 UDF 函数并在 Spark-sql 查询中使用:Scala的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！