pyspark sql常用操作

from pyspark import SparkContext, SparkConf
from pyspark.sql import SparkSession
from pyspark.sql import Row
from pyspark.sql.types import *

# 一、创建spark sql指挥官
spark = SparkSession.builder.config(conf=SparkConf()).getOrCreate()

# 二、读取/显示/保存
# 读取不同类型的文件的两种方式，返回的格式是dataframe
# 以下文件都是事例，实际需要给出hdfs或者本地的全称路径
textfile_df = spark.read.text("textfile.txt")
textfile_df = spark.read.format("text").load("textfile.txt")
jsonfile = spark.read.json("jsonfile.json")
parquetfile = spark.read.parquet("parquetfile.parquet")
# dataframe的显示，用show
textfile_df.show()
# dataframe保存的两种方式
textfile_df.write.text("textfile.txt")
textfile_df.write.format("text").save("textfile.txt")
jsonfile.write.json("jsonfile.json")
parquetfile.write.parquet("parquetfile.parquet")
# dataframe的"name","age"列保存，用select
# 最终会生成一个textfile.json的目录，不是文件，里面可能会有多个文件和一个SUCESS
# 加载的时候，括号里放目录的名称即可
textfile_df.select("name", "age").write.format("json").save("textfile.json")

# 三、其他常用操作
# 打印模式信息，即字段的名称和类型之类
textfile_df.printSchema()
# 选取某些列的值
textfile_df.select(textfile_df['name'], textfile_df['age'] + 1).show() #age列的值加1
# 过滤某些值
textfile_df.filter(textfile_df['age'] > 20).show()
# 根据某些值分组
textfile_df.groupBy("age").count().show()  #根据年龄进行分组并统计每组的数目
# 排序
textfile_df.sort(textfile_df['age'].desc()).show() #根据年龄降序排序
textfile_df.show(textfile_df['age'].desc(), textfile_df['name'].asc()).show() #先按年龄降序，后按名字生序

# 四、RDD转化为Dataframe
# 1、利用反射机制去推断RDD模式，适用于提前知道了数据结构
"""
    textFile是sparkContext方法，此时将数据加载到内存中，变成RDD对象；
    通过第一个map操作以后，变成新的RDD，每个元素都是一个列表；
    通过第二个map操作以后，又变成一个新的RDD，每个元素是一个Row对象；
    最终的people是一个RDD对象
"""
people = spark.sparkContext\
    .textFile("file:///usr/local/spark/examples/src/main/resources/people.txt")\
    .map(lambda line: line.split(","))\
    .map(lambda p: Row(name=p[0], age=int(p[1])))
# RDD转成Dataframe，用createDataFrame方法
schemaPeople = spark.createDataFrame(people)
# 必须注册为临时表才能提供下面的查询使用，临时表名称可随便取；
# 注意这里的"people"和上面的people RDD对象的区别
schemaPeople.createOrReplaceTempView("people")
# 对临时表进行查询，得到的结果被封装在dataframe中
personsDF = spark.sql("select name, age from people where age > 20")
# Dataframe中的每个元素都是一行记录，包含name和age两个字段，分别用p.name和p.age来获取值
# Dataframe转化成RDD，用rdd方法
personsRDD = personsDF.rdd.map(lambda p: "Name:" + p.name + "," + "Age" + str(p.age))
personsRDD.foreach(print)   #打印结果

# 2、利用编程方式去定义RDD模式，适用于提前不知道数据结构是什么
"""
    第一步：制作"表头"；
    第二步：制作"表中的记录"；
    第三步：把"表头"和"表中的记录"拼装在一起
"""
# 生成"表头"
schemaString = "name age"
fileds = [StructField(filed_name, StringType(), True) for filed_name in schemaString.split(" ")]
schema = StructType(fileds)   # StructType这个类生成的对象用来描述模式
# 生成"表中的记录"，生成多个Row对象
people = spark.sparkContext\
    .textFile("file:///usr/local/spark/examples/src/main/resources/people.txt")\
    .map(lambda x: x.split(","))\
    .map(lambda p: Row(p[0], p[1].strip())) #和第一种方法对比
# 两步拼接，表中记录在前，表头在后
schemaPeople = spark.createDataFrame(people, schema)
# 注册临时表，同上
schemaPeople.createOrReplaceTempView("people")
results = spark.sql("select name, age from people where age > 20")
results.show()

# 五、读写数据库
"""
    1、 在Linux中启动MySQL数据库
        >>> service mysql start
        >>> mysql -u root -p
        屏幕提示输入root账号的密码
    2、创建表
    3、安装mysql的jdbc驱动程序
    4、通过jdbc连接mysql
"""
# 1、读取数据库
jdbcDF = spark.read\
    .format("jdbc")\
    .option("driver", "com.mysql.jdbc.Driver")\
    .option("url", "jdbc:mysql://localhost:3306/spark")\
    .option("dbtable", "student")\
    .option("user", "root")\
    .option("password", "123456")\
    .load()
jdbcDF.show()
# 2、写入数据库
spark = SparkSession.builder.config(conf=SparkConf()).getOrCreate()
# 设置模式信息
schema = StructType([StructField("id", IntegerType(), True),
                     StructField("name", StringType(), True),
                     StructField("gender", StringType(), True),
                     StructField("age", IntegerType(), True)])
# 设置两条数据，表示两个学生的信息
# 需要封装两个Row对象，通过parallelize生成RDD对象
studentRDD = spark.sparkContext\
    .parallelize(["3 Yangyiru M 26", "4 Liusanbian M 27"])\
    .map(lambda x: x.split(" "))
# 创建Row对象，每个Row对象是rowRDD中的一行
rowRDD = studentRDD\
    .map(lambda p: Row(int(p[0].strip(), p[1].strip(), p[2].strip(), p[3].strip())))
# 建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
studentDF = spark.createDataFrame(rowRDD, schema)
# 写库
prop = {}
prop['user'] = 'root'
prop['password'] = '123456'
prop['driver'] = "com.mysql.jdbc.Driver"
# url(数据库) 表名 数据写入的方式:append 账号密码驱动器
studentDF.write.jdbc("jdbc:mysql://localhost:3306/spark", "student", "append", prop)