-
Notifications
You must be signed in to change notification settings - Fork 0
/
etl.py
180 lines (139 loc) · 6.86 KB
/
etl.py
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
import configparser
from datetime import datetime
import os
from pyspark.sql import SparkSession
from pyspark.sql.functions import udf, col
from pyspark.sql.functions import year, month, dayofmonth, hour, weekofyear, date_format
config = configparser.ConfigParser()
config.read('dl.cfg')
os.environ['AWS_ACCESS_KEY_ID']=config['AWS_ACCESS_KEY_ID']
os.environ['AWS_SECRET_ACCESS_KEY']=config['AWS_SECRET_ACCESS_KEY']
def create_spark_session():
"""
Creates a new or uses the existing spark session.
"""
spark = SparkSession \
.builder \
.config("spark.jars.packages", "org.apache.hadoop:hadoop-aws:2.7.0") \
.getOrCreate()
return spark
def process_song_data(spark, input_data, output_data):
"""
Processes all song data JSON files in the given input folder and stores them in parquet format in the output folder.
:param spark: spark session
:param input_data: input data path
:param output_data: output data path
"""
# get filepath to song data file
song_data = input_data + 'song_data/*/*/*/*.json'
# read song data file
df = spark.read.json(song_data)
# extract columns to create songs table
songs_table = df.select("song_id","title","artist_id","year","duration").drop_duplicates()
#songs_table = spark.sql("""
# SELECT sdtn.song_id,
# sdtn.title,
# sdtn.artist_id,
# sdtn.year,
# sdtn.duration
# FROM song_data_table sdtn
# WHERE song_id IS NOT NULL
# """)
# write songs table to parquet files partitioned by year and artist
songs_table.write.mode('overwrite').partitionBy("year", "artist_id").parquet(output_data+'songs_table/')
# extract columns to create artists table
artists_table = df.select("artist_id","artist_name","artist_location","artist_latitude","artist_longitude").drop_duplicates()
'''artists_table = spark.sql("""
SELECT DISTINCT arti.artist_id,
arti.artist_name,
arti.artist_location,
arti.artist_latitude,
arti.artist_longitude
FROM song_data_table arti
WHERE arti.artist_id IS NOT NULL
""")'''
# write artists table to parquet files
artists_table.write.mode('overwrite').parquet(output_data+'artists_table/')
def process_log_data(spark, input_data, output_data):
"""
Processes all log data JSON files in the given input folder and stores them in parquet format in the output folder.
:param spark: spark session
:param input_data: input data path
:param output_data: output data path
"""
# get filepath to log data file
log_data =input_data + 'log_data/*.json'
# read log data file
df = spark.read.json(log_path)
# filter by actions for song plays
df = df.filter(df.page == 'NextSong')
# created log view to write SQL Queries
df.createOrReplaceTempView("log_data_table")
# extract columns for users table
users_table = spark.sql("""
SELECT DISTINCT userT.userId as user_id,
userT.firstName as first_name,
userT.lastName as last_name,
userT.gender as gender,
userT.level as level
FROM log_data_table userT
WHERE userT.userId IS NOT NULL
""")
# write users table to parquet files
users_table.write.mode('overwrite').parquet(output_data+'users_table/')
# create timestamp column from original timestamp column
# get_timestamp = udf()
# df =
# create datetime column from original timestamp column
# get_datetime = udf()
# df =
# extract columns to create time table
time_table = spark.sql("""
SELECT
A.start_time_sub as start_time,
hour(A.start_time_sub) as hour,
dayofmonth(A.start_time_sub) as day,
weekofyear(A.start_time_sub) as week,
month(A.start_time_sub) as month,
year(A.start_time_sub) as year,
dayofweek(A.start_time_sub) as weekday
FROM
(SELECT to_timestamp(timeSt.ts/1000) as start_time_sub
FROM log_data_table timeSt
WHERE timeSt.ts IS NOT NULL
) A
""")
# write time table to parquet files partitioned by year and month
time_table.write.mode('overwrite').partitionBy("year", "month").parquet(output_data+'time_table/')
# read in song data to use for songplays table
song_df = spark.read.parquet(output_data+'songs_table/')
# read song data file
# song_df_upd = spark.read.json(input_data + 'song_data/*/*/*/*.json')
# created song view to write SQL Queries
# song_df_upd.createOrReplaceTempView("song_data_table")
# extract columns from joined song and log datasets to create songplays table
songplays_table = spark.sql("""
SELECT monotonically_increasing_id() as songplay_id,
to_timestamp(logT.ts/1000) as start_time,
month(to_timestamp(logT.ts/1000)) as month,
year(to_timestamp(logT.ts/1000)) as year,
logT.userId as user_id,
logT.level as level,
songT.song_id as song_id,
songT.artist_id as artist_id,
logT.sessionId as session_id,
logT.location as location,
logT.userAgent as user_agent
FROM log_data_table logT
JOIN song_data_table songT on logT.artist = songT.artist_name and logT.song = songT.title
""")
# write songplays table to parquet files partitioned by year and month
songplays_table.write.mode('overwrite').partitionBy("year", "month").parquet(output_data+'songplays_table/')
def main():
spark = create_spark_session()
input_data = "s3a://udacity-dend/"
output_data = "s3a://udacity-dend/dloutput/"
process_song_data(spark, input_data, output_data)
process_log_data(spark, input_data, output_data)
if __name__ == "__main__":
main()