SPARK：如何实现CASE ELSE部分和WHERE LIKE和BETWEEN [英] SPARK : How to implement CASE ELSE part and WHERE LIKE and BETWEEN

问题描述

我有类似下面的数据

hduser@ubuntu:~$ hadoop fs -cat /user/hduser/test_sample/sample1.txt
Eid1,EName1,EDept1,100
Eid2,EmpNm2,EDept1,102
Eid3,EName3,EDept1,101
Eid4,EName4,EDept2,110
Eid5,EName5,EDept2,121
Eid6,EName6,EDept4,99

我想要得到的输出像下面

I want to get the output like below

Eid1,EName1,IT,100
Eid3,EName3,IT,101
Eid5,EName4,ComSc,110
Eid6,EName6,Other,99

现在在SQL中的形式，我可以写

Now in form of SQL I can write

Select emp_id, emp_name, case when emp_dept='EDept1' then 'IT' 
when emp_dept='EDept2' then 'ComSc' else 'Other' end dept_name, emp_sal from emp 
Where emp_name like 'EName%' And emp_sal between 90 and 120

我的问题是1。如何实现CASE语句2. ELSE部分如何实现LIKE部分where子句3.如何在where子句的一部分之间实现。

My question is 1. how to implement ELSE part of CASE Statement 2. How to implement LIKE part of the where clause 3. How to implement between part of the where clause.

我不能使用sqlContext.sql作为SQL还含有分析功能和分析功能没有火花-SQL支持如现在。

I can't use sqlContext.sql as the sql also contain analytical function and analytical function is not supported in spark-sql as of now.

推荐答案

所以，你可以创建这样一个UDF：

So you can create a UDF like this:

val deptUdf = udf[String,String]( dept =>
  dept match {
    case "EDept1" => "IT"
    case "EDept2" => "ComSc"
    case _ => "Other"
  }
)

一旦你这样做，你可以在SELECT语句中使用它。假设你在部门code柱曾与你的部门名称的数据框，你可以这样做：

Once you do that you can use it in a select statement. Assuming you had a DataFrame with your dept name in the "deptCode" column, you could do:

myDf.select(deptUdf($"deptCode") as "deptName")

对于第二和第三部分，也可以在过滤器中使用像功能，并可以实现 使用和：

For the second and third parts, there is a like function you can use in a filter, and you can implement between using and:

myDf.filter($"emp_name" like "EName%" and $"emp_sal" >= 90 and $"emp_sal" <= 120)

这篇关于SPARK：如何实现CASE ELSE部分和WHERE LIKE和BETWEEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！