<jupyter_start><jupyter_text>IntroductionThis notebook tests selected queries from `sql_queries.py` to:1. Debug and tune the queries1. Justify the chouce of distribution style and keys for the DWH tablesThe code below caters for an active RedShift endpoint and IAM role for S3 access.> Run `dwh_iac.ipynb` first to create the RedShift cluster and setup access!<jupyter_code>dwh_cfg = configparser.ConfigParser()
dwh_cfg.read('dwh.cfg')

conn_values = (dwh_cfg.get('DB','DB_USER'),
               dwh_cfg.get('DB','DB_PASSWORD'),
               dwh_cfg.get('DB','DB_ENDPOINT'),
               dwh_cfg.get('DB','DB_PORT'),
               dwh_cfg.get('DB','DB_NAME'))

con = "postgresql://{}:{}@{}:{}/{}".format(*conn_values)
%sql $con<jupyter_output><empty_output><jupyter_text>Drop all DWH tables if you like to start from scratch. `$con` is [supperssing the connection string](https://stackoverflow.com/q/53818698) from output.<jupyter_code>for query in sq.drop_table_queries:
    %sql $con $query<jupyter_output><empty_output><jupyter_text>Create and populate the staging tables<jupyter_code>%sql $con $sq.staging_events_table_create
%sql $con $sq.staging_songs_table_create
%%time
%sql $con $sq.staging_events_copy
%%time
%sql $con $sq.staging_songs_copy<jupyter_output>Done.
CPU times: user 9.99 ms, sys: 369 µs, total: 10.4 ms
Wall time: 2min 45s<jupyter_text>With 2 nodes:<jupyter_code>CPU times: user 6.66 ms, sys: 3.7 ms, total: 10.4 ms
Wall time: 5min 43s<jupyter_output><empty_output><jupyter_text>Investigate the staging tablesHow many fact records can we potentially have in table `songplays`?<jupyter_code>%%sql $con 
SELECT COUNT (*) FROM (SELECT DISTINCT ts, userId FROM staging_events
WHERE   page    = 'NextSong'
    AND ts     is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL)<jupyter_output>1 rows affected.<jupyter_text>Are there `songplays` for which no song can be matched from `staging_songs` by the artist name and song title?<jupyter_code>%%sql $con 
SELECT COUNT (*) FROM (SELECT DISTINCT ts, userId FROM staging_events AS event
WHERE   page    = 'NextSong'
    AND ts     is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL
    AND NOT EXISTS (SELECT DISTINCT song_id FROM staging_songs AS song
                    WHERE event.song   = song.title
                      AND event.artist = song.artist_name
                   ))<jupyter_output>1 rows affected.<jupyter_text>How many potential records in `time` dimension? The number of actual records will not exceed the number of facts in `songplays`.<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT ts FROM staging_events
WHERE   page    = 'NextSong'
    AND ts     is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL)<jupyter_output>1 rows affected.<jupyter_text>So are there multiple `NextSong` events at the same time?<jupyter_code>%%sql $con
SELECT ts, COUNT(userId) as users FROM staging_events
WHERE   page    = 'NextSong'
    AND ts     is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL
GROUP BY ts HAVING users > 1<jupyter_output>7 rows affected.<jupyter_text>How many users are there in `users` dimension?<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT userId,  firstName,  lastName,  gender, level FROM staging_events
WHERE   userId is NOT NULL
    AND level  is NOT NULL)<jupyter_output>1 rows affected.<jupyter_text>For how many users at least one song can be matched from `staging_songs` by the artist name and song title??<jupyter_code>%%sql $con 
SELECT COUNT (*) FROM (SELECT DISTINCT userId, firstName,  lastName,  gender, level FROM staging_events AS event
WHERE   page    = 'NextSong'
    AND ts     is NOT NULL
    AND userId is NOT NULL
    AND level  is NOT NULL
    AND EXISTS (SELECT DISTINCT song_id FROM staging_songs AS song
                WHERE event.song   = song.title
                    AND event.artist = song.artist_name
               ))<jupyter_output>1 rows affected.<jupyter_text>How many songs are there in `songs` dimension?<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT song_id FROM staging_songs
