spark sql 操作
DSL风格语法
1、查看DataFrame中的内容
scala> df1.show +---+--------+---+ | id| name|age| +---+--------+---+ | 1|zhansgan| 16| | 2| lisi| 18| | 3| wangwu| 21| | 4|xiaofang| 22| +---+--------+---+
2、查看DataFrame部分列的数据
scala> df1.select(df1.col("name")).show
+--------+
| name|
+--------+
|zhansgan|
| lisi|
| wangwu|
|xiaofang|
+--------+
scala> df1.select(col("name"), col("age")).show
+--------+---+
| name|age|
+--------+---+
|zhansgan| 16|
| lisi| 18|
| wangwu| 21|
|xiaofang| 22|
+--------+---+
scala> df1.select("name").show
+--------+
| name|
+--------+
|zhansgan|
| lisi|
| wangwu|
|xiaofang|
+--------+
3、查看DataFrame schema信息
scala> df1.printSchema root |-- id: integer (nullable = false) |-- name: string (nullable = true) |-- age: integer (nullable = false)
4、查询name和age并将age + 1
scala> df1.select(col("name"), col("age") + 1).show
+--------+---------+
| name|(age + 1)|
+--------+---------+
|zhansgan| 17|
| lisi| 19|
| wangwu| 22|
|xiaofang| 23|
+--------+---------+
scala> df1.select(df1("name"), df1("age") + 1).show
+--------+---------+
| name|(age + 1)|
+--------+---------+
|zhansgan| 17|
| lisi| 19|
| wangwu| 22|
|xiaofang| 23|
+--------+---------+
5、过滤年龄大于20的人
scala> df1.filter(col("age") > 20).show
+---+--------+---+
| id| name|age|
+---+--------+---+
| 3| wangwu| 21|
| 4|xiaofang| 22|
+---+--------+---+
6、按年龄分组，并统计年龄相同的人数
scala> df1.groupBy("age").count().show
+---+-----+
|age|count|
+---+-----+
| 16| 1|
| 18| 1|
| 21| 1|
| 22| 1|
+---+-----+
SQL风格
在使用SQL风格前，首先需要将DataFrame注册成表
df1.registerTempTable("t_person")
1、查询年龄最大的前两个人
scala> sqlContext.sql("select * from t_person order by age desc limit 2").show
+---+--------+---+
| id| name|age|
+---+--------+---+
| 4|xiaofang| 22|
| 3| wangwu| 21|
+---+--------+---+
2、显示表的schema信息
scala> sqlContext.sql("desc t_person").show
+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
| id| int| |
| name| string| |
| age| int| |
+--------+---------+-------+
DataFrame api 操作
package bigdata.spark.sql
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkContext, SparkConf}
import scala.reflect.internal.util.TableDef.Column
/**
* Created by Administrator on 2017/4/27.
*/
object SparkSqlDemo {
def main(args: Array[String]) {
val conf = new SparkConf()
conf.setAppName("SparkSqlDemo")
conf.setMaster("local")
val sc = new SparkContext(conf)
val sqlContext = new SQLContext(sc)
val rdd1 = sc.textFile("hdfs://m1:9000/persons.txt").map(_.split(" "))
val rdd2 = rdd1.map(x => Person(x(0).toInt, x(1), x(2).toInt))
// 导入隐式转换，里面包含了RDD隐式转换为DataFrame的方法
import sqlContext.implicits._
// df1现在已经是DataFrame了
val df1 = rdd2.toDF
df1.show
df1.select("age").show()
df1.select(col="age").show
df1.select(df1.col("age")).show
import df1._
df1.select(col("age")).show
df1.select(col("age") > 20).show
df1.select(col("age") + 1).show
df1.filter(col("age") > 20).show()
df1.registerTempTable("t_person")
sqlContext.sql("select * from t_person").show()
sqlContext.sql("select * from t_person order by age desc limit 2").show()
sc.stop()
}
// 这个类必须放在main方法外面，不然的话会报错
case class Person(id:Int, name:String, age:Int)
}
StructType指定Schema
package bigdata.spark.sql
import org.apache.spark.sql.types.{StringType, IntegerType, StructField, StructType}
import org.apache.spark.sql.{Row, SQLContext}
import org.apache.spark.{SparkContext, SparkConf}
import scala.reflect.internal.util.TableDef.Column
/**
* Created by Administrator on 2017/4/27.
*/
object SparkSqlDemo {
def main(args: Array[String]) {
val conf = new SparkConf()
conf.setAppName("SparkSqlDemo")
conf.setMaster("local")
val sc = new SparkContext(conf)
val sqlContext = new SQLContext(sc)
val rdd1 = sc.textFile("hdfs://m1:9000/persons.txt").map(_.split(" "))
val rdd2 = rdd1.map(x => Row(x(0).toInt, x(1), x(2).toInt))
// 创建schema
val schema = StructType(
List(
// 名称 类型 是否可以为空
StructField("id", IntegerType, false),
StructField("name", StringType, false),
StructField("age", IntegerType, false)
)
)
// 创建DataFrame
val df1 = sqlContext.createDataFrame(rdd2, schema)
df1.registerTempTable("t_person")
sqlContext.sql("select * from t_person").show()
sc.stop()
}
}
spark sql操作关系型数据库
spark sql可以从关系型数据库读入数据创建DataFrame，也可以写数据到关系型数据库
1、创建数据库
CREATE DATABASE spark DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
2、创建person表
create table person(id int, name varchar(200), age int);
3、spark 操作关系型数据库
package bigdata.spark.sql
import java.util.Properties
import org.apache.spark.sql.types.{StringType, IntegerType, StructField, StructType}
import org.apache.spark.sql.{SaveMode, Row, SQLContext}
import org.apache.spark.{SparkContext, SparkConf}
import scala.reflect.internal.util.TableDef.Column
/**
* Created by Administrator on 2017/4/27.
*/
object SparkSqlDemo {
def main(args: Array[String]) {
val conf = new SparkConf()
conf.setAppName("SparkSqlDemo")
conf.setMaster("local")
val sc = new SparkContext(conf)
val sqlContext = new SQLContext(sc)
val rdd1 = sc.textFile("hdfs://m1:9000/persons.txt").map(_.split(" "))
val rdd2 = rdd1.map(x => Row(x(0).toInt, x(1), x(2).toInt))
// 创建schema
val schema = StructType(
List(
// 名称 类型 是否可以为空
StructField("id", IntegerType, false),
StructField("name", StringType, false),
StructField("age", IntegerType, false)
)
)
val props = new Properties()
props.put("user", "root")
props.put("password", "root")
// 创建DataFrame
val df1 = sqlContext.createDataFrame(rdd2, schema)
// 以追加的模式写入数据库
df1.write.mode(SaveMode.Append).jdbc("jdbc:mysql://m1:3306/spark", "person", props)
// 从数据库中读数据
sqlContext.read.jdbc("jdbc:mysql://m1:3306/spark", "person", props).show()
sc.stop()
}
}
