-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql_queries.py
289 lines (267 loc) · 6.51 KB
/
sql_queries.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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
import configparser
# CONFIG
config = configparser.ConfigParser()
config.read('dwh.cfg')
# DROP TABLES
staging_events_table_drop = ("""
DROP TABLE IF EXISTS staging_events
""")
staging_songs_table_drop = ("""
DROP TABLE IF EXISTS staging_songs
""")
songplay_table_drop = ("""
DROP TABLE IF EXISTS songplay
""")
user_table_drop = ("""
DROP TABLE IF EXISTS users
""")
song_table_drop = ("""
DROP TABLE IF EXISTS song
""")
artist_table_drop = ("""
DROP TABLE IF EXISTS artist
""")
time_table_drop = ("""
DROP TABLE IF EXISTS time
""")
# CREATE TABLES
"""Creating intermediate events/logs table to perform ETL"""
staging_events_table_create= ("""
CREATE TABLE IF NOT EXISTS staging_events (
event_id bigint IDENTITY(0,1) not null,
artist varchar null,
auth varchar null,
firstName varchar null,
lastName varchar null,
gender varchar null,
itemInSession varchar null,
length decimal null,
level varchar null,
location varchar null,
method varchar null,
page varchar null,
registration decimal null,
sessionId int not null,
song varchar null,
status int null,
ts bigint not null,
userAgent varchar null,
userId varchar null
);
""")
"""Creating intermediate songs table to perform ETL"""
staging_songs_table_create = ("""
CREATE TABLE IF NOT EXISTS staging_songs(
num_songs int not null,
artist_id varchar not null,
artist_latitude decimal null,
artist_longitude decimal null,
artist_location varchar null,
artist_name varchar not null,
song_id varchar not null,
title varchar not null,
duration decimal not null,
year int not null
);
""")
""" User: Dimensional table """
user_table_create = ("""
CREATE TABLE IF NOT EXISTS users (
user_id varchar,
first_name varchar,
last_name varchar,
gender varchar,
level varchar,
PRIMARY KEY(user_id)
);
""")
""" Song: Dimensional table """
song_table_create = ("""
CREATE TABLE IF NOT EXISTS songs (
song_id varchar,
title varchar,
artist_id varchar,
year int,
duration decimal,
PRIMARY KEY(song_id)
)
""")
""" Artist: Dimensional table """
artist_table_create = ("""
CREATE TABLE IF NOT EXISTS artists (
artist_id varchar,
artist_name varchar,
artist_latitude decimal,
artist_longitude decimal,
PRIMARY KEY(artist_id)
)
""")
""" Time: Dimensional table """
time_table_create = ("""
CREATE TABLE IF NOT EXISTS time (
start_time TIMESTAMP,
hour int,
day int,
week_year int,
month int,
year int,
week_day int,
PRIMARY KEY(start_time)
)
""")
"""Songplay: Dimensional table"""
songplay_table_create = ("""
CREATE TABLE IF NOT EXISTS songplay (
songplay_id bigint IDENTITY(0,1) PRIMARY KEY,
start_time TIMESTAMP NOT NULL,
user_id varchar NOT NULL,
level varchar,
song_id varchar,
artist_id varchar,
session_id int NOT NULL,
location text,
user_agent text
);
""")
# STAGING TABLES
"""Copying all the events/logs data to the intermediate table staging_events"""
staging_events_copy = ("""
copy staging_events from '{}'
credentials 'aws_iam_role={}'
json '{}'
compupdate off
region '{}'
timeformat as 'epochmillisecs'
truncatecolumns
blanksasnull
emptyasnull
""").format(
config['S3']['LOG_DATA'],
config['IAM_ROLE']['ARN'],
config['S3']['LOG_JSONPATH'],
config['CLUSTER']['REGION']
)
"""Copying all the songs data to the intermediate table staging_songs"""
staging_songs_copy = ("""
copy staging_songs from '{}'
credentials 'aws_iam_role={}'
format as json 'auto'
compupdate off
region '{}';
""").format(
config['S3']['SONG_DATA'],
config['IAM_ROLE']['ARN'],
config['CLUSTER']['REGION']
)
# FINAL TABLES
"""Insert data from staging_events and staging_songs to the dimensional songplay table"""
songplay_table_insert = ("""
INSERT INTO songplay (
start_time,
user_id,
level,
song_id,
artist_id,
session_id,
location,
user_agent
)
SELECT DISTINCT
TIMESTAMP 'epoch' + sevent.ts/1000 * INTERVAL '1 second' as start_time,
sevent.userId as user_id,
sevent.level,
ssongs.song_id,
ssongs.artist_id,
sevent.sessionid as session_id,
sevent.location,
sevent.userAgent as user_agent
FROM staging_events sevent
JOIN staging_songs ssongs ON sevent.artist = ssongs.artist_name AND sevent.song = ssongs.title
WHERE sevent.page = 'NextSong'
""")
"""Insert data from staging_events to the fact user table"""
user_table_insert = ("""
INSERT INTO users (
user_id,
first_name,
last_name,
gender,
level
)
SELECT DISTINCT
sevent.userId as user_id,
sevent.firstName as first_name,
sevent.lastName as last_name,
sevent.gender,
sevent.level
FROM staging_events sevent
WHERE sevent.page = 'NextSong'
""")
"""Insert data from staging_songs to the fact song table"""
song_table_insert = ("""
INSERT INTO songs (
song_id,
title,
artist_id,
year,
duration
)
SELECT DISTINCT
ssongs.song_id,
ssongs.title,
ssongs.artist_id,
ssongs.year,
ssongs.duration
FROM staging_songs ssongs
""")
"""Insert data from staging_songs to the fact artist table"""
artist_table_insert = ("""
INSERT INTO artists (
artist_id,
artist_name,
artist_latitude,
artist_longitude
)
SELECT DISTINCT
ssongs.artist_id,
ssongs.artist_name,
ssongs.artist_latitude,
ssongs.artist_longitude
FROM staging_songs ssongs
""")
"""Insert data from staging_events to the fact time table"""
time_table_insert = ("""
INSERT INTO time (
start_time,
hour,
day,
week_year,
month,
year,
week_day
)
SELECT DISTINCT
TIMESTAMP 'epoch' + sevent.ts/1000 * INTERVAL '1 second' as start_time,
EXTRACT(HOUR FROM start_time) as hour,
EXTRACT(DAY FROM start_time) as day,
EXTRACT(WEEK FROM start_time) as week,
EXTRACT(MONTH FROM start_time) as month,
EXTRACT(YEAR FROM start_time) as year,
EXTRACT(DOW FROM start_time) as week_day
FROM staging_events sevent;
""")
# QUERY LISTS
create_table_queries = [
staging_events_table_create, staging_songs_table_create,
songplay_table_create, user_table_create, song_table_create,
artist_table_create, time_table_create
]
drop_table_queries = [
staging_events_table_drop, staging_songs_table_drop, songplay_table_drop,
user_table_drop, song_table_drop, artist_table_drop, time_table_drop
]
copy_table_queries = [staging_events_copy, staging_songs_copy]
insert_table_queries = [
songplay_table_insert, user_table_insert, song_table_insert,
artist_table_insert, time_table_insert
]