hive-mysql-DataFrame

将json文件转成DataFrame：
spark-shell：
1.开启spark-shell
2.val df=spark.read.json(“file:///opt/File/users.json”) 默认读取hdfs，file:// 读取本地文件
3.df.show
java：
val conf = new SparkConf().setAppName(“1”).setMaster(“local[*]”)
val sc = new SparkContext(conf)
val rdd1 = sc.textFile(“in/users.json”)
rdd1.collect().foreach(println)

rdd转换成DataFrame：
方式1：通过反射获取RDD内的Schema
rdd打印出来是地址，.map(.toList)可转换成内容！
建样例类：case class Person(name:String,age:Int)
导入隐式包：import spark.implicits.
val people=sc.textFile(“file:///home/hadooop/data/people.txt”).map(_.split(",")).map(p => Person(p(0), p(1).trim.toInt)).toDF()
people.show
建临时表：people.registerTempTable(“t1”)
查询数据：val teen=spark.sql(“select * from t1 where age>22”)
展示结果：teen.show

方式2：通过编程接口指定Schema
创建样例类：case class Person(name: String,age :Int)
读文件：val people=sc.textFile(“file:///home/hadoop/data/people.txt”)
// 以字符串的方式定义DataFrame的Schema信息
val schemaString = “name age”
//导入所需要的类
import org.apache.spark.sql.Row
import org.apache.spark.sql.types.{StructType, StructField, StringType}
// 根据自定义的字符串schema信息产生DataFrame的Schema
val schema = StructType(schemaString.split(" “).map(fieldName =>StructField(fieldName,StringType, true)))
//将RDD转换成Row
val rowRDD = people.map(_.split(”,")).map(p => Row(p(0), p(1).trim))
// 将Schema作用到RDD上
val peopleDataFrame = spark.createDataFrame(rowRDD, schema)
// 将DataFrame注册成临时表
peopleDataFrame.registerTempTable(“people”)
val results = spark.sql(“SELECT name FROM people”)
results.show

DataFrame转换成RDD：
1.val df=spark.read.json(“file:///opt/File/users.json”)
2.df.rdd.collect

dataframe就是一个rdd,一个schame结构，将rdd通过map方法变成row，val row = rdd.map(_.split(",")).map(x=>Row(x(0),x(1)))与schema一一匹配，变成dataframe
spark SQL操作外部数据源：
操作Parquet文件：是一种流行的列式存储格式，以二进制存储，文件中包含数据与元数据
//Spark SQL写parquet文件
import org.apache.spark.sql.types.{StructType, StructField, StringType,ArrayType,IntegerType}
val schema=StructType(Array(StructField(“name”,StringType),
StructField(“favorite_color”,StringType),
StructField(“favorite_numbers”,ArrayType(IntegerType))))
val rdd=sc.parallelize(List((“Alyssa”,null,Array(3,9,15,20).toList),(“Ben”,“red”,null)))
import org.apache.spark.sql.{Row, SparkSession}
val rowRDD=rdd.map(p=>Row(p._1,p._2,p._3))
val df=spark.createDataFrame(rowRDD,schema)
df.write.parquet("/data/users") //在该目录下生成parquet文件 users也是文件夹
//Spark SQL读parquet文件
val df=spark.read.parquet("/data/users") //该目录下存在parquet文件
df.show
df.printSchema

操作hive表：hive-site.xml拷贝至${SPARK_HOME}/conf下
1.开启hdfs，hive里建表：
scala：
hive>create table toronto(full_name string, ssn string, office_address string);
hive>insert into toronto(full_name, ssn, office_address) values('John S. ', '111-222-333 ', '123 Yonge Street ');
scala>val df=spark.table(“toronto”) //第一次可能回Failed 再进行一次
df.printSchema
df.show

操作mysql等RDBMS表： mysql-connector-java-5.1.38.jar拷贝至/opt/spark/jars下
val url = “jdbc:mysql://localhost:3306/hive”
val tableName = “TBLS”
// 设置连接用户、密码、数据库驱动类
val prop = new java.util.Properties
prop.setProperty(“user”,“root”)
prop.setProperty(“password”,“ok”)
prop.setProperty(“driver”,“com.mysql.jdbc.Driver”)
// 取得该表数据
val jdbcDF = spark.read.jdbc(url,tableName,prop)
jdbcDF.show
//DF存为新的表
jdbcDF.write.mode(“append”).jdbc(url,“t1”,prop) //在myslq中建一个新表名为t1

内置函数：
val accessLog = Array(
“2016-12-27,001”,
“2016-12-27,001”,
“2016-12-27,002”,
“2016-12-28,003”,
“2016-12-28,004”,
“2016-12-28,002”,
“2016-12-28,002”,
“2016-12-28,001”
)

import org.apache.spark.sql.Row
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}
val accessLogRDD = sc.parallelize(accessLog).map(row => {
val splited = row.split(",")
Row(splited(0), splited(1).toInt)
})
val structTypes = StructType(Array(
StructField(“day”, StringType, true),
StructField(“userId”, IntegerType, true)
))
//Row是关键字，不能变
//根据数据以及Schema信息生成DataFrame
val accessLogDF = spark.createDataFrame(accessLogRDD, structTypes)
//导入Spark SQL内置的函数
import org.apache.spark.sql.functions._
//求每天所有的访问量(pv)
accessLogDF.groupBy(“day”).agg(count(“userId”).as(“pv”)).select(“day”, “pv”).collect.foreach(println)
//求每天的去重访问量(uv)
accessLogDF.groupBy(“day”).agg(countDistinct('userId).as(“uv”)).select(“day”, “uv”).collect.foreach(println)