Spark DataFrames DataSet

Json文件内容:

{"name":"Michael"}

{"name":"Andy", "age":30}
{"name":"Justin", "age":19}
--  加载json文件转换成DataFrames
scala> val df = sqlContext.jsonFile("/spark/json")
warning: there were 1 deprecation warning(s); re-run with -deprecation for details
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]          
// 显示
scala> df.show
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+
 -- 打印schema
scala> df.printSchema
root
 |-- age: long (nullable = true)
 |-- name: string (nullable = true)
 -- 按列选择
scala> df.select("age").show
+----+
| age|
+----+
|null|
|  30|
|  19|
+----+
scala> df.select("age","name").show
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+
 -- 对字段值进行操作 age这一列+2
scala> df.select(df("name"),df("age")+2).show
+-------+---------+
|   name|(age + 2)|
+-------+---------+
|Michael|     null|
|   Andy|       32|
| Justin|       21|
+-------+---------+
 -- filter过滤器
scala> df.filter(df("age")>20).show
+---+----+
|age|name|
+---+----+
| 30|Andy|
+---+----+
-- 分组函数  ---必须要加聚合函数
scala> df.groupBy("age").count().show()
+----+-----+                                                                    
| age|count|
+----+-----+
|null|    1|
|  19|    1|
|  30|    1|
+----+-----+
val sc: SparkContext // An existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)


// Create the DataFrame
val df = sqlContext.read.json("examples/src/main/resources/people.json")


// Show the content of the DataFrame
df.show()
// age  name
// null Michael
// 30   Andy
// 19   Justin


// Print the schema in a tree format
df.printSchema()
// root
// |-- age: long (nullable = true)
// |-- name: string (nullable = true)


// Select only the "name" column
df.select("name").show()
// name
// Michael
// Andy
// Justin


// Select everybody, but increment the age by 1
df.select(df("name"), df("age") + 1).show()
// name    (age + 1)
// Michael null
// Andy    31
// Justin  20


// Select people older than 21
df.filter(df("age") > 21).show()
// age name
// 30  Andy


// Count people by age
df.groupBy("age").count().show()
// age  count
// null 1
// 19   1
// 30   1
-- 读取hive表中的数据转化成DataFrame
scala> val df = sqlContext.sql("select * from t_hdrc_type_month");
scala> df.count
res6: Long = 121144




-- ***********创建RDD----DF和DS互相转化  -----Creating Datasets*****************/


-- DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name.
// Encoders for most common types are automatically provided by importing sqlContext.implicits._
val ds = Seq(1, 2, 3).toDS()
ds.map(_ + 1).collect() // Returns: Array(2, 3, 4)


// Encoders are also created for case classes.
case class Person(name: String, age: Long)
val ds = Seq(Person("Andy", 32)).toDS()


// DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name.
val path = "examples/src/main/resources/people.json"
val people = sqlContext.read.json(path).as[Person]


-- dataFrame转换成DataSet
scala> sqlContext.read.json("hdfs://suixingpay199:9000/user/app/spark/people.json")
res6: org.apache.spark.sql.DataFrame = [age: bigint, name: string]


scala> res6.as[Person]
res8: org.apache.spark.sql.Dataset[Person] = [name: string, age: bigint]




-- 数据源来源于mysql
val jdbcDF = sqlContext.load("jdbc", Map("url" -> "jdbc:mysql://suixingpay190:3306/azkaban?user=root&password=123", "dbtable" -> "projects"))
scala> jdbcDF.show
+---+--------------+------+-------------+-------------+-------+----------------+--------------+--------+--------------------+
| id|          name|active|modified_time|  create_time|version|last_modified_by|   description|enc_type|       settings_blob|
+---+--------------+------+-------------+-------------+-------+----------------+--------------+--------+--------------------+
|  1|          test|  true|1452602214512|1452599858502|      4|         azkaban|          test|       2|[31, -117, 8, 0, ...|
|  2|         touch|  true|1452655867334|1452601523613|      7|         azkaban|         touch|       2|[31, -117, 8, 0, ...|
|  3|DataWherehouse| false|1452688047362|1452662944078|     34|         azkaban|datawherehouse|       2|[31, -117, 8, 0, ...|
|  4|DataWhereHouse|  true|1452689575193|1452688067801|      3|         azkaban|DataWhereHouse|       2|[31, -117, 8, 0, ...|
|  5|  HIveAnalysis|  true|1453440935221|1453440922278|      1|           baige|  HIveAnalysis|       2|[31, -117, 8, 0, ...|
+---+--------------+------+-------------+-------------+-------+----------------+--------------+--------+--------------------+
-- 查询指定列
jdbcDF.select("name","id").show
+--------------+---+
|          name| id|
+--------------+---+
|          test|  1|
|         touch|  2|
|DataWherehouse|  3|
|DataWhereHouse|  4|
|  HIveAnalysis|  5|
+--------------+---+