Spark学习五:spark sql
标签(空格分隔): Spark
一,概述:
二,Spark的发展历史
三,Spark sql和hive对比
四,spark sql 架构
五,sprk sql访问hive数据
hive-site.xml需要拷贝到spark的conf目录下面
启动方式一:
//启动应用
bin/spark-shell --driver-class-path jars/mysql-connector-java-5.1.27-bin.jar --master local[2]
sqlContext.sql("show databases").show()
sqlContext.sql("use default").show()
sqlContext.sql("show tables").show()
启动方式二:
//启动应用
bin/spark-sql --driver-class-path jars/mysql-connector-java-5.1.27-bin.jar --master local[2]
show databases;
//缓存
cache table emp;
//取消缓存
uncache table emp;
六,catalyst
七,thriftserver
启动服务
sbin/start-thriftserver.sh --master local[2] --driver-class-path jars/mysql-connector-java-5.1.27-bin.jar
启动beeline客户端
bin/beeline
beeline> !connect jdbc:hive2://localhost:10000
八,Dataframe
九,加载外部数据源
1,加载json数据
val json_df=sqlContext.jsonFile("hdfs://study.com.cn:8020/spark/people.json")
json_df.show()
2,加载hive数据
sqlContext.table("default").show()
3,加载parquet格式数据
val parquet_df=sqlContext.jsonFile("hdfs://study.com.cn:8020/spark/users.parquet")
parquet_df.show()
4,jdbc方式获取数据
val df = sqlContext.jdbc("jdbc:mysql://localhost:3306/db_0306?user=root&password=123456", "my_user")
val mysql_df = sqlContext.load("jdbc", Map("url" -> "jdbc:mysql://localhost:3306/db_0306?user=root&password=123456","dbtable" -> "my_user"))
5,读取text file
第一种方式:
case class Person(name:String,age:Int)
val people_rdd = sc.textFile("spark/sql/people.txt")
val rowRdd = people_rdd.map(x => x.split(",")).map(x => Person(x(0), x(1).trim.toInt))
val people_df=rowRdd.toDF()
第二种方式:
val people_rdd = sc.textFile("spark/sql/people.txt")
import org.apache.spark.sql._
val rowRdd = people_rdd.map(x => x.split(",")).map(x => Row(x(0), x(1).trim.toInt))
import org.apache.spark.sql.types._
val schema = StructType(Array(StructField("name",StringType, true), StructField("age", IntegerType, false)))
val rdd2df = sqlContext.createDataFrame(rowRdd, schema)
测试:
Spark SQL强大诞生了,
Hive Table
emp
MySQL Table
dept
针对上述两个表进行join,
val hive_emp_df = sqlContext.table("db_0228.emp")
val mysql_dept_df = sqlContext.jdbc("jdbc:mysql://localhost:3306/db_0306?user=root&password=123456", "tb_dept")
val join_df = hive_emp_df.join(mysql_dept_df, hive_emp_df("deptno") === mysql_dept_df("deptno"))
join_df.show
案例分析
SQLLogAnalyzer.scala
package com.ibeifeng.bigdata.spark.app
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}
/**
* Created by XuanYu on 2016/4/17.
*/
object SQLLogAnalyzer {
def main(args: Array[String]) {
// create SparkConf instance
val sparkConf = new SparkConf()
.setAppName("SQLLogAnalyzer")
.setMaster("local[2]")
// create SparkContext instance
val sc = new SparkContext(sparkConf)
// create SQLcontext instance
val sqlContext = new SQLContext(sc)
import sqlContext.implicits._
// ==============================================================
// input files
val logFile = "hdfs://bigdata-senior01.ibeifeng.com:8020/user/beifeng/apache.access.log" //
//create rdd
val accessLogs_df = sc.textFile(logFile)
/**
* filter log datas
*/
.filter(ApacheAccessLog.isValidateLogLine)
/**
* parse log
*/
.map(log => ApacheAccessLog.parseLogLine(log))
.toDF()
accessLogs_df.registerTempTable("accessLogs")
// cache
accessLogs_df.cache()
// =======================================================================================
// compute
val avgContentSize = sqlContext.sql("select avg(contentSize) from accessLogs").first().get(0)
val minContentSize = sqlContext.sql("select min(contentSize) from accessLogs").first().get(0)
val maxcontentSize = sqlContext.sql("select max(contentSize) from accessLogs").first().get(0)
// println
println("Content Size Avg: %s, Min: %s , Max: %s".format(
avgContentSize, minContentSize, maxcontentSize
))
//
accessLogs_df.unpersist()
val avg_df = accessLogs_df.agg("contentSize" -> "avg")
val min_df = accessLogs_df.agg("contentSize" -> "min")
val max_df = accessLogs_df.agg("contentSize" -> "max")
// println
println(" === Content Size Avg: %s, Min: %s , Max: %s".format(
avg_df.first().get(0),min_df.first().get(0),max_df.first().get(0)
))
// ==============================================================
// stop SparkContext
sc.stop()
}
}