Spark类SQL要点总结

总 结

问：Spark中RDD和sparkDataFrame的区别

1 SparkDF创建与保存

1.1 toDF

将RDD转化为DataFrame

df = rdd.toDF(schema=['name', 'age'])

1.2 通过createDataFrame方法

1.2.1 将Pandas的DataFrame转换为pyspark的DataFrame

df = spark.createDataFrame(pd_df)

1.2.2 将python的list转换为pyspark的DataFrame

df = spark.createDataFrame(
    data=data,
    schema=['name', 'age'])

1.2.3指定rdd和schema创建DataFrame

schema = 'name string, age int, birth date'
df = spark.createDataFrame(data=rdd, schema=schema)

1.3 通过读取文件创建DataFrame

df = spark.read.json('data/iris.json')
df = spark.read.csv('data/iris.csv', sep=',', inferSchema=True, header=True)
df = spark.read.csv('data/iris.txt', sep=',', inferSchema=True, header=True)
df = spark.read.parquet('data/iris.parquet')
df = spark.sql('select * from edu.course')

1.4 SparkDF保存

df.write.csv('data/iris_csv', header=True)
df.rdd.saveAsTextFile('data/iris_txt')
df.rdd.saveAsTextFile('data/iris_txt')
df.write.json('data/iris_json')
df.write.parquet('data/iris_parquet')
# 分区, 分文件夹存储
df.write.partitionBy('label').format('parquet').save('data/iris_parquet_par')
spark.sql('create database db')

2 SparkDF类SQL

2.1 读取数据

• 预览前1行: df.first()

• 预览前3行: df.head(3)

• 预览后3行: df.tail(3)

• 预览所有行: df.collect()

• 预览表数据：df.show(5)

• 查看字段名称\数据类型\是否可空等, 元数据：df.printSchema()

• 查看字段列表：df.columns

• 查看字段数据类型：df.dtypes

• 查看行数：df.count()

• 查看文件路径：df.inputFiles()

2.2 类SQL基本操作（清洗）

2.2.1 读取列

# 读取所有字段
# df.select('*').show()
# df.show()
​
# 读取指定字段
# df.select('order_id', 'region', 'province', 'quantity').show(5)
# df[['order_id', 'region', 'province', 'quantity']].show(5)
# df['order_id', 'region', 'province', 'quantity'].show(5)
# df.select(df.order_id, df.region, df.province, df.quantity).show(5)
df.select(df['order_id'], df['region'], df['province'], df['quantity']).show(5)
# 正则表达式读取指定字段, 必须加``
df.select(df.colRegex('`order.*`')).show(5)

2.2.2 新增列

# 金额=价格*数量
df = df.withColumn('amount', df['price'] * df['quantity'])

2.2.3 删除列

df.drop('region', 'product_type').show(1)

2.2.4 重命名列

(df.withColumnRenamed('order_id', '订单编号')
    .withColumnRenamed('order_date', '订单日期')
    .withColumnRenamed('product_type', '产品类型')
    .show(1))

2.2.5 排序

# sort或orderBy
df.sort(df['order_date'].desc()).show(1)

# 多列排序
df.orderBy(df['region'].asc(), df['order_date'].desc()).show(1)

2.2.6 替换

# 将"办公用品"和"家具产品"都替换为"办公和家具"
lst = ['办公用品', '家具产品']
df.replace(to_replace=lst, value='办公和家具', subset='product_type').show(5)

2.2.7 删除空值

# df.na.drop或dropna
# 如两列均为空, 则删除整行, all and 与/且
df.dropna(how='all', subset=['region', 'province']).show(1)
​
# 如两列其中一列为空, 则删除整行, any or 或
df.dropna(how='any', subset=['region', 'province']).show(1)

2.2.8 填充空值

df.fillna(value=0, subset='profit').show(1)

2.2.9 删除重复值

# drop_duplicates或dropDuplicates
# 根据所有字段来删除重复行
df.dropDuplicates().show(1)
​
# 根据指定字段来删除重复行
df.drop_duplicates(subset=['order_id', 'region']).show(1)

2.2.10 转换字段数据类型

