1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
"""
author: 方星钧(ffman)
description: 清洗6大站点对应的 “ods_brand_analytics” 的表中的关键词对应的year_week关系，并且支持排序(目前只有周)
table_read_name: ods_brand_analytics
table_save_name: dim_st_year_week
table_save_level: dim
version: 2.0
created_date: 2022-05-05
updated_date: 2022-12-14
"""
import os
import sys
from pyspark.sql import Window
sys.path.append(os.path.dirname(sys.path[0])) # 上级目录
from utils.templates import Templates
# from ..utils.templates import Templates
from pyspark.sql import functions as F
class DimStYearWeek(Templates):
def __init__(self, site_name='us', date_type="week", date_info='2022-01'):
super().__init__()
self.site_name = site_name
self.date_type = date_type
self.date_info = date_info
self.db_save = f'dim_st_year_{self.date_type}'
self.spark = self.create_spark_object(app_name=f"{self.db_save}: {self.site_name}, {self.date_info}")
self.df_save = self.spark.sql(f"select 1+1;")
self.df_st = self.spark.sql(f"select 1+1;")
self.df_st_key = self.spark.sql(f"select 1+1;")
self.partitions_by = ['site_name']
self.reset_partitions(20)
def read_data(self):
sql = f"select search_term, date_info as year_week, site_name from ods_brand_analytics where site_name='{self.site_name}' and date_type='{self.date_type}';"
print("sql:", sql)
self.df_st = self.spark.sql(sqlQuery=sql)
self.df_st.cache()
sql = f"select search_term, st_key from ods_st_key where site_name='{self.site_name}';"
print("sql:", sql)
self.df_st_key = self.spark.sql(sqlQuery=sql)
self.df_st_key.cache()
def handle_data(self):
window = Window.partitionBy(['search_term']).orderBy(self.df_st.year_week.desc())
self.df_st = self.df_st.withColumn(
"row_number", F.row_number().over(window)
)
self.df_st = self.df_st.withColumn('row_number', F.row_number().over(window)) # 使用窗口函数为每个分区的行编号
self.df_st = self.df_st.filter(self.df_st.row_number == 1).drop(
'row_number') # 只保留每个分区中 row_number 最大的行，并删除 row_number 列
self.df_save = self.df_st.join(
self.df_st_key, on='search_term', how='left'
)
self.df_save.show(10, truncate=False)
if __name__ == '__main__':
site_name = sys.argv[1] # 参数1：站点
date_type = sys.argv[2] # 参数2：类型：week/4_week/month/quarter
date_info = sys.argv[3] # 参数3：年-周/年-月/年-季, 比如: 2022-1
handle_obj = DimStYearWeek(site_name=site_name, date_type=date_type, date_info=date_info)
handle_obj.run()