PySpark的select fliter agg join

1. 初始化

from __future__ import print_function, division
from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession,functions as F
from pyspark.sql.types import StringType,MapType
from pyspark.sql.types import *

2. 读取数据

def parse_log_schema(txt):
	try:
		fileds=txt.strip().split("\t")
		return {
		"userid":fileds[0],
		"docid":fileds[1],
		} 
	except:
		return None

#注册函数
udf_pasre_log=F.udf(parse_log_schema,MapType(StringType(),StringType()))			

# 1.text
spark = SparkSession.builder.master("local[2]").appName("test").enableHiveSupport().getOrCreate()
df=spark.read.text("path").select(udf_pasre_log(F.clo("value")).aslias("info") \
.filter(~F.isnull("info")) \
.select(F.clo("info")["docid"].alias("docid"),
		F.clo("info")["userid"].alias("userid")
		).dropDuplicates(["userid"],["docid"])
#2.csv
df = spark.read.csv('../data/rating.csv', sep = ',', header = True) #自动判断格式interSchema=True
df.show()

#.csv schema
data_schame= StructType([
StructField("docid",StringType(),ture),
StructField("userid",StringType(),ture),
StructField("ctr",DoubleType(),ture),
])
df=spark.read.csv("input_paths",schema=data_schame,sep="\t",header=False)
df.show()
#3.写入
df.coalesce(100).write.fromat("csv").mode("overwrite").option("delimiter","\t").save("out_path")

3. select

#select userid ,docid from data   -- sql语句
df.select('userid','docid').show() 

#select userid as uid from data
df.selectExpr('userid as uid').show()
# percent to double
df.selectExpr('cast(percent as double)').printSchema() #转换类型cast
#select docid,ctr * 2  as ctr from data
df.selectExpr('docid', 'ctr * 2 as ctr').show()

4. sum,agg

#select userid,docid sum(expose) expose,sum(click) click from data group by userid,docid
df.groupBy("userid","docid").agg({"expose":"sum","click":"sum"}).withColumnRenamed("sum(expose)","expose").withColumnRenamed("sum(click)","click")

5. filter

#select docid, ctr from data where docid="123" and ctr > 0.3
df.select("docid", "ctr").filter('docid== 2 and ctr > 0.3').show()

6. join

profile = [{'name': 'Alice', 'age': 1}, {'name': 'Bryan', 'age': 3}, {'name': 'Cool', 'age':2}]
df_profile = spark.createDataFrame(profile) #转换为数据框
df_profile.show()
# 创建数据框df_parents
parents = [{'name': 'Jason', 'child': 'Alice'}, 
     {'name': 'Bill', 'child': 'Bryan'}, 
     {'name': 'Sera', 'child': 'Bryan'}, 
     {'name': 'Jill', 'child': 'Ken'}]
df_parents = spark.createDataFrame(parents) #转换为数据框
df_parents.show()
#inner join 
df_profile.join(df_parents, df_profile.name == df_parents.child).show()
#left join
df_profile.join(df_parents, df_profile.name == df_parents.child, 'left').show()
#right join
df_profile.join(df_parents, df_profile.name == df_parents.child, 'right').show()
#full outer join
df_profile.join(df_parents, df_profile.name == df_parents.child, 'outer').show()