import os import sys sys.path.append(os.path.dirname(sys.path[0])) # 上级目录 from utils.hdfs_utils import HdfsUtils from utils.spark_util import SparkUtil from utils.templates import Templates from pyspark.sql import functions as F class DwdAmazonReport(Templates): def __init__(self, site_name='us', date_type="month", date_info='2021-10'): super().__init__() self.site_name = site_name self.date_type = date_type self.date_info = date_info self.db_save = f'dwd_amazon_report' self.spark = self.create_spark_object( app_name=f"{self.db_save}: {self.site_name}, {self.date_type}, {self.date_info}") self.reset_partitions(partitions_num=5) self.partitions_by = ['site_name', 'date_type', 'date_info'] self.df_buy_data = self.spark.sql(f"select 1+1;") self.df_st_count = self.spark.sql(f"select 1+1;") self.df_save = self.spark.sql(f"select 1+1;") def read_data(self): # 读取asin的月销数据 sql1 = f""" select asin, asin_amazon_orders as monthly_sales from dim_asin_amorders_info where site_name = '{self.site_name}' and date_type = '{self.date_type}' and date_info = '{self.date_info}'; """ print(sql1) self.df_buy_data = self.spark.sql(sqlQuery=sql1).repartition(15, 'asin').cache() self.df_buy_data.show(10, truncate=True) sql2 = f""" select asin, asin_zr_counts as zr_count, asin_sp_counts as sp_count, asin_st_counts as total_count from dwd_asin_measure where site_name = '{self.site_name}' and date_type = '{self.date_type}' and date_info = '{self.date_info}'; """ print(sql2) self.df_st_count = self.spark.sql(sqlQuery=sql2).repartition(15, 'asin').cache() self.df_st_count.show(10, truncate=True) def handle_data(self): hdfs_path = f"/home/{SparkUtil.DEF_USE_DB}/dwd/{self.db_save}/site_name={self.site_name}/date_type={self.date_type}/date_info={self.date_info}" print(f"清除hdfs目录中.....{hdfs_path}") HdfsUtils.delete_hdfs_file(hdfs_path) self.df_save = self.df_buy_data.join( self.df_st_count, on='asin', how='full' ) columns = self.df_save.columns for col_name in columns: self.df_save = self.df_save.withColumn( col_name, self.df_save[col_name].cast('string') ) self.df_save = self.df_save.fillna('-1') self.df_save = self.df_save.withColumn( "weekly_sales", F.lit(None) ).withColumn( "weekly_views", F.lit(None) ).withColumn( "monthly_views", F.lit(None) ).withColumn( "site_name", F.lit(self.site_name) ).withColumn( "date_type", F.lit(self.date_type) ).withColumn( "date_info", F.lit(self.date_info) ) if __name__ == '__main__': site_name = sys.argv[1] date_type = sys.argv[2] date_info = sys.argv[3] if (site_name in ['us', 'uk', 'de']) and (date_type == 'month') and (date_info >= '2024-04'): handle_obj = DwdAmazonReport(site_name=site_name, date_type=date_type, date_info=date_info) handle_obj.run() else: print("暂不计算该维度数据!") quit()