spark sql基础使用范例

使用spark 1.2.0 scala 2.10.0

cat b.txt
a 1 2 3 4.2 9.8
a 3 0 5 3.5 2.1
b 7 9 9 1.2 2.4
a 7 9 9 2.6 6.2
a 1 2 5 7.7 5.9
a 1 2 3 1.4 0.2
//b.txt出现空值出问题了,也许可以，取决于分隔符


val sqlContext = new org.apache.spark.sql.SQLContext(sc)  //sc已经存在的SparkContext
import sqlContext._
// case class在Scala 2.10里面最多支持22个列，，为了突破这个现实，最好是定义一个类实现Product接口
case class Person(name: String, col1: Int, col2: Int, col3: Int, col4: Double, col5: Double)
val people = sc.textFile("/usr/local/spark-1.2.0/b.txt").map(_.split(" ")).map(p => Person(p(0), p(1).trim.toInt, p(2).trim.toInt, p(3).trim.toInt, p(4).trim.toDouble, p(5).trim.toDouble))
people.registerAsTable("people")


val teenagers = sql("select * from people")
teenagers.map(t => "0: " + t(0) + " 1: " + t(1) + "2: " + t(2) + " 3: " + t(3) + "4: " + t(4) + " 5: " + t(5)).collect().foreach(println)


count
val teenagers = sql("SELECT COUNT(*) FROM people")
teenagers.map(t => "COUNT(*): " + t(0)).collect().foreach(println)


avg
val teenagers = sql("SELECT AVG(col4), AVG(col5) FROM people group by col1, col2, col3")
teenagers.map(t => "AVG1: " + t(0) + "AVG2: " + t(1)).collect().foreach(println)


sum
val teenagers = sql("SELECT SUM(col4), SUM(col5) FROM people group by col1, col2, col3")
teenagers.map(t => "SUM1: " + t(0) + " SUM2: " + t(1)).collect().foreach(println)


min
val teenagers = sql("SELECT MIN(col4), MIN(col5) FROM people group by col1, col2, col3")
teenagers.map(t => "MIN1: " + t(0) + " MIN2: " + t(1)).collect().foreach(println)


val teenagers = sql("SELECT MIN(col4), MIN(col5) FROM people group by col1, col2, col3")
teenagers.map(t => "MIN1: " + t(0) + " MIN2: " + t(1)).collect().foreach(println)


case when
val teenagers = sql("select case WHEN col1 >1 THEN 'aaa' ELSE 'bbb' END FROM people")
teenagers.map(t => "0: " + t(0)).collect().foreach(println)


左联 外连
cat a.txt
333
789
900


cat b.txt
200,aaa
333,bbb
789,bbb
789,ddd
789,ddd
333,bbb
1,abc
2,abc


case class A(col1: Int)
val a = sc.textFile("/usr/local/spark-1.2.0/a.txt").map(_.split(" ")).map(p => A(p(0).trim.toInt))
a.registerAsTable("a")


case class B(col1: Int, col2: String)
val b = sc.textFile("/usr/local/spark-1.2.0/b.txt").map(_.split(",")).map(p => B(p(0).trim.toInt, p(1)))
b.registerAsTable("b")


val teenagers = sql("select a.col1, b.col1, b.col2 FROM a left join b on a.col1 = b.col1")
teenagers.map(t => "a.col1: " + t(0) + "b.col1: " + t(1) + "b.col2: " + t(2)).collect().foreach(println)

单行函数没有测试，估计常用基础的都可以支持