Repository navigation
Expand file tree
/
Copy pathinit-postgres.sql
More file actions
221 lines (198 loc) · 7.74 KB
/
Copy pathinit-postgres.sql
File metadata and controls
221 lines (198 loc) · 7.74 KB
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
-- Bootstraps the analytics database on first PostgreSQL start.
-- Creates the superset role/database, the star-schema tables, seeds the
-- static dimensions (dates, times, languages) and builds the analytics
-- materialized views. This replaces the previous Alembic migrations.
DO
$$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_roles
WHERE rolname = 'superset'
) THEN
CREATE ROLE superset LOGIN PASSWORD 'superset';
END IF;
END
$$;
SELECT 'CREATE DATABASE superset OWNER superset'
WHERE NOT EXISTS (
SELECT 1
FROM pg_database
WHERE datname = 'superset'
)\gexec
\connect superset
CREATE SCHEMA IF NOT EXISTS twitch_analytics AUTHORIZATION superset;
GRANT ALL ON SCHEMA public TO superset;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO superset;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO superset;
-- ---------------------------------------------------------------------------
-- Dimension and fact tables (star schema)
-- ---------------------------------------------------------------------------
CREATE TABLE dim_games (
game_id SERIAL PRIMARY KEY,
twitch_game_id VARCHAR(50) UNIQUE NOT NULL,
game_name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now()
);
CREATE TABLE dim_streamers (
streamer_id SERIAL PRIMARY KEY,
twitch_user_id VARCHAR(50) UNIQUE NOT NULL,
username VARCHAR(100) NOT NULL,
display_name VARCHAR(100),
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now()
);
CREATE TABLE dim_languages (
language_id SERIAL PRIMARY KEY,
language_code VARCHAR(10) UNIQUE NOT NULL,
language_name VARCHAR(50) NOT NULL
);
CREATE TABLE dim_dates (
date_id SERIAL PRIMARY KEY,
date_actual DATE UNIQUE NOT NULL,
day_of_week INTEGER NOT NULL,
day_name VARCHAR(10) NOT NULL,
day_of_month INTEGER NOT NULL,
day_of_year INTEGER NOT NULL,
week_of_year INTEGER NOT NULL,
month INTEGER NOT NULL,
month_name VARCHAR(10) NOT NULL,
quarter INTEGER NOT NULL,
year INTEGER NOT NULL,
is_weekend BOOLEAN NOT NULL
);
CREATE TABLE dim_times (
time_id SERIAL PRIMARY KEY,
hour INTEGER NOT NULL,
minute INTEGER NOT NULL,
time_of_day VARCHAR(20) NOT NULL,
is_prime_time BOOLEAN NOT NULL
);
CREATE TABLE fact_stream_observations (
observation_id SERIAL PRIMARY KEY,
stream_id VARCHAR(50) NOT NULL,
game_id INTEGER NOT NULL REFERENCES dim_games(game_id),
streamer_id INTEGER NOT NULL REFERENCES dim_streamers(streamer_id),
date_id INTEGER NOT NULL REFERENCES dim_dates(date_id),
time_id INTEGER NOT NULL REFERENCES dim_times(time_id),
language_id INTEGER NOT NULL REFERENCES dim_languages(language_id),
viewer_count INTEGER NOT NULL,
is_mature BOOLEAN NOT NULL DEFAULT false,
duration_minutes INTEGER NOT NULL DEFAULT 15,
collection_timestamp TIMESTAMP NOT NULL
);
CREATE INDEX ix_fact_collection_timestamp ON fact_stream_observations (collection_timestamp);
CREATE INDEX ix_fact_date_id ON fact_stream_observations (date_id);
CREATE INDEX ix_fact_game_id ON fact_stream_observations (game_id);
CREATE INDEX ix_fact_streamer_id ON fact_stream_observations (streamer_id);
-- ---------------------------------------------------------------------------
-- Seed the static dimensions
-- ---------------------------------------------------------------------------
INSERT INTO dim_languages (language_code, language_name) VALUES
('en', 'English'), ('es', 'Spanish'), ('pt', 'Portuguese'), ('de', 'German'),
('fr', 'French'), ('it', 'Italian'), ('ru', 'Russian'), ('ja', 'Japanese'),
('ko', 'Korean'), ('zh', 'Chinese'), ('ar', 'Arabic'), ('tr', 'Turkish'),
('pl', 'Polish'), ('sv', 'Swedish'), ('da', 'Danish'), ('no', 'Norwegian'),
('fi', 'Finnish'), ('nl', 'Dutch'), ('cs', 'Czech'), ('other', 'Other');
INSERT INTO dim_dates (
date_actual, day_of_week, day_name, day_of_month, day_of_year,
week_of_year, month, month_name, quarter, year, is_weekend
)
SELECT
d::date,
EXTRACT(ISODOW FROM d)::int,
TRIM(TO_CHAR(d, 'Day')),
EXTRACT(DAY FROM d)::int,
EXTRACT(DOY FROM d)::int,
EXTRACT(WEEK FROM d)::int,
EXTRACT(MONTH FROM d)::int,
TRIM(TO_CHAR(d, 'Month')),
EXTRACT(QUARTER FROM d)::int,
EXTRACT(YEAR FROM d)::int,
EXTRACT(ISODOW FROM d) IN (6, 7)
FROM generate_series('2020-01-01'::date, '2027-12-31'::date, '1 day') AS d;
INSERT INTO dim_times (hour, minute, time_of_day, is_prime_time)
SELECT
h,
m,
CASE
WHEN h BETWEEN 6 AND 11 THEN 'Morning'
WHEN h BETWEEN 12 AND 17 THEN 'Afternoon'
WHEN h BETWEEN 18 AND 23 THEN 'Evening'
ELSE 'Night'
END,
(h BETWEEN 18 AND 23)
FROM generate_series(0, 23) AS h
CROSS JOIN generate_series(0, 45, 15) AS m;
-- ---------------------------------------------------------------------------
-- Analytics materialized views (refreshed by the processor DAG)
-- ---------------------------------------------------------------------------
CREATE MATERIALIZED VIEW mv_top_games_analytics AS
SELECT
g.game_name,
COUNT(DISTINCT f.stream_id) AS total_streams,
AVG(f.viewer_count)::INTEGER AS avg_viewers,
MAX(f.viewer_count) AS peak_viewers,
COUNT(DISTINCT f.streamer_id) AS unique_streamers
FROM fact_stream_observations f
JOIN dim_games g ON f.game_id = g.game_id
GROUP BY g.game_id, g.game_name
ORDER BY avg_viewers DESC;
CREATE MATERIALIZED VIEW mv_top_streamers_analytics AS
SELECT
s.username,
s.display_name,
COUNT(DISTINCT f.stream_id) AS total_streams,
AVG(f.viewer_count)::INTEGER AS avg_viewers,
MAX(f.viewer_count) AS peak_viewers,
COUNT(DISTINCT f.game_id) AS games_played
FROM fact_stream_observations f
JOIN dim_streamers s ON f.streamer_id = s.streamer_id
GROUP BY s.streamer_id, s.username, s.display_name
ORDER BY avg_viewers DESC;
CREATE MATERIALIZED VIEW mv_language_analytics AS
SELECT
l.language_name,
l.language_code,
COUNT(DISTINCT f.stream_id) AS total_streams,
AVG(f.viewer_count)::INTEGER AS avg_viewers,
MAX(f.viewer_count) AS peak_viewers,
COUNT(DISTINCT f.streamer_id) AS unique_streamers
FROM fact_stream_observations f
JOIN dim_languages l ON f.language_id = l.language_id
GROUP BY l.language_id, l.language_name, l.language_code
ORDER BY avg_viewers DESC;
CREATE MATERIALIZED VIEW mv_time_analytics AS
SELECT
d.day_name,
t.time_of_day,
t.is_prime_time,
d.is_weekend,
COUNT(DISTINCT f.stream_id) AS total_streams,
AVG(f.viewer_count)::INTEGER AS avg_viewers,
MAX(f.viewer_count) AS peak_viewers,
COUNT(DISTINCT f.streamer_id) AS unique_streamers
FROM fact_stream_observations f
JOIN dim_dates d ON f.date_id = d.date_id
JOIN dim_times t ON f.time_id = t.time_id
GROUP BY d.day_name, t.time_of_day, t.is_prime_time, d.is_weekend
ORDER BY d.day_name, t.time_of_day;
CREATE MATERIALIZED VIEW mv_daily_summary AS
SELECT
d.date_actual,
d.day_name,
d.is_weekend,
COUNT(DISTINCT f.stream_id) AS total_streams,
COUNT(DISTINCT f.streamer_id) AS unique_streamers,
COUNT(DISTINCT f.game_id) AS unique_games,
AVG(f.viewer_count)::INTEGER AS avg_viewers,
MAX(f.viewer_count) AS peak_viewers,
SUM(f.viewer_count) AS total_viewers
FROM fact_stream_observations f
JOIN dim_dates d ON f.date_id = d.date_id
GROUP BY d.date_id, d.date_actual, d.day_name, d.is_weekend
ORDER BY d.date_actual DESC;
-- Grant access to everything just created (tables, sequences, views).
GRANT ALL ON ALL TABLES IN SCHEMA public TO superset;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO superset;