WHERE song_id is NOT NULL)<jupyter_output>1 rows affected.<jupyter_text>How many of these songs will be mentioned in `songplays`?<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT song_id FROM staging_songs as song
WHERE song_id is NOT NULL
    AND EXISTS (SELECT DISTINCT ts FROM staging_events AS event
                WHERE   event.song   = song.title
                    AND event.artist = song.artist_name
                    AND event.page   = 'NextSong'
               ))<jupyter_output>1 rows affected.<jupyter_text>How many artists are there in `artists` dimension?<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT artist_id, artist_name FROM staging_songs
WHERE artist_id is NOT NULL
  AND song_id   is NOT NULL)<jupyter_output>1 rows affected.<jupyter_text>How many of these artists will be mentioned in `songplays`?<jupyter_code>%%sql $con
SELECT COUNT (*) FROM (SELECT DISTINCT artist_id FROM staging_songs as song
WHERE artist_id is NOT NULL
    AND EXISTS (SELECT DISTINCT ts FROM staging_events AS event
                WHERE   event.song   = song.title
                    AND event.artist = song.artist_name
                    AND event.page   = 'NextSong'
               ))<jupyter_output>1 rows affected.<jupyter_text>Choose the distribution styleBased on the above counts we can choose the distribution style for fact and dimensions as:- `songplays` and `time`: KEY with `start_time` as the distribution and the sort key- `users`: ALL, sort key `user_id`- `songs`: ALL, sort key `song_id`- `artists`: ALL, sort key `artist_id`  Create the star schema<jupyter_code>%sql $con $sq.songplay_table_create
%sql $con $sq.user_table_create
%sql $con $sq.song_table_create
%sql $con $sq.artist_table_create
%sql $con $sq.time_table_create<jupyter_output>Done.
Done.
Done.
Done.
Done.<jupyter_text>Populate the star schema<jupyter_code>%%time
%sql $con $sq.songplay_table_insert<jupyter_output>333 rows affected.
CPU times: user 4.78 ms, sys: 10.8 ms, total: 15.6 ms
Wall time: 2.38 s<jupyter_text>The number of actual songplays is below its potential because not all songs were matched by the song title and the artist name.<jupyter_code>%%time
%sql $con $sq.user_table_insert
%%time
%sql $con $sq.song_table_insert
%%time
%sql $con $sq.artist_table_insert
%%time
%sql $con $sq.time_table_insert<jupyter_output>333 rows affected.
CPU times: user 13.8 ms, sys: 0 ns, total: 13.8 ms
Wall time: 2.2 s<jupyter_text>Test running the DWH ETL scripts`create_tables.py` then `etl.py`<jupyter_code>%%time
import create_tables
create_tables.main()
%%sql $con SELECT
(SELECT COUNT(*) FROM staging_events) as staging_events,
(SELECT COUNT(*) FROM staging_songs) as staging_songs,
(SELECT COUNT(*) FROM songplays) as songplays,
(SELECT COUNT(*) FROM time) as time,
(SELECT COUNT(*) FROM users) as users,
(SELECT COUNT(*) FROM songs) as songs,
(SELECT COUNT(*) FROM artists) as artists
%%time
import etl
etl.main()
%%sql $con SELECT
(SELECT COUNT(*) FROM staging_events) as staging_events,
(SELECT COUNT(*) FROM staging_songs) as staging_songs,
(SELECT COUNT(*) FROM songplays) as songplays,
(SELECT COUNT(*) FROM time) as time,
(SELECT COUNT(*) FROM users) as users,
(SELECT COUNT(*) FROM songs) as songs,
(SELECT COUNT(*) FROM artists) as artists<jupyter_output>1 rows affected.
{'fraction_non_alphanumeric': 0.09057873485868102, 'fraction_numerical': 0.009959623149394347, 'mean_word_length': 4.153259361997226, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 60, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '203058', 'license': 'MIT', 'n_tokens_mistral': 2555, 'n_tokens_neox': 2389, 'n_words': 834, 'path': 'dwh_test.ipynb', 'repo_name': 'adzugaiev/Udacity-Data-Engineering-3', 'size': '7430'}