Spark秘籍-头歌spark_spark sql头歌

第五章章节测验

第一题：

import findspark
findspark.init()
from pyspark.sql import SparkSession
################ Begin ################
# 创建SparkSession对象
sc=SparkSession.builder.master("local[*]").appName('read').getOrCreate()
# 读取文件
df=sc.read.json("file:///data/bigfiles/employee.json")
# 查询DataFrame的所有数据
df.show()
# 查询所有数据，并去除重复的数据
df.distinct().show()
# 查询所有数据，打印时去除id字段
df.drop("id").show()
# 筛选age>20的记录
df.filter(df.age > 30).show()
# 将数据按name分组
df.groupBy("name").count().show()
# 将数据按name升序排列
df.sort(df.name.asc()).show()
# 取出前3行数据
df.take(3)
# 查询所有记录的name列，并为其取别名为username
df.select(df.name.alias("username")).show()
# 查询年龄age的平均值
df.agg({"age":"mean"}).show()
# 查询年龄age的最大值
df.agg({"age":"max"}).show()
# 关闭SparkSession对象
sc.stop()
################ End ################

第二题：

import findspark
findspark.init()
from pyspark.sql import SparkSession
from pyspark.sql.types import Row
################ Begin ################
# 创建SparkSession对象
spark = SparkSession.builder.getOrCreate()
# 创建SparkContext对象
sc = spark.sparkContext
# 读取文本文件
peopleRDD = sc.textFile("file:///data/bigfiles/employee.txt")
# 将 RDD 转换为 DataFrame
rowRDD = peopleRDD.map(lambda line: line.split(",")).map(lambda attributes: Row(id=int(attributes[0]), name=attributes[1], age=int(attributes[2])))
df = spark.createDataFrame(rowRDD)
# 创建临时视图
df.createOrReplaceTempView("employee")
# 执行SQL查询并打印结果
personsDF = spark.sql("SELECT id,name,age FROM employee")
personsDF.show()
# 关闭SparkSession对象
spark.stop()
################ End ################

第三题：

import findspark
findspark.init()
from pyspark import SparkContext
from pyspark.sql import SQLContext
from pyspark.sql.types import Row
from pyspark.sql.types import StructType
from pyspark.sql.types import StructField
from pyspark.sql.types import StringType
from pyspark.sql.types import IntegerType
################ Begin ################
# 创建SparkContext对象
sc = SparkContext( 'local', 'jdbc_test')
spark = SQLContext(sc)
# 读取 MySQL 数据
jdbcDF=spark.read.format("jdbc").option("url","jdbc:mysql://localhost:3306/sparktest").option("driver","com.mysql.jdbc.Driver").option("dbtable","employee").option("user", "root").option("password", "123123").load()
# 插入数据
studentRDD = sc.parallelize(["3 Mary F 26","4 Tom M 23"]).map(lambda line : line.split(" "))
schema = StructType([StructField("id",IntegerType(),True),StructField("name", StringType(), True),StructField("gender", StringType(), True),StructField("age",IntegerType(), True)])
rowRDD = studentRDD.map(lambda p : Row(int(p[0]),p[1].strip(), p[2].strip(),int(p[3])))
employeeDF = spark.createDataFrame(rowRDD, schema)
prop = {}
prop['user'] = 'root'
prop['password'] = '123123'
prop['driver'] = "com.mysql.jdbc.Driver"
employeeDF.write.jdbc("jdbc:mysql://localhost:3306/sparktest",'employee','append', prop)
# 计算 age 字段最大值和总和，输出结果
jdbcDF.collect()
jdbcDF.agg({"age": "max"}).show()
jdbcDF.agg({"age": "sum"}).show()
# 关闭SparkContext对象
sc.stop()