pyspark离线数据处理常用方法

读取数据

不指定schema读取数据

    def read_data_without_schema(self):
        print('read data without schema')
        sdf = self.spark.read.csv(f'file:///{BASE_PATH}/data/people.csv', header=None, sep=',', inferSchema=True)
        print(sdf.schema)
        print(sdf.show())
        return sdf

指定schema读取数据

    def read_data_with_schema(self):
        print('read data with schema')
        customer_schema = StructType([
            StructField('first_name', StringType(), True),
            StructField('last_name', StringType(), True),
            StructField('gender', StringType(), True),
            StructField('age', IntegerType(), True)
        ])
        sdf = self.spark.read.csv(f'file:///{BASE_PATH}/data/people.csv', header=None, sep=',', schema=customer_schema)
        print(sdf.schema)
        print(sdf.show())
        return sdf

将pandas dataframe转成pyspark.sql.dataframe.DataFrame

    def create_sdf_from_pd(self):
        pdf = pd.DataFrame({'year': [2022, 2022],
                           'month': [1, 1],
                           'amt': [100, 200]})
        sdf = self.spark.createDataFrame(pdf)
        return sdf

检查数据行数

    def check_row_count(self,sdf):
        # <dataframe>.count()
        return sdf.count()

筛选列

    def select_columns(self,sdf):
        # <dataframe>.select(<column_name>)
        sdf = sdf.select("first_name","last_name","age")
        return sdf

过滤行

    def filter_sdf(self,sdf):
        # where() :<dataframe>.where(<condition>)
        # filter():<dataframe>.filter(<condition>)
        sdf_filtered1 = sdf.where(col('age') >= 35)
        sdf_filtered2 = sdf.filter(col('age') >= 35)
        return sdf_filtered1,sdf_filtered2

删除列

    def drop_columns(self,sdf):
        # <dataframe>.drop(<column_name>)
        return sdf.drop('gender','age')

重命名列

    def rename_column(self,sdf):
        # <dataframe>.withColumnRenamed(<old_column_name>,<new_column_name>)
        return sdf.withColumnRenamed('first_name','name_1st')\
                .withColumnRenamed('last_name','name_last')

新增列

    def add_or_replace_columns(self,sdf):
        # <dataframe>.withColumn(<new_column>,<condition_or_transformation>)
        sdf = sdf.withColumn('gender_name',when(col('gender') == 'F','Female').when(col('gender') == 'M','Male').otherwise('Unknow'))
        return sdf

排序

    def sort_rows(self,sdf):
        # <dataframe>.sort(col(<column_name>).asc())
        # <dataframe>.sort(col(<column_name>).desc())
        # <dataframe>.orderBy(asc(<column_name>))
        # <dataframe>.orderBy(desc(<column_name>))
        return sdf.sort(col('age').desc())

去重

    def drop_duplicate_rows(self,sdf):
        # <dataframe>.dropDuplicates()
        # keep the first row,so it is always used along with function sort
        sdf = sdf.sort(col('age').asc()).dropDuplicates(subset=['gender'])
        return sdf

限制输出

    def limit_output_rows(self,sdf):
        # <dataframe>.limit(<number_of_rows>)
        return sdf.limit(10)

聚合计算

    def group_data(self,sdf):
        # <dataframe>.groupBy(<column_name>).agg({'aggregate_column_name1':'aggregate_function1','aggregate_column_name2':'aggregate_function2',...})
        sdf = sdf.select(col('first_name'),col('gender'),col('age').cast('int').alias('age_int'))\
            .groupBy('first_name')\
            .agg({'age_int': 'max','gender':'max'})\
            .withColumnRenamed('max(gender)','max_gender')\
            .withColumnRenamed('max(age_int)','max_age')
        # recommand
        sdf = sdf.groupBy(['first_name','last_name']).agg(
            max('age').alias('max_age'),
            max('gender').alias('max_gender')
        )
        return sdf

数据写出

    def write_data_to_sink(self,sdf,path_to_sink):
        # <dataframe>.write.format('delta/parquet/orc').mode('append/overwrite').option('path',<path_to_sink>).save()
        sdf.write.format('delta').option('path','<mountpoint_path>/path_to_sink/').save()