df.select(
    df['price'].astype(t.IntegerType()),
    df['quantity'].astype(t.FloatType())
).show(5)

2.2.11 转换DataFrame格式

• 将spark的df转换为pandas的df

pd_df = df.toPandas()

• 将spark的df转换为json

  df.toJSON().take(1)

• 将spark的df转换为python迭代器

it = df.toLocalIterator()
# 迭代器可以记住遍历的位置, 只能向前访问不能后退, 直到所有元素访问完毕为止
for row in it:
    print(row)
    time.sleep(3)

2.3 Spark类SQL的统计操作

2.3.1 整表聚合计算

df.agg({'price': 'max', 'quantity': 'sum'}).show()

2.3.2 描述统计

# describe
df.select('price', 'quantity', 'profit').describe().show()
​
# summary
df.select('price', 'quantity', 'profit').summary().show()

2.3.2 频数统计

df.freqItems(cols=['province', 'quantity'], support=0.6).show()

2.3.3 分位数

# col, 统计字段
# probabilities概率/百分位, 0-1, 必须为浮点数, 求多个分位数时传递列表或元组,
# relativeError相对误差, 浮点数, 0.0表示精确计算
# 该函数返回列表
# df.stat.approxQuantile
lst = df.approxQuantile('price', [0.0, 0.5, 1.0], 0.0)
type(lst)

2.4 Spark类Sql的表操作

2.4.1 表查询

• distinct去重

df.distinct().count()

• limit限制行数

df.select('order_id', 'order_date').limit(2).show()

• where筛选

df.where("product_type='办公用品' and quantity > 10").show(3)

• filter筛选,# where等价于filter

  # df.filter("product_type='办公用品' and quantity > 10").show(3)
  # df.filter((df['product_type'] == '办公用品') & (df['quantity'] > 10)).show(3)
  ​
  # df.where("region in ('华南', '华北') and price > 100").show(3)
  # df.where((df['region'].isin(['华南', '华北'])) & (df['price'] > 100)).show(3)
  ​
  # like通配符筛选, %表示任意数量任意字符, _任意单个字符
  # df.where("province like '湖%'").show(3)
  # df.where(df['province'].like('湖%')).show(3)
  ​
  # rlike正则表达式筛选
  # df.where("province rlike '湖北|湖南'").show(3)
  # df.where(df['province'].rlike('湖北|湖南')).show(3)
  ​
  # 广播变量筛选, 性能更好
  bc = sc.broadcast(['华南', '华北'])
  df.where(df['region'].isin(bc.value)).show(5)
  # df.where(~df['region'].isin(bc.value)).show(5)

• 条件判断

  # when和otherwise类似于SQL的case when
  from pyspark.sql import functions as f
  df.select(
      'order_id', 'profit',
      f.when(df['profit'] > 0, '盈利')
      .when(df['profit'] == 0, '持平').otherwise('亏损').alias('state')
  ).show(3)

2.4.2 表连接

• inner内连接

  people.join(salary, on='id', how='inner').show()

• left左连接

  people.join(salary, on='id', how='left').show()

• right右连接

  people.join(salary, on='id', how='right').show()

2.4.3 分组聚合

• groupBy，以区域分组，对数量求和

df.groupBy('region').agg(f.sum('quantity').alias('quantity')).show()

• groupBy+agg，以区域分组，统计单价的均值，合并产品类型

# collect_list不去重, collect_set去重
# mean等价于avg
from pyspark.sql import functions as f
df.groupBy('region').agg(
    f.avg('price').alias('price_avg'),
    f.collect_set('product_type').name('product_set')
).show()
​
# 上面的等价写法
df.groupBy('region').agg(
    f.expr('avg(price)').alias('price_avg'),
    f.expr('collect_set(product_type)').alias('product_set')
).show()
​
df.groupBy('region').agg(
    f.expr('avg(price) as price_avg'),
    f.expr('collect_set(product_type) as product_set')
).show()

• groupBy+pivot，数据透视表，行：区域，列：产品类型，值：数量，计算类型：求和

df.groupBy('region').pivot('product_type').sum('quantity').show()

2.4.4 窗口函数 window

window = Window.partitionBy('product_type').orderBy(df['order_date'].asc()) \
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)